We're building a data warehouse, 11.2.0.4 database, where our ETL user will need access to any and all tables created within the staging schema and others. Tables will be added to these schemas quite frequently from a handful of users as this project grows, so we need to grant basic DML privileges after each table is created. I've attempted to do this via a few trigger and procedure combinations. The goal is to store the procedures and triggers within a higher privileged, DBA owned schema and not allow the individual schemas to own the trigger or procedure. There are 0 errors on compilation or table creation. There are also 0 errors within the database alert log.
However, the privileges are not being updated. I'm currently using a testing schema to create a test table to test the privilege updates for the ETL user.
I've gone through multiple posts within this Community (you'll see similarities in the scripts below) and official Oracle documentation to no avail.
I'd appreciate any insight you could provide.
---- 1st Attempt --- Procedure/Trigger combo ---
create or replace PROCEDURE grant_priv_to_simetl( full_table_name IN VARCHAR2 )
AS
BEGIN
EXECUTE IMMEDIATE 'GRANT SELECT, DELETE, INSERT, UPDATE ON '||full_table_name||' TO SIMETL';
COMMIT;
END;
/
create or replace TRIGGER odi_staging_grant_on_creation
AFTER CREATE ON ODI_STAGING.SCHEMA
DECLARE
l_jobno PLS_INTEGER;
BEGIN
dbms_job.submit( l_jobno,'grant_priv_to_simetl('''||ora_dict_obj_owner||'.'||ora_dict_obj_name||''' ); END;',
sysdate + interval '10' second );
END;
/
---------- 2nd Attempt ------- Procedure/Trigger combo ---
create or replace PROCEDURE execute_grant_ddl (p_ddl IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_ddl;
COMMIT;
END execute_grant_ddl;
/
create or replace TRIGGER odiStagingGrantOnCreate_SIMETL
AFTER CREATE ON ODI_STAGING.SCHEMA
DECLARE
l_jobno PLS_INTEGER;
BEGIN
IF ora_dict_obj_type = 'TABLE' THEN
dbms_job.submit(l_jobno,'execute_grant_ddl("GRANT SELECT, INSERT, UPDATE, DELETE ON ODI_STAGING.'||ora_dict_obj_name||' TO SIMETL");');
END IF;
END odiStagingGrantOnCreate_SIMETL;
---------- 3rd Attempt ------- Procedure/Trigger combo ---
create or replace PROCEDURE execute_ODIgrant_ddl (p_ddl IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_ddl;
COMMIT;
END execute_ODIgrant_ddl;
create or replace TRIGGER ODI_STAGINGgrantRights_SIMETL
AFTER CREATE
ON ODI_STAGING.SCHEMA
DECLARE
l_jobno NUMBER;
BEGIN
IF UPPER(user)='ODI_STAGING' THEN
IF DICTIONARY_OBJ_TYPE = 'TABLE' THEN
dbms_job.submit(l_jobno,'execute_ODIgrant_ddl(''GRANT SELECT ON ODI_STAGING.'||DICTIONARY_OBJ_NAME||' TO SIMETL'');');
dbms_job.submit(l_jobno,'execute_ODIgrant_ddl(''GRANT INSERT, UPDATE, DELETE ON ODI_STAGING.'||DICTIONARY_OBJ_NAME||' TO SIMETL'');');
ELSIF DICTIONARY_OBJ_TYPE = 'VIEW' THEN
dbms_job.submit(l_jobno,'execute_ODIgrant_ddl(''GRANT SELECT ON ODI_STAGING.'||DICTIONARY_OBJ_NAME||' TO SIMETL'');');
END IF;
END IF;
END ODI_STAGINGgrantRights_SIMETL;
---------- 4th Attempt ------- Schema Trigger ---
create or replace TRIGGER SIMETL_trigger_grant_dml
AFTER CREATE ON SCHEMA
DECLARE
v_job number;
v_todo varchar2(800);
BEGIN
IF ora_sysevent ='CREATE' and ora_dict_obj_type='TABLE' and ora_dict_obj_owner IN ('ODI_STAGING', 'SIMCRM', 'SIMPET', 'SIMPOUL', 'SIMSFI') THEN
v_todo:='EXECUTE IMMEDIATE "GRANT SELECT, INSERT, UPDATE, DELETE ON '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' TO SIMETL";';
dbms_job.submit(job=>v_job, what=>v_todo);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
---------- 5th Attempt ------- Database Trigger ---
CREATE OR REPLACE TRIGGER SIMETL_ODI_STG_grant_dml
AFTER CREATE ON DATABASE
DECLARE
v_job number;
v_todo varchar2(800);
BEGIN
IF ora_sysevent ='CREATE' and ora_dict_obj_type='TABLE' and ora_dict_obj_owner='ODI_STAGING' THEN
v_todo:='EXECUTE IMMEDIATE "GRANT SELECT, INSERT, UPDATE, DELETE ON ODI_STAGING.'||ora_dict_obj_name||' TO SIMETL";';
dbms_job.submit(job=>v_job, what=>v_todo);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/