Skip Headers
Oracle® Database Security Guide
11g Release 2 (11.2)

Part Number E10574-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

4 Configuring Privilege and Role Authorization

This chapter contains:

About Privileges and Roles

Authorization includes primarily two processes:

A user privilege is the right to run a particular type of SQL statement, or the right to access an object that belongs to another user, run a PL/SQL package, and so on. The types of privileges are defined by Oracle Database.

Roles are created by users (usually administrators) to group together privileges or other roles. They are a way to facilitate the granting of multiple privileges or roles to users.

This section describes the following general categories:

Who Should Be Granted Privileges?

You grant privileges to users so they can accomplish tasks required for their jobs. You should grant a privilege only to a user who requires that privilege to accomplish the necessary work. Excessive granting of unnecessary privileges can compromise security. For example, you never should grant SYSDBA or SYSOPER privilege to users who do not perform administrative tasks.

A user can receive a privilege in two ways:

Because roles allow for easier and better management of privileges, you should usually grant privileges to roles and not to specific users.

See Also:

Managing System Privileges

This section contains:

About System Privileges

A system privilege is the right to perform a particular action or to perform an action on any schema objects of a particular type. For example, the privileges to create tablespaces and to delete the rows of any table in a database are system privileges.

There are over 100 distinct 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. You can find a complete list of system privileges and their descriptions in Oracle Database SQL Language Reference. To find the system privileges that have been granted to a user, you can query the DBA_SYS_PRIVS data dictionary view.

Why Is It Important to Restrict System Privileges?

Because system privileges are so powerful, by default the database is configured to prevent typical (non-administrative) users from exercising the ANY system privileges (such as UPDATE ANY TABLE) on the data dictionary. See "Guidelines for Securing User Accounts and Privileges" for additional guidelines about restricting system privileges.

Restricting System Privileges by Securing the Data Dictionary

To secure the data dictionary, set the O7_DICTIONARY_ACCESSIBILITY initialization parameter to FALSE, which is the default value. This feature is called the dictionary protection mechanism.

The O7_DICTIONARY_ACCESSIBILITY initialization parameter controls restrictions on system privileges when you upgrade from Oracle Database release 7 to Oracle8i and later releases. If the parameter is set to TRUE, then access to objects in the SYS schema is allowed (Oracle Database release 7 behavior). Because the ANY privilege applies to the data dictionary, a malicious user with ANY privilege could access or alter data dictionary tables.

To set the O7_DICTIONARY_ACCESSIBILTY initialization parameter, modify it in the initSID.ora file. Alternatively, you can log on to SQL*Plus as user SYS with the SYSDBA privilege and then enter an ALTER SYSTEM statement, assuming you have started the database using a server parameter file (SPFILE).

Example 4-1 shows how to set the O7_DICTIONARY_ACCESSIBILTY initialization parameter to FALSE by issuing an ALTER SYSTEM statement in SQL*Plus.

Example 4-1 Setting O7_DICTIONARY_ACCESSIBILITY to FALSE

ALTER SYSTEM SET O7_DICTIONARY_ACCESSIBILITY=FALSE SCOPE=SPFILE;

When you set O7_DICTIONARY_ACCESSIBILITY to FALSE, system privileges that enable access to objects in any schema (for example, users who have ANY privileges, such as CREATE ANY PROCEDURE) do not allow access to objects in the SYS schema. This means that access to the objects in the SYS schema (data dictionary objects) is restricted to users who connect using the SYSDBA privilege. Remember that the SYS user must log in with either the SYSDBA or SYSOPER privilege; otherwise, an ORA-28009: connection as SYS should be as SYSDBA or SYSOPER error is raised. If you set O7_DICTIONARY_ACCESSIBILITY to TRUE, then you would be able to log in to the database as user SYS without having to specify the SYSDBA or SYSOPER privilege.

System privileges that provide access to objects in other schemas do not give other users access to objects in the SYS schema. For example, the SELECT ANY TABLE privilege allows users to access views and tables in other schemas, but does not enable them to select dictionary objects (base tables of dynamic performance views, regular views, packages, and synonyms). You can, however, grant these users explicit object privileges to access objects in the SYS schema.

See Oracle Database Reference for more information about the O7_DICTIONARY_ACCESSIBILITY initialization parameter.

Allowing 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.

Table 4-1 lists roles that you can grant to users who need access to objects in the SYS schema.

Table 4-1 Roles to Allow Access to SYS Schema Objects

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.

DELETE_CATALOG_ROLE

Grant this role to allow users to delete records from the system audit tables SYS.AUD$ and SYS.FGA_LOG$.


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.

Caution:

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.

Granting and Revoking 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 the separation of duty guidelines described in "Guidelines for Securing Roles".

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

  • GRANT and REVOKE SQL statements

  • Oracle Enterprise Manager Database Control

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:

  • Users who were granted a specific system privilege with the ADMIN OPTION

  • Users with the system privilege GRANT ANY PRIVILEGE

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

About ANY and PUBLIC Privileges

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 allows 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 MALCOEUR 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 MALCOEUR created is running as him (JONES). If JONES has DBA privileges, letting MALCOEUR run a procedure as JONES could pose a security violation.

You can grant privileges to the PUBLIC role, which then makes the privileges available to every user in the Oracle database. Be careful about granting privileges to the PUBLIC role, particularly powerful privileges such as the ANY privileges and system privileges. For example, if MALCOEUR has the CREATE PUBLIC SYNONYM privilege, he could redefine an interface that he knows everyone else uses, and then point to it with the PUBLIC SYNONYM that he created. Instead of accessing the correct interface, users would access the interface of MALCOEUR, 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.

To protect the data dictionary (the contents of the SYS schema) against users who have one or more of the powerful ANY system privileges, set the O7_DICTIONARY_ACCESSIBILITY initialization parameter to FALSE. You can set this parameter by using an ALTER SYSTEM statement (see Example 4-1, "Setting O7_DICTIONARY_ACCESSIBILITY to FALSE") or by modifying the initSID.ora file. See "Guidelines for Securing a Database Installation and Configuration" for additional guidelines.

Managing User Roles

This section contains:

About User Roles

Managing and controlling privileges is easier when you use roles, which are named groups of related privileges that you grant as a group to users or other roles. Within a database, each role name must be unique, different from all user names and all other role names. Unlike schema objects, roles are not contained in any schema. Therefore, a user who creates a role can be dropped with no effect on the role.

This section contains:

The Functionality of Roles

Roles are useful for quickly and easily granting permissions to users. Although you can use Oracle Database-defined roles, you have more control and continuity if you create your own roles that contain only the privileges pertaining to your requirements. Oracle may change or remove the privileges in an Oracle Database-defined role, as it has with the CONNECT role, which now has only the CREATE SESSION privilege. Formerly, this role had eight other privileges.

Roles have the following functionality:

  • A role can be granted system or object privileges.

  • Any role can be granted to any database user.

  • Each role granted to a user is, at a given time, either enabled or disabled. A user's security domain includes the privileges of all roles currently enabled for the user and excludes the privileges of any roles currently disabled for the user. Oracle Database allows database applications and users to enable and disable roles to provide selective availability of privileges.

  • A role can be granted to other roles. However, a role cannot be granted to itself and cannot be granted circularly. For example, role role1 cannot be granted to role role2 if role role2 has previously been granted to role role1.

  • If a role is not password authenticated or a secure application role, then you can grant the role indirectly to the user. An indirectly granted role is a role granted to the user through another role that has already been granted to this user. For example, suppose you grant user psmith the role1 role. Then you grant the role2 and role3 roles to the role1 role. Roles role2 and role3 are now under role1. This means psmith has been indirectly granted the roles role2 and role3, in addition to the direct grant of role1. Enabling the direct role1 for psmith enables the indirect roles role2 and role3 for this user as well.

  • Optionally, you can make a directly granted role a default role. You enable or disable the default role status of a directly granted role by using the DEFAULT ROLE clause of the ALTER USER statement. Ensure that the DEFAULT ROLE clause refers only to roles that have been directly granted to the user. To find the directly granted roles for a user, query the DBA_ROLE_PRIVS data dictionary view. This view does not include the user's indirectly granted roles. To find roles that are granted to other roles, query the ROLE_ROLE_PRIVS view.

  • If the role is password authenticated or a secure application role, then you cannot grant it indirectly to the user, nor can you make it a default role. You only can grant this type of role directly to the user. Typically, you enable password authenticated or secure application roles by using the SET ROLE statement.

Properties of Roles and Why They Are Advantageous

Table 4-2 describes the properties of roles that enable easier privilege management within a database.

Table 4-2 Properties of Roles and Their Description

Property Description

Reduced privilege administration

Rather than granting the same set of privileges explicitly to several users, you can grant the privileges for a group of related users to a role, and then only the role must be granted to each member of the group.

Dynamic privilege management

If the privileges of a group must change, then only the privileges of the role need to be modified. The security domains of all users granted the group's role automatically reflect the changes made to the role.

Selective availability of privileges

You can selectively enable or disable the roles granted to a user. This allows specific control of a user's privileges in any given situation.

Application awareness

The data dictionary records which roles exist, so you can design applications to query the dictionary and automatically enable (or disable) selective roles when a user attempts to execute the application by way of a given user name.

Application-specific security

You can protect role use with a password. Applications can be created specifically to enable a role when supplied the correct password. Users cannot enable the role if they do not know the password.


Database administrators often create roles for a database application. You should grant a secure application role all privileges necessary to run the application. You then can grant the secure application role to other roles or users. An application can have several different roles, each granted a different set of privileges that allow for more or less data access while using the application.

The DBA can create a role with a password to prevent unauthorized use of the privileges granted to the role. Typically, an application is designed so that when it starts, it enables the proper role. As a result, an application user does not need to know the password for an application role.

See Also:

"How Roles Aid or Restrict DDL Usage" for information about restrictions for procedures

Common Uses of Roles

In general, you create a role to serve one of two purposes:

Figure 4-1 and the sections that follow describe the two uses of roles.

Figure 4-1 Common Uses for Roles

Common Uses for Roles
Description of "Figure 4-1 Common Uses for Roles"

Common Uses of Application Roles

Grant an application role all privileges necessary to run a given database application. Then, grant the secure application role to other roles or to specific users. An application can have several different roles, with each role assigned a different set of privileges that allow for more or less data access while using the application.

Common Uses of User Roles

Create a user role for a group of database users with common privilege requirements. You can manage user privileges by granting secure application roles and privileges to the user role and then granting the user role to appropriate users.

How Roles Affect the Scope of a User's Privileges

Each role and user has its own unique security domain. The security domain of a role includes the privileges granted to the role plus those privileges granted to any roles that are granted to the role.

The security domain of a user includes privileges on all schema objects in the corresponding schema, the privileges granted to the user, and the privileges of roles granted to the user that are currently enabled. (A role can be simultaneously enabled for one user and disabled for another.) This domain also includes the privileges and roles granted to the user group PUBLIC. The PUBLIC user group represents all users in the database.

How Roles Work in PL/SQL Blocks

The use of roles in a PL/SQL block depends on whether it is an anonymous block or a named block (stored procedure, function, or trigger), and whether it executes with definer's rights or invoker's rights.

Roles Used in Named Blocks with Definer's Rights

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.

The SESSION_ROLES view shows all roles that are currently enabled. If a named PL/SQL block that executes with definer's rights queries SESSION_ROLES, then the query does not return any rows.

Roles Used in Named Blocks with Invoker's Rights and Anonymous PL/SQL Blocks

Named PL/SQL blocks that execute with invoker's rights and anonymous PL/SQL blocks are executed based on privileges granted through enabled roles. Current roles are used for privilege checking within an invoker's rights PL/SQL block. You can use dynamic SQL to set a role in the session.

See Also:

How Roles Aid or Restrict DDL Usage

A user requires one or more privileges to successfully execute a DDL statement, depending on the statement. For example, to create a table, the user must have the CREATE TABLE or CREATE ANY TABLE system privilege. To create a view of a table that belongs to another user, the creator requires the CREATE VIEW or CREATE ANY VIEW system privilege and either the SELECT object privilege for the table or the SELECT ANY TABLE system privilege.

Oracle Database avoids the dependencies on privileges received by way of roles by restricting the use of specific privileges in certain DDL statements. The following rules describe these privilege restrictions concerning DDL statements:

  • All system privileges and object privileges that permit a user to perform a DDL operation are usable when received through a role. For example:

    • System privileges: CREATE TABLE, CREATE VIEW, and CREATE PROCEDURE privileges

    • Object privileges: ALTER and INDEX privileges for a table

      You cannot use the REFERENCES object privilege for a table to define the foreign key of a table if the privilege is received through a role.

  • All system privileges and object privileges that allow a user to perform a DML operation that is required to issue a DDL statement are not usable when received through a role. The security domain does not contain roles when a CREATE VIEW statement is used. For example, a user who is granted the SELECT ANY TABLE system privilege or the SELECT object privilege for a table through a role cannot use either of these privileges to create a view on a table that belongs to another user. This is because views are definer's rights objects, so when creating them you cannot use any privileges (neither system privileges or object privileges) granted to you through a role. If the privilege is granted directly to you, then you can use the privilege. However, if the privilege is revoked at a later time, then the view definition becomes invalid ("contains errors") and must recompiled before it can be used again.

The following example further clarifies the permitted and restricted uses of privileges received through roles.

Assume that a user is:

  • Granted a role that has the CREATE VIEW system privilege

  • Directly granted a role that has the SELECT object privilege for the employees table

  • Directly granted the SELECT object privilege for the departments table

Given these directly and indirectly granted privileges:

  • The user can issue SELECT statements on both the employees and departments tables.

  • Although the user has both the CREATE VIEW and SELECT privilege for the employees table through a role, the user cannot create a view on the employees table, because the SELECT object privilege for the employees table was granted through a role.

  • The user can create a view on the departments table, because the user has the CREATE VIEW privilege through a role and the SELECT privilege for the departments table directly.

How Operating Systems Can Aid Roles

In some environments, you can administer database security using the operating system. The operating system can be used to grant and revoke database roles and to manage their password authentication. This capability is not available on all operating systems.

See Also:

Your operating system-specific Oracle Database documentation for details about managing roles through the operating system

How Roles Work in a Distributed Environment

When you use roles in a distributed database environment, ensure that all needed roles are set as the default roles for a distributed (remote) session. These roles cannot be enabled when the user connects to a remote database from within a local database session. For example, the user cannot execute a remote procedure that attempts to enable a role at the remote site.

Predefined Roles in an Oracle Database Installation

Oracle Database provides a set of predefined roles to help in database administration. These roles, listed in Table 4-3, are automatically defined for Oracle databases when you run the standard scripts that are part of database creation. If you install other options or products, then other predefined roles may be created. You can grant privileges and roles to, and revoke privileges and roles from, these predefined roles in the same way as you do with any role you define.

Table 4-3 Oracle Database Predefined Roles

Predefined Role Description

ADM_PARALLEL_EXECUTE_TASK

Provides privileges to update table data in parallel by using the DBMS_PARALLEL_EXECUTE PL/SQL package.

See Also: Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_PARALLEL_EXECUTE PL/SQL package.

AQ_ADMINISTRATOR_ROLE

Provides privileges to administer Advanced Queuing. Includes ENQUEUE ANY QUEUE, DEQUEUE ANY QUEUE, and MANAGE ANY QUEUE, SELECT privileges on Advanced Queuing tables and EXECUTE privileges on Advanced Queuing packages.

AQ_USER_ROLE

Obsolete, but kept mainly for release 8.0 compatibility. Provides EXECUTE privileges on the DBMS_AQ and DBMS_AQIN packages.

AUTHENTICATEDUSER

Used by the XDB protocols to define any user who has logged in to the system.

CAPI_USER_ROLE

Provides access to packages used for implementing Information Lifecycle Management (ILM) and hierarchical storage and other applications.

See Also: Oracle Database SecureFiles and Large Objects Developer's Guide

CONNECT

Provides the CREATE SESSION system privilege.

This role is provided for compatibility with previous releases of Oracle Database. You can determine the privileges encompassed by this role by querying the DBA_SYS_PRIVS data dictionary view.

Note: Oracle recommends that you design your own roles for database security rather than relying on this role. This role may not be created automatically by future releases of Oracle Database.

See Also: Oracle Database Reference for a description of the DBA_SYS_PRIVS view

CSW_USR_ROLE

Provides user privileges to manage the Catalog Services for the Web (CSW) component of Oracle Spatial.

See Also: Oracle Spatial Developer's Guide for more information

CTXAPP

Provides privileges to create Oracle Text indexes and index preferences, and to use PL/SQL packages. This role should be granted to Oracle Text users.

See Also: Oracle Text Application Developer's Guide for more information

CWM_USER

Provides privileges to manage Common Warehouse Metadata (CWM), which is a repository standard used by Oracle data warehousing and decision support.

See Also: Oracle Database Data Warehousing Guide for more information

DATAPUMP_EXP_FULL_DATABASE

Provides privileges to export data from an Oracle database using Oracle Data Pump.

Caution: This is a very powerful role because it provides a user access to any data in any schema in the database. Use caution when granting this role to users.

See Also: Oracle Database Utilities for more information

DATAPUMP_IMP_FULL_DATABASE

Provides privileges to import data into an Oracle database using Oracle Data Pump.

Caution: This is a very powerful role because it provides a user access to any data in any schema in the database. Use caution when granting this role to users.

See Also: Oracle Database Utilities for more information

DBA

Provides all system privileges WITH ADMIN OPTION.

This role is provided for compatibility with previous releases of Oracle Database. You can determine the privileges encompassed by this role by querying the DBA_SYS_PRIVS data dictionary view.

Note: Oracle recommends that you design your own roles for database security rather than relying on this role. This role may not be created automatically by future releases of Oracle Database.

