ORA-01031 Create User in Procedure

[gcastle]

I have a procedure that is executed by an insert trigger that works fine when I am connected as Sys with Sysdba rights when I insert a row of data. When I am connected as a different user I receive the error message ORA-01031: insufficient privileges. I can run the single statement within the procedure through SQL Plus with no problem. Any idea as to what privilege I need to assign other users so that this procedure will execute correctly. The other user is assigned execute any procedure.

create or replace procedure cr8user (cuser IN varchar2, cpass IN varchar2) as

pragma autonomous_transaction;

begin

execute immediate 'create user ' | | cuser | | ' profile default identified by ' | | cpass | | ' default tablespace users temporary tablespace temp account unlock';

end cr8user;

This is on Oracle 8i, 8.1.5. Any help would be greatly appreciated.

Thanks,

Greg

 

[altink]

Create User is a system privilege and as such the privilege can not be used through a stored procedure. You have to run that command directly from your application.

Altin

 

[Devil]

try AUTHID DEFINER, if in your oracle version it's possible and your procedure created in schema as Sys or Sysdba

create or replace procedure cr8user (cuser IN varchar2, cpass IN varchar2) AUTHID DEFINER as
pragma autonomous_transaction;
begin
...

=============================================
AUTHID CURRENT_USER
Specify CURRENT_USER if you want the methods of the class to execute with the privileges of CURRENT_USER. This clause is the default and creates an "invoker-rights class."

This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER. External names in all other statements resolve in the schema in which the methods reside.

AUTHID DEFINER
Specify DEFINER if you want the methods of the class to execute with the privileges of the user who defined it.

This clause also specifies that external names resolve in the schema where the methods reside.

 

[Frank Schmitt]

Either declare the procedure as AUTHID DEFINER like Devil proposed, or grant the system privilege "CREATE USER" to the user that calls the procedure.

BTW, you might want to reconsider your naming conventions - procedure names like "cr8user" are *SO* much harder to read than "createUser", and you'll read the procedure name a lot more often than write it.

HTH & kind regards
frank

 

[Devil]

Frank Schmitt right. Or for user who create this procedure(is will be definer this procedure) grant the system privilege "CREATE USER".
The procedure "cr8user" declare with AUTHID DEFINER invoker rights clause.

And to the user that calls this procedure grant the object privilege "grant execute cr8user to ...".

 

[ldsandon]

The other user is assigned execute any procedure.

I suggest you to remove this privilege from users that aren't DBAs. It will open huge security issues, because a user with such privileges can run code he should not be allowed to - and with privileges it shouldn't have.