爱华网實例：------------------------------------------------------------------------
begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL('sendmail_acl.xml');
end;
begin
dbms_network_acl_admin.create_acl (
acl => 'sendmail_acl.xml',
description => 'Normal Access',
principal => 'WH_DA',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
end;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl=> 'sendmail_acl.xml',
principal => 'WH_DA',
is_grant => TRUE,
privilege => 'resolve');
END;
/
--給控制列表ACL sendmail.xml指配host，
--實際上一個服務器上只能設置一個控制列表，但是一個控制列表可以設置多個服務器
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'sendmail_acl.xml',
host =>'172.17.36.88');--172.17.36.88是郵件服務器，用來發郵件
END;
/
commit;
---------------------給用戶指定connect和resolve某控制列表的權限
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl=> 'sendmail_acl.xml',
principal => 'WH_IP',
is_grant => TRUE,
privilege => 'connect');
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl=> 'sendmail_acl.xml',
principal => 'WH_IP',
is_grant => TRUE,
privilege => 'resolve');
END;
/
commit;
-------------------------------------------------------------------- ------------------------------------------
--查詢控制列表
select * from DBA_NETWORK_ACLS
SELECT any_path
FROM resource_view
WHERE any_path like '/sys/acls/%.xml';
--查詢控制列表的權限分配
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
FROMdba_network_acl_privileges
--給控制列表指定host
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'sendmail_acl.xml',
host =>'172.17.36.111');--給控制列表指定host，一個控制列表可以指定多個host
END;
但是一個host只能指定給一個控制列表
例如：
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'sendmail_acl1.xml',
host => '172.17.36.111');
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'sendmail_acl2.xml',
host => '172.17.36.111');
END;
/
COMMIT;
執行完上面的命令后則172.17.36.111實際上是分配給了sendmail_acl2.xml
--------收回控制列表對某host的訪問，因一個host只能指派給一個控制列表，所以可以不指定控制列表名稱
BEGIN
DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(host =>'172.17.36.111');
END;
/
---------------------取消用戶connect和resolve某控制列表的權限
BEGIN
DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
acl=> 'sendmail_acl.xml',
principal => 'WH_IP',
is_grant => TRUE,
privilege => 'connect');
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
acl=> 'sendmail_acl.xml',
principal => 'WH_IP',
is_grant => TRUE,
privilege => 'resolve');
END;
/
commit;
---------------------------------------------------------------------------------
語法
1.創建ACL
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
aclIN VARCHAR2,
descriptionIN VARCHAR2,
principalIN VARCHAR2,
is_grantIN BOOLEAN,
privilegeIN VARCHAR2,
start_dateIN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
end_dateIN TIMESTAMP WITH TIMEZONE DEFAULT NULL );
2.給用戶添加訪問ACL的權限
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
aclIN VARCHAR2,
principalIN VARCHAR2,
is_grantIN BOOLEAN,
privilegeIN VARCHAR2,
positionIN PLS_INTEGER DEFAULT NULL,
start_dateIN TIMESTAMP WITH TIMESTAMP DEFAULT NULL,
end_dateIN TIMESTAMP WITH TIMESTAMP DEFAULT NULL );
3.指派ACL對應的host，一個ACL可以對應多個host，但是一個host只能指派給一個ACL
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
aclIN VARCHAR2,
hostIN VARCHAR2,
lower_portIN PLS_INTEGER DEFAULT NULL,
upper_portIN PLS_INTEGER DEFAULT NULL);
4.檢驗用戶是否有某ACL的某權限
DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE (
aclIN VARCHAR2,
userIN VARCHAR2,
privilegeIN VARCHAR2)
RETURN NUMBER;
Returns 1 when the privilege is granted; 0 when the privilege isdenied; NULL when the privilege is neither granted or denied.
5.刪除acl中的connect或者resolve權限
DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE (
aclIN VARCHAR2,
principalIN VARCHAR2,
is_grantIN BOOLEAN DEFAULT NULL,
privilegeIN VARCHAR2 DEFAULT NULL);
6.刪除ACL
DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
aclIN VARCHAR2);
7.取消ACL已分配的host
DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL (
aclIN VARCHAR2 DEFAULT NULL,
hostIN VARCHAR2 DEFAULT NULL,
lower_portIN PLS_INTEGER DEFAULT NULL,
upper_portIN PLS_INTEGER DEFAULT NULL);
8.
--查詢控制列表
select * from DBA_NETWORK_ACLS
SELECT any_path
FROM resource_view
WHERE any_path like '/sys/acls/%.xml';
--查詢控制列表的權限分配
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
FROMdba_network_acl_privileges