See Also: Oracle Database Reference for a description of the DBA_SYS_PRIVS view

DELETE_CATALOG_ROLE

Provides the DELETE privilege on the system audit table (AUD$).

EJBCLIENT

Provides privileges to connect to EJBs from a Java stored procedure.

EXECUTE_CATALOG_ROLE

Provides EXECUTE privileges on objects in the data dictionary.

EXP_FULL_DATABASE

Provides the privileges required to perform full and incremental database exports using the Export utility (later replaced with Oracle Data Pump). It includes these privileges: SELECT ANY TABLE, BACKUP ANY TABLE, EXECUTE ANY PROCEDURE, EXECUTE ANY TYPE, ADMINISTER RESOURCE MANAGER, and INSERT, DELETE, and UPDATE on the tables SYS.INCVID, SYS.INCFIL, and SYS.INCEXP. Also the following roles: EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE.

This role is provided for convenience in using the export and import utilities.

Caution: This is a very powerful role because it provides a user access to any data in any schema in the database. Use caution when granting this role to users.

See Also: Oracle Database Utilities for more information

GATHER_SYSTEM_STATISTICS

Provides privileges to update system statistics, which are collected using the DBMS_STATS.GATHER_SYSTEM_STATISTICS procedure

See Also: Oracle Database Performance Tuning Guide for more information about managing optimizer statistics

GLOBAL_AQ_USER_ROLE

Provides privileges to establish a connection to an LDAP server, for use with Oracle Streams AQ.

See Also: Oracle Streams Advanced Queuing User's Guide for more information

HS_ADMIN_EXECUTE_ROLE

Provides the EXECUTE privilege for users who want to use the Heterogeneous Services (HS) PL/SQL packages.

See Also: Oracle Database Heterogeneous Connectivity Administrator's Guide for more information

HS_ADMIN_ROLE

Provides privileges to both use the Heterogeneous Services (HS) PL/SQL packages and query the HS-related data dictionary views.

See Also: Oracle Database Heterogeneous Connectivity Administrator's Guide for more information

HS_ADMIN_SELECT_ROLE

Provides privileges to query the Heterogeneous Services data dictionary views.

See Also: Oracle Database Heterogeneous Connectivity Administrator's Guide for more information

IMP_FULL_DATABASE

Provides the privileges required to perform full database imports using the Import utility (later replaced with Oracle Data Pump). Includes an extensive list of system privileges (use view DBA_SYS_PRIVS to view privileges) and the following roles: EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE.

This role is provided for convenience in using the export and import utilities.

Caution: This is a very powerful role because it provides a user access to any data in any schema in the database. Use caution when granting this role to users.s.

See Also: Oracle Database Utilities for more information

JAVADEBUGPRIV

Provides privileges to run the Oracle Database Java applications debugger.

See Also: Oracle Database Java Developer's Guide for more information about managing security for Oracle Java applications

JAVAIDPRIV

Deprecated for this release.

JAVASYSPRIV

Provides major permissions to use Java2, including updating Oracle JVM-protected packages.

See Also: Oracle Database Java Developer's Guide for more information about managing security for Oracle Java applications

JAVAUSERPRIV

Provides limited permissions to use Java2.

See Also: Oracle Database Java Developer's Guide for more information about managing security for Oracle Java applications

JAVA_ADMIN

Provides administrative permissions to update policy tables for Oracle Database Java applications.

See Also: Oracle Database Java Developer's Guide for more information about managing security for Oracle Java applications

JAVA_DEPLOY

Provides privileges to deploy ncomp DLLs into the javavm/admin directory using the ncomp and deployns utilities. Without this role, the javavm/deploy and javavm/admin directories can be accessible.

See Also: Oracle Database Advanced Application Developer's Guide for more information

JMXSERVER

Provides privileges to start and maintain a JMX agent in a database session.

See Also: Oracle Database Java Developer's Guide for more information about managing Oracle Java applications

LBAC_DBA

Provides permissions to use the SA_SYSDBA PL/SQL package.

See Also: Oracle Label Security Administrator's Guide for more information

LOGSTDBY_ADMINISTRATOR

Provides administrative privileges to manage the SQL Apply (logical standby database) environment.

See Also: Oracle Data Guard Concepts and Administration for more information

MGMT_USER

Grants the SELECT privilege on the different views used for the SYSMAN schema.

OEM_ADVISOR

Provides privileges to create, drop, select (read), load (write), and delete a SQL tuning set through the DBMS_SQLTUNE PL/SQL package, and to access to the Advisor framework using the ADVISOR PL/SQL package.

See Also: Oracle Database Performance Tuning Guide for more information

OEM_MONITOR

Provides privileges needed by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database.

See Also: Oracle Database Performance Tuning Guide for more information

OLAPI_TRACE_USER

Provides privileges to perform OLAP API tracing. Contact Oracle Support for more information.

OLAP_DBA

Provides administrative privileges to create dimensional objects in different schemas for Oracle OLAP.

See Also: Oracle OLAP User's Guide for more information

OLAP_USER

Provides application developers privileges to create dimensional objects in their own schemas for Oracle OLAP.

See Also: Oracle OLAP User's Guide for more information

OLAP_XS_ADMIN

Provides privileges to administer security for Oracle OLAP.

See Also: Oracle OLAP User's Guide for more information

ORDADMIN

Provides privileges to administer Oracle Multimedia DICOM.

See Also: Oracle Multimedia DICOM Developer's Guide

OWB$CLIENT

Provides privileges to perform standard client-related tasks for Oracle Warehouse Builder, such as creating projects, modules, tables, views, maps, and so on. Warehouse Builder automatically grants this role to all workspace owners and users. (That is, you do not need to explicitly grant it to anyone who must use Warehouse Builder.) For security reasons, the OWB$CLIENT role is not a default role for Warehouse Builder users: Oracle Warehouse Builder enables this role only when it is needed.

See Also: Oracle Warehouse Builder User's Guide for more information

OWB_DESIGNCENTER_VIEW

Provides privileges from the database level for any registered Oracle Warehouse Builder user to query the Warehouse Builder public views, such as ALL_IV_PROJECTS. A Warehouse Builder administrator can use the ACCESS_PUBLICVIEW_BROWSER system privilege from the Warehouse Builder security level to control an Warehouse Builder user's access to those public views.

See Also: Oracle Warehouse Builder User's Guide for more information

OWB_USER

Provides privileges to create and own an Oracle Warehouse Builder workspace. When a workspace owner registers other database users to this workspace, Oracle Database grants this role to these users. Users with this role also have access to Warehouse Builder Control Center public views and other Control Center utilities. Oracle Warehouse Builder grants this role to all Warehouse Builder users.

See Also: Oracle Warehouse Builder User's Guide for more information

RECOVERY_CATALOG_OWNER

Provides privileges for owner of the recovery catalog. Includes: CREATE SESSION, ALTER SESSION, CREATE SYNONYM, CREATE VIEW, CREATE DATABASE LINK, CREATE TABLE, CREATE CLUSTER, CREATE SEQUENCE, CREATE TRIGGER, and CREATE PROCEDURE

RESOURCE

Provides the following system privileges: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE.

This role is provided for compatibility with previous releases of Oracle Database. You can determine the privileges encompassed by this role by querying the DBA_SYS_PRIVS data dictionary view.

Note: Oracle recommends that you design your own roles for database security rather than relying on this role. This role may not be created automatically by future releases of Oracle Database.

See Also: Oracle Database Reference for a description of the DBA_SYS_PRIVS view

SCHEDULER_ADMIN

Allows the grantee to execute the procedures of the DBMS_SCHEDULER package. It includes all of the job scheduler system privileges and is included in the DBA role.

See Also: Oracle Database Administrator's Guide for more information about the DBMS_SCHEDULER package

SELECT_CATALOG_ROLE

Provides SELECT privilege on objects in the data dictionary.

SNMPAGENT

Used by the Enterprise Manager Management Agent.

SPATIAL_CSW_ADMIN

Provides administrative privileges to manage the Catalog Services for the Web (CSW) component of Oracle Spatial.

See Also: Oracle Spatial Developer's Guide for more information

SPATIAL_WFS_ADMIN

Provides administrative privileges to manage the Web Feature Service (WFS) component of Oracle Spatial.

See Also: Oracle Spatial Developer's Guide for more information

WFS_USR_ROLE

Provides user privileges for the Web Feature Service (WFS) component of Oracle Spatial.

See Also: Oracle Spatial Developer's Guide for more information

WM_ADMIN_ROLE

Provides administrative privileges for Oracle Workspace Manage. This enables users to run any DBMS_WM procedures on all version enabled tables, workspaces, and savepoints regardless of their owner. It also enables the user to modify the system parameters specific to Workspace Manager.

See Also: Oracle Database Workspace Manager Developer's Guide for more information

XDBADMIN

Allows the grantee to register an XML schema globally, as opposed to registering it for use or access only by its owner. It also lets the grantee bypass access control list (ACL) checks when accessing Oracle XML DB Repository.

See Also: Oracle XML DB Developer's Guide for information about XML schemas and the XML DB Repository

XDB_SET_INVOKER

Allows the grantee to define invoker's rights handlers and to create or update the resource configuration for XML repository triggers. By default, Oracle Database grants this role to the DBA role but not to the XDBADMIN role.

See Also: Oracle XML DB Developer's Guide for information about Oracle Database XML repository triggers

XDB_WEBSERVICES

Allows the grantee to access Oracle Database Web services over HTTPS. However, it does not provide the user access to objects in the database that are public. To allow public access, you need to grant the user the XDB_WEBSERVICES_WITH_PUBLIC role. For a user to use these Web services, SYS must enable the Web service servlets.

See Also: Oracle XML DB Developer's Guide for information about Oracle Database Web services

XDB_WEBSERVICES_OVER_HTTP

Allows the grantee to access Oracle Database Web services over HTTP. However, it does not provide the user access to objects in the database that are public. To allow public access, you need to grant the user the XDB_WEBSERVICES_WITH_PUBLIC role.

See Also: Oracle XML DB Developer's Guide for information about Oracle Database Web services

XDB_WEBSERVICES_WITH_PUBLIC

Allows the grantee access to public objects through Oracle Database Web services.

See Also: Oracle XML DB Developer's Guide for information about Oracle Database Web services


Note:

Each installation should create its own roles and assign only those privileges that are needed, thus retaining detailed control of the privileges in use. This process also removes any need to adjust existing roles, privileges, or procedures whenever Oracle Database changes or removes roles that Oracle Database defines. For example, the CONNECT role now has only one privilege: CREATE SESSION. Both CONNECT and RESOURCE roles will be deprecated in future Oracle Database releases.

Creating a Role

You can create a role using the CREATE ROLE statement, but you must have the CREATE ROLE system privilege to do so. Typically, only security administrators have this system privilege.

Note:

Immediately after creation, a role has no privileges associated with it. To associate privileges with a new role, you must grant privileges or other roles to the new role.

You must give each role you create a unique name among existing user names and role names of the database. Roles are not contained in the schema of any user. In a database that uses a multibyte character set, Oracle recommends that each role name contain at least one single-byte character. If a role name contains only multibyte characters, then the encrypted role name and password combination is considerably less secure. See Guideline 1 in "Guidelines for Securing Passwords" for password guidelines.

Example 4-2 creates the clerk role.

Example 4-2 Creating a User Role Authorized by a Password

CREATE ROLE clerk IDENTIFIED BY password;

The IDENTIFIED BY clause specifies how the user must be authorized before the role can be enabled for use by a specific user to which it has been granted. If you do not specify this clause, or if you specify NOT IDENTIFIED, then no authorization is required when the role is enabled. Roles can be specified to be authorized by:

  • The database using a password

  • An application using a specified package

  • Externally by the operating system, network, or other external source

  • Globally by an enterprise directory service

These authorizations are discussed in the following sections.

You can set or change the authorization method for a role using the ALTER ROLE statement. Remember that you can only directly grant secure application roles or password-authenticated roles to a user.

Example 4-3 shows how to alter the clerk role to specify that the user must have been authorized by an external source before enabling the role.

Example 4-3 Altering a Role to be Authorized by an External Source

ALTER ROLE clerk IDENTIFIED EXTERNALLY;

To alter the authorization method for a role, you must have the ALTER ANY ROLE system privilege or have been granted the role with the ADMIN OPTION.

See Also:

Oracle Database SQL Language Reference for syntax, restrictions, and authorization information about the SQL statements used to manage roles and privileges

Specifying the Type of Role Authorization

The methods of authorizing roles are presented in this section. A role must be enabled for you to use it.

This section contains:

See Also:

"When Do Grants and Revokes Take Effect?" for a discussion about enabling roles

Authorizing a Role by Using the Database

You can protect a role authorized by the database by assigning the role a password. If a user is granted a role protected by a password, then you can enable or disable the role by supplying the proper password for the role in the SET ROLE statement. You cannot authenticate a password-authenticated role on logon, even if you add it to the list of default roles. You must explicitly enable it with the SET ROLE statement using the required password.

Example 4-4 shows how to set a password-authenticated role by using the SET ROLE statement.

Example 4-4 Using SET ROLE for a Password-Authenticated Role

SET ROLE clerk IDENTIFIED BY password;

Example 4-2, "Creating a User Role Authorized by a Password" shows a CREATE ROLE statement that creates a role called clerk. When it is enabled, the password must be supplied.

Note:

In a database that uses a multibyte character set, passwords for roles must include only single-byte characters. Multibyte characters are not accepted in passwords. See Guideline 1 in "Guidelines for Securing Passwords" for password guidelines.

Authorizing a Role by Using an Application

An application role (secure application role) can be enabled only by applications using an authorized PL/SQL package. Application developers do not need to secure a role by embedding passwords inside applications. Instead, they can create an application role and specify which PL/SQL package is authorized to enable the role.

To create a role enabled by an authorized PL/SQL package, use the IDENTIFIED USING package_name clause in the CREATE ROLE SQL statement.

Example 4-5 indicates that the role admin_role is an application role and the role can only be enabled by any module defined inside the PL/SQL package hr.admin.

Example 4-5 Creating a Role Authorized by a PL/SQL Package for an Application

CREATE ROLE admin_role IDENTIFIED USING hr.admin;

See the following for more information about secure application roles:

Authorizing a Role by Using an External Source

You can define the external role locally in the database, but you cannot grant the external role to global users, to global roles, or to any other roles in the database. You can create roles that are authorized by the operating system or network clients.

Example 4-6 creates a role named accts_rec and requires that the user is authorized by an external source before it can be enabled:

Example 4-6 Creating a Role Authorized by an External Source

CREATE ROLE accts_rec IDENTIFIED EXTERNALLY;
Authorizing a Role by Using the Operating System

Role authentication through the operating system is useful only when the operating system is able to dynamically link operating system privileges with applications. When a user starts an application, the operating system grants an operating system privilege to the user. The granted operating system privilege corresponds to the role associated with the application. At this point, the application can enable the application role. When the application is terminated, the previously granted operating system privilege is revoked from the operating system account of the user.

If a role is authorized by the operating system, then you must configure information for each user at the operating system level. This operation is operating system dependent.

If roles are granted by the operating system, then you do not need to have the operating system authorize them also.

See Also:

"Granting Roles Using the Operating System or Network" for more information about roles granted by the operating system
Authorizing a Role by Using a Network Client

If users connect to the database over Oracle Net, then by default, their roles cannot be authenticated by the operating system. This includes connections through a shared server configuration, as this connection requires Oracle Net. This restriction is the default because a remote user could impersonate another operating system user over a network connection. Oracle recommends that you set REMOTE_OS_ROLES to FALSE, which is the default.

If you are not concerned with this security risk and want to use operating system role authentication for network clients, then set the initialization parameter REMOTE_OS_ROLES in the database initialization parameter file to TRUE. The change will take effect the next time you start the instance and mount the database.

Global Role Authorization by an Enterprise Directory Service

A role can be defined as a global role, where a (global) user can only be authorized to use the role by an enterprise directory service. You define the global role locally in the database by granting privileges and roles to it, but you cannot grant the global role itself to any user or other role in the database. When a global user attempts to connect to the database, the enterprise directory is queried to obtain any global roles associated with the user.

Example 4-7 creates a global role.

Example 4-7 Creating a Global Role

CREATE ROLE supervisor IDENTIFIED GLOBALLY;

Global roles are one component of enterprise user security. A global role only applies to one database, but you can grant it to an enterprise role defined in the enterprise directory. An enterprise role is a directory structure that contains global roles on multiple databases and can be granted to enterprise users.

See "Configuring Global User Authentication and Authorization" for a general discussion of global authentication and authorization of users, and its role in enterprise user management.

See Also:

Oracle Database Enterprise User Security Administrator's Guide for information about implementing enterprise user management

Granting and Revoking Roles

You can grant system or object privileges to a role, and any role can be granted to any database user or to another role (but not to itself). However, a role cannot be granted circularly, that is, role X cannot be granted to role Y if role Y has previously been granted to role X.

To provide selective availability of privileges, Oracle Database permits applications and users to enable and disable roles. Each role granted to a user is, at any given time, either enabled or disabled. The security domain of a user includes the privileges of all roles currently enabled for the user and excludes the privileges of any roles currently disabled for the user.

A role granted to a role is called an indirectly granted role. You can explicitly enable or disable it for a user. However, whenever you enable a role that contains other roles, you implicitly enable all indirectly granted roles of the directly granted role.

You grant roles to (or revoke roles from) users or other roles by using either of the following methods:

  • Oracle Enterprise Manager Database Control

  • The GRANT and REVOKE SQL statements

Privileges are granted to and revoked from roles using the same options.

Who Can Grant or Revoke Roles?

