| Creating user and inserting data [message #3058] |
Mon, 02 September 2002 22:11  |
Christine Pollhaus
Messages: 18
Registered: May 2002
|
Junior Member |
|
|
Hi everybody,
My problem:
I created a proc that creates a new user and grants a role i created also, looks like this:
CREATE OR REPLACE
PROCEDURE NEWUSER (user_in IN VARCHAR2, pw_in IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE
'CREATE USER '||user_in||' IDENTIFIED BY '||pw_in||' DEFAULT TABLESPACE PROFILE_C TEMPORARY TABLESPACE TEMP ';
EXECUTE IMMEDIATE
'GRANT STANDARD_USER TO '||user_in||' ';
END;
Then in the next step i want to add (insert) data in a table that lists all users. Table is like this:
TABLE PFC_BWV_ANWENDER:
USER_ID CHAR (20) NOT NULL,
USER_NAME CHAR (200),
SPRACHE CHAR (3) NOT NULL,
THEMA CHAR (10) NOT NULL,
DATEN_GRP CHAR (100)
How do I include the INSERT-statement into my procedure?
user_id column is ||user_in|| user_name column must be variable (cursor or something?), SPRACHE column is 'D'; THEMA column ist 'SYS'; DATEN_GRP column is blank.
Or do I have to create a trigger for the insert-statement? e.g. system trigger 'after grant' or something like that ???
Any help would be really kind!!!
|
|
|
|
| Re: Creating user and inserting data [message #3059 is a reply to message #3058] |
Mon, 02 September 2002 22:46  |
Manoj
Messages: 101
Registered: August 2000
|
Senior Member |
|
|
just a thought, you can create an another procedure that when called will simply insert the values from the variables and other date into the PFC_BWV_ANWENDER table. you can execute this procedure immediately following the create user procedure. (just a thought)
Manoj
|
|
|
|
| Re: Creating user and inserting data [message #3061 is a reply to message #3058] |
Mon, 02 September 2002 22:57 |
Christine
Messages: 18
Registered: March 2002
|
Junior Member |
|
|
Thanks, yes I thought so, too, but i can't manage to do it, 'cos I do not know how to call it automatically.
I want to have as less input-type-work as possible. Just want to execute the proc when needing a new user (anyway there will be hundreds of new users in the near future for my database and application!)
|
|
|
|
| Re: Creating user and inserting data [message #3063 is a reply to message #3058] |
Mon, 02 September 2002 23:18 |
Manoj
Messages: 101
Registered: August 2000
|
Senior Member |
|
|
ok,
there is no specific trigger call after grant or anything else close to that. another thought, why don't you just add the 'insert line' to add the data into the PFC_BWV_ANWENDER table between the
'GRANT STANDARD_USER TO '||user_in||' '; and END; statement. since it's a procedure it should not be a problem and sicne you have every variable that you need in the procedure, i think it might work. (just a thought)
|
|
|
|
| Re: Creating user and inserting data [message #3067 is a reply to message #3058] |
Tue, 03 September 2002 01:20 |
Christine Pollhaus
Messages: 18
Registered: May 2002
|
Junior Member |
|
|
Hi Manoj,
sorry but your're wrong! There exist so called 'SYSTEM TRIGGERS' with several possibilities to react on Database-actions, schema-action etc. (they are possible since ORA 8i!). Unfortunately I never used them, only read of it. Now a good question how to manage it. Or (just as thought) manage to create a trigger on the dba-users_view ??? Things still go on and i try and try and try. Will post a message if solved!
Greetings Christine
|
|
|
|
| Re: Creating user and inserting data [message #3084 is a reply to message #3058] |
Tue, 03 September 2002 21:15 |
Manoj
Messages: 101
Registered: August 2000
|
Senior Member |
|
|
Hi christine,
i see what you mean, i did check it out, and you are right , there do exists so called 'SYSTEM TRIGGERS' with several possibilities to react on Database-actions, schema-action. i'm just trying them out now. i'll work it our in a couple of days from now, thanks for the info
Manoj
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
