Skip Headers
Oracle® Database Security Guide
12c Release 1 (12.1)

E48135-09
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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

This chapter contains:

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

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

  • Configuring fine-grained access control for users and roles that need to access external network services from the database. This way, specific groups of users can connect to one or more host computers, based on privileges that you grant them. Typically, you use this feature to control access to applications that run on specific host addresses.

  • Configuring fine-grained access control to Oracle wallets to make HTTP requests that require password or client-certificate authentication. This feature enables you to grant privileges to users who are using passwords and client certificates stored in Oracle wallets to access external protected HTTP resources through the UTL_HTTP package. For example, you can configure applications to use the credentials stored in the wallets instead of hard-coding the credentials in the applications.

About Fine-Grained Access Control to External Network Services

To implement fine-grained access control to external network services, you use Oracle Application Security access control lists (ACL). This guide explains how to configure the access control for database users and roles by using the DBMS_NETWORK_ACL_ADMIN PL/SQL package.

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

See Also:

"Tutorial: Adding an Email Alert to a Fine-Grained Audit Policy" for an example of configuring access control to external network services for email alerts

About Access Control to Oracle Wallets

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

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

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

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

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

Upgraded Applications That Depend on Packages That Use External Network Services

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

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

Use the procedures in this section to reconfigure the network access for the application.

See Also:

Configuring Access Control for External Network Services

This section contains:

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. It 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 insensitive, 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.

Example 6-1 Granting Privileges to a Database Role External Network Services

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

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.

Example 6-2 Revoking External Network Services 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.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_NETWORK_ACL_ADMIN.REMOVE_HOST_ACE procedure

Configuring Access Control to an Oracle Wallet

This section contains:

About Configuring Access Control to an Oracle Wallet

This method of access control configuration lets you grant access to the passwords and client certificates that are stored in an Oracle wallet to users to authenticate themselves to an external 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

To create the wallet, you can use either the mkstore command-line utility or the Oracle Wallet Manager user interface. To store passwords in the wallet, you must use mkstore. You can use both standard and PKCS11 wallet types, and the wallet can be an auto-login wallet if you want.

When you create the wallet, do the following:

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

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

Step 2: 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. To do so, you use the DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE procedure.

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

See Also:

Oracle Database Real Application Security Administrator's and Developer's Guide for information about additional XS$ACE_TYPE parameters that you can include for the ace parameter setting: granted, inverted, start_date, and end_date

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

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

For example:

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

In this specification:

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

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

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

    For example:

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

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

    For example:

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

    For example:

    request_context      => req_context);
    

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

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

For example:

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

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

Using Only a Client Certificate to Authenticate

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

Using the Password to Authenticate

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

For example:

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

In this specification:

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

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

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

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

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

    For example:

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

    For example:

    for_proxy       => TRUE);
    

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

Revoking Access Control Privileges for Oracle Wallets

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.

Example 6-3 shows how to revoke access control privileges from an Oracle wallet.

Example 6-3 Revoking Access Control Privileges for an Oracle Wallet

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 specification, the TRUE setting for remove_empty_acl removes the ACL when it becomes empty when the wallet ACE is removed.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_NETWORK_ACL_ADMIN.REMOVE_WALLET_ACE procedure

Examples of Configuring Access Control for External Network Services

This section contains:

See Also:

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

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

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

Example 6-4 Configuring Access Control for a Single Role and Network Connection

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

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

Example 6-5 Configuring Access Control List Using a Grant and a Deny for User and Role

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-6 shows how the DBA_HOST_ACES data dictionary view displays the privilege granted in the previous access control list.

Example 6-6 Using the DBA_HOST_ACES View to Show Granted Privileges

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-7 configures wallet access for two Human Resources department roles, hr_clerk and hr_manager. These roles use the use_passwords privilege to access passwords stored in the wallet. In this example, the wallet will not be shared with other applications within the same database session.

Example 6-7 Configuring ACL Access Using Passwords in a Non-Shared Wallet

/* 1. At a command prompt, create the wallet. The following example uses the 
      user name hr_access as the alias to identify the user name and password 
      stored in the wallet. You must use this alias name when you call the
      SET_AUTHENTICATION_FROM_WALLET procedure later on. */ 
$ mkstore -wrl $ORACLE_HOME/wallets/hr_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-8 configures the wallet to be used for a shared database session; that is, all applications within the current database session will have access to this wallet.

Example 6-8 Configuring ACL Access for a Wallet in a Shared Database Session

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

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

Specifying a Group of Network Host Computers

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

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

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

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

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

  1. server.us.example.com

  2. *.us.example.com

  3. *.example.com

  4. *.com

  5. *

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

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

  1. 192.0.2.3 (or ::ffff:192.0.2.3)

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

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

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

  5. ...

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

  7. ...

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

  9. ...

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

  11. ...

  12. ::ffff:192.0.2.3/95

  13. ::ffff:192.0.2.3/94

  14. ...

  15. *

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

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

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

Checking Privilege Assignments That Affect User Access to Network Hosts

This section contains:

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 view to find the privileges that have been granted for specific users or roles. 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.

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

Example 6-9 Administrator Checking User Network Access Control Permissions

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 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-10 shows how user preston can check her privileges to connect to www.us.example.com.

Example 6-10 User Checking Network Access Control Permissions

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 

Configuring Network Access for Java Debug Wire Protocol Operations

If you want to debug Java and PL/SQL stored procedures in the database through a Java Debug Wire Protocol (JDWP)-based debugger, such as SQL Developer, JDeveloper, or Oracle Developer Tools For Visual Studio (ODT), then you must be granted the jdwp ACL privilege to connect your database session to the debugger at a particular host. The jdwp privilege is needed in conjunction with the DEBUG CONNECT SESSION system privilege.

If you are not granted the jdwp ACL privilege, then when you try to debug your Java and PL/SQL stored procedures from a remote host, the following errors may appear:

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line line_number

Example 6-11 illustrates how to configure network access for JDWP operations.

Example 6-11 Configuring Network Access for JDWP Operations

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host         => 'host',
    lower_port   => null|port_number,
    upper_port   => null|port_number,
    ace => xs$ace_type(privilege_list => xs$name_list('jdwp'),
                       principal_name => 'username',
                       principal_type => xs_acl.ptype_db));
END;
/

In this specification:

  • host can be a host name, domain name, IP address, or subnet.

  • port_number enables you to specify a range of ports. If you want to use any port, then omit the lower_port and upper_port values.

  • username is case-insensitive unless it is quoted (for example, principal_name => '"PSMITH"').

See Also:

Data Dictionary Views for Access Control Lists Configured for User Access

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

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

View Description

DBA_HOST_ACES

Shows the network privileges defined for the network hosts. The SELECT privilege on this view is granted to the SELECT_CATALOG_ROLE role only.

DBA_WALLET_ACES

Lists the wallet path, ACE order, start and end times, grant type, privilege, and information about principals

DBA_WALLET_ACLS

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

DBA_HOST_ACLS

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

USER_HOST_ACES

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

USER_WALLET_ACES

Shows the status of the wallet privileges for the current user to access contents in the wallets. The SELECT privilege on the view is granted to PUBLIC.