4 Configuring Privilege and Role Authorization

4.3.2.1 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 SYS /AS SYSDBA 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.

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 as SYS or connect using the SYSDBA 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.

4.3.2.2 Securing Scheduler Jobs That Run in the Schema of a Grantee

The CREATE EXTERNAL JOB privilege is automatically created in the schema of the grantee user so that operating system jobs can run outside the database. To support backward compatibility, by default, this privilege is granted to all existing users who have the CREATE JOB privilege. For greater security, grant the CREATE EXTERNAL JOB privilege only to SYS, database administrators, and those users who need it.

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

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.

4.4.1.1 Properties of Roles and Why They Are Advantageous

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

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.

4.4.1.2 Common Uses of Roles

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

• To manage the privileges for a database application (see "Common Uses of Application Roles")

• To manage the privileges for a user group (see "Common Uses of User Roles")

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

Figure 4-1 Common Uses for Roles

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

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

4.4.1.5 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 outline these privilege restrictions concerning DDL statements:

• All system privileges and schema 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

  • Schema object privileges: ALTER and INDEX privileges for a table

  Note:

  The REFERENCES object privilege for a table cannot be used 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. For example, a user who receives the SELECT ANY TABLE system privilege or the SELECT object privilege for a table through a role can use neither privilege to create a view on a table that belongs to another user.

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

• Granted a role that has the SELECT object privilege for the employees table, but also indirectly granted 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 usable view on the employees table, because the SELECT object privilege for the employees table was granted through a role. Any views created will produce errors when accessed.

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

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

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

4.4.4.1 Authorizing a Roles 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 a SET ROLE statement. However, if the role is made a default role and enabled at connection time, then the user is not required to enter a 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 morework2do must be supplied.

4.4.4.2 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-4 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.

CREATE ROLE admin_role IDENTIFIED USING hr.admin;

When you enable the default roles of the user at login as specified in the user profile, Oracle Database performs no checking for application roles.

See the following for more information about secure application roles:

• "Further Securing Role Privileges by Using Secure Application Roles"

• "Creating a Secure Application Role to Control Access to Applications"

• Oracle Database 2 Day + Security Guide

4.4.4.3 Authorizing a Role by Using an External Source

You can create roles that are authorized by the operating system or network clients.

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

CREATE ROLE accts_rec IDENTIFIED EXTERNALLY;

4.4.4.4 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-6 creates 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.

4.4.5.1 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 on a selective basis.

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

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

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

4.5.3.1 Granting and Revoking Schema Object Privileges

Schema 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 for users and roles using the following:

• The GRANT and REVOKE SQL statements

• Oracle Enterprise Manager Database Control

4.5.3.2 Who Can Grant Schema Object Privileges?

A user automatically has all object privileges for schema objects contained in his or her schema. A user can grant any object privilege on any schema object the user owns to any other user or role. A user with the GRANT ANY OBJECT PRIVILEGE can grant or revoke any specified object privilege to another user with or without the GRANT OPTION of the GRANT statement. Otherwise, the grantee can use the privilege, but cannot grant it to other users.

4.5.3.3 Using Privileges with Synonyms

A schema object and its synonym are equivalent with respect to privileges. That is, the object privileges granted for 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 to 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.

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

4.5.4.2 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).

4.5.5.1 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. Schema object privileges for a view allow various DML operations, which as noted affect the base tables from which the view is derived.

4.5.5.2 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, to grant other users access to your view, you must have received object privileges to the base objects with the GRANT OPTION clause or appropriate system privileges with the ADMIN OPTION clause. If you have not, then grantees cannot access your view.

  See Also:

  Oracle Database SQL Language Reference

4.5.5.3 Increasing Table Security with Views

To use a view, you require appropriate privileges only for the view itself. You do not require privileges on base objects underlying the view.

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

• 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 pseudocolumn, whose values always refer to the current user. This view combines both column-level security and value-based security.

4.5.6.1 Using the EXECUTE Privilege for Procedure Privileges

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

4.5.6.2 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 does not perform a run-time privilege check when the procedure 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.

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 owner grants to another user the right to use that procedure, then the owner of the object privileges for the objects referenced by the procedure apply to that user's exercise of the procedure. 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 the privileges of the owner of a definer's rights stored procedure 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.

