[Oracle] Network ACL

Oracle 11g可利用 ACL(Access Control List)來做外部訪問權限設定.

Principal will have what privileges on what object. --Principal: WHO, Object: WHAT

1. Create the ACL

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',
      description => 'WWW ACL',
      principal   => 'SCOTT', --表示權限賦予給哪個用戶 or PUBLIC
      is_grant    => true,
      privilege   => 'connect');

2.Grant privilege to user or Public

  -- Grant connect permission 
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'www.xml',
principal => 'PUBLIC',  --if you want user like 'SCOTT' 
is_grant => true,
privilege => 'connect');
COMMIT;
END;
/

-- grant resolve permission
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'www.xml',
principal => 'PUBLIC',  --if you want user like 'SCOTT' 
is_grant => true,
privilege => 'resolve');
COMMIT;
END;
/

3.Assign ACL to network. 分配受該ACL制約的主機以及其他訊息

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'www.xml',
host => '192.168.63.63', -- specify the host IP address 
lower_port => 34,       -- specify the port range lower value (* for all)
upper_port => 63);      -- specify the port range higher value
COMMIT;
END;
/

4.Verified the ACL is created.

SELECT * FROM DBA_NETWORK_ACLS;
SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

5.Drop the ACL

BEGIN
   DBMS_NETWORK_ACL_ADMIN.drop_acl ( acl => 'www.xml');
END;