Any user with the GRANT ANY ROLE system privilege can grant or revoke any role except a global role to or from other users or roles of the database. (A global role is managed in a directory, such as Oracle Internet Directory, but its privileges are contained within a single database.) By default, the SYS or SYSTEM user has this privilege. You should grant this system privilege conservatively because it is very powerful.

Any user granted a role with the ADMIN OPTION can grant or revoke that role to or from other users or roles of the database. This option allows administrative powers for roles to be granted on a selective basis.

See Also:

Oracle Database Enterprise User Security Administrator's Guide for information about global roles

Dropping Roles

In some cases, it may be appropriate to drop a role from the database. The security domains of all users and roles granted a dropped role are immediately changed to reflect the absence of the dropped role privileges. All indirectly granted roles of the dropped role are also removed from affected security domains. Dropping a role automatically removes the role from all user default role lists.

Because the existence of objects is not dependent on the privileges received through a role, tables and other objects are not dropped when a role is dropped.

You can drop a role using the SQL statement DROP ROLE. To drop a role, you must have the DROP ANY ROLE system privilege or have been granted the role with the ADMIN OPTION.

The following statement drops the role CLERK:

DROP ROLE clerk;

Restricting SQL*Plus Users from Using Database Roles

This section describes features that you can use to restrict SQL*Plus users from using database roles and thus, prevent serious security problems.

Potential Security Problems of Using Ad Hoc Tools

Prebuilt database applications explicitly control the potential actions of a user, including the enabling and disabling of user roles while using the application. By contrast, ad hoc query tools such as SQL*Plus, permit a user to submit any SQL statement (which may or may not succeed), including enabling and disabling a granted role.

Potentially, an application user can exercise the privileges attached to that application to issue destructive SQL statements against database tables by using an ad hoc tool.

For example, consider the following scenario:

  • The Vacation application has a corresponding vacation role.

  • The vacation role includes the privileges to issue SELECT, INSERT, UPDATE, and DELETE statements against the emp_tab table.

  • The Vacation application controls the use of privileges obtained through the vacation role.

Now, consider a user who has been granted the vacation role. Suppose that, instead of using the Vacation application, the user executes SQL*Plus. At this point, the user is restricted only by the privileges granted to him explicitly or through roles, including the vacation role. Because SQL*Plus is an ad hoc query tool, the user is not restricted to a set of predefined actions, as with designed database applications. The user can query or modify data in the emp_tab table as he or she chooses.

Limiting Roles Through the PRODUCT_USER_PROFILE Table

You can use the PRODUCT_USER_PROFILE table, which is in the SYSTEM schema, to disable certain SQL and SQL*Plus commands in the SQL*Plus environment for each user. SQL*Plus, not the Oracle Database, enforces this security. You can even restrict access to the GRANT, REVOKE, and SET ROLE commands to control user ability to change their database privileges.

The PRODUCT_USER_PROFILE table enables you to list roles that you do not want users to activate with an application. You can also explicitly disable the use of various commands, such as SET ROLE.

For example, you could create an entry in the PRODUCT_USER_PROFILE table to:

  • Disallow the use of the clerk and manager roles with SQL*Plus

  • Disallow the use of SET ROLE with SQL*Plus

Suppose user Marla connects to the database using SQL*Plus. Marla has the clerk, manager, and analyst roles. As a result of the preceding entry in PRODUCT_USER_PROFILE, Marla is only able to exercise her analyst role with SQL*Plus. Also, when Ginny attempts to issue a SET ROLE statement, she is explicitly prevented from doing so because of the entry in the PRODUCT_USER_PROFILE table prohibiting use of SET ROLE.

Be aware that the PRODUCT_USER_PROFILE table does not completely guarantee security, for multiple reasons. In the preceding example, while SET ROLE is disallowed with SQL*Plus, if Marla had other privileges granted to her directly, then she could exercise these using SQL*Plus.

See Also:

SQL*Plus User's Guide and Reference for more information about the PRODUCT_USER_PROFILE table

Using Stored Procedures to Encapsulate Business Logic

Stored procedures encapsulate the use of privileges with business logic so that privileges are only exercised in the context of a well-formed business transaction. For example, an application developer can create a procedure to update the employee name and address in the employees table, which enforces that the data can only be updated in normal business hours. Also, rather than grant a human resources clerk the UPDATE privilege on the employees table, a security administrator may grant the privilege on the procedure only. Then, the human resources clerk can exercise the privilege only in the context of the procedures, and cannot update the employees table directly.

Further Securing Role Privileges by Using Secure Application Roles

A secure application role is a role that can be enabled only by an authorized PL/SQL package (or procedure). The PL/SQL package itself reflects the security policies needed to control access to the application.

This method of role creation restricts the enabling of this type of role to the invoking application. For example, the application can perform authentication and customized authorization, such as checking whether the user has connected through a proxy.

This type of role strengthens security because passwords are not embedded in application source code or stored in a table. This way, the actions the database performs are based on the implementation of your security policies, and these definitions are stored in one place, the database, rather than in your applications. If you need to modify the policy, you do so in one place without having to modify your applications. No matter how users connect to the database, the result is always the same, because the policy is bound to the role.

To enable the secure application role, you must execute its underlying package by invoking it directly from the application when the user logs in, before the user exercises the privileges granted by the secure application role. You cannot use a logon trigger to enable a secure application role, nor can you have this type of role be a default role.

When you enable the secure application role, Oracle Database verifies that the authorized PL/SQL package is on the calling stack, that is, it verifies that the authorized PL/SQL package is issuing the command to enable the role.

You can use secure application roles to ensure the existence of a database connection. Because a secure application role is a role implemented by a package, the package can validate that users can connect to the database through a middle tier or from a specific IP address. In this way, the secure application role prevents users from accessing data outside an application. They are forced to work within the framework of the application privileges that they have been granted.

Managing Object Privileges

This section contains:

About Object Privileges

An object privilege is a right that you grant to a user on a database object. Some examples of object privileges include the right to:

  • Use an edition

  • Update a table

  • Select rows from another user's table

  • Execute a stored procedure of another user

See Also:

Oracle Database SQL Language Reference for a list of object privileges and the operations they authorize

Granting or Revoking Object Privileges

Each type of object has different privileges associated with it.

You can specify ALL [PRIVILEGES] to grant or revoke all available object privileges for an object. ALL is not a privilege; rather, it is a shortcut, or a way of granting or revoking all object privileges with one GRANT and REVOKE statement. If all object privileges are granted using the ALL shortcut, then individual privileges can still be revoked.

Similarly, you can revoke all individually granted privileges by specifying ALL. However, if you REVOKE ALL, and revoking causes integrity constraints to be deleted (because they depend on a REFERENCES privilege that you are revoking), then you must include the CASCADE CONSTRAINTS option in the REVOKE statement.

Example 4-8 revokes all privileges on the orders table in the HR schema using CASCADE CONSTRAINTS.

Example 4-8 Revoking All Object Privileges Using CASCADE CONSTRAINTS

REVOKE ALL 
 ON orders FROM hr
 CASCADE CONSTRAINTS;

Managing Object Privileges

An object privilege grants permission to perform a particular action on a specific schema object.

Different object privileges are available for different types of schema objects. The privilege to delete rows from the departments table is an example of an object privilege.

Some schema objects, such as clusters, indexes, triggers, and database links, do not have associated object privileges. Their use is controlled with system privileges. For example, to alter a cluster, a user must own the cluster or have the ALTER ANY CLUSTER system privilege.

The following sections discuss granting and revoking such privileges:

The following sections discuss object privileges that apply to specific schema objects:

Granting and Revoking Object Privileges

Object privileges can be granted to and revoked from users and roles. If you grant object privileges to roles, then you can make the privileges selectively available.

You can grant or revoke object privileges to or from users and roles using the following methods:

  • The GRANT and REVOKE SQL statements

  • Oracle Enterprise Manager Database Control

See Also:

Oracle Database 2 Day DBA for more information about Database Control

Who Can Grant Object Privileges?

A user automatically has all object privileges for schema objects contained in his or her schema. A user with the GRANT ANY OBJECT PRIVILEGE can grant any specified object privilege to another user with or without the WITH GRANT OPTION clause of the GRANT statement. A user with the GRANT ANY OBJECT PRIVILEGE can also use that privilege to revoke any object privilege that was granted either by the object owner or by some other user with the GRANT ANY OBJECT PRIVILEGE privilege. Otherwise, the grantee can use the privilege, but cannot grant it to other users.

See Also:

Oracle Database SQL Language Reference for information about GRANT and GRANT ANY OBJECT PRIVILEGE

Using Privileges with Synonyms

A schema object and its synonym are equivalent with respect to privileges. That is, the object privileges granted on a table, view, sequence, procedure, function, or package apply whether referencing the base object by name or by using a synonym.

For example, assume there is a table jward.emp with a synonym named jward.employee. The user jward issues the following statement:

GRANT SELECT ON emp TO swilliams; 

The user swilliams can query jward.emp by referencing the table by name or by using the synonym jward.employee:

SELECT * FROM jward.emp; 
SELECT * FROM jward.employee; 

If you grant object privileges on a table, view, sequence, procedure, function, or package by referring to the object through a synonym for the object, then the effect is the same as if no synonym were used. For example, if jward wanted to grant the SELECT privilege for the emp table to swilliams, then jward could issue either of the following statements:

GRANT SELECT ON emp TO swilliams; 
GRANT SELECT ON employee TO swilliams; 

If a synonym is dropped, then all grants for the underlying schema object remain in effect, even if the privileges were granted by specifying the dropped synonym.

Managing Table Privileges

Object privileges for tables enable table security at the DML (data manipulation language) or DDL (data definition language) level of operation.

The following sections discuss table privileges and DML and DDL operations:

How Table Privileges Affect Data Manipulation Language Operations

You can grant privileges to use the DELETE, INSERT, SELECT, and UPDATE DML operations on a table or view. Grant these privileges only to users and roles that need to query or manipulate data in a table.

You can restrict INSERT and UPDATE privileges for a table to specific columns of the table. With a selective INSERT privilege, a privileged user can insert a row with values for the selected columns. All other columns receive NULL or the default value of the column. With a selective UPDATE privilege, a user can update only specific column values of a row. You can use selective INSERT and UPDATE privileges to restrict user access to sensitive data.

For example, if you do not want data entry users to alter the salary column of the employees table, then selective INSERT or UPDATE privileges can be granted that exclude the salary column. Alternatively, a view that excludes the salary column could satisfy this need for additional security.

See Also:

Oracle Database SQL Language Reference for more information about DML operations

How Table Privileges Affect Data Definition Language Operations

The ALTER, INDEX, and REFERENCES privileges allow DDL operations to be performed on a table. Because these privileges allow other users to alter or create dependencies on a table, you should grant these privileges conservatively.

A user attempting to perform a DDL operation on a table may need additional system or object privileges. For example, to create a trigger on a table, the user requires both the ALTER TABLE object privilege for the table and the CREATE TRIGGER system privilege.

As with the INSERT and UPDATE privileges, you can grant the REFERENCES privilege on specific columns of a table. The REFERENCES privilege enables the grantee to use the table on which the grant is made as a parent key to any foreign keys that the grantee wishes to create in his or her own tables. This action is controlled with a special privilege because the presence of foreign keys restricts the data manipulation and table alterations that can be done to the parent key. A column-specific REFERENCES privilege restricts the grantee to using the named columns (which, of course, must include at least one primary or unique key of the parent table).

See Also:

"Data Integrity" in Oracle Database Concepts for more information about primary keys, unique keys, and integrity constraints

Managing View Privileges

This section contains:

About View Privileges

A view is a presentation of data selected from one or more tables, possibly including other views. A view shows the structure of the underlying tables. Its selected data can be thought of as the result of a stored query. A view contains no actual data but rather derives what it shows from the tables and views on which it is based. You can query a view, and change the data it represents. Data in a view can be updated or deleted, and new data inserted. These operations directly alter the tables on which the view is based, and are subject to the integrity constraints and triggers of the base tables.

You can apply DML object privileges to views, similar to tables. Object privileges for a view allow various DML operations, which as noted affect the base tables from which the view is derived.

Privileges Required to Create Views

To create a view, you must meet the following requirements:

  • You must have been granted one of the following system privileges, either explicitly or through a role:

    • The CREATE VIEW system privilege (to create a view in your schema)

    • The CREATE ANY VIEW system privilege (to create a view in the schema of another user)

  • You must have been explicitly granted one of the following privileges:

    • The SELECT, INSERT, UPDATE, or DELETE object privileges on all base objects underlying the view

    • The SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, or DELETE ANY TABLE system privileges

  • In addition, before you can grant other users access to you view, you must have object privileges to the base objects with the GRANT OPTION clause or appropriate system privileges with the ADMIN OPTION clause. If you do not have these privileges, then you cannot to grant other users access to your view. If you try, an ORA-01720: grant option does not exist for object_name error is raised, with object_name referring to the view's underlying object for which you do not have the sufficient privilege.

Increasing Table Security with Views

To use a view, the user must have the appropriate privileges but only for the view itself, not its underlying objects. However, if access privileges for the underlying objects of the view are removed, then the user no longer has access. This behavior occurs because the security domain that is used when a user queries the view is that of the definer of the view. If the privileges on the underlying objects are revoked from the view's definer, then the view becomes invalid, and no one can use the view. Therefore, even if a user has been granted access to the view, the user may not be able to use the view if the definer's rights have been revoked from the view's underlying objects.

For example, suppose User A creates a view. User A has definer's rights on the underlying objects of the view. User A then grants the SELECT privilege on that view to User B so that User B can query the view. But if User A no longer has access to the underlying objects of that view, then User B no longer has access either.

Views add two more levels of security for tables, column-level security and value-based security, as follows:

  • A view can provide access to selected columns of base tables. For example, you can define a view on the employees table to show only the employee_id, last_name, and manager_id columns:

    CREATE VIEW employees_manager AS 
        SELECT last_name, employee_id, manager_id FROM employees; 
    
  • A view can provide value-based security for the information in a table. A WHERE clause in the definition of a view displays only selected rows of base tables. Consider the following two examples:

    CREATE VIEW lowsal AS 
        SELECT * FROM employees 
        WHERE salary < 10000; 
    

    The lowsal view allows access to all rows of the employees table that have a salary value less than 10000. Notice that all columns of the employees table are accessible in the lowsal view.

    CREATE VIEW own_salary AS 
        SELECT last_name, salary 
        FROM employees 
        WHERE last_name = USER; 
    

    In the own_salary view, only the rows with an last_name that matches the current user of the view are accessible. The own_salary view uses the user pseudo column, whose values always refer to the current user. This view combines both column-level security and value-based security.

Managing Procedure Privileges

This section contains:

Using the EXECUTE Privilege for Procedure Privileges

The EXECUTE privilege is the only object privilege for procedures, including standalone procedures and functions, and for those within packages. Grant this privilege only to users who need to run a procedure or to compile another procedure that calls a desired procedure.

Procedure Execution and Security Domains

A user with the EXECUTE object privilege for a specific procedure can execute the procedure or compile a program unit that references the procedure. Oracle Database performs a run-time privilege check when any PL/SQL unit is called. A user with the EXECUTE ANY PROCEDURE system privilege can execute any procedure in the database. Privileges to run procedures can be granted to a user through roles.

See Also:

Oracle Database PL/SQL Language Reference for more information about how Oracle Database checks privileges at run-time

How Procedure Privileges Affect Definer's Rights

The owner of a procedure, called the definer, must have all the necessary object privileges for referenced objects. If the procedure owner grants to another user the right to use that procedure, then the privileges of the procedure owner (on the objects referenced by the procedure) apply to the grantee user's exercise of the procedure. The privileges of the procedure's definer must be granted directly to the user, not granted through roles. These are termed definer's rights.

The user of a procedure who is not its owner is called the invoker. Additional privileges on referenced objects are required for invoker's rights procedures, but not for definer's rights procedures.

A user of a definer's rights procedure requires only the privilege to execute the procedure and no privileges on the underlying objects that the procedure accesses. This is because a definer's rights procedure operates under the security domain of the user who owns the procedure, regardless of who is executing it. The owner of the procedure must have all the necessary object privileges for referenced objects. Fewer privileges have to be granted to users of a definer's rights procedure. This results in stronger control of database access.

You can use definer's rights procedures to control access to private database objects and add a level of database security. By writing a definer's rights procedure and granting only EXECUTE privilege to a user, the user can be forced to access the referenced objects only through the procedure.

At run time, Oracle Database checks whether the privileges of the owner of a definer's rights stored procedure allow access to that procedure's referenced objects, before the procedure is executed. If a necessary privilege on a referenced object was revoked from the owner of a definer's rights procedure, then the procedure cannot be run by the owner or any other user.

Note:

Trigger processing follows the same patterns as definer's rights procedures. The user runs a SQL statement, which that user is privileged to run. As a result of the SQL statement, a trigger is fired. The statements within the triggered action temporarily execute under the security domain of the user that owns the trigger. For more information, see "Overview of Triggers" in Oracle Database Concepts.

How Procedure Privileges Affect Invoker's Rights

An invoker's rights procedure executes with all of the invoker's privileges. Oracle Database enables the privileges that were granted to the invoker through any of the invoker's enabled roles to take effect, unless a definer's rights procedure calls the invoker's rights procedure directly or indirectly. A user of an invoker's rights procedure needs privileges (granted to the user either directly or through a role) on objects that the procedure accesses through external references that are resolved in the schema of the invoker.

The invoker needs privileges at run time to access program references embedded in DML statements or dynamic SQL statements, because they are effectively recompiled at run time.

For all other external references, such as direct PL/SQL function calls, Oracle Database checks the privileges of the owner at compile time, but does not perform a run-time check. Therefore, the user of an invoker's rights procedure does not need privileges on external references outside DML or dynamic SQL statements. Alternatively, the developer of an invoker's rights procedure must only grant privileges on the procedure itself, not on all objects directly referenced by the invoker's rights procedure.

You can create a software bundle that consists of multiple program units, some with definer's rights and others with invoker's rights, and restrict the program entry points (controlled step-in). A user who has the privilege to run an entry-point procedure can also execute internal program units indirectly, but cannot directly call the internal programs. For very precise control over query processing, you can create a PL/SQL package specification with explicit cursors.

See Also:

System Privileges Needed to Create or Alter a Procedure

To create a procedure, a user must have the CREATE PROCEDURE or CREATE ANY PROCEDURE system privilege. To alter a procedure, that is, to manually recompile a procedure, a user must own the procedure or have the ALTER ANY PROCEDURE system privilege.

The user who owns the procedure also must have privileges for schema objects referenced in the procedure body. To create a procedure, you need to have been explicitly granted the necessary privileges (system or object) on all objects referenced by the procedure. You cannot obtain the required privileges through roles. This includes the EXECUTE privilege for any procedures that are called inside the procedure being created.

Note:

Triggers require that privileges on referenced objects be granted directly to the owner of the trigger. Anonymous PL/SQL blocks can use any privilege, whether the privilege is granted explicitly or through a role.

How Procedure Privileges Affect Packages and Package Objects

A user with the EXECUTE object privilege for a package can execute any public procedure or function in the package, and can access or modify the value of any public package variable. You cannot grant specific EXECUTE privileges for individual constructs in a package. Therefore, you may find it useful to consider two alternatives for establishing security when developing procedures, functions, and packages for a database application. The following examples describe these alternatives.

Procedure Privileges and Packages and Package Objects: Example 1

Example 4-9 shows four procedures created in the bodies of two packages.

Example 4-9 Package Objects Affected by Procedure Privileges

CREATE PACKAGE BODY hire_fire AS 
  PROCEDURE hire(...) IS 
    BEGIN 
      INSERT INTO employees . . . 
    END hire; 
  PROCEDURE fire(...) IS 
    BEGIN 
      DELETE FROM employees . . . 
    END fire; 
END hire_fire; 

CREATE PACKAGE BODY raise_bonus AS 
  PROCEDURE give_raise(...) IS 
    BEGIN 
      UPDATE employees SET salary = . . . 
    END give_raise; 
  PROCEDURE give_bonus(...) IS 
    BEGIN 
      UPDATE employees SET bonus = . . . 
    END give_bonus; 
END raise_bonus; 

The following GRANT EXECUTE statements enable the big_bosses and little_bosses roles to run the appropriate procedures:

GRANT EXECUTE ON hire_fire TO big_bosses; 
GRANT EXECUTE ON raise_bonus TO little_bosses; 

Note:

Granting EXECUTE privilege for a package provides uniform access to all package objects.

Procedure Privileges and Packages and Package Objects: Example 2

This example shows four procedure definitions within the body of a single package. Two additional standalone procedures and a package are created specifically to provide access to the procedures defined in the main package.

CREATE PACKAGE BODY employee_changes AS 
  PROCEDURE change_salary(...) IS BEGIN ... END; 
  PROCEDURE change_bonus(...) IS BEGIN ... END; 
  PROCEDURE insert_employee(...) IS BEGIN ... END; 
  PROCEDURE delete_employee(...) IS BEGIN ... END; 
END employee_changes; 
 
CREATE PROCEDURE hire 
  BEGIN 
    employee_changes.insert_employee(...) 
  END hire; 
 
CREATE PROCEDURE fire 
  BEGIN 
    employee_changes.delete_employee(...) 
  END fire; 
 
PACKAGE raise_bonus IS 
  PROCEDURE give_raise(...) AS 
    BEGIN 
      employee_changes.change_salary(...) 
    END give_raise; 
 
  PROCEDURE give_bonus(...) 
    BEGIN 
      employee_changes.change_bonus(...) 
    END give_bonus; 

Using this method, the procedures that actually do the work (the procedures in the employee_changes package) are defined in a single package and can share declared global variables, cursors, on so on. By declaring top-level procedures, hire and fire, and an additional package, raise_bonus, you can grant selective EXECUTE privileges on procedures in the main package:

GRANT EXECUTE ON hire, fire TO big_bosses; 
GRANT EXECUTE ON raise_bonus TO little_bosses; 

Managing Type Privileges

The following sections describe the use of privileges for types, methods, and objects:

System Privileges for Named Types

Table 4-4 lists system privileges for named types (object types, VARRAYs, and nested tables).

Table 4-4 System Privileges for Named Types

Privilege Enables you to ...

CREATE TYPE

Create named types in your own schemas

CREATE ANY TYPE

Create a named type in any schema

ALTER ANY TYPE

Alter a named type in any schema

DROP ANY TYPE

Drop a named type in any schema

EXECUTE ANY TYPE

Use and reference a named type in any schema


The RESOURCE role includes the CREATE TYPE system privilege. The DBA role includes all of these privileges.

Object Privileges

The only object privilege that applies to named types is EXECUTE. If the EXECUTE privilege exists on a named type, then a user can use the named type to:

  • Define a table

  • Define a column in a relational table

  • Declare a variable or parameter of the named type

The EXECUTE privilege permits a user to invoke the methods in the type, including the type constructor. This is similar to the EXECUTE privilege on a stored PL/SQL procedure.

Method Execution Model

Method execution is the same as any other stored PL/SQL procedure.

Privileges Required to Create Types and Tables Using Types

To create a type, you must meet the following requirements:

  • You must have the CREATE TYPE system privilege to create a type in your schema or the CREATE ANY TYPE system privilege to create a type in the schema of another user. These privileges can be acquired explicitly or through a role.

  • The owner of the type must be explicitly granted the EXECUTE object privileges to access all other types referenced within the definition of the type, or have been granted the EXECUTE ANY TYPE system privilege. The owner cannot obtain the required privileges through roles.

  • If the type owner intends to grant access to the type to other users, then the owner must receive the EXECUTE privileges to the referenced types with the GRANT OPTION or the EXECUTE ANY TYPE system privilege with the ADMIN OPTION. If not, then the type owner has insufficient privileges to grant access on the type to other users.

To create a table using types, you must meet the requirements for creating a table and the following additional requirements:

  • The owner of the table must have been directly granted the EXECUTE object privilege to access all types referenced by the table, or has been granted the EXECUTE ANY TYPE system privilege. The owner cannot exercise the required privileges if these privileges were granted through roles.

  • If the table owner intends to grant access to the table to other users, then the owner must have the EXECUTE privilege to the referenced types with the GRANT OPTION or the EXECUTE ANY TYPE system privilege with the ADMIN OPTION. If not, then the table owner has insufficient privileges to grant access on the table.

See Also:

"Managing Table Privileges" for the requirements for creating a table

Example of Privileges for Creating Types and Tables Using Types

Assume that three users exist with the CONNECT and RESOURCE roles:

  • user1

  • user2

  • user3

The following DDL is run in the schema of user1:

CREATE TYPE type1 AS OBJECT (
  attr1 NUMBER);

CREATE TYPE type2 AS OBJECT (
  attr2 NUMBER);

GRANT EXECUTE ON type1 TO user2;
GRANT EXECUTE ON type2 TO user2 WITH GRANT OPTION;

The following DDL is performed in the schema of user2:

CREATE TABLE tab1 OF user1.type1;
CREATE TYPE type3 AS OBJECT (
  attr3 user1.type2);
CREATE TABLE tab2 (
  col1 user1.type2);

The following statements succeed because user2 has EXECUTE privilege on user1.type2 with the GRANT OPTION:

GRANT EXECUTE ON type3 TO user3;
GRANT SELECT on tab2 TO user3;

However, the following grant fails because user2 does not have EXECUTE privilege on user1.type1 with the GRANT OPTION:

GRANT SELECT ON tab1 TO user3;

The following statements can be successfully run by user3:

CREATE TYPE type4 AS OBJECT (
  attr4 user2.type3);
CREATE TABLE tab3 OF type4;

Note:

Customers should discontinue using the CONNECT and RESOURCE roles, as they will be deprecated in future Oracle Database releases. The CONNECT role presently retains only the CREATE SESSION privilege.

Privileges on Type Access and Object Access

Existing column-level and table-level privileges for DML statements apply to both column objects and row objects.

Table 4-5 lists the privileges for object tables.

Table 4-5 Privileges for Object Tables

Privilege Enables you to...

SELECT

Access an object and its attributes from the table

UPDATE

Modify the attributes of the objects that make up the rows in the table

INSERT

Create new objects in the table

DELETE

Delete rows


Similar table privileges and column privileges apply to column objects. Retrieving instances does not in itself reveal type information. However, clients must access named type information to interpret the type instance images. When a client requests type information, Oracle Database checks for the EXECUTE privilege on the type.

Consider the following schema:

CREATE TYPE emp_type (
    eno NUMBER, ename CHAR(31), eaddr addr_t);
CREATE TABLE emp OF emp_t;

In addition, consider the following two queries:

SELECT VALUE(emp) FROM emp;
SELECT eno, ename FROM emp;

For either query, Oracle Database checks the SELECT privilege of the user for the emp table. For the first query, the user must obtain the emp_type type information to interpret the data. When the query accesses the emp_type type, Oracle Database checks the EXECUTE privilege of the user.

The second query, however, does not involve named types, so Oracle Database does not check type privileges.

In addition, by using the schema from the previous section, user3 can perform the following queries:

SELECT tab1.col1.attr2 FROM user2.tab1 tab1;
SELECT attr4.attr3.attr2 FROM tab3;

Note that in both SELECT statements, user3 does not have explicit privileges on the underlying types, but the statement succeeds because the type and table owners have the necessary privileges with the GRANT OPTION.

Oracle Database checks privileges on the following events, and returns an error if the client does not have the privilege for the action:

  • Pinning an object in the object cache using its REF value causes Oracle Database to check for the SELECT privilege on the containing object table.

  • Modifying an existing object or flushing an object from the object cache causes Oracle Database to check for the UPDATE privilege on the destination object table.

  • Flushing a new object causes Oracle Database to check for the INSERT privilege on the destination object table.

  • Deleting an object causes Oracle Database to check for the DELETE privilege on the destination table.

  • Pinning an object of a named type causes Oracle Database to check EXECUTE privilege on the object.

Modifying the attributes of an object in a client third-generation language application causes Oracle Database to update the entire object. Therefore, the user needs the UPDATE privilege on the object table. Having the UPDATE privilege on only certain columns of the object table is not sufficient, even if the application only modifies attributes corresponding to those columns. Therefore, Oracle Database does not support column-level privileges for object tables.

Type Dependencies

As with stored objects, such as procedures and tables, types being referenced by other objects are called dependencies. There are some special issues for types on which tables depend. Because a table contains data that relies on the type definition for access, any change to the type causes all stored data to become inaccessible. Changes that can cause this are when necessary privileges required to use the type are revoked, or the type or dependent types are dropped. If these actions occur, then the table becomes invalid and cannot be accessed.

A table that is invalid because of missing privileges can automatically become valid and accessible if the required privileges are granted again. A table that is invalid because a dependent type was dropped can never be accessed again, and the only permissible action is to drop the table.

Because of the severe effects that revoking a privilege on a type or dropping a type can cause, the SQL statements REVOKE and DROP TYPE, by default, implement restricted semantics. This means that if the named type in either statement has table or type dependents, then an error is received and the statement cancels. However, if the FORCE clause for either statement is used, then the statement always succeeds. If there are depended-upon tables, then they are invalidated.

See Also:

Oracle Database Reference for details about using the REVOKE, DROP TYPE, and FORCE clauses

Granting a User Privileges and Roles

This section contains:

It is also possible to grant roles to a user connected through a middle tier or proxy. This is discussed in "Using a Middle Tier Server for Proxy Authentication".

Granting System Privileges and Roles

You can use the GRANT SQL statement to grant system privileges and roles to users and roles. The following privileges are required:

  • To grant a system privilege, a user must be granted the system privilege with the ADMIN OPTION or must be granted the GRANT ANY PRIVILEGE system privilege.

  • To grant a role, a user must be granted the role with the ADMIN OPTION or was granted the GRANT ANY ROLE system privilege.

Example 4-10 grants the system privilege CREATE SESSION and the accts_pay role to the user jward.

Example 4-10 Granting a System Privilege and a Role to a User

GRANT CREATE SESSION, accts_pay TO jward;

Example 4-10 grants the EXECUTE privilege on the exec_dir directory object to the user jward.

Example 4-11 Granting the EXECUTE Privilege on a Directory Object

GRANT EXECUTE ON DIRECTORY exec_dir TO jward;

Note:

Object privileges cannot be granted along with system privileges and roles in the same GRANT statement.

Granting the ADMIN OPTION

If you specify the WITH ADMIN OPTION clause when you grant a privilege or role to a user or role, then the privilege grant has the following expanded capabilities:

  • The grantee can grant or revoke the system privilege or role to or from any other user or role in the database. Users cannot revoke a role from themselves.

  • The grantee can grant the system privilege or role with ADMIN OPTION.

  • The grantee of a role can alter or drop the role.

Example 4-12 grants the new_dba role with the WITH ADMIN OPTION clause to user michael.

Example 4-12 Granting the ADMIN OPTION

GRANT new_dba TO michael WITH ADMIN OPTION;

User michael is able to not only use all of the privileges implicit in the new_dba role, but he can also grant, revoke, and drop the new_dba role as deemed necessary. Because of these powerful capabilities, use caution when granting system privileges or roles with the ADMIN OPTION. These privileges are usually reserved for a security administrator, and are rarely granted to other administrators or users of the system.

Note:

When a user creates a role, the role is automatically granted to the creator with the ADMIN OPTION.

Creating a New User with the GRANT Statement

Oracle Database enables you to create a new user with the GRANT statement. If you specify a password using the IDENTIFIED BY clause, and the user name does not exist in the database, then a new user with that user name and password is created.

Example 4-13 creates psmith as a new user while granting psmith the CREATE SESSION system privilege.

Example 4-13 Creating a New User with the GRANT Statement

GRANT CREATE SESSION TO psmith IDENTIFIED BY password;

Granting Object Privileges

You can use the GRANT statement to grant object privileges to roles and users. To grant an object privilege, you must fulfill one of the following conditions:

  • You own the object specified.

  • You have been granted the GRANT ANY OBJECT PRIVILEGE system privilege. This privilege enables you to grant and revoke privileges on behalf of the object owner.

  • The WITH GRANT OPTION clause was specified when you were granted the object privilege.

    Note:

    System privileges and roles cannot be granted along with object privileges in the same GRANT statement.

Example 4-14 grants the SELECT, INSERT, and DELETE object privileges for all columns of the emp table to the users jfee and tsmith.

Example 4-14 Granting Object Privileges to Users

GRANT SELECT, INSERT, DELETE ON emp TO jfee, tsmith;

To grant all object privileges on the salary view to user jfee, use the ALL keyword as shown in the following example:

GRANT ALL ON salary TO jfee;

Note:

A grantee cannot regrant access to objects unless the original grant included the GRANT OPTION. Thus in the example just given, jfee cannot use the GRANT statement to grant object privileges to anyone else.

Specifying the GRANT OPTION Clause

Specify the WITH GRANT OPTION clause with the GRANT statement to enable the grantee to grant the object privileges to other users. The user whose schema contains an object is automatically granted all associated object privileges with the GRANT OPTION. This special privilege allows the grantee several expanded privileges:

  • The grantee can grant the object privilege to any user in the database, with or without the GRANT OPTION, and to any role in the database.

  • If both of the following conditions are true, then the grantee can create views on the table, and grant the corresponding privileges on the views to any user or role in the database:

    • The grantee receives object privileges for the table with the GRANT OPTION.

    • The grantee has the CREATE VIEW or CREATE ANY VIEW system privilege.

Note:

The GRANT OPTION is not valid when granting an object privilege to a role. Oracle Database prevents the propagation of object privileges through roles so that grantees of a role cannot propagate object privileges received by means of roles.

Granting Object Privileges on Behalf of the Object Owner

The GRANT ANY OBJECT PRIVILEGE system privilege enables users to grant and revoke any object privilege on behalf of the object owner. This privilege provides a convenient means for database and application administrators to grant access to objects in any schema without requiring that they connect to the schema. Login credentials do not need to be maintained for schema owners who have this privilege, which reduces the number of connections required during configuration.

This system privilege is part of the Oracle Database supplied DBA role and is thus granted (with the ADMIN OPTION) to any user connecting AS SYSDBA (user SYS). As with other system privileges, the GRANT ANY OBJECT PRIVILEGE system privilege can only be granted by a user who possesses the ADMIN OPTION.

The recorded grantor of access rights to an object is either the object owner or the person exercising the GRANT ANY OBJECT PRIVILEGE system privilege. If the grantor with GRANT ANY OBJECT PRIVILEGE does not have the object privilege with the GRANT OPTION, then the object owner is shown as the grantor. Otherwise, when that grantor has the object privilege with the GRANT OPTION, then that grantor is recorded as the grantor of the grant.

Note:

The audit record generated by the GRANT statement always shows the actual user who performed the grant.

For example, consider the following scenario. User adams possesses the GRANT ANY OBJECT PRIVILEGE system privilege. He does not possess any other grant privileges. He issues the following statement:

GRANT SELECT ON HR.EMPLOYEES TO blake WITH GRANT OPTION;

If you examine the DBA_TAB_PRIVS view, then you will see that hr is shown as the grantor of the privilege:

SELECT GRANTEE, GRANTOR, PRIVILEGE, GRANTABLE
  FROM DBA_TAB_PRIVS 
  WHERE TABLE_NAME = 'EMPLOYEES' and OWNER = 'HR';

GRANTEE  GRANTOR PRIVILEGE    GRANTABLE
-------- ------- -----------  ----------
BLAKE    HR       SELECT      YES       

Now assume that user blake also has the GRANT ANY OBJECT PRIVILEGE system. He issues the following statement:

GRANT SELECT ON HR.EMPLOYEES TO clark;

In this case, when you query the DBA_TAB_PRIVS view again, you see that blake is shown as being the grantor of the privilege:

GRANTEE  GRANTOR  PRIVILEGE  GRANTABLE
-------- -------- ---------  ----------
BLAKE    HR       SELECT     YES       
CLARK    BLAKE    SELECT     NO        

This occurs because blake already possesses the SELECT privilege on HR.EMPLOYEES with the GRANT OPTION.

Granting Privileges on Columns

You can grant INSERT, UPDATE, or REFERENCES privileges on individual columns in a table.

Caution:

Before granting a column-specific INSERT privilege, determine if the table contains any columns on which NOT NULL constraints are defined. Granting selective insert capability without including the NOT NULL columns prevents the user from inserting any rows into the table. To avoid this situation, ensure that each NOT NULL column can either be inserted into or has a non-NULL default value. Otherwise, the grantee will not be able to insert rows into the table and will receive an error.

The following statement grants the INSERT privilege on the acct_no column of the accounts table to user psmith:

GRANT INSERT (acct_no) ON accounts TO psmith;

In the following example, object privilege for the ename and job columns of the emp table are granted to the users jfee and tsmith:

GRANT INSERT(ename, job) ON emp TO jfee, tsmith;

Row-Level Access Control

You can also provide access control at the row level, that is, within objects, using Virtual Private Database (VPD) or Oracle Label Security (OLS).

Revoking Privileges and Roles from a User

This section contains:

Revoking System Privileges and Roles

You can revoke system privileges and roles using the SQL statement REVOKE. Any user with the ADMIN OPTION for a system privilege or role can revoke the privilege or role from any other database user or role. The revoker does not have to be the user that originally granted the privilege or role. Users with GRANT ANY ROLE can revoke any role.

The following statement revokes the CREATE TABLE system privilege and the accts_rec role from user psmith:

REVOKE CREATE TABLE, accts_rec FROM psmith;

Note:

The ADMIN OPTION for a system privilege or role cannot be selectively revoked. Instead, revoke the privilege or role, and then grant the privilege or role again but without the ADMIN OPTION.

Revoking Object Privileges

To revoke an object privilege, you must fulfill one of the following conditions:

  • You previously granted the object privilege to the user or role.

  • You possess the GRANT ANY OBJECT PRIVILEGE system privilege that enables you to grant and revoke privileges on behalf of the object owner.

You can only revoke the privileges that you, the person who granted the privilege, directly authorized. You cannot revoke grants that were made by other users to whom you granted the GRANT OPTION. However, there is a cascading effect. If the object privileges of the user who granted the privilege are revoked, then the object privilege grants that were propagated using the GRANT OPTION are revoked as well.

Assuming you are the original grantor of the privilege, the following statement revokes the SELECT and INSERT privileges on the emp table from users jfee and psmith:

REVOKE SELECT, INSERT ON emp FROM jfee, psmith;

The following statement revokes all object privileges for the dept table that you originally granted to the human_resource role:

REVOKE ALL ON dept FROM human_resources;

Note:

The GRANT OPTION for an object privilege cannot be selectively revoked. Instead, revoke the object privilege and then grant it again but without the GRANT OPTION. Users cannot revoke object privileges from themselves.

Revoking Object Privileges on Behalf of the Object Owner

The GRANT ANY OBJECT PRIVILEGE system privilege enables you to revoke any specified object privilege where the object owner is the grantor. This occurs when the object privilege is granted by the object owner, or on behalf of the owner by any user holding the GRANT ANY OBJECT PRIVILEGE system privilege.

In a situation where the object privilege was granted by both the owner of the object and the user executing the REVOKE statement (who has both the specific object privilege and the GRANT ANY OBJECT PRIVILEGE system privilege), Oracle Database only revokes the object privilege granted by the user issuing the REVOKE statement. This can be illustrated by continuing the example started in "Granting Object Privileges on Behalf of the Object Owner".

At this point, user blake granted the SELECT privilege on HR.EMPLOYEES to clark. Even though blake possesses the GRANT ANY OBJECT PRIVILEGE system privilege, he also holds the specific object privilege, thus this grant is attributed to him. Assume that user HR also grants the SELECT privilege on HR.EMPLOYEES to user clark. A query of the DBA_TAB_PRIVS view shows that the following grants are in effect for the HR.EMPLOYEES table:

GRANTEE  GRANTOR PRIVILEGE    GRANTABLE
-------- ------- -----------  ----------
BLAKE    HR       SELECT       YES       
CLARK    BLAKE    SELECT       NO        
CLARK    HR       SELECT       NO        

User blake now issues the following REVOKE statement:

REVOKE  SELECT ON HR.EMPLOYEES FROM clark;

Only the object privilege for user clark granted by user blake is removed. The grant by the object owner, HR, remains.

GRANTEE  GRANTOR PRIVILEGE    GRANTABLE
-------- ------- -----------  ----------
BLAKE    HR       SELECT      YES       
CLARK    HR       SELECT      NO        

If blake issues the REVOKE statement again, then this time the effect is to remove the object privilege granted by adams (on behalf of HR), using the GRANT ANY OBEJCT PRIVILEGE system privilege.

Revoking Column-Selective Object Privileges

Although users can grant column-specific INSERT, UPDATE, and REFERENCES privileges for tables and views, they cannot selectively revoke column-specific privileges with a similar REVOKE statement. Instead, the grantor must first revoke the object privilege for all columns of a table or view, and then selectively repeat the grant of the column-specific privileges that the grantor intends to keep in effect.

For example, assume that role human_resources was granted the UPDATE privilege on the deptno and dname columns of the table dept. To revoke the UPDATE privilege on just the deptno column, issue the following two statements:

REVOKE UPDATE ON dept FROM human_resources;
GRANT UPDATE (dname) ON dept TO human_resources;

The REVOKE statement revokes the UPDATE privilege on all columns of the dept table from the role human_resources. The GRANT statement then repeats, restores, or reissues the grant of the UPDATE privilege on the dname column to the role human_resources.

Revoking the REFERENCES Object Privilege

If the grantee of the REFERENCES object privilege has used the privilege to create a foreign key constraint (that currently exists), then the grantor can revoke the privilege only by specifying the CASCADE CONSTRAINTS option in the REVOKE statement:

REVOKE REFERENCES ON dept FROM jward CASCADE CONSTRAINTS;

Any foreign key constraints currently defined that use the revoked REFERENCES privilege are dropped when the CASCADE CONSTRAINTS clause is specified.

Cascading Effects of Revoking Privileges

Depending on the type of privilege, there may be cascading effects when a privilege is revoked. This is discussed in the following sections:

Cascading Effects When Revoking System Privileges

There are no cascading effects when revoking a system privilege related to DDL operations, regardless of whether the privilege was granted with or without the ADMIN OPTION. For example, assume the following:

  1. The security administrator grants the CREATE TABLE system privilege to user jfee with the ADMIN OPTION.

  2. User jfee creates a table.

  3. User jfee grants the CREATE TABLE system privilege to user tsmith.

  4. User tsmith creates a table.

  5. The security administrator revokes the CREATE TABLE system privilege from user jfee.

  6. The table created by user jfee continues to exist. User tsmith still has the table and the CREATE TABLE system privilege.

You can observe cascading effects when you revoke a system privilege related to a DML operation. If the SELECT ANY TABLE privilege is revoked from a user, then all procedures contained in the users schema relying on this privilege can longer be executed successfully until the privilege is reauthorized.

Cascading Effects When Revoking Object Privileges

Revoking an object privilege can have cascading effects. Remember the following:

  • Object definitions that depend on a DML object privilege can be affected if the DML object privilege is revoked. For example, assume that the body of the test procedure includes a SQL statement that queries data from the emp table. If the SELECT privilege on the emp table is revoked from the owner of the test procedure, then the procedure can no longer be executed successfully.

  • When a REFERENCES privilege for a table is revoked from a user, any foreign key integrity constraints that are defined by the user and require the dropped REFERENCES privilege are automatically dropped. For example, assume that user jward is granted the REFERENCES privilege for the deptno column of the dept table. This user now creates a foreign key on the deptno column in the emp table that references the deptno column of the dept table. If the REFERENCES privilege on the deptno column of the dept table is revoked, then the foreign key constraint on the deptno column of the emp table is dropped in the same operation.

  • The object privilege grants propagated using the GRANT OPTION are revoked if the object privilege of a grantor is revoked. For example, assume that user1 is granted the SELECT object privilege with the GRANT OPTION, and grants the SELECT privilege on emp to user2. Subsequently, the SELECT privilege is revoked from user1. This REVOKE statement is also cascaded to user2. Any objects that depend on the revoked SELECT privilege of user1 and user2 can also be affected, as described earlier.

Object definitions that require the ALTER and INDEX DDL object privileges are not affected if the ALTER or INDEX object privilege is revoked. For example, if the INDEX privilege is revoked from a user that created an index on a table that belongs to another user, then the index continues to exist after the privilege is revoked.

Granting to and Revoking from the PUBLIC User Group

You can grant and revoke privileges and roles from the user group PUBLIC. Because PUBLIC is accessible to every database user, all privileges and roles granted to PUBLIC are accessible to every database user.

Security administrators and database users should grant a privilege or role to PUBLIC only if every database user requires the privilege or role. This recommendation reinforces the general rule that, at any given time, each database user should have only the privileges required to accomplish the current group tasks successfully.

Revoking a privilege from PUBLIC can cause significant cascading effects. If any privilege related to a DML operation is revoked from PUBLIC (for example, SELECT ANY TABLE or UPDATE ON emp), then all procedures in the database, including functions and packages, must be reauthorized before they can be used again. Therefore, be careful when you grant and revoke DML-related privileges to or from PUBLIC.

See Also:

Granting Roles Using the Operating System or Network

This section contains:

About Granting Roles Using the Operating System or Network

Instead of a security administrator explicitly granting and revoking database roles to and from users using GRANT and REVOKE statements, the operating system on which Oracle Database runs can grant roles to users at connect time. Roles can be administered using the operating system and passed to Oracle Database when a user creates a session. As part of this mechanism, the default roles of a user and the roles granted to a user with the ADMIN OPTION can be identified. If the operating system is used to authorize users for roles, then all roles must be created in the database and privileges assigned to the role with GRANT statements.

Roles can also be granted through a network service.

The advantage of using the operating system to identify the database roles of a user is that privilege management for an Oracle database can be externalized. The security facilities offered by the operating system control user privileges. This option may offer advantages of centralizing security for several system activities, such as the following situation:

  • MVS Oracle administrators want RACF groups to identify database user roles.

  • UNIX Oracle administrators want UNIX groups to identify database user roles.

  • VMS Oracle administrators want to use rights identifiers to identify database user roles.

The main disadvantage of using the operating system to identify the database roles of a user is that privilege management can only be performed at the role level. Individual privileges cannot be granted using the operating system, but they can still be granted inside the database using GRANT statements.

A second disadvantage of using this feature is that, by default, users cannot connect to the database through the shared server or any other network connection if the operating system is managing roles. However, you can change this default as described in "Using Network Connections with Operating System Role Management".

Note:

The features described in this section are available only on some operating systems. See your operating system-specific Oracle Database documentation to determine if you can use these features.

Using Operating System Role Identification

To cause a database to use the operating system to identify the database roles of each user when a session is created, set the initialization parameter OS_ROLES to TRUE (and restart the instance, if it is currently running). When a user tries to create a session with the database, Oracle Database initializes the user security domain using the database roles identified by the operating system.

To identify database roles for a user, the operating system account for each Oracle Database user must have operating system identifiers (these may be called groups, rights identifiers, or other similar names) that indicate which database roles are to be available for the user. Role specification can also indicate which roles are the default roles of a user and which roles are available with the ADMIN OPTION. No matter which operating system is used, the role specification at the operating system level follows the format:

ora_ID_ROLE[[_d][_a][_da]]

In this specification:

  • ID has a definition that varies on different operating systems. For example, on VMS, ID is the instance identifier of the database; on VMS, it is the computer type; and on UNIX, it is the system ID.

    Note:

    ID is case-sensitive to match your ORACLE_SID. ROLE is not case-sensitive.
  • ROLE is the name of the database role.

  • d is an optional character that indicates this role is to be a default role of the database user.

  • a is an optional character that indicates this role is to be granted to the user with the ADMIN OPTION. This allows the user to grant the role to other roles only. Roles cannot be granted to users if the operating system is used to manage roles.

    Note:

    If either the d or a character is specified, then precede that character by an underscore (_).

For example, an operating system account might have the following roles identified in its profile:

ora_PAYROLL_ROLE1
ora_PAYROLL_ROLE2_a
ora_PAYROLL_ROLE3_d
ora_PAYROLL_ROLE4_da

When the corresponding user connects to the payroll instance of Oracle Database, role3 and role4 are defaults, while role2 and role4 are available with the ADMIN OPTION.

Using Operating System Role Management

When you use operating system-managed roles, remember that database roles are being granted to an operating system user. Any database user to which the operating system user is able to connect will have the authorized database roles enabled. For this reason, you should consider defining all Oracle Database users as IDENTIFIED EXTERNALLY if you are using OS_ROLES = TRUE, so that the database accounts are tied to the operating system account that was granted privileges.

Granting and Revoking Roles When OS_ROLES Is Set to TRUE

If the OS_ROLES parameter is set to TRUE, then the operating system completely manages the granting and revoking of roles to users. Any previous granting of roles to users using GRANT statements do not apply. However, they are still listed in the data dictionary. Only the role grants to users made at the operating system level apply. Users can still grant privileges to roles and users.

Note:

If the operating system grants a role to a user with the ADMIN OPTION, then the user can grant the role only to other roles.

Enabling and Disabling Roles When OS_ROLES Is Set to TRUE

If the OS_ROLES initialization parameter is set to TRUE, then any role granted by the operating system can be dynamically enabled using the SET ROLE statement. This still applies, even if the role was defined to require a password or operating system authorization. However, any role not identified in the operating system account of a user cannot be specified in a SET ROLE statement, even if a role was granted using a GRANT statement when OS_ROLES = FALSE. (If you specify such a role, then Oracle Database ignores it.)

When OS_ROLES is set to TRUE, then the user can enable up to 148 roles. Remember that this number includes other roles that may have been granted to the role.

Using Network Connections with Operating System Role Management

If you have the operating system manage roles, then, by default, users cannot connect to the database through the shared server. This restriction is the default because a remote user could impersonate another operating system user over an unsecure connection.

If you are not concerned with this security risk and want to use operating system role management with the shared server, or any other network connection, then set the initialization parameter REMOTE_OS_ROLES to TRUE. The change takes effect the next time you start the instance and mount the database. The default setting of this parameter is FALSE.

When Do Grants and Revokes Take Effect?

Depending on what is granted or revoked, a grant or revoke takes effect at different times:

You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view.

How the SET ROLE Statement Affects Grants and Revokes

During the user session, the user or an application can use the SET ROLE statement any number of times to change the roles currently enabled for the session. The user must already be granted the roles that are named in the SET ROLE statement.

Example 4-15 enables the role clerk, which you have already been granted, and specifies the password.

Example 4-15 Using SET ROLE to Grant a Role and Specify a Password

SET ROLE clerk IDENTIFIED BY password;

Replace password with a password that is secure. "Minimum Requirements for Passwords" describes the minimum requirements for passwords.

Example 4-16 shows how to use SET ROLE to disable all roles.

Example 4-16 Using SET ROLE to Disable All Roles

SET ROLE NONE;

Specifying Default Roles

When a user logs on, Oracle Database enables all privileges granted explicitly to the user and all privileges in the default roles of the user.

You can set and alter a list of default roles for a user by using the ALTER USER SQL statement. The ALTER USER statement specifies roles that are to be enabled when a user connects to the database. The user must have been directly granted the roles with a GRANT statement, or the roles must have been created by the user with the CREATE ROLE privilege. For information about the restrictions of the DEFAULT ROLE clause of the ALTER USER statement, see Oracle Database SQL Language Reference.

Example 4-17 sets the default roles payclerk and pettycash for user jane:

Example 4-17 Using ALTER USER to Set Default Roles

ALTER USER jane DEFAULT ROLE payclerk, pettycash;

You cannot set default roles for a user in the CREATE USER statement. When you first create a user, the default user role setting is ALL, which causes all roles subsequently granted to the user to be default roles. Use the ALTER USER statement to limit the default user roles.

Caution:

When you create a role (other than a global role or an application role), it is granted implicitly to you, and your set of default roles is updated to include the new role. You can grant as many roles as you want to a user, but remember that the user can have no more than 148 roles enabled by default. Otherwise, the user will be unable to log in to the database and an ORA-28031: maximum of 148 enabled roles exceeded error is raised. When aggregate roles, such as the DBA role, are granted to a user, the roles granted to the role are included in the number of roles the user has. For example, if a role has 20 roles granted to it and you grant that role to the user, then the user now has 21 additional roles. Therefore, when you grant new roles to a user, use the DEFAULT ROLE clause of the ALTER USER statement to ensure that not too many roles are specified as that user's default roles.

The Maximum Number of Roles That a User Can Enable

A user can enable no more than 148 roles.You can grant a user as many roles as you want, but you should restrict the number of roles granted to a user to the minimum roles the user needs. See "Guidelines for Securing Roles" for additional guidelines on granting roles to users.

Managing Fine-Grained Access in PL/SQL Network Utility Packages

You can configure user access control to external network services and wallets through the UTL_TCP, UTL_SMTP, UTL_HTTP, UTL_INADDR PL/SQL packages and the HttpUriType type.

