GRANT ROLE inside a trigger?

Question

is there a way in Oracle to execute the GRANT someRole TO someUser; command inside a trigger?
I tried it, but it does not seem to work.

Thanks a lot.

Answer 1

Most DDL statements are not allowed in triggers because they force a COMMIT to be executed. The few exceptions to this rule are only applicable to system event triggers (startup, shutdown, etc) but it doesn't appear this trigger is a system trigger. Reference here.

Share and enjoy.

Answer 2

It is not recommended to use DDL commands within a trigger since a commit a transaction and you don't have a way to rollback a transaction if something goes wrong. If it is very important and you have to do it, then only you should execute DDL commmands within a trigger. Perhaps you could achieve it using an autonomous transaction.

Here is an example =

CREATE OR REPLACE TRIGGER my_trigger
  AFTER INSERT ON my_table
   DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
     EXECUTE IMMEDIATE 'GRANT someRole TO someUser';
     COMMIT;
   END;

Please let me know if that works for you.

Thanks, Aditya