CODEWITHSUNDEEP
sqldatabaseoracle-databasetriggers
user7721536
user7721536

Reputation:

DDL Trigger which grants permission to new created users

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

Upvotes: 2

Views: 279

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

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.

Upvotes: 1

Related Questions