| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: CREATE USER VIA DYNAMIC SQL
On Wed, 11 Aug 1999 16:17:29 +0200, p_at_eskom.co.za wrote:
>Hi,
>
>I have a problem with creating a user through dynamic SQL in a Stored Proc.
>
>I get the error :1301 - Insufficient priviledges, when executing the proc in
>SQL Plus.
>
>If however, I run the same 'create user' statement in SQL Plus, it works
>fine.
>
>Both times the proc owner executes the statement.
>
>Can someone PLEASE help?
Yup,
The simple answer is that roles are not enabled in pl/sql. I assume that this owner can create users via the dba role. To test this, log in to sql*plus and do this
SQL> connect clbeck/clbeck
Connected.
SQL> create user xxx identified by xxx; User created.
SQL> drop user xxx;
User dropped.
SQL> set role none;
Role set.
SQL> create user xxx identified by xxx; create user xxx identified by xxx
*
As you can see, this user was able to create a user via a role and when roles were turned off, no dice. So you can grant create user directly to the owner and all will be fine.
SQL> connect system/manager
Connected.
SQL> grant create user to clbeck;
Grant succeeded.
SQL> connect clbeck/clbeck
Connected.
SQL> set role none;
Role set.
SQL> create user xxx identified by xxx; User created.
Anytime you are doing stuff like this it is a good idea to try this little test by setting role none and trying to run the command from sql*plus. If it works, then it will work in pl/sql, otherwise the owner will need that privilege granted directly to them, not through a role.
hope this helps
chris.
>
>Pierre
>
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |