Managing System Privileges

To perform actions on schema objects, you must be granted the appropriate system privileges.

About System Privileges

A system privilege is the right to perform an action or to perform actions on schema objects.

For example, the privileges to create tablespaces and to delete the rows of any table in a database are system privileges.

There are many different kinds of system privileges. Each system privilege allows a user to perform a particular database operation or class of database operations. Remember that system privileges are very powerful. Only grant them when necessary to roles and trusted users of the database. To find the system privileges that have been granted to a user, you can query the DBA_SYS_PRIVS data dictionary view.

If you want to restrict a system privilege to a specific schema, then you can do so by granting it as a schema privilege. A schema privilege enables you to grant a specific system privilege on a schema without having to perform a grant on every object within the schema.

System privileges such as SELECT ANY TABLE do not work on SYS objects or other objects that are owned by schemas that are marked as DICTIONARY PROTECTED.

Related Topics

Who Can Grant or Revoke System Privileges?

Only two types of users can grant system privileges to other users or revoke those privileges from them.

These users are as follows:

For this reason, only grant these privileges to trusted users.

Why Is It Important to Restrict System Privileges?

System privileges are very powerful, so only grant them to trusted users. You should also secure the data dictionary and SYS schema objects.

About the Importance of Restricting System Privileges

System privileges are very powerful, so by default the database is configured to prevent typical (non-administrative) users from exercising the ANY system privileges.

For example, users are prevented from exercising ANY system privileges such as UPDATE ANY TABLE on the data dictionary.

Related Topics

User Access to Objects in the SYS Schema

Users with explicit object privileges or those who connect with administrative privileges (SYSDBA) can access objects in the SYS schema.

The following table lists roles that you can grant to users who need access to objects in the SYS schema.

Role Description
SELECT_CATALOG_ROLE Grant this role to allow users SELECT privileges on data dictionary views.
EXECUTE_CATALOG_ROLE Grant this role to allow users EXECUTE privileges for packages and procedures in the data dictionary.

Additionally, you can grant the SELECT ANY DICTIONARY system privilege to users who require access to tables created in the SYS schema. This system privilege allows query access to any object in the SYS schema, including tables created in that schema. It must be granted individually to each user requiring the privilege. It is not included in GRANT ALL PRIVILEGES, but it can be granted through a role.

In earlier releases, the DBA_TAB_STAT_PREFS view could be queried by any user with the PUBLIC role. Starting with Oracle Database 19c, Release Update 19.28, this view adheres to the convention for DBA_ views, that is, it can be queried only by users with the SYSDBA system privilege or SELECT ANY DICTIONARY privilege, or SELECT_CATALOG_ROLE role, or by users with direct privileges granted to them. This change improves access control for optimizer and statistics metadata and reduces information disclosure.

Note: You should grant these roles and the SELECT ANY DICTIONARY system privilege with extreme care, because the integrity of your system can be compromised by their misuse.

Grants and Revokes of System Privileges

You can grant or revoke system privileges to users and roles.

If you grant system privileges to roles, then you can use the roles to exercise system privileges. For example, roles permit privileges to be made selectively available. Ensure that you follow separation of duty guidelines for securing roles.

Use either of the following methods to grant or revoke system privileges to or from users and roles:

Related Topics

About ANY Privileges and the PUBLIC Role

System privileges that use the ANY keyword enable you to set privileges for an entire category of objects in the database.

For example, the CREATE ANY PROCEDURE system privilege permits a user to create a procedure anywhere in the database. The behavior of an object created by users with the ANY privilege is not restricted to the schema in which it was created. For example, if user JSMITH has the CREATE ANY PROCEDURE privilege and creates a procedure in the schema JONES, then the procedure will run as JONES. However, JONES may not be aware that the procedure JSMITH created is running as JONES. If JONES has DBA privileges, letting JSMITH run a procedure as JONES could pose a security violation.

The PUBLIC role is a special role that every database user account automatically has when the account is created. By default, it has no privileges granted to it, but it does have numerous grants, mostly to Java objects. You cannot drop the PUBLIC role, and a manual grant or revoke of this role has no meaning, because the user account will always assume this role. Because all database user accounts assume the PUBLIC role, it does not appear in the DBA_ROLES and SESSION_ROLES data dictionary views.

