Forum Stats

  • 3,770,020 Users
  • 2,253,049 Discussions
  • 7,875,269 Comments

Discussions

How to fire a schema trigger from outside the schema

Criccard-Oracle
Criccard-Oracle Member Posts: 16
edited Sep 26, 2012 2:07PM in General Database Discussions
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?
Tagged:

Answers

  • EdStevens
    EdStevens Member Posts: 28,532 Gold Crown
    criccard wrote:
    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?
    Triggers fire purely based on event. Context doesn't come into play for that.
    However, what the trigger actually does is whatever you code it to do, and at that point you could examine the context and decide how to proceed.
  • Mark Malakanov (user11181920)
    Mark Malakanov (user11181920) Member Posts: 1,389 Silver Badge
    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.
    consider either of:
    a) Grant privileges directly by the user.
    b) Run a job created by SYSADM that finds new objects and grants privileges.
  • >
    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.
    >
    The user CANNOT create objects in any schema without the proper privileges.

    Setting the current_schema parameter does not confer ANY additional privileges to a user. See ALTER SESSION in the SQL language doc
    http://docs.oracle.com/cd/E14072_01/server.112/e10592/statements_2013.htm
    >
    CURRENT_SCHEMA
    Syntax:

    CURRENT_SCHEMA = schema
    The CURRENT_SCHEMA parameter changes the current schema of the session to the specified schema. Subsequent unqualified references to schema objects during the session will resolve to objects in the specified schema. The setting persists for the duration of the session or until you issue another ALTER SESSION SET CURRENT_SCHEMA statement.

    This setting offers a convenient way to perform operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name. This setting changes the current schema, but it does not change the session user or the current user, nor does it give the session user any additional system or object privileges for the session.
    >
    If the user connects and sets SYSADM as the current schema and creates objects in SYSADM the user already had privileges to create objects in the SYSADM schema; setting the current schema has nothing to do with it.

    So your issue is that the user has privileges to create objects in the SYSADM schema; you need to revoke those privilges (or the role that grants them) to solve your problem.
  • What appears to be happening is that the event isn't noticed unless you are the owner of the schema, since it is a trigger applied to the schema (as opposed to a table). I ran the same test but using a table with a trigger and that worked fine -- an insert on the table fired the trigger. The same doesn't seem to apply to schema triggers. I could very well be wrong, though.
  • "b) Run a job created by SYSADM that finds new objects and grants privileges."

    I think that's the best suggestion, and it would seem relatively easy to retrofit the existing trigger.
  • "If the user connects and sets SYSADM as the current schema and creates objects in SYSADM the user already had privileges to create objects in the SYSADM schema; setting the current schema has nothing to do with it."

    That's an excellent observation and I don't know why I didn't put 2 and 2 together. Yes, the non SYSADM does have privileges on the schema despite not being able to log on as that user. So again, I think the workaround will have to be creating a procedure that performs the grants and allow the "outside" user to execute it.
This discussion has been closed.