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.
- Application creates User1.RandomTable
- User2 needs select, update, delete, insert on User1.RandomTable after table creation
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;