Oracle 11gR1中细粒度访问网络服务(3)

作者: 黄永兵 出处:　

 

权限检查

处理访问控制列表视图外，还可以使用DBMS_NETWORK_ACL_ADMIN包中的CHECK_PRIVILEGE和CHECK_PRIVILEGE_ACLID函数来检查权限。

CONN sys/password@db11g AS SYSDBA

SELECT DECODE(
         DBMS_NETWORK_ACL_ADMIN.check_privilege('test_acl_file.xml', 'TEST1', 'connect'),
         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>

DBMS_NETWORK_ACL_UTILITY包包括了帮助判断可能匹配的域的函数，DOMAINS表函数按顺序返回所有可能受影响的主机，域，ip地址或子网的集合。

SELECT *
FROM   TABLE(DBMS_NETWORK_ACL_UTILITY.domains('oel5-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>

DOMAIN_LEVEL函数返回主机，域，ip地址或子网的级数。

SELECT DBMS_NETWORK_ACL_UTILITY.domain_level('oel5-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>

在为可能匹配的主机，域，ip地址或子网查询访问控制列表视图是这些函数可能非常有用。

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