DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: create user

  1. #1
    Join Date
    Jan 2001
    Posts
    216
    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

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    Do it the 'execute immediate' way, except get rid of your first statement with the SELECT in it.


  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Dec 2000
    Posts
    138
    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

  5. #5
    Join Date
    Jan 2001
    Posts
    216
    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

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Jan 2001
    Posts
    216
    BIG BIG THANKS !!

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

    Thanks a lot

    Neelima

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width