Fine-grained access to network services in Oracle11gR1

Oracle allows several PL/SQL APIs (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR) to access external network services. These APIs use the TCP protocol in the previous database version (10 Gb) it is implemented through an on/off switch based on whether the user is authorized to execute a package. Oracle 11g introduces fine-grained access to network services,

You can use the access control list (ACL) in the XML database to control which user can access which network resources, regardless of the package authorization.

You can use FTP or WebDav to create, modify, and delete access control lists directly in the xml db database. Oracle provides the DBMS_NETWORK_ACL_ADMIN and DBMS_NETWORK_ACL_UTILITY packages to allow you to manage access control lists from PL/SQL, these APIs are the main character of this article.

Create an access control list (ACL)

The access control list is operated by using the DBMS_NETWORK_ACL_ADMIN package. The CREATE_ACL stored procedure uses the following parameters to create a new access control list:

? Acl-the name of the XML file in the access control list, which is generated under the/sys/acls directory in the xml db database? Description-description of the access control list? Principal-the first authorized or denied user account. Is it case sensitive? Is_grant-TRUE indicates that the permission is granted. FALSE indicates that the permission is denied? Privilege-grant the connect permission to UTL_TCP, UTL_SMTP, UTL_MAIL, and UTL_HTTP, and grant the resolve permission to UTL_INADDR name/ip resolution. Is it case sensitive? Start_date-the default value is NULL. After a value is specified, the access control list is activated only when or after the specified date is reached? En_date-end date of the access control list (optional)

The following code creates two test users as the principal, and then creates a new access control list.

CONN sys/password @ db11g AS SYSDBA

Create user test1 identified by test1;

Grant connect to test1;

Create user test2 identified by test2;

Grant connect to test2;

BEGIN

DBMS_NETWORK_ACL_ADMIN.create_acl (

Acl => 'test _ acl_file.xml ',

Description => 'a test of the ACL functionality ',

Principal => 'test1 ',

Is_grant => TRUE,

Privilege => 'connect ',

Start_date => policimestamp,

End_date => NULL );

COMMIT;

END;

/

Once created, the access control list is displayed in the http: // host: port/sys/acls directory.

Use the ADD_PRIVILEGE stored procedure to add other users or roles to the access control list. Its parameters are similar to those of the CREATE_ACL stored procedure. The DESCRIPTION parameter is omitted and the POSITION parameter is added, it is used to set the priority.

BEGIN

DBMS_NETWORK_ACL_ADMIN.add_privilege (

Acl => 'test _ acl_file.xml ',

Principal => 'test2 ',

Is_grant => FALSE,

Privilege => 'connect ',

Position => NULL,

Start_date => NULL,

End_date => NULL );

COMMIT;

END;

/

Each principal is defined in the access control list as an independent access control unit (ACE). When multiple principles are defined, they are evaluated in the order from top to bottom, until the last rule Defines permissions, this means that a role that denies access to a resource can be granted to a user, but if the user is defined as a principal in the file, this definition will overwrite the definition of the role and use the POSITION parameter to ensure that the permissions are evaluated in order.

Use the DELETE_PRIVILEGE stored procedure to remove permissions. If the value of IS_GRANT or PRIVILEGE is NULL, all permissions granted will be removed.

BEGIN

DBMS_NETWORK_ACL_ADMIN.delete_privilege (

Acl => 'test _ acl_file.xml ',

Principal => 'test2 ',

Is_grant => FALSE,

Privilege => 'connect ');

COMMIT;

END;

/

Use DROP_ACL to delete an access control list

BEGIN

DBMS_NETWORK_ACL_ADMIN.drop_acl (

Acl => 'test _ acl_file.xml ');

COMMIT;

END;

/Attach an access control list to the network

Use the ASSIGN_ACL stored procedure to assign an access control list to the network. It has the following parameters:

? Acl-name of the XML file in the access control list? Host-host Name, domain name, IP address or assigned subnet. The host name is case sensitive. Can wildcards be used for IP addresses and domain names? Lower_port-the default value is NULL, which specifies the lower port range for the connect permission? Upper_port-the default value is NULL. If lower_port is specified and the value of upper_port is NULL, upper_port is equivalent to lower_port.

The following code shows that the previously created access control list is assigned a specific IP address and a subnet.

BEGIN

DBMS_NETWORK_ACL_ADMIN.assign_acl (

Acl => 'test _ acl_file.xml ',

Host => '2014. 168.2.3 ',

Lower_port => 80,

Upper_port => NULL );

DBMS_NETWORK_ACL_ADMIN.assign_acl (

Acl => 'test _ acl_file.xml ',

Host => '10. 1. 10 .*',

Lower_port => NULL,

Upper_port => NULL );

COMMIT;

END;

/