This section contains:

About Fine-Grained Access Control to External Network Services

To configure fine-grained access control to external network services, you create an access control list (ACL), which is stored in Oracle XML DB. You can create the access control list by using Oracle XML DB itself, or by using the DBMS_NETWORK_ACL_ADMIN and DBMS_NETWORK_ACL_UTILITY PL/SQL packages. This guide explains how to use these packages to create and manage the access control list. To create an access control list by using Oracle XML DB and for general conceptual information about access control lists, see Oracle XML DB Developer's Guide.

This feature enhances security for network connections because it restricts the external network hosts that a database user can connect to using the PL/SQL network utility packages such as UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR. Otherwise, an intruder who gained access to the database could maliciously attack the network, because, by default, the PL/SQL utility packages are created with the EXECUTE privilege granted to PUBLIC users. These PL/SQL network utility packages, and the DBMS_NETWORK_ACL_ADMIN and DBMS_NETWORK_ACL_UTILTIY packages, support both IP Version 4 (IPv4) and IP Version 6 (IPv6) addresses. This guide explains how to manage access control to both versions. For detailed information about how the IPv4 and IPv6 notation works with Oracle Database, see Oracle Database Net Services Administrator's Guide.

See Also:

"Tutorial: Adding an E-Mail Alert to a Fine-Grained Audit Policy" for an example of configuring access control to external network services for e-mail alerts

About Access Control to Wallets

When a user accesses Web pages that are protected by a remote Web server, the user can authenticate himself or herself by supplying the passwords and client certificates that are stored in an Oracle wallet. The Oracle wallet provides secure storage of user passwords and client certificates.

To configure access control to a wallet, you need the following components:

  • An Oracle wallet. You can create the wallet using the Oracle Database mkstore utility or Oracle Wallet Manager. The HTTP request will use the external password store or the client certificate in the wallet to authenticate the user

  • An access control list to grant privileges to the user to use the wallet. To create the access control list, you use the DBMS_NETWORK_ACL_ADMIN PL/SQL package.

  • A way to associate the wallet with the access control list. To do so, use the DBMS_NETWORK_ACL_ADMIN PL/SQL package.

The use of wallets is beneficial because it provides secure storage of passwords and client certificates necessary to access protected Web pages.

Upgrading Applications That Depend on the PL/SQL Network Utility Packages

If you have upgraded from a release before Oracle Database 11g Release 1 (11.1), and your applications depend on PL/SQL network utility packages such as UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR, the following error may occur when you try to run the application:

ORA-24247: network access denied by access control list (ACL)

Use the procedures in this section to reconfigure the network access for the application. See also Oracle Database Upgrade Guide for compatibility issues for applications that depend on the PL/SQL network utility packages. For detailed information about the network utility packages, see Oracle Database PL/SQL Packages and Types Reference.

Creating an Access Control List for External Network Services

When you create access control lists for network connections, you should create one access control list dedicated to a group of common users, for example, users who need access to a particular application that resides on a specific host computer. For ease of administration and for good system performance, do not create too many access control lists. Network hosts accessible to the same group of users should share the same access control list.

To create the access control list by using the DBMS_NETWORK_ACL_ADMIN package, follow these steps:

Step 1: Create the Access Control List and Its Privilege Definitions

Use the DBMS_NETWORK_ACL_ADMIN.CREATE_ACL procedure to create the content of the access control list. It contains a name of the access control list, a brief description, and privilege settings for one user or role that you want to associate with the access control list. In an access control list, privileges for each user or role are grouped together as an access control entry (ACE). An access control list must have the privilege settings for at least one user or role.

Note:

You cannot import or export the access control list settings by using the Oracle Database import or export utilities such as Oracle Data Pump.

for example:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  acl          => 'file_name.xml', 
  description  => 'file description',
  principal    => 'user_or_role',
  is_grant     => TRUE|FALSE, 
  privilege    => 'connect|resolve',
  start_date   => null|timestamp_with_time_zone,
  end_date     => null|timestamp_with_time_zone); 
END;

In this specification:

  • acl: Enter a name for the access control list XML file. Oracle Database creates this file relative to the /sys/acls directory in the XML DB Repository in the database. Include the .xml extension. For example:

    acl => 'us-example-com-permissions.xml',
    
  • description: Enter a brief description of the purpose of this file. For example:

    description => 'Network connection permission for ACCT_MGR role',
    
  • principal: Enter the first user account or role being granted or denied permissions. For example:

    principal => 'ACCT_MGR',
    

    Enter the name of the user account or role in case sensitive characters. For example, if the database stores the role name ACCT_MGR in all capital letters, entering it in mixed or lower case will not work. You can find the user accounts and roles in the current database instance by querying the DBA_USERS and DBA_ROLES data dictionary views. Typically, user names and roles are stored in upper-case letters.

    If you want to enter multiple users or grant additional privileges to this user or role, use the DBMS_NETWORK_ACL.ADD_PRIVILEGE procedure (described next) after you have created this access control list XML file.

  • is_grant: Enter either TRUE or FALSE, to indicate whether the privilege is to be granted or denied. For example:

    is_grant => TRUE,
    
  • privilege: Enter either connect or resolve. This setting is case sensitive, so always enter it in lowercase. For example:

    privilege => 'connect',
    

    A database user needs the connect privilege to an external network host computer if he or she is connecting using the UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL utility packages. To resolve the host name that was given a host IP address, or the IP address that was given a host name, with the UTL_INADDR package, grant the database user the resolve privilege instead.

    You can use the data dictionary views described in "Finding Information About Access Control Lists Configured for User Access" to find more information about existing privileges and network connections.

  • start_date: (Optional) Enter the start date for the access control entry (ACE), in TIMESTAMP WITH TIME ZONE format (YYYY-MM-DD HH:MI:SS.FF TZR). When specified, the access control entry will be valid only on or after the specified date. The default is null. For example, to set a start date of February 28, 2008, at 6:30 a.m. in San Francisco, California, U.S., which is in the Pacific time zone:

    start_date => '2008-02-28 06:30:00.00 US/Pacific',
    

    The NLS_TIMESTAMP_FORMAT initialization parameter sets the default timestamp format. See Oracle Database Reference for more information.

  • end_date: (Optional) Enter the end date for the access control entry (ACE), in TIMESTAMP WITH TIME ZONE format (YYYY-MM-DD HH:MI:SS.FF TZR). When specified, the access control entry expires after the specified date. The end_date setting must be greater than or equal to the start_date setting. The default is null.

    For example, to set an end date of December 10, 2008, at 11:59 p.m. in San Francisco, California, U.S., which is in the Pacific time zone:

    end_date => '2008-12-10 23:59:00.00 US/Pacific');
    

To add more users or roles to the access control list, or grant additional privileges to one user or role, use the DBMS_NETWORK_ACL.ADD_PRIVILEGE procedure. The syntax is as follows:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ( 
  acl         => 'file_name.xml', 
  principal   => 'user_or_role',
  is_grant    => TRUE|FALSE, 
  privilege   => 'connect|resolve', 
  position    => null|value, 
  start_date  => null|timestamp_with_time_zone,
  end_date    => null|timestamp_with_time_zone);
END;

As you can see, the parameters to add the privilege are the similar to those in the CREATE_ACL procedure, except that description is not included and the position parameter, which sets the order of precedence for multiple users or roles, was added. Because you now are adding more than one user or role, you may want to consider setting their precedence. "Setting the Precedence of Multiple Users and Roles in One Access Control List" provides more information.

Other DBMS_NETWORK_ACL_ADMIN procedures that are available for this step are DELETE_PRIVILEGE and DROP_ACL.

At this stage, you have created an access control list that defines the privileges needed to connect to a network host. However, the access control list has no effect until you complete Step 2: Assign the Access Control List to One or More Network Hosts.

Step 2: Assign the Access Control List to One or More Network Hosts

After you create the access control list, then you are ready to assign it to one or more network host computers. You can use the DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL procedure to do so.

For example:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
  acl         => 'file_name.xml',
  host        => 'network_host', 
  lower_port  => null|port_number,
  upper_port  => null|port_number); 
END;

In this specification:

  • acl: Enter the name of the access control list XML file (from Step 1: Create the Access Control List and Its Privilege Definitions) to assign to the network host. Oracle Database creates this file relative to the /sys/acls directory in the XML DB Repository in the database. Include the .xml extension. For example:

    acl => 'us-example-com-permissions.xml',
    
  • host: Enter the network host to which this access control list will be assigned. This setting can be a name or IP address of the network host. Host names are case insensitive. For example:

    host => 'us.example.com',
    

    See the following sections for more information about how network host computers in access control list assignments work:

  • lower_port: (Optional) For TCP connections, enter the lower boundary of the port range. Use this setting for the connect privilege only; omit it for the resolve privilege. The default is null, which means that there is no port restriction (that is, the ACL applies to all ports). For example:

    lower_port => 80,
    
  • upper_port: (Optional) For TCP connections, enter the upper boundary of the port range. Use this setting for connect privileges only; omit it for resolve privileges. The default is null, which means that there is no port restriction (that is, the ACL applies to all ports) For example:

    upper_port => 3999);
    

    If you enter a value for the lower_port and leave the upper_port at null (or just omit it), Oracle Database assumes the upper_port setting is the same as the lower_port. For example, if you set lower_port to 80 and omit upper_port, the upper_port setting is assumed to be 80.

    The resolve privilege in the access control list takes no effect when a port range is specified in the access control list assignment.

Only one access control list can be assigned to any host computer, domain, or IP subnet, and if specified, the TCP port range. When you assign a new access control list to a network target, Oracle Database unassigns the previous access control list that was assigned to the same target. However, Oracle Database does not drop the access control list. You can drop the access control list by using the DROP_ACL procedure. To remove an access control list assignment, use the UNASSIGN_ACL procedure.

Depending on how you create and maintain the access control list, the two steps may overlap. For example, you can create an access control list that has privileges for five users in it, and then apply it to two host computers. Later on, you can modify this access control list to have different or additional users and privileges, and assign it to different or additional host computers.

All access control list changes, including the assignment to network hosts, are transactional. They do not take effect until the transaction is committed.

You can find information about existing privileges and network connections by using the data dictionary views described in Table 4-6, "Data Dictionary Views That Display Information about Access Control Lists".

For information about using the DBMS_NETWORK_ACL_ADMIN package, see Oracle Database PL/SQL Packages and Types Reference.

Configuring Access Control to a Wallet

This method lets you grant access to the passwords and client certificates that are stored in an Oracle wallet to users to authenticate themselves to an external Web server. This enables the user to retrieve protected Web pages from the Web server.

This section contains:

Step 1: Create an Oracle Wallet

To create the wallet, you can use either the mkstore command-line utility or the Oracle Wallet Manager user interface. To store passwords in the wallet, you must use mkstore. You can use both standard and PKCS11 wallet types, and the wallet can be an auto-login wallet if you want. For detailed information about creating wallets, see Oracle Database Advanced Security Administrator's Guide.

When you create the wallet, do the following:

  • Ensure that you have exported the wallet to a file.

  • Make a note of the directory in which you created the wallet. You will need this directory path when you complete the procedures in this section.

Step 2: Create an Access Control List that Grants the Wallet Privileges

After you have created the wallet, you are ready to create the access control list that will assign the password or client certificate privilege the user needs to use password credentials in the wallet for HTTP authentication.

For example:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  acl         => 'file_name.xml',
  description => 'description',
  principal   => 'user_or_role',
  is_grant    => TRUE|FALSE,
  privilege   => 'privilege';
...
END;

In this specification:

  • acl: Enter a name for the ACL, and make a note of this name. You will need this name in Step 3: Assign the Access Control List to the Wallet, next. Oracle Database creates this file relative to the /sys/acls directory in the XML DB Repository in the database. Include the .xml extension. For example:

    acl         => 'hr_access_wallet_acl.xml',
    
  • description: Enter a brief description of the purpose of this file. For example:

    description => 'Wallet ACL for the hr_access application',
    
  • principal: Enter the user account or role being granted or denied privileges. For example:

    principal   => 'HR_CLERK',
    

    Enter this name using case sensitive characters. For example, if the database stores the role name HR_CLERK in all capital letters, entering it in mixed or lower-case letters will not work. You can find the user accounts and roles in the current database instance by querying the DBA_USERS and DBA_ROLES data dictionary views. Typically, user names and roles are stored in upper-case letters.

    If you want to add multiple users, or if you want to grant this user an additional privilege, you can use the DBMS_NETWORK_ACL.ADD_PRIVILEGE procedure after you have created this access control list XML file.

  • is_grant: Enter either TRUE or FALSE, to indicate whether the privilege is to be granted or denied. For example:

    is_grant    => TRUE,
    
  • privilege: Enter one of the following settings using lowercase letters and hyphens. Remember that the privilege name is case-sensitive.

    • use-passwords to give the user permission to use passwords in the wallet

    • use-client-certificates to authenticate the user with a client certificate in the wallet

    For example:

    privilege    => 'use-client-certificates');
    

Step 3: Assign the Access Control List to the Wallet

In this step, you assign this access control list to the wallet you created earlier. Afterward, you can check your settings by querying the DBA_WALLET_ACLS data dictionary view.

For example:

BEGIN
...
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL (
  acl         => 'file_name.xml',
  wallet_path => 'file:path_to_directory_containing_wallet');
END;

In this specification:

  • acl: Enter the name that you created for this wallet in Step 2: Create an Access Control List that Grants the Wallet Privileges, in the previous section. For example:

    acl         => 'hr_access_wallet_acl.xml',
    
  • wallet_path: Enter the path to the directory that contains the wallet. When you specify the wallet path, you must use an absolute path and include file: before this directory path. Do not use environment variables, such as $ORACLE_HOME, nor insert a space after file: and before the path name. For example:

    wallet_path => 'file:/oracle/wallets/hr_access_access'
    

Step 4: Make the HTTP Request with the Passwords and Client Certificates

In this step, you use the UTL_HTTP PL/SQL package to create a request context object that is used privately with the HTTP request and its response. For detailed information about the UTL_HTTP package, see Oracle Database PL/SQL Packages and Types Reference.

For example:

DECLARE
 req_context UTL_HTTP.REQUEST_CONTEXT_KEY;
 req         UTL_HTTP.REQ;
BEGIN
 req_context := UTL_HTTP.CREATE_REQUEST_CONTEXT (
              wallet_path          => 'file:path_to_directory_containing_wallet',
              wallet_password      => 'wallet_password'|NULL);
 req := UTL_HTTP.BEGIN_REQUEST( 
              url                  => 'URL_to_application',
              request_context      => 'request_context'|NULL);
 ...
END;

In this specification:

  • req_context: Use the UTL_HTTP.CREATE_REQUEST_CONTEXT_KEY datatype to create the request context object. This object stores a randomly-generated numeric key that Oracle Database uses to identify the request context. The UTL_HTTP.CREATE_REQUEST_CONTEXT function creates the request context itself.

  • req: Use the UTL_HTTP.REQ datatype to create the object that will be used to begin the HTTP request. You will refer to this object later on, when you set the user name and password from the wallet to access a password-protected Web page.

  • wallet_path: Enter the path to the directory that contains the wallet. Ensure that this path is the same path you specified when you created access control list in Step 3: Assign the Access Control List to the Wallet in the previous section.You must include file: before the directory path. Do not use environment variables, such as $ORACLE_HOME.

    For example:

    wallet_path     => 'file:/oracle/wallets/hr_access_access',
    
  • wallet_password: Enter the password used to open the wallet. The default is NULL, which is used for auto-login wallets. For example:

    wallet_password      => NULL);
    
  • url: Enter the URL to the application that uses the wallet.

    For example:

    url                  => 'www.hr_access.example.com',
    
  • request_context: Enter the name of the request context object that you created earlier in this section. This object prevents the wallet from being shared with other applications in the same database session.

    For example:

    request_context      => req_context);
    

Using a Request Context to Hold the Wallet When Sharing the Session with Other Applications

You should use a request context to hold the wallet when the database session is shared with other applications. If your application has exclusive use of the database session, you can hold the wallet in the database session by using the SET_WALLET procedure instead.

For example:

DECLARE
 req         UTL_HTTP.REQ;
BEGIN
 UTL_HTTP.SET_WALLET(
              path          => 'file:path_to_directory_containing_wallet',
              password      => 'wallet_password'|NULL);
 req := UTL_HTTP.BEGIN_REQUEST( 
              url           => 'URL_to_application');
 ...
END;

If the protected URL being requested requires the user name and password to authenticate, then use the SET_AUTHENTICATION_FROM_WALLET procedure to set the user name and password from the wallet to authenticate.

Using Only a Client Certificate to Authenticate

If the protected URL being requested requires only the client certificate to authenticate, the BEGIN_REQUEST function sends the necessary client certificate from the wallet. assuming the user has been granted the use-client-certificates privilege in the ACL assigned to the wallet. The authentication should succeed at the remote Web server and the user can proceed to retrieve the HTTP response by using the GET_RESPONSE function.

Using the Password to Authenticate

If the protected URL being requested requires the username and password to authenticate, you should use the SET_AUTHENTICATION_FROM_WALLET procedure to set the username and password from the wallet to authenticate.

For example:

DECLARE
 req_context UTL_HTTP.REQUEST_CONTEXT_KEY;
 req         UTL_HTTP.REQ;
BEGIN
...
 UTL_HTTP.SET_AUTHENTICATION_FROM_WALLET(
  r               => HTTP_REQUEST,
  alias           => 'alias_to_retrieve_credentials_stored_in_wallet',
  scheme          => 'AWS|Basic', 
  for_proxy       => TRUE|FALSE);
END;

