oracle dbms_network_acl_admin,oracle数据库network acl连接问题

创建acl文件

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 => SYSTIMESTAMP,

end_date => NULL);

COMMIT;

END;

/

TEST1是要授权的用户

授权

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;

/

删除权限

BEGIN

DBMS_NETWORK_ACL_ADMIN.delete_privilege (

acl => 'test_acl_file.xml',

principal => 'TEST2',

is_grant => FALSE,

privilege => 'connect');

COMMIT;

END;

/

移除acl文件

BEGIN

DBMS_NETWORK_ACL_ADMIN.drop_acl (

acl => 'test_acl_file.xml');

COMMIT;

END;

/

添加允许连接的ip

BEGIN

DBMS_NETWORK_ACL_ADMIN.assign_acl (

acl => 'test_acl_file.xml',

host => '192.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;

/

--查询

SELECT host, lower_port, upper_port, acl FROM dba_network_acls;

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;

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>

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

*

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.

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>