ORA-01031: insufficient privileges even though it has been granted the rights-CodePudding

I have a user named BT_MASTER when connected to SYSTEM, and granted BT_MASTER the role MAIN_ADMIN_ROLE:

select * from dba_role_privs where grantee = 'BT_MASTER';

GRANTEE	GRANTED_ROLE	ADMIN_OPTION	DELEGATE_OPTION	DEFAULT_ROLE	COMMON	INHERITED
BT_MASTER	MAIN_ADMIN_ROLE	NO	NO	YES	NO	NO

This role, again using SYSTEM to GRANT, has the privilege to CREATE USER:

select * from role_sys_privs

ROLE	PRIVILEGE	ADMIN_OPTION	COMMON	INHERITED
MAIN_ADMIN_ROLE	CREATE USER	NO	NO	NO

However, when trying something simple like this, connected to the DB as BT_MASTER:

CREATE USER new_user_example;

I get this error:

Error starting at line : 1 in command - CREATE USER new_user_example Error report - ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges" *Cause: An attempt was made to perform a database operation without the necessary privileges. *Action: Ask your database administrator or designated security administrator to grant you the necessary privileges

I am using Oracle XE 18c, the slim version (gvenzl/oracle-xe:18-slim)

Does anyone know if this is a limitation of this version, or if I am doing something incorrect?

CodePudding user response：

I'm on 21XE, but that shouldn't matter:

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production

SQL>

It usually helps if you post everything you did, step-by-step - then we can replicate it and (hopefully) spot the error. As you didn't do it, I tried to simulate the process. Have a look.

Connected as SYS, I'll create the bt_master user and a role (and grant appropriate privileges):

SQL> connect sys/pwd@pdb1 as sysdba
    Connected.
SQL> create user bt_master identified by bt_master
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users;

User created.

SQL> grant create session to bt_master;

Grant succeeded.

SQL> create role main_admin_role;

Role created.

SQL> grant create user to main_admin_role;

Grant succeeded.

SQL> grant main_admin_role to bt_master;

Grant succeeded.

It's all set; now, connect as bt_master and try to create a new user:

SQL> connect bt_master/bt_master@pdb1
Connected.
SQL> create user bt_test identified by bt_test;

User created.

SQL>

As you can see, everything went just fine. Can you find the error in your steps? If not, do as instructed - post the whole process, just like I did.

CodePudding user response：

I managed to get around this issue by connecting as SYSTEM and doing the following:

grant DBA to BT_MASTER with admin option;