6 Managing Fine-Grained Access in PL/SQL Packages and Types

Syntax for Configuring Access Control for External Network Services

You can use the DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE procedure to grant the necessary privileges to a user.

This procedure appends an access control entry (ACE) with the specified privilege to the ACL for the given host, and creates the ACL if it does not exist yet. The resultant configuration resides in the SYS schema, not the schema of the user who created it.

The syntax is as follows:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
  host         => 'host_name', 
  lower_port   => null|port_number,
  upper_port   => null|port_number,
  ace          => ace_definition); 
END;

In this specification:

• host: Enter the name of the host. It can be the host name or an IP address of the host. You can use a wildcard to specify a domain or an IP subnet. (See "Precedence Order for a Host Computer in Multiple Access Control List Assignments" for the precedence order when you use wildcards in domain names.) The host or domain name is case insensitive. Examples are as follows:

  host     => 'www.example.com',
  
  host     => '*example.com',
  

• 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). The range of port numbers is between 1 and 65535.

  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 the resolve privilege. The default is null, which means that there is no port restriction (that is, the ACL applies to all ports). The range of port numbers is between 1 and 65535

  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), then 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 has no effect when a port range is specified in the access control list assignment.

• ace: Define the ACE by using the XS$ACE_TYPE constant, in the following format:

  ace    => xs$ace_type(privilege_list => xs$name_list('privilege'),
                        principal_name => 'user_or_role',
                        principal_type => xs$ace_type_user));
  

  In this specification:

  • privilege_list: Enter one or more of the following privileges, which are case insensitive. Enclose each privilege with single quotation marks and separate each with a comma (for example, 'http', 'http_proxy').

    For tighter access control, grant only the http, http_proxy, or smtp privilege instead of the connect privilege if the user uses the UTL_HTTP, HttpUriType, UTL_SMTP, or UTL_MAIL only.

    - http: Makes an HTTP request to a host through the UTL_HTTP package and the HttpUriType type

    - http_proxy: Makes an HTTP request through a proxy through the UTL_HTTP package and the HttpUriType type. You must include http_proxy in conjunction to the http privilege if the user makes the HTTP request through a proxy.

    - smtp: Sends SMTP to a host through the UTL_SMTP and UTL_MAIL packages

    - resolve: Resolves a network host name or IP address through the UTL_INADDR package

    - connect: Grants the user permission to connect to a network service at a host through the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and DBMS_LDAP packages, or the HttpUriType type

    - jdwp: Used for Java Debug Wire Protocol debugging operations for Java or PL/SQL stored procedures. See "Configuring Network Access for Java Debug Wire Protocol Operations" for more information.

  • principal_name: Enter a database user name or role. This value is case insensistive, unless you enter it in double quotation marks (for example, '"ACCT_MGR'").

  • principal_type: Enter XS_ACL.PTYPE_DB for a database user or role. You must specify PTYPE_DB because the principal_type value defaults to PTYPE_XS, which is used to specify an Oracle Database Real Application Security application user.

Example: Configuring Access Control for External Network Services

Example 6-1 shows how to grant the http and smtp privileges to the acct_mgr database role for an ACL created for the host www.example.com.

BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
  host       => 'www.example.com',
  ace        =>  xs$ace_type(privilege_list => xs$name_list('http', 'smtp'),
                             principal_name => 'acct_mgr',
                             principal_type => xs_acl.ptype_db));
END;
/

Revoking Access Control Privileges for External Network Services

You can remove access control privileges for external network services.

• To revoke access control privileges for external network services, run the DBMS_NETWORK_ACL_ADMIN.REMOVE_HOST_ACE procedure.

Example 6-2 shows how to revoke external network privileges.

BEGIN
 DBMS_NETWORK_ACL_ADMIN.REMOVE_HOST_ACE (
  host       => 'www.example.com',
  lower_port => 80,
  upper_port => upper_port => 3999,
  ace        => xs$ace_type(privilege_list => xs$name_list('http', 'smtp'),
                            principal_name => 'acct_mgr',
                            principal_type => xs_acl.ptype_db),
  remove_empty_acl => TRUE);
END;
/

In this specification, the TRUE setting for remove_empty_acl removes the ACL when it becomes empty when the ACE is removed.

About Configuring Access Control to an Oracle Wallet

You can configure access control to grant access to the passwords and client certificates that are stored in an Oracle wallet to users to authenticate themselves to an external network service when using the PL/SQL network utility packages.

This enables the user to gain access to the network service that requires password or certificate identification.

Step 1: Create an Oracle Wallet

When you create the Oracle wallet, you can use both standard and PKCS11 wallet types, and the wallet can be an auto-login wallet if you want.

• To create the wallet, use either the mkstore command-line utility or the Oracle Wallet Manager user interface.

  To store passwords in the wallet, you must use the mkstore utility.

