Procedure to create a user (merged) [message #294107] |
Wed, 16 January 2008 08:37  |
harshad.gohil
Messages: 157 Registered: April 2007 Location: USA
|
Senior Member |
|
|
I have created procedure, and during execution it's throwing error.
SQL> CREATE OR REPLACE PROCEDURE
test_idadmin.ACCESS_ADMIN_ADD_USER(
USER_NAME in VARCHAR2,
uSER_PASSWD in VARCHAR2)
AS
BEGIN
execute immediate 'CREATE USER USER_NAME IDENTIFIED BY USER_PASSWD DEFAULT TABLESPACE USERS TEMPorary TABLESPACE TEMP PROFILE MINE_DEFAULT';
END;
/ 2 3 4 5 6 7 8 9
Procedure created.
SQL> exec test_idadmin.ACCESS_ADMIN_ADD_USER ('ABC','DIWP#01')
BEGIN test_idadmin.ACCESS_ADMIN_ADD_USER ('ABC','DIWP#01'); END;
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password must have at least 2 letters and 2 other characters
ORA-06512: at "TEST_IDADMIN.ACCESS_ADMIN_ADD_USER", line 7
ORA-06512: at line 1
SQL>
I dont think of any password related error. When Try to create user command with same passoword it's executed succesfully.
Any inputs...?
|
|
|
Re: Procedure has some password issue [message #294111 is a reply to message #294107] |
Wed, 16 January 2008 08:50   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
because your password is 'USER_PASSWD' and not the one you supply.
It should be
execute immediate 'CREATE USER ' || USER_NAME ||' IDENTIFIED BY ' || USER_PASSWD ||
' DEFAULT TABLESPACE USERS TEMPorary TABLESPACE TEMP PROFILE MINE_DEFAULT'; Having said that why do you want to do something like this ? Just ask the same question twice to you
Still what puzzles me is after posting nearly 100+ still why you are also not formatting the code ?
Regards
Raj
[Updated on: Wed, 16 January 2008 08:53] Report message to a moderator
|
|
|
|
|
|
execute privilege [message #294444 is a reply to message #294107] |
Thu, 17 January 2008 12:11   |
harshad.gohil
Messages: 157 Registered: April 2007 Location: USA
|
Senior Member |
|
|
DB Version 10.2.0.1
I am having 2 user let's assume user A & B. A owns one procedures let say proc1 and B has execute privielege on A.PROC1. Proceudure A.PROC1 is able to create user. When B is trying to execute it's throwing error that insufficient privilege....? Am I missing something...?
|
|
|
|
|
Re: execute privilege [message #294464 is a reply to message #294457] |
Thu, 17 January 2008 12:54   |
harshad.gohil
Messages: 157 Registered: April 2007 Location: USA
|
Senior Member |
|
|
Thanks Michael for merging two posts.
Actual scenario here is
having 2 users A which has create user privilege through role...(you are right.. ) And I am giving execute for A.proc1 to B.
grant execute on a.proc1 to b;
but user b is not able to execute a.proc1, as it's not having create user privilege.
But user A who owns that object and having respected role. Still not able to execute that...
SQL> show user
USER is "ACADM"
SQL> select * from dba_sys_privs where grantee='ACADM';
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
ACADM CREATE SESSION NO
SQL> select * from dba_sys_privs where grantee='ROLE_ACCOUNT_ADMIN';
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
ROLE_ACCOUNT_ADMIN SELECT ANY DICTIONARY NO
ROLE_ACCOUNT_ADMIN ALTER USER NO
ROLE_ACCOUNT_ADMIN GRANT ANY ROLE NO
ROLE_ACCOUNT_ADMIN CREATE SESSION NO
ROLE_ACCOUNT_ADMIN CREATE USER NO
SQL> select * from dba_role_privs where grantee='ACADM';
GRANTEE
------------------------------------------------------------------------------------------------------------------------
GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
------------------------------ ------------ ------------
ACADM
ROLE_ACCOUNT_ADMIN YES YES
SQL> exec ACADM.ACCESS_ADMIN_ADD_USER ('tesT4','Just4now#1')
BEGIN ACADM.ACCESS_ADMIN_ADD_USER ('tesT4','Just4now#1'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "ACADM.ACCESS_ADMIN_ADD_USER", line 7
ORA-06512: at line 1
later on i had gave create user to user A, and now I am able to execute that proc successfully.
[Updated on: Thu, 17 January 2008 12:56] Report message to a moderator
|
|
|
|
|
|
Re: execute privilege [message #298571 is a reply to message #294491] |
Wed, 06 February 2008 08:38   |
harshad.gohil
Messages: 157 Registered: April 2007 Location: USA
|
Senior Member |
|
|
Hi Michel,
How about to enable role in store procedure...?
SYS.DBMS_SESSION.SET_ROLE by this can we enable role inside the store procedure of owner...?
|
|
|
|
|
Re: execute privilege [message #298584 is a reply to message #298577] |
Wed, 06 February 2008 10:18  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
This is what I said in my previous answer: "use "authid current_user" but in this case you have the privileges and roles of the CALLER and not the OWNER of the procedure."?
Regards
Michel
[Updated on: Wed, 06 February 2008 10:19] Report message to a moderator
|
|
|