My Oracle Support Banner

ORA-01031 When Creating User, Granting Connect, Resource From A Procedure (Doc ID 370013.1)

Last updated on APRIL 06, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 10.2.0.4 [Release 9.2 to 10.2]
Information in this document applies to any platform.

Symptoms

When a user with DBA role granted executes a procedure to create a user, then grant resource and  connect roles to that user, the following error is returned:

ERROR
ORA-01031: insufficient privileges

The issue can be reproduced with the following steps:

SQL> create or replace PROCEDURE CreateUser(username Varchar, password  Varchar) IS
BEGIN
    EXECUTE IMMEDIATE 'CREATE USER ' || username || ' IDENTIFIED BY ' || password || '
                                                                  DEFAULT TABLESPACE USERS ' || ' TEMPORARY
                                                                 TABLESPACE TEMP ' || ' PROFILE DEFAULT ' || ' QUOTA
                                                                 UNLIMITED ON USERS';
    EXECUTE IMMEDIATE ' GRANT CONNECT, RESOURCE TO ' || username;
END  CreateUser;
/


Example :

As SYSTEM :

SQL> grant connect, resource, DBA to <user1>;

As <user1> :

 

SQL> create or replace PROCEDURE CreateUser(username Varchar, password Varchar) IS
2 BEGIN
3   EXECUTE IMMEDIATE 'CREATE USER ' || username || ' IDENTIFIED BY ' || password || '
                  DEFAULT TABLESPACE USERS ' || ' TEMPORARY
                  TABLESPACE TEMP ' || ' PROFILE DEFAULT ' || ' QUOTA
                  UNLIMITED ON USERS';
4   EXECUTE IMMEDIATE ' GRANT CONNECT, RESOURCE TO ' || username;
5 END CreateUser;
6 /
Procedure created.
SQL> exec createuser('u1', 'u1');

BEGIN createuser('u1',  'u1'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "USER1.CREATEUSER", line 3
ORA-06512: at line 1

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.