-
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.
-
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
-
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?
-
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!
-
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
-
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.
-
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!
-
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?
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|