Invoke GRANT in Trigger?

[stoggers]

Hi,

Is it possible to invoke the GRANT statement in a trigger?

I have a packaged application that inserts rows in a 'user' table - thereby creating application users.

What I would also like this to do is create Oracle users & grant appropriate permissions.

Not being able to alter the application directly means that the use of a trigger is the only way that I can execute the statements.

 

[carp]

I haven't tried it, but if you're using 8i you should be able to build the command within the trigger and then do an "Execute Immediate" call, passing in the command.
Something like:
.
.
.
v_command := 'GRANT EXECUTE ON my_procedure TO '||:new.user;
EXECUTE IMMEDIATE v_command;
.
.
.

 

[jquaid]

You can also use the package DBMS_SESSION for running these statements from within PL/SQL:

-- This package provides access to SQL "alter session" statements, and
-- other session information from, stored procedures.

 

[sem]

Execute immediate will not work due to explicit commit issued by DDL statement.

You have to create some stored procedure with PRAGMA AUTONOMOUS_TRANSACTION and place your DDL code called via execute immediate or dbms_sql to it and then call it from trigger.