DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: CREATE USER in a trigger

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    CREATE USER in a trigger

    Can you have a create user command in a database trigger in 8.1.5+ ?

    I want to have the application create an application user in one of its own tables, and then have a DB trigger on the APPLICATION_USERS table fire AFTER INSERT to create the underlying Oracle account.

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: CREATE USER in a trigger

    Originally posted by JMac
    Can you have a create user command in a database trigger in 8.1.5+ ?

    I want to have the application create an application user in one of its own tables, and then have a DB trigger on the APPLICATION_USERS table fire AFTER INSERT to create the underlying Oracle account.
    Yes you can.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    Smile

    Well I'd better try it again then :-)

    My initial attempt failed with... "Warning: Trigger created with compilation errors."

    But no way am I posting my CREATE TRIGGER code here now. I'm too embarassed!!!

    Where are those PL/SQL manuals?

  4. #4
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Originally posted by JMac
    Well I'd better try it again then :-)

    My initial attempt failed with... "Warning: Trigger created with compilation errors."

    But no way am I posting my CREATE TRIGGER code here now. I'm too embarassed!!!

    Where are those PL/SQL manuals?
    Killjoy
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by JMac
    Well I'd better try it again then :-)

    My initial attempt failed with... "Warning: Trigger created with compilation errors."

    But no way am I posting my CREATE TRIGGER code here now. I'm too embarassed!!!

    Where are those PL/SQL manuals?
    OK, post only the part which creates the trigger.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Well ahem! (coughs) ... it's a while since I did this sort of thing ...

    CREATE TABLE table1 (username VARCHAR2(25);

    CREATE OR REPLACE TRIGGER trigger_name
    AFTER INSERT ON table1
    FOR EACH ROW
    BEGIN
    CREATE USER :new.username IDENTIFIED BY :new.username
    DEFAULT TABLESPACE ... etc;
    END;

    Go on, then - throw me to the PL/SQL wolves.

  7. #7
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    You will need to issue the create user statement as something similar to the following:

    Code:
      p_username varchar2(20);
      p_password varchar2(20);
      send_line varchr2(2000);
    begin
      send_line := 'create user '||p_username||
                   ' identified by "'||p_password||'"'||
                   ' default tablespace users temporary tablespace temp';
      execute immediate send_line;
    exception
      ....
    HTH
    Last edited by jovery; 04-02-2003 at 10:24 AM.
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  8. #8
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Hit a problem here...

    If I do it (CREATE USER) in an AFTER INSERT trigger it tries to commit the insert, and I get an error:

    ORA-04092: cannot COMMIT in a trigger
    ORA-06512: at "SYSTEM.TEST_TRIGGER", line 5
    ORA-04088: error during execution of trigger 'SYSTEM.TEST_TRIGGER'

    Creating a user works like DDL and commits any transactions, it seems.
    Looks like I'll have to implememnt this is a stored procedure to be called after the user insert has been committed.

    Suggestions, folks? Or should I direct this to the Development Forum?

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You should use either:

    a) AUTONOMOUS TRANSACTION pragma in your trigger - not shure in which release if 8i it was introduced.
    b) submit a DBMS_JOB from within your trigger and you take care of user cration process in that job
    c) probably there are some other more or less clever methods available for this....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Dec 2000
    Posts
    138
    If you are still wary of Jurij's three ways of doing it..well give a try with dbms_pipe

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