Execute a stored procedure through a trigger right after a user was created on the database

Question

I would like to write a procedure can grant role permissions to a new created user.

My thoughts were that I first create a procedure like this:

CREATE OR REPLACE PROCEDURE P_CREATE_USER

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

END;
/

Then, I create a trigger, which execute this procedure, after a user is created on the database. Like this:

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

BEGIN
  P_CREATE_USER;
END;
/

It did not really work, do you have other suggestions?

I use Oracle as DBMS.

This is a solution from [your previous question](http://stackoverflow.com/a/42837302/146325) which you accepted and said "it worked". Now you say "it did not really work". So, it what way does it not work? What additional requiremenst do you have? — APC, Mar 17 '17 at 12:50
@APC yes you are right, i said "it worked", but the additional requirement is, that i need to know, how i can do it with a procedure, which is called by a trigger. — , Mar 17 '17 at 13:35

APC · Answer 1 · 2017-03-17T14:47:24.567

So the problem is this: your trigger throws ORA-30511: invalid DDL operation in system triggers.

The reason is, we cannot commit in triggers. DDL issues implicit commits (before and after the statement). So there is no way your trigger can work, nor could it ever have worked.

The workaround for commits in triggers is pragma AUTONOMOUS TRANSACTION, which causes the trigger to operate in an isolated session. That won't here because the freshly created user won't be visible in the autonomous session.

The best approach you can get to encapsulate the logic would be this:

CREATE OR REPLACE PROCEDURE P_CREATE_USER
   (p_user_name in varchar2
    , p_password in varchar2)
is
BEGIN
    EXECUTE IMMEDIATE ' create user '||p_user_name ||' identified by '||p_password;
    EXECUTE IMMEDIATE 'GRANT RESOURCE TO'||p_user_name ;
    EXECUTE IMMEDIATE 'GRANT CONNECT TO'||p_user_name ;

END;
/

score -2 · Answer 2 · answered Mar 17 '17 at 14:34

-2

In SQL server a user, such as you can execute a procedure to grant SQL permission to a newly created user. The condition to make this work is that the user’s account, such as your account need ‘With Grant’ to that SQL permission in order to be able to grant other new user this SQL permission

answered Mar 17 '17 at 14:34

coco

1

2

Please note, on SO the `[sql]` tage refers to just SQL question, It dooes not mean MSSQL which has the tag `[sql-server]` (or similar). This question is tagged `[oracle]`. So what happens in SQL Server is irrelevant. As it happens Oracle also has WITH GRANT functionality but it has no bearing on the OP's actual problem. There are plenty of question on SO which are about SQL Server: if that's your area of expertise. Good luck. – APC Mar 17 '17 at 14:53
Thank you for the clarification – coco Mar 17 '17 at 18:52

Execute a stored procedure through a trigger right after a user was created on the database

2 Answers2