Only one access control list can be assigned to a special combination of host and port range, assigning a new access control list to the host + port range will delete the previously allocated access control list, when you start a new allocation operation, pay special attention to the fact that the port closed in the previous access control list is now opened again, so it is allocated to 192.168.2.3: the Priority Ratio of the access control list of 80 is assigned to 192.168.2. * the access control list must be high.

The UNASSIGN_ACL stored procedure allows you to manually delete the access control list. It uses the same parameters as the ASSIGN_ACL stored procedure and uses the NULL parameter as the wildcard.

BEGIN

DBMS_NETWORK_ACL_ADMIN.unassign_acl (

Acl => 'test _ acl_file.xml ',

Host => '2014. 168.2.3 ',

Lower_port => 80,

Upper_port => NULL );

COMMIT;

END;

/

Access Control List View

The DBA_NETWORK_ACLS, DBA_NETWORK_ACL_PRIVILEGES, and USER_NETWORK_ACL_PRIVILEGES views display the current access control list settings. The following output shows that the delete/drop/unassign operation has not been performed.

The DBA_NETWORK_ACLS view displays information about network and access control list allocation.

COLUMN host FORMAT A30

COLUMN acl FORMAT A30

SELECT host, lower_port, upper_port, acl

FROM dba_network_acls;

HOST

------------------------------

10.1.10 .*

192.168.2.3

LOWER_PORT UPPER_PORT ACL ---------- --------------------------------/sys/acls/test_acl_file.xml

80 80/sys/acls/test_acl_file.xml

2 rows selected.

SQL>

The DBA_NETWORK_ACL_PRIVILEGES view displays the permissions associated with the access control list.

COLUMN acl FORMAT A30

COLUMN principal FORMAT A30

SELECT acl,

Principal,

Privilege,

Is_grant,

TO_CHAR (start_date, 'dd-MON-YYYY ') AS start_date,

TO_CHAR (end_date, 'dd-MON-YYYY ') AS end_date

FROM dba_network_acl_privileges;

ACL PRINCIPAL

------------------------------------------------------------

/Sys/acls/test_acl_file.xml TEST1

/Sys/acls/test_acl_file.xml TEST2

PRIVILE IS_GR START_DATE END_DATE ------- ----- ----------- connect true 02-APR-2008connect false2 rows selected.

SQL>

The USER_NETWORK_ACL_PRIVILEGES view displays the network access control list settings of the current user.

CONN test1/test1 @ db11g

COLUMN host FORMAT A30

SELECT host, lower_port, upper_port, privilege, status

FROM user_network_acl_privileges;

HOST LOWER_PORT UPPER_PORT PRIVILE STATUS

----------------------------------------------------------------

10.1.10. * connect GRANTED

192.168.2.3 80 80 connect GRANTED

2 rows selected.

SQL>

CONN test2/test2 @ db11g

COLUMN host FORMAT A30

SELECT host, lower_port, upper_port, privilege, status

FROM user_network_acl_privileges;

HOST LOWER_PORT UPPER_PORT PRIVILE STATUS

----------------------------------------------------------------

10.1.10. * connect DENIED

192.168.2.3 80 80 connect DENIED

2 rows selected.

SQL> transfer permission check

In addition to the access control list view, you can use the CHECK_PRIVILEGE and CHECK_PRIVILEGE_ACLID functions in the DBMS_NETWORK_ACL_ADMIN package to check permissions.

CONN sys/password @ db11g AS SYSDBA

Select decode (

DBMS_NETWORK_ACL_ADMIN.check_privilege ('test _ acl_file.xml ', 'test1', 'connection '),

1, 'granted', 0, 'denied ', NULL) privilege

FROM dual;

PRIVILE

-------

GRANTED

1 row selected.

SQL>

COLUMN acl FORMAT A30

COLUMN host FORMAT A30

SELECT acl,

Host,

DECODE (

DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid (aclid, 'test2', 'connect '),

1, 'granted', 0, 'denied ', NULL) privilege

FROM dba_network_acls;

PRIVILE

-------

DENIED

1 row selected.

SQL>

The DBMS_NETWORK_ACL_UTILITY package includes functions to help identify the DOMAINS that may match. The DOMAINS table function returns in sequence a set of all hosts, DOMAINS, IP addresses, or subnets that may be affected.

SELECT *

From table (DBMS_NETWORK_ACL_UTILITY.domains ('el5-11g. localdomain '));

COLUMN_VALUE

-------------------------------

Oel5-11g.localdomain

*. Localdomain

*

3 rows selected.

SQL>

SELECT *

From table (DBMS_NETWORK_ACL_UTILITY.domains ('192. 168.2.3 '));

COLUMN_VALUE

-------------------------------

192.168.2.3

192.168.2 .*

192. 168 .*

192 .*

*

5 rows selected.

SQL>

The DOMAIN_LEVEL function returns the host, domain, IP address, or subnet level.

SELECT DBMS_NETWORK_ACL_UTILITY.domain_level ('el5-11g. localdomain ')

