Why After Create on DB Trigger does not work for more than 1 schema?
Have multiple schemas with several users having the capability of creating tables in the multiple schemas. Management does not want to use proxy authentication to cut down on the Create ANY table system privilege from being granted to several users.
I am attempting to use an after create on database trigger to dynamically grant select access to the newly created tables to several database roles.
I have created the trigger and the PL/SQL procedure it invokes to execute the GRANT statements under schema1.
create or replace trigger schema1.db_trig_3
after create on database
DECLARE
l_jobno NUMBER;
BEGIN