In this specification:

  • r: Enter the HTTP request defined in the UTL_HTTP.BEGIN_REQUEST procedure that you created above, in the previous section. For example:

    r               => req,
    
  • alias: Enter the alias used to identify and retrieve the user name and password credential stored in the Oracle wallet. For example, assuming the alias used to identify this user name and password credential is hr_access.

    alias           => 'hr_access',
    
  • scheme: Enter one of the following:

    • AWS: Specifies the Amazon Simple Storage Service (S3) scheme. Use this scheme only if you are configuring access to the Amazon.com Web site. (Contact Amazon for more information about this setting.)

    • Basic: Specifies HTTP basic authentication. The default is Basic.

    For example:

    scheme          => 'Basic',
    
  • for_proxy: Specify whether the HTTP authentication information is for access to the HTTP proxy server instead of the Web server. The default is FALSE.

    For example:

    for_proxy       => TRUE);
    

The use of the user name and password in the wallet requires the use-passwords privilege to be granted to the user in the ACL assigned to the wallet.

Examples of Creating Access Control Lists

The following examples demonstrate how to create access control lists.

See Also:

Oracle Database Vault Administrator's Guide for a tutorial that demonstrates how to use an access control list when an administrator must use the UTL_MAIL PL/SQL package to configure an e-mail alert

Example of an Access Control List for a Single Role and Network Connection

Example 4-18 shows how you would create an access control list called us-example-com-permissions.xml to grant users who have the ACCT_MGR role access to network services that run on the host us.example.com.

Example 4-18 Creating an Access Control List for a Single Role and Network Connection

-- 1. Create the access control list, which includes one role:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  acl          => 'us-example-com-permissions.xml', 
  description  => 'Network connection permission for ACCT_MGR', 
  principal    => 'ACCT_MGR', -- Must be in upper case
  is_grant     => TRUE, 
  privilege    => 'connect'); 
END;
/

-- 2. Assign the access control list a network host:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
  acl         => 'us-example-com-permissions.xml'',
  host        => 'www.us.example.com', 
  lower_port  => 80,
  upper_port  => 80); 
END;
/

This example creates the us-example-com-permissions.xml file in the /sys/acls directory, which is the default location. The XML file appears as follows:

<acl description="Network connection permission for ACCT_MGR" 
  xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
  xmlns:plsql="http://xmlns.oracle.com/plsql" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"   
  xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd    http://xmlns.oracle.com/xdb/acl.xsd">
  <security-class>plsql:network</security-class>
  <ace>
    <grant>true</grant>
    <principal>ACCT_MGR</principal>
    <privilege><plsql:connect/></privilege>
 </ace>
</acl>

The xmlns and xsi elements are fixed and should not be modified, for example, in a text editor.

You can check the contents of the access control list in SQL*Plus. See Oracle XML DB Developer's Guide for examples.

Example of an Access Control List with Multiple Roles Assigned to Multiple Hosts

Example 4-19 shows how to create a slightly more complex version of the us-example-com-permissions.xml access control list. In this example, you specify multiple role privileges and their precedence position, and assigned to multiple host computers.

See"Specifying a Group of Network Host Computers" and "Precedence Order for a Host Computer in Multiple Access Control List Assignments" for more information about host names. See also "Setting the Precedence of Multiple Users and Roles in One Access Control List" to determine the order of multiple ACE elements in the access control list XML file.

Example 4-19 Creating an Access Control List for Multiple Roles and Network Connections

-- 1. Create the access control list:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  acl          => 'us-example-com-permissions.xml', 
  description  => 'Network connection permission for ACCT_MGR and ACCT_CLERK', 
  principal    => 'ACCT_MGR', -- Must be in upper case
  is_grant     => TRUE,
  privilege    => 'resolve');
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ( -- Creates the second role privilege
  acl          => 'us-example-com-permissions.xml',
  principal    => 'ACCT_CLERK', 
  is_grant     => TRUE, 
  privilege    => 'connect',
  position     => null);
END;
/

--2. Assign the access control list to hosts:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( -- Creates the first target host
  acl          => 'us-example-com-permissions.xml',
  host         => '*.us.example.com'); 
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( -- Creates the second target host 
  acl          => 'us-example-com-permissions.xml',
  host         => '*.uk.example.com',
  lower_port   => 80,
  upper_port   => 99); 
END;
/

The us-example-com-permissions.xml appears as follows:

<acl description="Network connection permission for ACCT_MGR and ACCT_CLERK" 
  xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
  xmlns:plsql="http://xmlns.oracle.com/plsql" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"   
  xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd    http://xmlns.oracle.com/xdb/acl.xsd">
  <security-class>plsql:network</security-class>
  <ace>
    <grant>true</grant>
    <principal>ACCT_MGR</principal>
    <privilege><plsql:resolve/></privilege>
  </ace>
  <ace>
    <grant>true</grant>
    <principal>ACCT_CLERK</principal>
    <privilege><plsql:connect/></privilege>
  </ace>
</acl>

Example 4-20 shows how the DBA_NETWORK_ACL_PRIVILEGES data dictionary view displays the privilege granted in the previous access control list.

Example 4-20 Using the DBA_NETWORK_ACL_PRIVILEGES View to Show Granted Privileges

ACL  
ACLID                                  PRINCIPAL  PRIVILEGE IS_GRANT INVERT
START_DATE END_DATE
------------------------------------------
-------------------------------- ---------- -------   -------- -------
---------- ----------
/sys/acls/us-example-com-permissions.xml 2EF86135D0E29B2AE040578CE4043250 ACCT_
MGR   resolve   true       false
/sys/acls/us-example-com-permissions.xml 2EF86135D0E29B2AE040578CE4043250 ACCT_
CLERK connect   true       false

Example 4-21 shows how the DBA_NETWORK_ACLS data dictionary view displays the host assignment of the access control list.

Example 4-21 Using the DBA_NETWORK_ACLS View to Show Host Assignments

HOST                       LOWER_PORT UPPER_PORT
ACL
ACLID
-------------------- ---------- ----------
------------------------------------------
--------------------------------
*.us.example.com                   
/sys/acls/us-example-com-permissions.xml 2EF86135D0E29B2AE040578CE4043250
*.uk.example.com      80             99
/sys/acls/us-example-com-permissions.xml 2EF86135D0E29B2AE040578CE4043250

In these examples, the ACCT_MGR role has the resolve privilege to the first host, and the ACCT_CLERK role has the connect privilege to the first and second target hosts. The ACCT_MGR role does not have the resolve privilege to the second host because a port range is specified in the assignment to the second host.

To check the contents of the access control list in SQL*Plus, see Oracle XML DB Developer's Guide for examples.

Example of an Access Control List for Using Passwords in a Non-Shared Wallet

Example 4-22 configures wallet access for two Human Resources department roles, hr_clerk and hr_manager. These roles use the use-passwords privilege to access passwords stored in the wallet. In this example, the wallet will not be shared with other applications within the same database session.

Example 4-22 Configuring ACL Access Using Passwords in a Non-Shared Wallet

-- 1. At a command prompt, create the wallet. The following example uses the 
--    user name hr_access as the alias to identify the user name and password 
--    stored in the wallet. You must use this alias name when you call the
--    SET_AUTHENTICATION_FROM_WALLET procedure later on. 

$ mkstore -wrl $ORACLE_HOME/wallets/hr_access_access -create
Enter password: password
Enter password again: password
$ mkstore -wrl $ORACLE_HOME/wallets/hr_access_access -createCredential hr_access hr_usr 
Your secret/Password is missing in the command line
Enter your secret/Password: password
Re-enter your secret/Password: password
Enter wallet password: password

-- 2. In SQL*Plus, create an access control list to grant privileges for the 
--    wallet. The following example grants the use-passwords privilege to the
--    hr_clerk and hr_manager roles, and then it assigns this ACL to the wallet.  

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl         => 'hr_access_wallet_acl.xml', 
    description => 'Wallet ACL for hr_access application',
    principal   => 'HR_CLERK', -- must be in upper case
    is_grant    => TRUE,
    privilege   => 'use-passwords');
 
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl         => 'hr_access_wallet_acl.xml', 
    principal   => 'HR_MANAGER',
    is_grant    => TRUE,
    privilege   => 'use-passwords');
 
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL(
    acl         => 'hr_access_wallet_acl.xml', 
    wallet_path => 'file:/oracle/wallets/hr_access_access');
end;
/
COMMIT;

-- 3. Create a request context and request object, and then set the 
--    authentication for the wallet

DECLARE
  req_context  UTL_HTTP.REQUEST_CONTEXT_KEY;
  req          UTL_HTTP.REQ;

BEGIN
 req_context := UTL_HTTP.CREATE_REQUEST_CONTEXT(
     wallet_path          => 'file:/oracle/wallets/hr_access_access',
     wallet_password      => NULL,
     enable_cookies       => TRUE,
     max_cookies          => 300,
     max_cookies_per_site => 20);
  req := UTL_HTTP.BEGIN_REQUEST(
     url                  => 'www.hr_access.example.com',
     request_context      => req_context);
  UTL_HTTP.SET_AUTHENTICATION_FROM_WALLET(
     r                    => req,
     alias                => 'hr_access'),
     scheme               => 'Basic', 
     for_proxy            => FALSE);
END;
/

Example of an Access Control List for Wallets in a Shared Database Session

Example 4-23 is almost the same as Example 4-22, except that it configures the wallet to be used for a shared database session; that is, all applications within the current database session will have access to this wallet.

Example 4-23 Configuring ACL Access for a Wallet in a Shared Database Session

Follow these steps:
-- 1. Use Oracle Wallet Manager to create the wallet and add the client
--    certificate. See Oracle Database Advanced Security Administrator's Guide 
--    for detailed information about using Oracle Wallet Manager. 

-- 2. In SQL*Plus, create an access control list to grant privileges for the 
--    wallet. The following example grants the use-client-certificates privilege 
--    to the hr_clerk and hr_manager roles, then it assigns this ACL to the 
--    wallet.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl         => 'hr_access_wallet_acl.xml', 
    description => 'Wallet ACL for hr_access application',
    principal   => 'HR_CLERK', -- must be in upper case
    is_grant    => TRUE,
    privilege   => 'use-client-certificates');
 
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl         => 'hr_access_wallet_acl.xml', 
    principal   => 'HR_MANAGER',
    is_grant    => TRUE,
    privilege   => 'use-client-certificates');
 
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL(
    acl         => 'hr_access_wallet_acl.xml', 
    wallet_path => 'file:/oracle/wallets/hr_access_access');
end;
/
COMMIT;

-- 3. Create a request object to handle the HTTP authentication for this wallet.

DECLARE
  req  UTL_HTTP.req;
BEGIN
  UTL_HTTP.SET_WALLET(
   path            => 'file: $ORACLE_HOME/wallets/hr_access_access'
   password        => NULL);
 req := UTL_HTTP.BEGIN_REQUEST(
   url             => 'www.hr_access.example.com',
   method          => 'POST',
   http_version    => NULL,
   request_context => NULL);
END;
/

Specifying a Group of Network Host Computers

If you want to assign an access control list to a group of network host computers, you can use the asterisk (*) wildcard character. For example, enter *.example.com for host computers that belong to a domain or 192.0.2.* for IPv4 addresses that belong to an IP subnet. The asterisk wildcard must be at the beginning, before a period (.) in a domain, or at the end, after a period (.), in an IP subnet. For example, *.example.com is valid, but *example.com and *.example.* are not. Be aware that the use of wildcard characters affects the order of precedence for multiple access control lists that are assigned to the same host computer. You cannot use wildcard characters for IPv6 addresses.

The Classless Inter-Domain Routing (CIDR ) notation defines how IPv4 and IPv6 addresses are categorized for routing IP packets on the internet. The DBMS_NETWORK_ACL_ADMIN package supports CIDR notation for both IPv4 and IPv6 addresses. This package considers an IPv4-mapped IPv6 address or subnet equivalent to the IPv4-native address or subnet it represents. For example, ::ffff:192.0.2.1 is equivalent to 192.0.2.1, and ::ffff:192.0.2.1/120 is equivalent to 192.0.2.*.

Precedence Order for a Host Computer in Multiple Access Control List Assignments

For multiple access control lists that are assigned to the host computer and its domains, the access control list that is assigned to the host computer takes precedence over those assigned to the domains. The access control list assigned to a domain has a lower precedence than those assigned to the subdomains.

For example, Oracle Database first selects the access control list assigned to the host server.us.example.com, ahead of other access control lists assigned to its domains. If additional access control lists were assigned to the sub domains, their order of precedence is as follows:

  1. server.us.example.com

  2. *.us.example.com

  3. *.example.com

  4. *.com

  5. *

Similarly, for multiple access control lists that are assigned to the IP address (both IPv4 and IPv6) and the subnets it belongs to, the access control list that is assigned to the IP address takes precedence over those assigned to the subnets. The access control list assigned to a subnet has a lower precedence than those assigned to the smaller subnets it contains.

For example, Oracle Database first selects the access control list assigned to the IP address 192.0.2.3, ahead of other access control lists assigned to the subnets it belongs to. If additional access control lists were assigned to the subnets, their order of precedence is as follows:

  1. 192.0.2.3 (or ::ffff:192.0.2.3)

  2. 192.0.2.3/31 (or ::ffff:192.0.2.3/127)

  3. 192.0.2.3/30 (or ::ffff:192.0.2.3/126)

  4. 192.0.2.3/29 (or ::ffff:192.0.2.3/125)

  5. ...

  6. 192.0.2.3/24 (or ::ffff:192.0.2.3/120 or 192.0.2.*)

  7. ...

  8. 192.0.2.3/16 (or ::ffff:192.0.2.3/112 or 192.0.*)

  9. ...

  10. 192.0.2.3/8 (or ::ffff:192.0.2.3/104 or 192.*)

  11. ...

  12. ::ffff:192.0.2.3/95

  13. ::ffff:192.0.2.3/94

  14. ...

  15. *

Precedence Order for a Host in Access Control List Assignments with Port Ranges

When an access control list is assigned to a host computer, a domain, or an IP subnet with a port range, it takes precedence over the access control list assigned to the same host, domain, or IP subnet without a port range.

For example, for TCP connections to any port between port 80 and 99 at server.us.example.com, Oracle Database first selects the access control list assigned to port 80 through 99 at server.us.example.com, ahead of the other access control list assigned to server.us.example.com that is without a port range.

Checking Privilege Assignments That Affect User Access to a Network Host

Database administrators can use the DBA_NETWORK_ACL_PRIVILEGES data dictionary view to query network privileges that have been granted to or denied from database users and roles in the access control lists, and whether those privileges take effect during certain times only. Using the information provided by the view, you may need to combine the data to determine if a user is granted the privilege at the current time, the roles the user has, the order of the access control entries, and so on. To simplify this privilege evaluation, you can use the following DBMS_NETWORK_ACL_ADMIN functions to check the privilege granted to a user in an access control list:

  • CHECK_PRIVILEGE: Checks if the specified privilege is granted to or denied from the specified user in an access control list. This procedure identifies the access control list by its path in the XML DB Repository. Use CHECK_PRIVILEGE if you want to evaluate a single access control list with a known path.

  • CHECK_PRIVILEGE_ACLID: Similar to the CHECK_PRIVILEGE procedure, except that it enables you to specify the object ID of the access control list. Use CHECK_PRIVILEGE_ACLID if you need to evaluate multiple access control lists, when you query the DBA_NETWORK_ACLS data dictionary view. For better performance, call CHECK_PRIVILEGE_ACLID on multiple access control lists rather than using CHECK_PRIVILEGE on each one individually.

Users without database administrator privileges do not have the privilege to access the access control lists or to invoke those DBMS_NETWORK_ACL_ADMIN functions. However, they can query the USER_NETWORK_ACL_PRIVILEGES data dictionary view to check their privileges instead.

Database administrators and users can use the following DBMS_NETWORK_ACL_UTILITY functions to determine if two hosts, domains, or subnets are equivalent, or if a host, domain, or subnet is equal to or contained in another host, domain, or subnet:

  • EQUALS_HOST: Returns a value to indicate if two hosts, domains, or subnets are equivalent

  • CONTAINS_HOST: Returns a value to indicate if a host, domain, or subnet is equal to or contained in another host, domain, or subnet, and the relative order of precedence of the containing domain or subnet for its ACL assignments

If you do not use IPv6 addresses, database administrators and users can use the following DBMS_NETWORK_ACL_UTILITY functions to generate the list of domains or IPv4 subnet a host belongs to and to sort the access control lists by their order of precedence according to their host assignments:

  • DOMAINS: Returns a list of the domains or IP subnets whose access control lists may affect permissions to a specified network host, subdomain, or IP subnet

  • DOMAIN_LEVEL: Returns the domain level of a given host

The following sections explain how database administrators and users can check permissions for the user to connect to a network host or to perform domain name resolutions:

How a DBA Can Check User Network Connection and Domain Privileges

A database administrator can query the DBA_NETWORK_ACLS view to determine which access control lists are present for a specified host computer. This view shows the access control lists that determine the access to the network connection or domain, and then determines if each access control list grants (GRANTED), denies (DENIED), or does not apply (NULL) to the access privilege of the user. Only the database administrator can query this view.

The following sections provide examples that demonstrate how the database administrator can check user privileges for network connections and domain name resolution.

Database Administrator Checking User Connection Privileges

Example 4-24 shows how a database administrator can check the privileges for user preston to connect to www.us.example.com. Remember that the user name you enter for the user parameter in the CHECK_PRIVILEGE_ACLID procedure is case sensitive. In this example, entering the user name preston is correct, but entering Preston or preston is incorrect.

You can find the users in the current database instance by querying the DBA_USERS data dictionary view, for example:

SELECT USERNAME FROM DBA_USERS;

Example 4-24 Administrator Checking User Permissions for Network Host Connections

