Home » SQL & PL/SQL » SQL & PL/SQL » Procedure to create a user (merged)
Procedure to create a user (merged) [message #294107] Wed, 16 January 2008 08:37 Go to next message
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 Go to previous messageGo to next message
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

Re: Procedure has some password issue [message #294112 is a reply to message #294107] Wed, 16 January 2008 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Any inputs...?

After 132 posts, only one:
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: Procedure has some password issue [message #294113 is a reply to message #294107] Wed, 16 January 2008 09:00 Go to previous messageGo to next message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
Thanks Raj. Just checked my syntax error. I am new to PL-SQL. Thanks for the correction.
Re: Procedure has some password issue [message #294133 is a reply to message #294113] Wed, 16 January 2008 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you spell S Q L IN JEC TION?

Regards
Michel
execute privilege [message #294444 is a reply to message #294107] Thu, 17 January 2008 12:11 Go to previous messageGo to next message
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 #294445 is a reply to message #294444] Thu, 17 January 2008 12:15 Go to previous messageGo to next message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
is it that user B also requires to create user privilege...?
Re: execute privilege [message #294457 is a reply to message #294444] Thu, 17 January 2008 12:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Am I missing something...?

Yes, to post the code.
But maybe it is the same as your previous question.

I bet A has create user privilege through a role.

Regards
Michel
Re: execute privilege [message #294464 is a reply to message #294457] Thu, 17 January 2008 12:54 Go to previous messageGo to next message
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..Wink) 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 #294469 is a reply to message #294464] Thu, 17 January 2008 13:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, roles are not enable inside a procedure (by default).

Regards
Michel
Re: execute privilege [message #294487 is a reply to message #294469] Thu, 17 January 2008 14:45 Go to previous messageGo to next message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
"Yes, roles are not enable inside a procedure (by default)."

Is it possible to enable role inside the procedure...?

I have got the solution for that..

http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96624/08_subs.htm#18575

[Updated on: Thu, 17 January 2008 15:04]

Report message to a moderator

Re: execute privilege [message #294491 is a reply to message #294487] Thu, 17 January 2008 15:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it possible to enable role inside the procedure...?

Yes, 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
Re: execute privilege [message #298571 is a reply to message #294491] Wed, 06 February 2008 08:38 Go to previous messageGo to next message
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 #298572 is a reply to message #298571] Wed, 06 February 2008 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.
ORA-06565: cannot execute SET ROLE from within stored procedure


Regards
Michel
Re: execute privilege [message #298577 is a reply to message #298572] Wed, 06 February 2008 09:44 Go to previous messageGo to next message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member

Please check the below link....

http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14266/apdvntro.htm#i1006338

They are enabling the store procedures as an AUTHID CURRENT_USER.

[Updated on: Wed, 06 February 2008 09:45]

Report message to a moderator

Re: execute privilege [message #298584 is a reply to message #298577] Wed, 06 February 2008 10:18 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
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

Previous Topic: Not able to compile wrapped package bodies.
Next Topic: Procedure Call
Goto Forum:
  


Current Time: Wed Dec 07 10:31:35 CST 2016

Total time taken to generate the page: 0.11019 seconds