You can grant privileges to the PUBLIC role, but remember that this makes the privileges available to every user in the Oracle database. For this reason, be careful about granting privileges to the PUBLIC role, particularly powerful privileges such as the ANY privileges and system privileges. For example, if JSMITH has the CREATE PUBLIC SYNONYM system privilege, JSMITH could redefine an interface that they know everyone else uses, and then point to it with the PUBLIC SYNONYM that JSMITH created. Instead of accessing the correct interface, users would access the interface of JSMITH, which could possibly perform illegal activities such as stealing the login credentials of users.

These types of privileges are very powerful and could pose a security risk if given to the wrong person. Be careful about granting privileges using ANY or PUBLIC. As with all privileges, you should follow the principles of “least privilege” when granting these privileges to users.

Related Topics

Managing Schema Privileges

Schema privileges enable certain system privileges to be granted on a schema.

About Managing Schema Privileges

When a schema privilege is granted on a schema, the grantee has the system privilege on all the objects in the schema on which the grant has been made.

The system privilege applies to both current and future objects in the schema. For example, suppose you grant the CREATE ANY TABLE system privilege to user psmith for use on the HR schema. User psmith is then able to create tables in the HR schema and not in any other schema for which psmith does not have permission. You can grant the schema privilege to either users or roles. Schema privilege grants can be used on a wide range of system privileges, though not all. In addition, you cannot use schema privileges on the SYS schema. Because this grant provides powerful privileges to the grantee, ensure that you grant the schema privilege to trusted users only.

Granting users schema privileges has the following benefits:

To grant or revoke schema privileges, you must have the GRANT ANY SCHEMA PRIVILEGE or the GRANT ANY PRIVILEGE system privilege.

The ANY system privileges that you can include in the schema grants cover operations such as creation, altering, executing, dropping of objects.

The Oracle AI Database SQL Language Reference provides a list of the available system privileges that you can grant as schema privileges.

To find information about schema privilege grants, query the following data dictionary views:

Related Topics

Privileges That Are Excluded from Schema Privilege Grants

Many administrative and system privileges cannot be used in schema privilege grants.

The following administrative privileges are excluded from schema privilege grants:

The following table lists system privileges that are excluded from schema privilege grants.

System Privilege Type Privilege
Advisor framework ADVISOR
ADMINISTER SQL TUNING SET
Application context CREATE ANY CONTEXT
DROP ANY CONTEXT
Application continuity KEEP DATE TIME
KEEP SYSGUID
Database change notification CHANGE NOTIFICATION
Database links CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK
Database triggers ADMINISTER DATABASE TRIGGER
Debugging DEBUG CONNECT SESSION
Dictionary protection SELECT ANY DICTIONARY
ANALYZE ANY DICTIONARY
Directories CREATE ANY DIRECTORY
DROP ANY DIRECTORY
READ
WRITE
Editions CREATE ANY EDITION
DROP ANY EDITION
Exports and imports EXPORT FULL DATABASE
IMPORT FULL DATABASE
Flashback FLASHBACK ARCHIVE ADMINISTER
SELECT ANY TRANSACTION
Key management ADMINISTER KEY MANAGEMENT
Logminer LOGMINING
Plan management ADMINISTER SQL MANAGEMENT OBJECT
Pluggable databases CREATE PLUGGABLE DATABASE
SET CONTAINER
Profiles CREATE PROFILE
ALTER PROFILE
DROP PROFILE
Public synonyms CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
Recycle bin PURGE DBA_RECYCLEBIN
Resource management ADMINISTRATE RESOURCE MANAGER
Resumable space allocation RESUMABLE
Roles CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
ALTER ANY ROLE
Rollback segment CREATE ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT
Sessions CREATE SESSION
ALTER SESSION
RESTRICT SESSION
Stored outlines CREATE ANY OUTLINE
ALTER ANY OUTLINE
DROP ANY OUTLINE
System ALTER DATABASE
ALTER SYSTEM
AUDIT SYSTEM
ALTER RESOURCE COST
Tablespaces CREATE TABLESPACE
ALTER TABLESPACE
MANAGE TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
Transactions FORCE TRANSACTION
FORCE ANY TRANSACTION
Users CREATE USER
BECOME USER
ALTER USER
DROP USER

Granting a Schema Privilege

