oracle, trying to create a procedure which create a user

Question

I try to create this procedure (as a system), which takes 2 parameters, username and password, and create a user according to them:

create or replace procedure procedure_create_client 
( user_name IN varchar2 , u_password IN varchar2 )  IS

tmp_query varchar(150);
user_name_upper varchar(30) := UPPER(user_name) ;

BEGIN

    tmp_query := 'create user C##' || user_name_upper || ' identified by ' || u_password ;
    EXECUTE IMMEDIATE ( tmp_query );

    tmp_query := 'grant create session to ' || user_name_upper ;
    EXECUTE IMMEDIATE ( tmp_query );

END ;

The problem is, when I try to execute this procedure (as a system), I got this error:

Error report: ORA-01031: insufficient privileges ORA-06512: at "SYSTEM.PROCEDURE_CREATE_CLIENT", line 9 ORA-06512: at line 1 01031. 00000 - "insufficient privileges" *Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. When Trusted Oracle is configure in DBMS MAC, this error may occur if the user was granted the necessary privilege at a higher label than the current login. *Action: Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the the appropriate privilege at a higher label, ask the database administrator to regrant the privilege at the appropriate label.

Littlefoot Littlefoot · Accepted Answer · 2017-12-31T12:44:35

Consider avoiding SYS (and SYSTEM) for tasks other than database maintenance; perhaps you'd rather create your own user, let's call it MYDBA and grant it DBA role.

Something like that is what I did in the following example: I created a new user, SCOTT, and granted it only privileges to create another users. GRANT CREATE SESSION has additional WITH ADMIN OPTION, otherwise SCOTT wouldn't be able to grant CREATE SESSION to users it creates via a procedure.

SQL> show user
USER is "SYS"
SQL> create user scott identified by tiger quota unlimited on users;

User created.

SQL> grant create session to scott with admin option;

Grant succeeded.

SQL> grant create procedure to scott;

Grant succeeded.

SQL> grant create user to scott;

Grant succeeded.

SQL>

Now, connected as newly created SCOTT user, create a procedure (copy/paste of your code, with additional "C##" in line 10) and execute it in order to create yet another user, MIKE:

SQL> connect scott/tiger@xe
Connected.
SQL>
SQL> create or replace procedure procedure_create_client
  2    (user_name IN varchar2 , u_password IN varchar2 )
  3  IS
  4    tmp_query varchar(150);
  5    user_name_upper varchar(30) := UPPER(user_name) ;
  6  BEGIN
  7    tmp_query := 'create user C##' || user_name_upper || ' identified by ' || u_password;
  8    EXECUTE IMMEDIATE ( tmp_query );
  9
 10    tmp_query := 'grant create session to C##' || user_name_upper ;
 11    EXECUTE IMMEDIATE ( tmp_query );
 12  END;
 13  /

Procedure created.

SQL>
SQL> exec procedure_create_client('mike', 'lion');

PL/SQL procedure successfully completed.

SQL>

Fine; C## seems to be created. Let's connect:

SQL> connect c##mike/lion@xe
Connected.
SQL>

Looks all right, doesn't it? Though, poor C##mike doesn't have any privileges but CREATE SESSION, but that's what you planned for it.

oracle, trying to create a procedure which create a user

3 Answers