create user

[chikkodi]

hI,

I want to create a user in a trigger. I use the following code:

CREATE OR REPLACE TRIGGER add_user
BEFORE INSERT ON ADMININFO
FOR EACH ROW
DECLARE
v_roleName VARCHAR2 (100);
v_cursor number;
v_cmd varchar2(100);
v_result integer;
BEGIN
--execute immediate 'select username from admininfo';
--execute immediate 'create user ' || :new.username || ' identified by ' || :new.passwd;
--execute immediate 'grant dba to ' || :new.username;

v_cursor := dbms_sql.open_cursor;
v_cmd := 'create user ' || :new.username || ' identified by ' || :new.passwd;
dbms_sql.parse(v_cursor, v_cmd, DBMS_SQL.NATIVE);
v_result := dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
END;
/

As can be seen, I have tried both options of execute immediate as well as dbms_sql. For execute immediate, I get the error message 'Invalid option' and for dbms_sql I get the error message 'cannot commit inside trigger' These are runtime errors, the trigger compiles fine

I dont know how else to do this.

Please help
Neelima

[jdorlon]

Do it the 'execute immediate' way, except get rid of your first statement with the SELECT in it.

[jmodic]

Creating users and granting privileges are data definition language (DDL) commands, which perform implicit commits. As you might know, using COMMIT or ROLLBACK is not allowed in triggers. To overcome this restriction you might want to use autonomous transactions (new in 8i). Check the Oracle documentation or some newer PL/SQL book for the details about the use of utonomous transactions.

HTH,

[dharma]

put all these stuff in a packaged procedure or a procedure with a Pragma autonomous_transaction and call the procedure in ur trigger. this works and for further ref see autonomous transactions
HTH
Dharma

[chikkodi]

Thanks a lot for your inputs. Its working great with the autonomous transactions, however, its giving an error "insufficient privileges'. The user executing this trigger has dba role. Why do you think this might be happenign ?

Thanks again
Neelima

[jmodic]

Roles are invalidated inside PL/SQL. The owner of the procedure or trigger has to have appropriate privileges granted explicitely, not through roles. In your case,

GRANT CREATE USER TO owner_of_the_trigger;
and
GRANT GRANT ANY ROLE TO owner_of_the_trigger;

HTH,

[chikkodi]

BIG BIG THANKS !!

Everything works great now.
I am so happy you guys exist and this forum exists !!!!

Thanks a lot

Neelima