• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4025
  • Last Modified:

Stored Procedure to create new users

Hi all,

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.
0
djguvnor
Asked:
djguvnor
  • 5
  • 4
  • 2
  • +1
2 Solutions
 
MetanilCommented:
I don't think so..
i think
grant create user to <owner_of_SP>;
will be enough..

however u may try recompling procedure.. and try.

here is the output of my test system:

SQL>create or replace procedure sp_createuser (username_ varchar2)
as
strSQL VARCHAR2(1000);
begin
strSQL := 'create user ' || username_ || ' identified by oracle default tablespace tblsp_name';
dbms_output.put_line(strSQL);
execute immediate strSQL;

end;
/

procedure sp_createuser Compiled.

SQL> exec sp_createuser('ORAFREAK');

Error starting at line 12 in command:
exec sp_createuser('ORAFREAK');
Error report:
ORA-01031: insufficient privileges
ORA-06512: at "SA.SP_CREATEUSER", line 7
ORA-06512: at line 1

create user ORAFREAK identified by oracle default tablespace tblsp_name

SQL> grant create user to sa;
grant create succeeded.

SQL>exec sp_createuser('ORAFREAK');
anonymous block completed
create user ORAFREAK identified by oracle default tablespace tblsp_name

SQL>select * from all_users where username = 'ORAFREAK';
USERNAME                       USER_ID                CREATED                  
------------------------------ ---------------------- -------------------------
ORAFREAK                       458                    19-APR-06                

1 rows selected

Metanil
0
 
actonwangCommented:
can you run "create user .." directly in sp?
0
 
actonwangCommented:
oops. don't try. my fault :)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
actonwangCommented:
is it possible that other codes in your sp trigger this error?
0
 
djguvnorAuthor Commented:
Ok, here's the crunch (which I've just discovered) it appears that if the stored procedure is not owned by the user attempting to run it, it fails.  When I setup exactly as Metanil suggested it all worked fine until I moved the procedure to a different schema.
0
 
MetanilCommented:
i think this will explain more...

SQL> connect orafreak/oracle
Connected.
SQL> exec sa.sp_createuser('orafreak1');
BEGIN sa.sp_createuser('orafreak1'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SA.SP_CREATEUSER' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> connect sa/oracle
Connected.
SQL> grant execute on sp_createuser to orafreak;

Grant succeeded.

SQL> connect orafreak/oracle
Connected.
SQL> set serveroutput on size unlimited
SQL> exec sa.sp_createuser('orafreak1');
create user orafreak1 identified by oracle default tablespace tblsp_name

PL/SQL procedure successfully completed.

SQL> select * from all_users where username = 'ORAFREAK1';

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
ORAFREAK1                             461 19-APR-06

SQL>


SQL> connect sa/oracle
Connected.
SQL> drop user ORAFREAK1;

User dropped.

SQL> revoke execute on sp_createuser from orafreak;

Revoke succeeded.

SQL> connect orafreak/oracle
Connected.
SQL> exec sa.sp_createuser('orafreak1');
BEGIN sa.sp_createuser('orafreak1'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SA.SP_CREATEUSER' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL>

Metanil
0
 
RCorfmanCommented:
You are running into definer rights vs. Invoker rights problems.
See this post from me on prior PAQ question. It is definitely the problem.
http://www.experts-exchange.com/Databases/Oracle/Q_21762459.html

I'm going to be offline all day so hopefully the above answers your questions on this topic. If not, I can either pick it up this evening (If I have a modem available... I'm on vacation) or hopefully somone else would. I KNOW that this is the problem though.
0
 
djguvnorAuthor Commented:
Metanil, Your first answer was essentially right, thankyou very much but RCorfman, your linked post explains something I have been baffled about for sometime and is indeed the answer to this problem (and possibly many others!) thank you very much, now go and enjoy your holiday!!
0
 
actonwangCommented:
hi,

    hmmm...

    diguvnor, from your post:

create or replace procedure sp_createuser (username_ varchar2)
as
..

   you didn't specify "authid current_user".

   so your problem is you didn't grant execute on package to other people not definer/invoker problem, right?

Acton
0
 
djguvnorAuthor Commented:
Actonwang: No, the users had specific execute rights on the procedure but when it ran it fell over on the create user command because the owner of the procedure did not have the create user privelage.
0
 
actonwangCommented:
ho ho , i c::) is that simple ?
0
 
djguvnorAuthor Commented:
Now that I understand it yes!  ;)
0
 
RCorfmanCommented:
I'm done with vacation and now at the Collaborate '06 Conference in Nashville. I'll be online on and off. I hope that post explained enough. If you have specific questions on whether it should be definer rights vs. invoker rights, I'm happy to answer. There are specific reasons why you would want to use one or the other, and either/both have implications on the specific rights you need to grant to get the create user procedure to work properly.  I posted the link then ran off and was offline for since then till now....
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now