You can use the GRANT statement to grant a schema privilege to a user or a role.

  1. Log in to the CDB root or to a PDB as a user who has been granted the GRANT ANY SCHEMA PRIVILEGE or GRANT ANY PRIVILEGE system privilege.

  2. To find the available schema privileges that you can grant, see Oracle AI Database SQL Language Reference

  3. Grant the schema privilege to the user or role.

    For example, suppose you grant the SELECT ANY TABLE system privilege to user psmith for use on the HR schema. User psmith is then able to select from existing and future tables that are created in the HR schema.

    GRANT SELECT ANY TABLE ON SCHEMA HR TO psmith;

    If you have the GRANT ANY SCHEMA PRIVILEGE WITH ADMIN OPTION privilege, then you can do two additional types of grants:

    • Grant GRANT ANY SCHEMA PRIVILEGE to another user.

    • Grant a schema privilege WITH ADMIN OPTION, so that the user can grant the schema privilege to another user.

Revoking a Schema Privilege

You can use the REVOKE statement to revoke a schema privilege from a user or a role.

  1. Log in to the CDB root or to a PDB as a user who has been granted the GRANT ANY SCHEMA PRIVILEGE system privilege with WITH ADMIN OPTION.

  2. To find the schema privileges that have been granted to the user or role, run a query similar to the following:

    For example:

    SELECT PRIVILEGE, SCHEMA FROM DBA_SCHEMA_PRIVS
WHERE GRANTEE = 'PSMITH';

    Output similar to the following appears:

    PRIVILEGE             SCHEMA
--------------------- ------------
SELECT ANY TABLE      HR

  3. Revoke the schema privileges from the user or role.

    For example, to revoke the SELECT ANY TABLE schema privilege from user psmith:

    REVOKE SELECT ANY TABLE ON SCHEMA HR FROM psmith;

Administering Schema Security Policies

To manage schema security policies for row level security, fine-grained auditing, and Oracle Data Redaction, users must be granted the appropriate system privilege.

About Administering Schema System Security Policies

Security policies for row level security, fine-grained auditing, and Oracle Data Redaction require special schema-related system privileges.

The system privileges and their corresponding PL/SQL packages that the user must be granted are as follows:

You must grant the system privilege to the user in addition to the other required privileges that are needed for the security policy, such as the EXECUTE privilege on any PL/SQL packages. You can grant the system privilege in either of the following ways:

Granting an Administrator Schema Security Policy

You can use the GRANT statement to grant a schema system privilege to a user or role.

  1. Log in to the CDB root or to a PDB as a user who has been granted the GRANT ANY SCHEMA PRIVILEGE system privilege with WITH ADMIN OPTION.

  2. Grant the user the EXECUTE privilege on the PL/SQL package (and any other necessary privileges) to administer the security policy.

    For example, for a user who is responsible for creating row level security policies:

    GRANT EXECUTE ON DBMS_RLS TO preston;

  3. Grant the user the schema system privilege.

    For example, to restrict row level security policies to the HR schema:

    GRANT ADMINISTER ROW LEVEL SECURITY POLICY ON SCHEMA HR TO preston;

    To enable the user to create the policy in any non-SYS schema in the database:

    GRANT ADMINISTER ROW LEVEL SECURITY POLICY TO preston;

Revoking an Administrator Security Policy

You can use the REVOKE statement to revoke a schema system privilege from a user or role.

  1. Log in to the CDB root or to a PDB as a user who has been granted the GRANT ANY SCHEMA PRIVILEGE system privilege with WITH ADMIN OPTION.

  2. To find the system privileges that have been granted to the user or role, run a query similar to the following:

    For example:

    SELECT PRIVILEGE FROM DBA_SYS_PRIVS_ALL WHERE GRANTEE = 'PRESTON';

    Output similar to the following appears:

    PRIVILEGE
-------------------------------------
ADMINISTER ROW LEVEL SECURITY POLICY

  3. Revoke the system privilege from the user or role.

    For example:

    REVOKE ADMINISTER ROW LEVEL SECURITY POLICY ON SCHEMA HR FROM preston;

    Or:

    REVOKE ADMINISTER ROW LEVEL SECURITY POLICY FROM preston;

  4. Revoke any other privileges as necessary, such as the EXECUTE privilege on the associated PL/SQL package.

    For example:

    REVOKE EXECUTE ON DBMS_RLS FROM preston;

Managing Privileges to Enable Diagnostics

Only users who have the SYSDBA administrative privilege or the ENABLE_DIAGNOSTICS system privilege can enable diagnostics.

The kinds of diagnostics that you can restrict control of include the following: debug-events (events++, error-numbers) and debug-actions through ALTER SESSION and ALTER SYSTEM operations.

To control the ability of users to perform these types of diagnostics, set the DIAGNOSTICS_CONTROL initialization parameter in the initialization file. DIAGNOSTICS_CONTROL values are as follows: