CODEWITHSUNDEEP
oracle-databasetriggersprivilegessql-grant
mobule
mobule

Reputation: 1

Oracle 'after create' trigger to grant privileges

I have an 'after create on database' trigger to provide select access on newly created tables within specific schemas to different Oracle roles.

If I execute a create table ... as select statement and then query the new table in the same block of code within TOAD or a different UI I encounter an error, but it works if I run the commands separately:

create table schema1.table1 as select * from schema2.table2 where rownum < 2;

select count(*) from schema1.table1;

If I execute them as one block of code I get:

  ORA-01031: insufficient privileges

If I execute them individually, I don't get an error and am able to obtain the correct count.

Sample snippet of AFTER CREATE trigger

 CREATE OR REPLACE TRIGGER TGR_DATABASE_AUDIT AFTER
 CREATE OR DROP OR ALTER ON Database
 DECLARE
    vOS_User              VARCHAR2(30);
    vTerminal             VARCHAR2(30);
    vMachine              VARCHAR2(30);
    vSession_User         VARCHAR2(30);
    vSession_Id           INTEGER;
    l_jobno               NUMBER;

 BEGIN

   SELECT sys_context('USERENV', 'SESSIONID'),
          sys_context('USERENV', 'OS_USER'),
          sys_context('USERENV', 'TERMINAL'),
          sys_context('USERENV', 'HOST'),
          sys_context('USERENV', 'SESSION_USER')
   INTO   vSession_Id,
          vOS_User,
          vTerminal,
          vMachine,
          vSession_User
    FROM Dual;

    insert into schema3.event_table VALUES (vSession_Id, SYSDATE,   
    vSession_User, vOS_User, vMachine, vTerminal, ora_sysevent,  
    ora_dict_obj_type,ora_dict_obj_owner,ora_dict_obj_name);

    IF ora_sysevent = 'CREATE' THEN
       IF (ora_dict_obj_owner = 'SCHEMA1')  THEN
           IF DICTIONARY_OBJ_TYPE = 'TABLE' THEN
              dbms_job.submit(l_jobno,'sys.execute_app_ddl(''GRANT SELECT 
              ON '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' TO 
              Role1,Role2'');');
           END IF;
       END IF;
    END IF;
 END;

Upvotes: 0

Views: 1882

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

Jobs are asynchronous. Your code is not.

Ignoring for the moment the fact that if you're dynamically granting privileges that something in the world is creating new tables live in production without going through a change control process (at which point a human reviewer would ensure that appropriate grants were included) which implies that you have a much bigger problem...

When you run the CREATE TABLE statement, the trigger fires and a job is scheduled to run. That job runs in a separate session and can't start until your CREATE TABLE statement issues its final implicit commit and returns control to the first session. Best case, that job runs a second or two after the CREATE TABLE statement completes. But it could be longer depending on how many background jobs are allowed to run simultaneously, what other jobs are running, how busy Oracle is, etc.

The simplest approach would be to add a dbms_lock.sleep call between the CREATE TABLE and the SELECT that waits a reasonable amount of time to give the background job time to run. That's trivial to code (and useful to validate that this is, in fact, the only problem you have) but it's not foolproof. Even if you put in a delay that's "long enough" for testing, you might encounter a longer delay in the future. The more complicated approach would be to query dba_jobs, look to see if there is a job there related to the table you just created, and sleep if there is in a loop.

Upvotes: 2

Related Questions