When you create the wallet, you must 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: Configure Access Control Privileges for the Oracle Wallet

After you have created the wallet, you are ready to configure access control privileges for the wallet.

• Use the DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE procedure to configure the wallet access control privileges.

  The syntax for the DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE procedure is as follows:

  BEGIN
   DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE (
    wallet_path => 'directory_path_to_wallet',
    ace         => xs$ace_type(privilege_list => xs$name_list('privilege'),
                               principal_name => 'user_or_role',
                               principal_type => xs$ace_type_user));
  END;
  

  In this specification:

  • wallet_path: Enter the path to the directory that contains the wallet that you created in "Step 1: Create an Oracle 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_wallet',
    

  • ace: Define the ACL by using the XS$ACE_TYPE constant. For example:

      ace         =>  xs$ace_type(privilege_list => xs$name_list(privilege),
                                 principal_name => 'hr_clerk',
                                 principal_type => xs_acl.ptype_db);
    

    In this specification, privilege must be one of the following when you enter wallet privileges using xs$ace_type (note the use of underscores in these privilege names):

    • use_client_certificates

    • use_passwords

    For detailed information about these parameters, see the ace parameter description in "Syntax for Configuring Access Control for External Network Services". Be aware that for wallets, you must specify either the use_client_certificates or use_passwords privileges.

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

You can use the UTL_HTTP package to create a request object, which in turn can be used to hold wallet information. You can authenticate using a client certificate or a password.

Topics:

• Making the HTTPS Request with the Passwords and Client Certificates

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

• Use of Only a Client Certificate to Authenticate

• Use of a Password to Authenticate

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;

Revoking Access Control Privileges for Oracle Wallets

You can revoke access control privileges for an Oracle wallet.

• To revoke privileges from access control entries (ACE) in the access control list (ACL) of a wallet, run the DBMS_NETWORK_ACL_ADMIN.REMOVE_WALLET_ACE procedure.

  For example:

  BEGIN
   DBMS_NETWORK_ACL_ADMIN.REMOVE_WALLET_ACE (
    wallet_path   => 'file:/oracle/wallets/hr_wallet',
    ace           =>  xs$ace_type(privilege_list => xs$name_list(privilege),
                               principal_name => 'hr_clerk',
                               principal_type => xs_acl.ptype_db),
    remove_empty_acl  => TRUE);
  END;
  /
  

  In this example, the TRUE setting for remove_empty_acl removes the ACL when it becomes empty when the wallet ACE is removed.

Example: Access Control Configuration for a Single Role and Network Connection

Example 6-3 shows how you would configure access control for a single role (acct_mgr) and grant this role the http privilege for access to the www.us.example.com host. The privilege expires January 1, 2013.

BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
  host       => 'www.us.example.com',
  lower_port => 80,
  ace        =>  xs$ace_type(privilege_list => xs$name_list('http'),
                           principal_name => 'acct_mgr',
                           principal_type => xs_acl.ptype_db,
                           end_date => TIMESTAMP '2013-01-01 00:00:00.00 -08:00');
END;
/

Example: Access Control Using a Deny and a Grant for a User and a Role

You can configure access control to deny or grant privileges for a user and a role. Afterwards, you can query the DBA_HOST_ACES data dictionary view to find information about the privilege grants.

Example 6-4 grants to a database role (acct_mgr) but denies a particular user (psmith) even if he has the role. The order is important because ACEs are evaluated in the given order. In this case, the deny ACE (granted => false) must be appended first or else the user cannot be denied.

BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
  host       => 'www.us.example.com',
  lower_port => 80,
  upper_port => 80,
  ace        =>  xs$ace_type(privilege_list => xs$name_list('http'),
                             principal_name => 'psmith',
                             principal_type => xs_acl.ptype_db,
                             granted        => false));

 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
  host       => 'www.us.example.com',
  lower_port => 80,
  upper_port => 80,
  ace        =>  xs$ace_type(privilege_list => xs$name_list('http'),
                             principal_name => 'acct_mgr',
                             principal_type => xs_acl.ptype_db,
                             granted        => true));
END; 

Example 6-5 shows how the DBA_HOST_ACES data dictionary view displays the privilege granted in the previous access control list.

SELECT PRINCIPAL, PRIVILEGE, GRANT_TYPE FROM DBA_HOST_ACE WHERE PRIVILEGE = 'HTTP';

PRINCIPAL    PRIVILEGE  GRANT_TYPE
------------ ---------- --------------------
PSMITH       HTTP       FALSE
ACCT_MGR     HTTP       TRUE

Example: Access Control Configuring for Passwords in a Non-Shared Wallet

Example 6-6 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.