How Procedure Privileges Affect Invoker's Rights

An invoker's rights procedure executes with all of the invoker's privileges. Oracle Database enables roles 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 (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, and does not performs 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 only needs to 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.

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

4.5.6.4 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-8 shows four procedures created in the bodies of two packages.

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; 

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; 

4.5.7.1 System Privileges for Named Types

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

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

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

4.5.7.3 Method Execution Model

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

4.5.7.4 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 explicitly granted the EXECUTE object privileges to access all types referenced by the table, or were granted the EXECUTE ANY TYPE system privilege. The owner cannot obtain the required privileges through roles.

• If the table owner intends to grant access to the table to other users, then the owner must have 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 table owner has insufficient privileges to grant access on the type to other users.

  See Also:

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

4.5.7.5 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;

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

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 needs to 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.

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

4.5.7.7 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 by 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.

4.6.1.1 Granting the ADMIN OPTION

A user or role that is granted a privilege or role, which specifies the WITH ADMIN OPTION clause, has the following expanded capabilities:

• The grantee can grant or revoke the system privilege or role to or from any user or other 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-10 grants the new_dba role with the WITH ADMIN OPTION clause to user michael.

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.

4.6.1.2 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 and password do not exist in the database, then a new user with that user name and password is created.

Example 4-11 creates psmith as a new user while granting psmith the CONNECT system privilege.

GRANT CONNECT TO psmith IDENTIFIED BY two_4_one;

4.6.2.1 Specifying the GRANT OPTION

Specify the WITH GRANT OPTION clause with the GRANT statement to enable the grantee to grant the object privileges to other users and roles. 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.

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

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, OWNER, GRANTOR, PRIVILEGE, GRANTABLE
  FROM DBA_TAB_PRIVS 
  WHERE TABLE_NAME = 'EMPLOYEES' and OWNER = 'HR';

GRANTEE  OWNER GRANTOR PRIVILEGE    GRANTABLE
-------- ----- ------- -----------  ----------
BLAKE    HR    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  OWNER GRANTOR  PRIVILEGE    GRANTABLE
-------- ----- -------- --------     ----------
BLAKE    HR    HR       SELECT       YES       
CLARK    HR    BLAKE    SELECT       NO        

This occurs because blake already possesses the SELECT privilege on hr.employees with the GRANT OPTION.

4.6.2.3 Granting Privileges on Columns

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

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

GRANT INSERT (acct_no) ON accounts TO scott;

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;

4.6.2.4 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).

4.7.2.1 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  OWNER GRANTOR PRIVILEGE    GRANTABLE
-------- ----- ------- -----------  ----------
BLAKE    HR    HR       SELECT       YES       
CLARK    HR    BLAKE    SELECT       NO        
CLARK    HR    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  OWNER GRANTOR PRIVILEGE    GRANTABLE
-------- ----- ------- -----------  ----------
BLAKE    HR    HR       SELECT      YES       
CLARK    HR    HR       SELECT      NO        

If blake issues the REVOKE statement again, then this time the effect will be to remove the object privilege granted by hr.

4.7.2.2 Revoking Column-Selective Object Privileges

Although users can grant column-selective 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 grant again the column-specific privileges that should remain.

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 grants again the UPDATE privilege on the dname column to the role human_resources.

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

4.7.3.1 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 fails until the privilege is reauthorized.

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

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

The syntax for creating an access control list is as follows:

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-mycompany-com-permissions.xml',
  

• description: Enter a brief description of the file's purpose. For example:

  description => 'Network connection permission for ACCT_MGR role',
  

• principal: Enter the 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 user name preston 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 views, for example:

  SELECT USERNAME FROM DBA_USERS;
  SELECT ROLE FROM DBA_ROLES;
  

• 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 "Using Data Dictionary Views to Find Information About Access Control Lists" 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, 2007, at 6:30 a.m. in San Francisco, California, U.S., which is in the Pacific time zone:

  start_date => '2006-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 will expire 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, 2007, at 11:59 p.m. in San Francisco, California, U.S., which is in the Pacific time zone:

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

