The DBMS PRIVILEGE MANAGEMENT privileges
AuraDB Business CriticalAuraDB Virtual Dedicated CloudEnterprise Edition

The DBMS privileges for privilege management can be granted, denied, or revoked like other privileges.

For more details about the syntax descriptions, see Reading the administration commands syntax.
Table 1. Privilege management privileges command syntax
Command Description 
GRANT [IMMUTABLE] SHOW PRIVILEGE
  ON DBMS
  TO role[, ...]

Enables the specified roles to list privileges.

 
GRANT [IMMUTABLE] ASSIGN PRIVILEGE
  ON DBMS
  TO role[, ...]

Enables the specified roles to assign privileges using the GRANT and DENY commands.

 
GRANT [IMMUTABLE] REMOVE PRIVILEGE
  ON DBMS
  TO role[, ...]

Enables the specified roles to remove privileges using the REVOKE command.

 
GRANT [IMMUTABLE] PRIVILEGE MANAGEMENT
  ON DBMS
  TO role[, ...]

Enables the specified roles to list, assign, and remove privileges.

Grant privilege to list privileges

You can grant the SHOW PRIVILEGE privilege to allow a user to list privileges using the SHOW PRIVILEGE, SHOW ROLE roleName PRIVILEGES, and SHOW USER username PRIVILEGES administration commands. The SHOW USER username PRIVILEGES command also requires the SHOW USER privilege.

For example:

GRANT SHOW PRIVILEGE ON DBMS TO privilegeViewer;

As a result, the privilegeViewer role has privileges that only allow showing privileges. To list all privileges for the role privilegeViewer as commands, use the following query:

SHOW ROLE privilegeViewer PRIVILEGES AS COMMANDS;
Table 2. Result
command

"GRANT SHOW PRIVILEGE ON DBMS TO `privilegeViewer`"

Rows: 1

No specific privileges are required for showing the current user’s privileges through the SHOW USER username PRIVILEGES or SHOW USER PRIVILEGES commands.

If a non-native auth provider like LDAP is in use, SHOW USER PRIVILEGES will only work with a limited capacity by making it only possible for a user to show their own privileges. Other users' privileges cannot be listed when using a non-native auth provider.

Grant privilege to assign privileges

You can grant the privilege to assign privileges using the ASSIGN PRIVILEGE privilege.
A user with this privilege is allowed to execute GRANT and DENY administration commands.
For example:

GRANT ASSIGN PRIVILEGE ON DBMS TO privilegeAssigner;

As a result, the privilegeAssigner role has privileges that only allow assigning privileges. To list all privileges for the role privilegeAssigner as commands, use the following query:

SHOW ROLE privilegeAssigner PRIVILEGES AS COMMANDS;
Table 3. Result
command

"GRANT ASSIGN PRIVILEGE ON DBMS TO `privilegeAssigner`"

Rows: 1

Grant privilege to remove privileges

You can grant the privilege to remove privileges from roles using the REMOVE PRIVILEGE privilege.
A user with this privilege is allowed to execute REVOKE administration commands.
For example:

GRANT REMOVE PRIVILEGE ON DBMS TO privilegeRemover;

As a result, the privilegeRemover role has privileges that only allow removing privileges. To list all privileges for the role privilegeRemover as commands, use the following query:

SHOW ROLE privilegeRemover PRIVILEGES AS COMMANDS;
Table 4. Result
command

"GRANT REMOVE PRIVILEGE ON DBMS TO `privilegeRemover`"

Rows: 1

Grant privilege to manage privileges

You can grant the privilege to list, assign, and remove privileges using the PRIVILEGE MANAGEMENT privilege.
For example:

GRANT PRIVILEGE MANAGEMENT ON DBMS TO privilegeManager;

As a result, the privilegeManager role has all privileges to manage privileges. To list all privileges for the role privilegeManager as commands, use the following query:

SHOW ROLE privilegeManager PRIVILEGES AS COMMANDS;
Table 5. Result
command

"GRANT PRIVILEGE MANAGEMENT ON DBMS TO `privilegeManager`"

Rows: 1