FROM dual;

DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL ('oel5-11G. LOCALDOMAIN ')

-------------------------------------------------------------

2

1 row selected.

SQL>

SELECT DBMS_NETWORK_ACL_UTILITY.domain_level ('192. 168.2.3 ')

FROM dual;

DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL ('192. 168.2.3 ')

----------------------------------------------------

4

1 row selected.

SQL>

These functions may be useful in querying the access control list view of hosts, domains, IP addresses, or subnets that may match.

SELECT host,

Lower_port,

Upper_port,

Acl,

DECODE (

DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid (aclid, 'test1', 'connect '),

1, 'granted', 0, 'denied ', null) PRIVILEGE

FROM dba_network_acls

WHERE host IN (SELECT *

From table (DBMS_NETWORK_ACL_UTILITY.domains ('10. 1.10.191 ')))

ORDER

DBMS_NETWORK_ACL_UTILITY.domain_level (host) desc, lower_port, upper_port;

HOST LOWER_PORT UPPER_PORT ACL PRIVILE

---------------------------------------------------------------------------------------

10.1.10. */sys/acls/test_acl_file.xml GRANTED

1 row selected.

SQL> Test Access Control List

Users TEST1 and TEST2 have the allowed and denied access control lists respectively, this means that we can start to test the access control list function by comparing their responses when accessing external network services, the following code grants the UTL_HTTP package execution permissions to both users, and then tries to access a web page from each user.

CONN sys/password @ db11g AS SYSDBA

Grant execute on UTL_HTTP TO test1, test2;

CONN test1/test1 @ db11g

DECLARE

Rochelle URL VARCHAR2 (50): = 'HTTP: // 192.168.2.3: 80 ';

Rochelle http_request UTL_HTTP.req;

Rochelle http_response UTL_HTTP.resp;

BEGIN

-- Make a HTTP request and get the response.

L_http_request: = UTL_HTTP.begin_request (l_url );

Rochelle http_response: = UTL_HTTP.get_response (Rochelle http_request );

UTL_HTTP.end_response (l_http_response );

END;

/

PL/SQL procedure successfully completed.

SQL>

CONN test2/test2 @ db11g

DECLARE

Rochelle URL VARCHAR2 (50): = 'HTTP: // 192.168.2.3: 80 ';

Rochelle http_request UTL_HTTP.req;

Rochelle http_response UTL_HTTP.resp;

BEGIN

-- Make a HTTP request and get the response.

L_http_request: = UTL_HTTP.begin_request (l_url );

Rochelle http_response: = UTL_HTTP.get_response (Rochelle http_request );

UTL_HTTP.end_response (l_http_response );

END;

/

DECLARE

*

ERROR at line 1:

ORA-29273: HTTP request failed

ORA-06512: at & quot; SYS. UTL_HTTP & quot;, line 1029

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

ORA-06512: at line 7

SQL>

From the returned information, we can easily see that TEST1 can access the web page, while TEST2 is denied by the access control list. By default, the server rejects access to external network services, the test results of a new user are shown below.

CONN sys/password @ db11g AS SYSDBA

Create user test3 identified by test3;

Grant connect to test3;

Grant execute on UTL_HTTP TO test3;

CONN test3/test3 @ db11g

DECLARE

Rochelle URL VARCHAR2 (50): = 'HTTP: // 192.168.2.3: 80 ';

Rochelle http_request UTL_HTTP.req;

Rochelle http_response UTL_HTTP.resp;

BEGIN

-- Make a HTTP request and get the response.

L_http_request: = UTL_HTTP.begin_request (l_url );

Rochelle http_response: = UTL_HTTP.get_response (Rochelle http_request );

UTL_HTTP.end_response (l_http_response );

END;

/

DECLARE

*

ERROR at line 1:

ORA-29273: HTTP request failed

ORA-06512: at & quot; SYS. UTL_HTTP & quot;, line 1029

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

ORA-06512: at line 7

SQL>

When upgrading from 10 Gb to 11 GB, access to external network services may cause some confusion. In that case, you need to implement a reasonable access control list.

Other security factors

In his blog, Pete Finnigan and his security statement on the access control list only did not include a specific package, which means that through UTL_TCP, UTL_SMTP, UTL_MAIL and UTL_HTTP can open a port on the server with the connect permission. Keep this in mind and consider the following:

◆ Fine-grained access to network services cannot be used as an excuse to ignore basic security assessment, such as revoking unnecessary permissions for network service-related packages.

◆ By limiting access to specific ports, your service is available. If you only need to access http port 80, specifying this port is much better than opening all ports on the server.

◆ Using wildcards during authorization is more secure and dangerous than not using wildcards.

◆ You must protect your access control list. If someone can modify them, they become useless due to protection mechanisms and prevent direct access to the access control list stored in the xml db database, make sure that you cannot access the management API.