To add more users or roles to the access control list, 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.

4.11.3.2 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 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-mycompany-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.mycompany.com',
  

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

  • "Using Wildcard Characters in Network Host Computers"

  • "Checking Privilege Assignments That Affect User Access to a Network Host"

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

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

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

4.11.4.1 Example of Creating a Simple Access Control List

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

-- First, create the access control list, which includes one role:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  acl          => 'us-mycompany-com-permissions.xml', 
  description  => 'Network connection permission for ACCT_MGR', 
  principal    => 'ACCT_MGR', 
  is_grant     => TRUE, 
  privilege    => 'connect'); 
END;

-- Second, assign the access control list a network host:

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

This example creates the us-mycompany-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.

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

Example 4-17 shows how to create a slightly more complex version of the us-mycompany-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"Using Wildcard Characters in 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.

-- Create the access control list:

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

-- Assign the access control list to hosts:

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

The us-mycompany-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-18 shows how the DBA_NETWORK_ACL_PRIVILEGES data dictionary view displays the privilege granted in the previous access control list.

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

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

HOST                       LOWER_PORT UPPER_PORT
ACL
ACLID
-------------------- ---------- ----------
------------------------------------------
--------------------------------
*.us.mycompany.com                   
/sys/acls/us-mycompany-com-permissions.xml 2EF86135D0E29B2AE040578CE4043250
*.uk.mycompany.com      80             99
/sys/acls/us-mycompany-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.

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

This section provides examples that demonstrate how the database administrator can check user privileges for network connections and domain name resolution.

• Database Administrator Checking User Connection Privileges

• Database Administrator Checking User Privileges for Domain Name Resolution

Database Administrator Checking User Connection Privileges

Example 4-20 shows how a database administrator can check the privileges for user preston to connect to www.us.mycompany.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;

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 DBA_NETWORK_ACLS
WHERE host IN
  (SELECT * FROM
     TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('www.us.mycompany.com')))
 ORDER BY 
  DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL(host) DESC, LOWER_PORT, UPPER_PORT;

 HOST                 LOWER_PORT UPPER_PORT ACL                  PRIVILEGE
 -------------------- ---------- ---------- -------------------- ---------
 www.us.mycompany.com         80         80 /sys/acls/www.xml    GRANTED
 www.us.mycompany.com       3000       3999 /sys/acls/www.xml    GRANTED
 www.us.mycompany.com                       /sys/acls/www.xml    GRANTED
 *.mycompany.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.mycompany.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-21 shows how a database administrator can check the privileges of user preston to perform domain name resolution for the host www.us.mycompany.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.)

SELECT HOST, ACL, DECODE(
     DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'PRESTON', 'resolve'),
     1, 'GRANTED', 0, 'DENIED', null) privilege
FROM DBA_NETWORK_ACLS
WHERE host IN
 (SELECT * FROM
    TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('www.us.mycompany.com'))) AND
 LOWER_PORT IS NULL AND UPPER_PORT IS NULL
ORDER BY DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL(host) DESC;

 HOST                 ACL                  PRIVILEGE
 -------------------- -------------------- ---------
 www.us.mycompany.com /sys/acls/www.xml    GRANTED
 *.mycompany.com      /sys/acls/all.xml
 *                    /sys/acls/all.xml

4.11.8.2 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 *.mycompany.com and * that appear in the output from the database administrator-specific DBA_NETWORK_ACLS view.

These 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

• User Checking Own Privileges for Domain Name Resolution

User Checking His or Her Network Connection Privileges

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

SELECT HOST, LOWER_PORT, UPPER_PORT, STATUS PRIVILEGE
FROM USER_NETWORK_ACL_PRIVILEGES
WHERE host IN
   (SELECT * FROM
      TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('www.us.mycompany.com'))) AND
      PRIVILEGE = 'connect'
ORDER BY DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL(host) DESC, LOWER_PORT;

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

User Checking Own Privileges for Domain Name Resolution

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

SELECT host, status privilege
  FROM user_network_acl_privileges
 WHERE host IN
   (SELECT * FROM
      TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('www.us.mycompany.com'))) AND
      privilege = 'resolve'
ORDER BY DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL(host) DESC;

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