Oracle CREATE USER privilege

Question

Good evening!

The problem is- I granted certain user a privilege to create, alter and drop users (CREATE USER, ALTER USER, DROP USER) but when I try to CREATE USER using that user, it says that that user has insufficient privileges. What should be done to fix this?

Thank you all in advance!

EDIT: I use Oracle Database 18c Express Edition

EDIT2: User creation:

SQL> CREATE USER predefined
  2  IDENTIFIED BY pf
  3  DEFAULT TABLESPACE USERS
  4  QUOTA 50M ON USERS
  5  TEMPORARY TABLESPACE TEMP;

User created.

SQL> GRANT CREATE SESSION TO predefined;

Grant succeeded.

SQL> GRANT CREATE ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY VIEW, CREATE ANY PROCEDURE, CREATE ANY INDEX, CREATE ANY TRIGGER TO predefined

Grant succeeded.

SQL> GRANT CREATE USER TO predefined;

Grant succeeded.

SQL> GRANT ALTER USER TO predefined;

Grant succeeded.

SQL> GRANT DROP USER TO predefined;

Grant succeeded.

And when I try to create user with that user, i get this:

SQL> CREATE USER predefined2
  2  IDENTIFIED BY pf2
  3  DEFAULT TABLESPACE USERS
  4  QUOTA 5M ON USERS
  5  TEMPORARY TABLESPACE TEMP;
CREATE USER predefined2
*
ERROR at line 1:
ORA-01031: insufficient privileges

Another thing- I have checked USER_SYS_PRIVS table, and it shows me this:

USERNAME                                                                                                                         PRIVILEGE                                ADM COM INH
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- --- --- ---
PREDEFINED                                                                                                                         ALTER USER                               NO  NO  NO
PREDEFINED                                                                                                                         CREATE USER                              NO  NO  NO
PREDEFINED                                                                                                                         DROP USER                                NO  NO  NO
PREDEFINED                                                                                                                         CREATE ANY VIEW                          NO  YES NO
PREDEFINED                                                                                                                         CREATE ANY PROCEDURE                     NO  YES NO
PREDEFINED                                                                                                                         CREATE ANY SEQUENCE                      NO  YES NO
PREDEFINED                                                                                                                         CREATE SESSION                           NO  YES NO
PREDEFINED                                                                                                                         CREATE ANY TRIGGER                       NO  YES NO
PREDEFINED                                                                                                                         CREATE ANY INDEX                         NO  YES NO
PREDEFINED                                                                                                                         CREATE ANY TABLE                         NO  YES NO

I noticed, that in the COM column it has a NO for CREATE USER, ALTER USER and DROP USER privileges. So maybie it has to do something with that?

Answer 1

So the problem was- i had not added the CONTAINTER=ALL clause while granting privs.

So the GRANT clause should look like this GRANT CREATE USER TO predefined CONTAINTER=ALL;

After that, creating of a new user was successful.

EDIT: After doing some research, i found out that those are System privileges, so if you want to grant them from system user to another user, you have to add that CONTAINER = ALL clause in the end of grant clause. Correct me if I'm wrong, but i hope this will help someone in the future too! :)

Answer 2

Well, you shouldn't have that problems. I don't, at least.

As SYS, I'm creating a brand new user which will be granted CREATE USER privilege:

SQL> show user
USER is "SYS"
SQL> create user predefined identified by pf;

User created.

SQL> grant create session, create user to predefined;

Grant succeeded.

So, does it work?

SQL> connect predefined/pf
Connected.
SQL> create user test identified by test;

User created.

SQL>

Yes, it does.

---

Please, edit the question and post what you exactly did (just like me) so that we could see it.