create user
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: create user

Hybrid View

  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
    Location
    Israel
    Posts
    268
    Hi
    you can use:
    CREATE OR REPLACE TRIGGER ADD_USER BEFORE INSERT ON ADMININFO FOR EACH ROW
    BEGIN
    dbms_utility.exec_ddl_statement('create user ' :new.username || ' identified by ' || :new.passwd);
    dbms_utility.exec_ddl_statement('grant dba to ' || :new.username);
    END ADD_USER;


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