Stored Procedure to create new users
Posted on 2006-04-19
Environment: Oracle 10, Win 2k3 server
I have a stored procedure to create database users which includes the following code:
sqlstring := 'create user ' || upper(inusername) || ' identified by "123456" default tablespace ' || intablespace;
execute immediate sqlstring;
which always fails due to:
ORA-01031: insufficient privileges ORA-06512: at "SPUSER", line 20 ORA-06512: at line 1
The execute statement is on line 20 so I'm assuming it can't run that due to lack of privelages.
The same user can run the command on it's own, i.e. the following SQL runs fine:
create user newusername identified by '123456' default tablespace tablespacename;
I have read in numerous places that permissions granted to users via roles are not used through stored procedures. I've also read that that's only true when compiling the stored procedure, which is the case?
I have since tried granting the create user privelage directly to the user but still I can't run the stored procedure. Is there another permission required to run create user?
thanks for your time.