DDL Trigger which grants permission to new created users

Question

I got a question, is there a possibilty, that a DDL Trigger can grant roles right after the user is created.

For example:

CREATE OR REPLACE TRIGGER DDL_TRIGGER
AFTER CREATE ROLE ON DATABASE

And after that, the trigger should grant the new user with some roles.

For example:

BEGIN
GRANT Resourse to *new created user*;
GRANT CONNECT to *new created user*;

I use oracle database.

Thank you guys, Marki

check this answer http://stackoverflow.com/questions/20734309/oracle-is-it-possible-to-create-a-role-in-a-trigger — Moudiz, Mar 16 '17 at 13:48
Thank you Moudiz Unfortunately in my situation the role is already created. The trigger should only add certain roles to the user, which was just created. — , Mar 16 '17 at 14:16

score 1 · Accepted Answer · edited May 23 '17 at 11:54

I think it should be this one:

CREATE OR REPLACE TRIGGER T_CREATE_USER 
    AFTER CREATE ON DATABASE
    WHEN (ora_dict_obj_type = 'USER')
BEGIN

    EXECUTE IMMEDIATE 'GRANT RESOURCE TO '||ora_dict_obj_name;
    EXECUTE IMMEDIATE 'GRANT CONNECT TO '||ora_dict_obj_name;

END;
/

Check Using Triggers for further details.

Please check also this post: How do I create a Oracle trigger that grants permissions - perhaps my solution does not work.

DDL Trigger which grants permission to new created users

1 Answers1

Linked