SELECT HOST, LOWER_PORT, UPPER_PORT, ACL,
       DECODE(
         DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(ACLID, 'PRESTON',
                                                      'connect'),
         1, 'GRANTED', 0, 'DENIED', NULL) PRIVILEGE
  FROM (SELECT HOST, LOWER_PORT, UPPER_PORT, ACL, ACLID,
               DBMS_NETWORK_ACL_UTILITY.CONTAINS_HOST('www.us.example.com',
                                                      HOST) PRECEDENCE
          FROM DBA_NETWORK_ACLS)
 WHERE PRECEDENCE IS NOT NULL
 ORDER BY PRECEDENCE DESC,
          LOWER_PORT NULLS LAST,
          UPPER_PORT NULLS LAST;

 HOST                 LOWER_PORT UPPER_PORT ACL                  PRIVILEGE
 -------------------- ---------- ---------- -------------------- ---------
 www.us.example.com           80         80 /sys/acls/www.xml    GRANTED
 www.us.example.com         3000       3999 /sys/acls/www.xml    GRANTED
 www.us.example.com                         /sys/acls/www.xml    GRANTED
 *.example.com                              /sys/acls/all.xml
 *                                          /sys/acls/all.xml

In this example, user preston was granted privileges for all the network host connections found for www.us.example.com. However, suppose preston had been granted access to a host connection on port 80, but then denied access to the host connections on ports 3000–3999. In this case, you need to create one access control list for the host connection on port 80, and a separate access control list for the host connection on ports 3000–3999.

Database Administrator Checking User Privileges for Domain Name Resolution

Example 4-25 shows how a database administrator can check the privileges of user preston to perform domain name resolution for the host www.us.example.com. In this example, only the access control lists assigned to hosts without a port range because the resolve privilege has no effect to those with a port range. (Remember that the user name you enter for the user parameter in CHECK_PRIVILEGE_ACLID is case sensitive.)

Example 4-25 Administrator Checking Permissions for Domain Name Resolution

SELECT HOST, ACL,
       DECODE(
         DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(ACLID, 'PRESTON',
                                                      'resolve'),
         1, 'GRANTED', 0, 'DENIED', NULL) PRIVILEGE
  FROM (SELECT HOST, LOWER_PORT, UPPER_PORT, ACL, ACLID,
               DBMS_NETWORK_ACL_UTILITY.CONTAINS_HOST('www.us.example.com',
                                                      HOST) PRECEDENCE
          FROM DBA_NETWORK_ACLS
         WHERE LOWER_PORT IS NULL AND UPPER_PORT IS NULL)
 WHERE PRECEDENCE IS NOT NULL
 ORDER BY PRECEDENCE DESC;

 HOST                 ACL                  PRIVILEGE
 -------------------- -------------------- ---------

 www.us.example.com   /sys/acls/www.xml    GRANTED
 *.example.com        /sys/acls/all.xml
 *                    /sys/acls/all.xml

How Users Can Check Their Network Connection and Domain Privileges

Users can query the USER_NETWORK_ACL_PRIVILEGES view to check their network and domain permissions. The USER_NETWORK_ACL_PRIVILEGES view is PUBLIC, so all users can select from it.

This view hides the access control lists from the user. It evaluates the permission status for the user (GRANTED or DENIED) and filters out the NULL case because the user does not need to know when the access control lists do not apply to him or her. In other words, Oracle Database only shows the user on the network hosts that explicitly grant or deny access to him or her. Therefore, the output does not display the *.example.com and * that appear in the output from the database administrator-specific DBA_NETWORK_ACLS view.

The following sections provide examples that demonstrate how a database administrator can check user permissions for network connections and domain name resolution.

User Checking His or Her Network Connection Privileges

Example 4-26 shows how user preston can check her privileges to connect to www.us.example.com.

Example 4-26 User Checking Permissions for Network Host Connections

SELECT HOST, LOWER_PORT, UPPER_PORT, STATUS PRIVILEGE
  FROM (SELECT HOST, LOWER_PORT, UPPER_PORT, STATUS,
               DBMS_NETWORK_ACL_UTILITY.CONTAINS_HOST('www.us.example.com',
                                                      HOST) PRECEDENCE
          FROM USER_NETWORK_ACL_PRIVILEGES
         WHERE PRIVILEGE = 'connect')
 WHERE PRECEDENCE IS NOT NULL
 ORDER BY PRECEDENCE DESC,
          LOWER_PORT NULLS LAST,
          UPPER_PORT NULLS LAST;

 HOST                 LOWER_PORT UPPER_PORT ACL                  PRIVILEGE
 -------------------- ---------- ---------- -------------------- ---------
 www.us.example.com           80         80 /sys/acls/www.xml    GRANTED
 www.us.example.com         3000       3999 /sys/acls/www.xml    GRANTED
 www.us.example.com                         /sys/acls/www.xml    GRANTED

User Checking Own Privileges for Domain Name Resolution

Example 4-25 shows how the user preston can check her privileges to perform domain name resolution for www.us.example.com:

Example 4-27 User Checking Privileges for Domain Name Resolution

SELECT HOST, STATUS PRIVILEGE
  from (SELECT HOST, STATUS,
               DBMS_NETWORK_ACL_UTILITY.CONTAINS_HOST('www.us.example.com',
                                                      HOST) PRECEDENCE
          FROM USER_NETWORK_ACL_PRIVILEGES
         WHERE PRIVILEGE = 'resolve' AND
               LOWER_PORT IS NULL AND UPPER_PORT IS NULL)
 WHERE PRECEDENCE IS NOT NULL
 ORDER BY PRECEDENCE DESC;

 HOST                 PRIVILEGE
 -------------------- ---------
 www.us.example.com   GRANTED

Setting the Precedence of Multiple Users and Roles in One Access Control List

By default, Oracle Database grants or denies privileges to users and roles based on their physical position in the access control list. The first user or role listed is granted or denied privileges first, followed the second user or role, and so on. For instance, suppose the code in Example 4-19 defined one role, ACCT_MGR, and two users, sebastian and preston, and the access control list XML file ordered these three as follows:

<acl ...>
  ...
  <ace>
    <principal>ACCT_MGR</principal>
    <grant>true</grant>
    <privilege><plsql:connect/></privilege>
   </ace>
  <ace>
    <principal>SEBASTIAN</principal>
    <grant>false</grant>
    <privilege><plsql:connect/></privilege>
  </ace>
  <ace>
    <principal>PRESTON</principal>
    <grant>false</grant>
    <privilege><plsql:connect/></privilege>
  </ace>
</acl>

ACCT_MGR is granted permissions first, followed by permission denials for sebastian and then preston. However, if sebastian and preston have been granted the ACCT_MGR role, they still could log in, because the ACCT_MGR role appears first in the list.

Even though these two users were granted the acct_mgr role, their specific jobs do not require them to have access to the www.example.com host. If the positions were reversed—the acct_mgr role listed after sebastian and preston—they would be denied the privilege of connecting to the network. To set the order of precedence of the ACE elements irrespective of their physical location in the CREATE_ACL and ADD_PRIVILEGE statements, you can use the position attribute.

For example, the following statements set the ACE elements in the resultant XML file in this order:

  1. The ACE element for sebastian appears first.

  2. The ACE element for preston appears second.

  3. The acct_mgr role appears last.

In this case, neither of these users will be able to connect, because their grant privileges, which are set to FALSE, are evaluated before the acct_mgr role.

BEGIN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  acl          => 'us-example-com-permissions.xml', 
  description  => 'Network connection permission for ACCT_MGR and users'); 
  principal    => 'ACCT_MGR',
  is_grant     => TRUE,
  privilege    => 'connect');
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ( 
  acl          => 'us-example-com-permissions.xml' 
  principal    => 'SEBASTIAN', 
  is_grant     => FALSE, 
  privilege    => 'connect',
  position     => 1); 
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ( 
  acl          => 'us-example-com-permissions.xml' 
  principal    => 'PRESTON', 
  is_grant     => FALSE, 
  privilege    => 'connect',
  position     => 2); 
END;
/

Finding Information About Access Control Lists Configured for User Access

Table 4-6 lists data dictionary views that you can use to find information about existing access control lists. See Oracle Database Reference for more information about these views.

Table 4-6 Data Dictionary Views That Display Information about Access Control Lists

View Description

DBA_NETWORK_ACLS

Shows the access control list assignments to the network hosts. The SELECT privilege on this view is granted to the SELECT_CATALOG_ROLE role only.

DBA_NETWORK_ACL_PRIVILEGES

Shows the network privileges defined in all access control lists that are currently assigned to network hosts. The SELECT privilege on this view is granted to the SELECT_CATALOG_ROLE role only.

DBA_WALLET_ACLS

Lists wallets that have been assigned access control lists.

USER_NETWORK_ACL_PRIVILEGES

Shows the status of the network privileges for the current user to access network hosts. The SELECT privilege on the view is granted to PUBLIC.


Finding Information About User Privileges and Roles

Table 4-7 lists data dictionary views that you can query to access information about grants of privileges and roles. See Oracle Database Reference for detailed information about these views.

Table 4-7 Data Dictionary Views That Display Information about Privileges and Roles

View Description

ALL_COL_PRIVS

Describes all column object grants for which the current user or PUBLIC is the object owner, grantor, or grantee

ALL_COL_PRIVS_MADE

Lists column object grants for which the current user is object owner or grantor.

ALL_COL_PRIVS_RECD

Describes column object grants for which the current user or PUBLIC is the grantee

ALL_TAB_PRIVS

Lists the grants on objects where the user or PUBLIC is the grantee

ALL_TAB_PRIVS_MADE

Lists the all object grants made by the current user or made on the objects owned by the current user.

ALL_TAB_PRIVS_RECD

Lists object grants for which the user or PUBLIC is the grantee

DBA_COL_PRIVS

Describes all column object grants in the database

DBA_EPG_DAD_AUTHORIZATION

Describes the database access descriptors (DAD) that are authorized to use a different user's privileges.

DBA_TAB_PRIVS

Lists all grants on all objects in the database

DBA_ROLES

Lists all roles that exist in the database, including secure application roles

DBA_ROLE_PRIVS

Lists roles directly granted to users and roles

DBA_SYS_PRIVS

Lists system privileges granted to users and roles

ROLE_ROLE_PRIVS

Lists roles granted to other roles. Information is provided only about roles to which the user has access.

ROLE_SYS_PRIVS

Lists system privileges granted to roles. Information is provided only about roles to which the user has access.

ROLE_TAB_PRIVS

Lists object privileges granted to roles. Information is provided only about roles to which the user has access.

USER_COL_PRIVS

Describes column object grants for which the current user is the object owner, grantor, or grantee

USER_COL_PRIVS_MADE

Describes column object grants for which the current user is the grantor

USER_COL_PRIVS_RECD

Describes column object grants for which the current user is the grantee

USER_EPG_DAD_AUTHORIZATION

Describes the database access descriptors (DAD) that are authorized to use a different user's privileges.

USER_ROLE_PRIVS

Lists roles directly granted to the current user

USER_TAB_PRIVS

Lists grants on all objects where the current user is the grantee

USER_SYS_PRIVS

Lists system privileges granted to the current user

USER_TAB_PRIVS_MADE

Lists grants on all objects owned by the current user

USER_TAB_PRIVS_RECD

Lists object grants for which the current user is the grantee

SESSION_PRIVS

Lists the privileges that are currently enabled for the user

SESSION_ROLES

Lists the roles that are currently enabled to the user


This section provides some examples of using these views. For these examples, assume the following statements were issued:

CREATE ROLE security_admin IDENTIFIED BY password;

GRANT CREATE PROFILE, ALTER PROFILE, DROP PROFILE,
    CREATE ROLE, DROP ANY ROLE, GRANT ANY ROLE, AUDIT ANY,
    AUDIT SYSTEM, CREATE USER, BECOME USER, ALTER USER, DROP USER
    TO security_admin WITH ADMIN OPTION;

GRANT SELECT, DELETE ON SYS.AUD$ TO security_admin;

GRANT security_admin, CREATE SESSION TO swilliams;

GRANT security_admin TO system_administrator;

GRANT CREATE SESSION TO jward;

GRANT SELECT, DELETE ON emp TO jward;

GRANT INSERT (ename, job) ON emp TO swilliams, jward;

See Also:

Oracle Database Reference for a detailed description of these data dictionary views

Listing All System Privilege Grants

The following query returns all system privilege grants made to roles and users:

SELECT * FROM DBA_SYS_PRIVS;

GRANTEE            PRIVILEGE                         ADM
--------------     --------------------------------- ---
SECURITY_ADMIN     ALTER PROFILE                     YES
SECURITY_ADMIN     ALTER USER                        YES
SECURITY_ADMIN     AUDIT ANY                         YES
SECURITY_ADMIN     AUDIT SYSTEM                      YES
SECURITY_ADMIN     BECOME USER                       YES
SECURITY_ADMIN     CREATE PROFILE                    YES
SECURITY_ADMIN     CREATE ROLE                       YES
SECURITY_ADMIN     CREATE USER                       YES
SECURITY_ADMIN     DROP ANY ROLE                     YES
SECURITY_ADMIN     DROP PROFILE                      YES
SECURITY_ADMIN     DROP USER                         YES
SECURITY_ADMIN     GRANT ANY ROLE                    YES
SWILLIAMS          CREATE SESSION                    NO
JWARD              CREATE SESSION                    NO

See Oracle Database Reference for detailed information about the DBA_SYS_PRIVS view.

Listing All Role Grants

The following query returns all the roles granted to users and other roles:

SELECT * FROM DBA_ROLE_PRIVS;

GRANTEE            GRANTED_ROLE                         ADM
------------------ ------------------------------------ ---
SWILLIAMS          SECURITY_ADMIN                       NO

See Oracle Database Reference for detailed information about the DBA_ROLE_PRIVS view.

Listing Object Privileges Granted to a User

The following query returns all object privileges (not including column-specific privileges) granted to the specified user:

SELECT TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS
    WHERE GRANTEE = 'jward';

TABLE_NAME   PRIVILEGE    GRANTABLE
-----------  ------------ ----------
EMP          SELECT       NO
EMP          DELETE       NO

To list all the column-specific privileges that have been granted, use the following query:

SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE
    FROM DBA_COL_PRIVS;

GRANTEE      TABLE_NAME     COLUMN_NAME      PRIVILEGE
-----------  ------------   -------------    --------------
SWILLIAMS    EMP            ENAME            INSERT
SWILLIAMS    EMP            JOB              INSERT
JWARD        EMP            NAME             INSERT
JWARD        EMP            JOB              INSERT

See Oracle Database Reference for detailed information about the DBA_TAB_PRIVS view.

Listing the Current Privilege Domain of Your Session

The following query lists all roles currently enabled for the issuer:

SELECT * FROM SESSION_ROLES;

If user swilliams has the security_admin role enabled and issues the previous query, then Oracle Database returns the following information:

ROLE
------------------------------
SECURITY_ADMIN

The following query lists all system privileges currently available in the security domain of the issuer, both from explicit privilege grants and from enabled roles:

SELECT * FROM SESSION_PRIVS;

If user swilliams has the security_admin role enabled and issues the previous query, then Oracle Database returns the following results:

PRIVILEGE
----------------------------------------
AUDIT SYSTEM
CREATE SESSION
CREATE USER
BECOME USER
ALTER USER
DROP USER
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
AUDIT ANY
CREATE PROFILE
ALTER PROFILE
DROP PROFILE

If the security_admin role is disabled for user swilliams, then the first query would return no rows, while the second query would only return a row for the CREATE SESSION privilege grant.

See Oracle Database Reference for detailed information about the SESSION_ROLES view.

Listing Roles of the Database

You can use the DBA_ROLES data dictionary view to list all roles of a database and the authentication used for each role. For example, the following query lists all the roles in the database:

SELECT * FROM DBA_ROLES;

ROLE                  PASSWORD
----------------      --------
CONNECT               NO
RESOURCE              NO
DBA                   NO
SECURITY_ADMIN        YES

See Oracle Database Reference for detailed information about the DBA_ROLES view.

Listing Information About the Privilege Domains of Roles

The ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS data dictionary views contain information about the privilege domains of roles. For example, the following query lists all the roles granted to the system_admin role:

SELECT GRANTED_ROLE, ADMIN_OPTION
   FROM ROLE_ROLE_PRIVS
   WHERE ROLE = 'SYSTEM_ADMIN';

GRANTED_ROLE              ADM
----------------          ----
SECURITY_ADMIN            NO

The following query lists all the system privileges granted to the security_admin role:

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'SECURITY_ADMIN';

ROLE                    PRIVILEGE                      ADM
----------------------- -----------------------------  ---
SECURITY_ADMIN           ALTER PROFILE                 YES
SECURITY_ADMIN           ALTER USER                    YES
SECURITY_ADMIN           AUDIT ANY                     YES
SECURITY_ADMIN           AUDIT SYSTEM                  YES
SECURITY_ADMIN           BECOME USER                   YES
SECURITY_ADMIN           CREATE PROFILE                YES
SECURITY_ADMIN           CREATE ROLE                   YES
SECURITY_ADMIN           CREATE USER                   YES
SECURITY_ADMIN           DROP ANY ROLE                 YES
SECURITY_ADMIN           DROP PROFILE                  YES
SECURITY_ADMIN           DROP USER                     YES
SECURITY_ADMIN           GRANT ANY ROLE                YES

The following query lists all the object privileges granted to the security_admin role:

SELECT TABLE_NAME, PRIVILEGE FROM ROLE_TAB_PRIVS
    WHERE ROLE = 'SECURITY_ADMIN';

TABLE_NAME                     PRIVILEGE
---------------------------    ----------------
AUD$                           DELETE
AUD$                           SELECT

See Oracle Database Reference for detailed information about the ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS views.