/* 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_wallet -create
Enter password: password
Enter password again: password
$ mkstore -wrl $ORACLE_HOME/wallets/hr_wallet -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 role.*/ 
BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE (
  wallet_path => 'file:/oracle/wallets/hr_wallet',
  ace         => xs$ace_type(privilege_list => xs$name_list('use_passwords'),
                             principal_name => 'hr_clerk',
                             principal_type => xs_acl.ptype_db));
END;
/

/* 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_wallet',
     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: Access Control Configuration for Wallets in a Shared Database Session

Example 6-7 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.

/* Follow these steps:
   1. Use Oracle Wallet Manager to create the wallet and add the client
      certificate.  

   2. In SQL*Plus, configure access control to grant privileges for the wallet.
      The following example grants the use_client_certificates privilege 
      to the hr_clerk and hr_mgr roles. */ 
BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE (
  wallet_path => 'file:/oracle/wallets/hr_wallet',
  ace         => xs$ace_type(privilege_list => xs$name_list('use-client_certificates'),
                             principal_name => 'hr_clerk',
                             principal_type => xs_acl.ptype_db));

 DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE (
  wallet_path => 'file:/oracle/wallets/hr_wallet',
  ace         => xs$ace_type(privilege_list => xs$name_list('use_client_certificates'),
                             principal_name => 'hr_mgr',
                             principal_type => xs_acl.ptype_db));
END;
/
COMMIT;

/* 3. Create a request object to handle the HTTP authentication for the wallet.*/
DECLARE
  req  UTL_HTTP.req;
BEGIN
  UTL_HTTP.SET_WALLET(
   path            => 'file:/oracle/wallets/hr_wallet',
   password        => NULL);
 req := UTL_HTTP.BEGIN_REQUEST(
   url             => 'www.hr_access.example.com',
   method          => 'POST',
   http_version    => NULL,
   request_context => NULL);
END;
/

About Checking Privilege Assignments that Affect User Access to Network Hosts

Database administrators can use the DBA_HOST_ACES 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.

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_HOST_ACES 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

How Administrators Can Check User Network Connection and Domain Privileges

A database administrator can query the DBA_HOST_ACES data dictionary view to find the privileges that have been granted for specific users or roles.

The DBA_HOST_ACES 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.

Example 6-8 shows how a database administrator can check the privileges for user preston to connect to www.us.example.com.

SELECT HOST, LOWER_PORT, UPPER_PORT,
       ACE_ORDER, PRINCIPAL, PRINCIPAL_TYPE,
       GRANT_TYPE, INVERTED_PRINCIPAL, PRIVILEGE,
       START_DATE, END_DATE
  FROM (SELECT ACES.*,
DBMS_NETWORK_ACL_UTILITY.CONTAINS_HOST('www.us.example.com', HOST) PRECEDENCE
          FROM DBA_HOST_ACES ACES)
 WHERE PRECEDENCE IS NOT NULL
 ORDER BY PRECEDENCE DESC,
          LOWER_PORT NULLS LAST,
          UPPER_PORT NULLS LAST,
          ACE_ORDER;
HOST               LOWER_PORT UPPER_PORT ACE_ORDER PRINCIPAL PRINCIPAL_TYPE   GRANT_TYPE INVERTED_PRINCIPAL PRIVILEGE START_DATE END_DATE
------------------ ---------- ---------- --------- --------- ---------------- ---------- ------------------ --------- ---------- --------
www.us.example.com         80        80          1 PRESTON DATABASE USER      GRANT      NO                 HTTP
www.us.example.com         80        80          2 SEBASTIAN DATABASE USER    GRANT      NO                 HTTP
*.us.example.com                                 1 ACCT_MGR DATABASE USER     GRANT      NO                 CONNECT
*                                                1 HR_DBA DATABASE USER       GRANT      NO                 CONNECT
*                                                1 HR_DBA DATABASE USER       GRANT      NO                 RESOLVE 

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 must configure access control for the host connection on port 80, and a separate access control configuration for the host connection on ports 3000–3999.

How Users Can Check Their Network Connection and Domain Privileges

Users can query the USER_HOST_ACES data dictionary view to check their network and domain permissions. The USER_HOST_ACES 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_HOST_ACES view.

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

SELECT HOST, LOWER_PORT, UPPER_PORT, PRIVILEGE, STATUS
  FROM (SELECT ACES.*,
DBMS_NETWORK_ACL_UTILITY.CONTAINS_HOST('www.us.example.com', HOST) PRECEDENCE
          FROM USER_HOST_ACES ACES)
 WHERE PRECEDENCE IS NOT NULL
 ORDER BY PRECEDENCE DESC,
          LOWER_PORT NULLS LAST,
          UPPER_PORT NULLS LAST;
 
HOST               LOWER_PORT UPPER_PORT PRIVILEGE STATUS
------------------ ---------- ---------- --------- -------
www.us.example.com         80        80  HTTP      GRANTED