What user privilege is required by a user to be able to run a GRANT sql command in Oracle-CodePudding

I created a user "UserA" and granted it Create session, user, tablespace privileges. Now logged in as "UserA", I have successfully created another user "UserB" but unable to run grant privileges to "UserB" with error: ORA-01031: insufficient privileges. What privileges should "UserA" have to be able to run grant statements?

CodePudding user response：

This is what you currently have (I presume so, as you didn't post what you exactly did):

Connected as a privileged user (sys), I'm creating a new user who's being granted create session and create user privileges:

SQL> connect sys@pdb1 as sysdba
Enter password:
Connected.
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

SQL> create user usera identified by usera
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users;

User created.

SQL> grant create session, create user to usera;

Grant succeeded.

As usera has been granted the create user privilege, it is allowed to create a new user - userb:

SQL> connect usera/usera@pdb1
Connected.
SQL> create user userb identified by userb
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users;

User created.

But, granting create session fails because usera isn't allowed to do that:

SQL> grant create session to userb;
grant create session to userb
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>

So, what can you do?

One option is to use the with admin option; back to sys and re-grant it to usera:

SQL> connect sys@pdb1 as sysdba
Enter password:
Connected.
SQL> grant create session to usera with admin option;

Grant succeeded.

Can usera now grant create session to userb? Yes!

SQL> connect usera/usera@pdb1
Connected.
SQL> grant create session to userb;

Grant succeeded.

SQL>

Another option is to grant dba role to usera:

SQL> connect sys@pdb1 as sysdba
Enter password:
Connected.

Revoking create session from usera will cascade to userb who can't create session any more:

SQL> revoke create session from usera;

Revoke succeeded.

Only usera has create session privilege:

SQL> grant create session to usera;

Grant succeeded.

But, grant dba as well

SQL> grant dba to usera;

Grant succeeded.

Can usera now grant create session to userb? Yes!

SQL> connect usera/usera@pdb1
Connected.
SQL> grant create session to userb;

Grant succeeded.

SQL>

However: note that both with admin option and dba are powerful and - if you don't pay attention - you might have a security hole in your system. Leave powerful privileges to powerful users only; all the others shouldn't be doing such tasks.