Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a application that creates tables in one user's schema and another user needs to be able to use the dynamically created tables.

Example:

  • Application creates User1.RandomTable
  • User2 needs select, update, delete, insert on User1.RandomTable after table creation

I'm trying this out: http://stackoverflow.com/questions/9262539/how-do-i-create-a-oracle-trigger-that-grants-permissions

But have an issue with DBMS_JOB.submit

Error(7,5): PLS-00201: identifier 'DBMS_JOB' must be declared

I ran this connected as User1 and got the above error. I ran it as SYS and the procedure and trigger created but didn't work when I created a table in User1's schema. No grants existed User1.RandomTable after creation.

CREATE OR REPLACE TRIGGER GRANT_MYROLE_TRIG 
AFTER CREATE ON USER1.SCHEMA
DECLARE
  l_jobno PLS_INTEGER;
BEGIN
  if ora_sysevent = 'CREATE' and ora_dict_obj_type = 'TABLE' then
    DBMS_JOB.submit( l_jobno,
                   'BEGIN GRANT_MYROLE_PROC( ''' || ora_dict_obj_name || ''' ); END;',
                   sysdate + interval '10' second );
  end if;
END;

CREATE OR REPLACE PROCEDURE GRANT_MYROLE_PROC ( p_table_name IN VARCHAR2 ) 
AS 
BEGIN
  EXECUTE IMMEDIATE 'grant select, insert, update, delete on ' || p_table_name || ' to MY_USER_ROLE';
END;
share|improve this question
    
Try WITH GRANT option in your trigger grant query. –  atenz Jul 16 at 14:41

2 Answers 2

up vote 1 down vote accepted

The error you're getting indicates that the User1 user does not have access to the dbms_job package. You'd need a DBA to

GRANT EXECUTE ON dbms_job
   TO user1;

If you create the trigger and procedure to be owned by a user other than the one that owns the table, you'd need to include the schema name in your GRANT statement inside the procedure.

EXECUTE IMMEDIATE 
  'grant select, insert, update, delete on user1.' || p_table_name || 
  '   to MY_USER_ROLE';

Additionally, you'll want to make sure that your job_queue_processes initialization parameter is set to something greater than 0 to ensure that jobs will actually run.

share|improve this answer
    
Yes, GRANT EXECUTE ON dbms_job TO user1; does the trick thanks! –  Andy Arismendi Jul 17 at 1:18

For one thing DBMS_JOB.submit requires COMMIT and you lack that.

Another thing is that DBMS_JOB is ugly and long deprecated. Use another guide/howto, one written in this millennium.x

And you don't need WITH GRANT.

Retaining this answer only to keep informative comments.

share|improve this answer
    
dbms_job is certainly not as powerful as dbms_scheduler in general. I don't know of a way, however, to use dbms_scheduler in this case because submitting a dbms_scheduler job causes a commit and you can't have a commit in a trigger. You wouldn't want the commit in the trigger because you don't want the job to be executed until just after the transaction that submitted it closed. –  Justin Cave Jul 16 at 18:35
    
dbms_job participates quite happily in the underlying transaction. If, somehow, the DDL transaction failed (I can't think of a way that it would fail after the trigger was fired but let's assume that it would) then the dbms_job.submit would be rolled back as well. A dbms_scheduler job would not be rolled back which is why you cannot in general use dbms_scheduler in triggers. –  Justin Cave Jul 16 at 19:15
    
If DDL fails, the trigger is not executed. If the DDL succeeds, it is implicitly committed and then the trigger fires in the next transaction. –  kubanczyk Jul 16 at 19:18
    
A DDL statement implicitly commits before the DDL statement starts. The DDL statement runs. As part of that transaction, the trigger fires. Once all the triggers fire, the DDL statement commits (and is rolled back if the trigger were to throw an exception). The trigger is part of the transaction in which the actual DDL is run. That's why you cannot do DDL (or use dbms_scheduler) from within the trigger. –  Justin Cave Jul 16 at 19:24
    
I trust you based on your reputation, so I retract my answer/comments. –  kubanczyk Jul 16 at 21:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.