Skip to Main Content

Oracle Database Discussions

How to fire a schema trigger from outside the schema

Criccard-OracleSep 24 2012 — edited Sep 26 2012
A user is using an ad hoc tool similar to SQL Developer called PeopleSoft Application Designer.

He creates a connection to the db, then issues an alter session set current_schema = 'restricted_schema'. The connected user does not have direct privileges on the "restricted_schema" which they call SYSADM.

After changing the schema context in that manner he creates objects in SYSADM. A schema trigger is then fired and grants privileges on the new objects created in SYSADM. Doing the same in either SQL Plus or SQL Developer does not fire the schema trigger.

I think SQL Plus and SQL Dev are working as they should. Altering the session like that does not change your identity - just the schema context. But, when you examine v_$session, the connection with this other tool looks exactly the same as one from SQL Plus or SQL Dev when changing the schema context in the session.

Instead of trying to figure out what this other tool is doing, is there any way for that schema trigger to fire when using this process from one of our tools?
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 24 2012
Added on Sep 24 2012