ACL Creation in Oracle 11GR2
Step 1:
To configure ACL for your Oracle database first you need to install XDB:To install XDB
$ORACLE_HOME/rdbms/admin/catqm.sql
Step 2:
Create ACL for your Oracle Database:BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => '/sys/acls/netacl.xml',
description => 'fox ACL',
principal => 'fox',
is_grant => true,
privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => '/sys/acls/netacl.xml',
principal => 'fox',
is_grant => true,
privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => '/sys/acls/netacl.xml',host => '1.159.17.48',lower_port => 80,upper_port => 80);
END;
/
------------------------------------------------------------------------------------
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => '/sys/acls/fox_rule_acl.xml',host => '1.230.241.157',lower_port => 39081,upper_port => 39081);
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => '/sys/acls/fox_rule_acl.xml',host => '1.230.240.219',lower_port => 8080,upper_port => 8080);
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => '/sys/acls/netacl.xml',host => '10.1.10.*');
END;
/
=================================================================================================
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'www.xml',
principal => 'fox',
is_grant => true,
privilege => 'resolve');
END;
/
COMMIT;
==========================================================================================================
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'www.xml',
host => 'www-proxy.us.oracle.com');
END;
/
COMMIT;
======================================================================================================
SQL> BEGIN
31. 2 dbms_network_acl_admin.create_acl('test_smtp.xml','TEST SMTP ACL','TESTACL',true,'connect');
34. 3 dbms_network_acl_admin.assign_acl('test_smtp.xml','localhost',25);
35. 4 commit;
36. 5 END;
37. 6 /
38.PL/SQL procedure successfully completed.
39.SQL> SELECT host, lower_port, upper_port, acl FROM dba_network_acls;
40.HOST LOWER_PORT UPPER_PORT ACL
41.-------------------- ---------- ---------- ----------------------------------------
42.localhost 25 25 /sys/acls/test_smtp.xml
41.SQL> SELECT acl,principal,privilege,is_grant FROM dba_network_acl_privileges;
44.ACL PRINCIPAL PRIVILEGE IS_GRANT
45.---------------------------------------- --------------- ---------- --------
46./sys/acls/test_smtp.xml TESTACL connect true
SQL> CREATE USER testacl IDENTIFIED BY testacl;
28.User created.
29.SQL> GRANT CONNECT TO testacl;
30.Grant succeeded.
============================================================================================================
SELECT host, lower_port, upper_port, acl,
DECODE(
DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'SCOTT', 'connect'),
1, 'GRANTED', 0, 'DENIED', null) privilege
FROM dba_network_acls
WHERE host IN
(SELECT * FROM
TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('www.us.oracle.com')))
ORDER BY DBMS_NETWORK_ACL_UTLITITY.DOMAIN_LEVEL(host) desc, lower_port,
upper_port;
===========================================================================================================
SELECT host, acl,
DECODE(
DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'SCOTT', 'resolve'),
1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dba_network_acls
WHERE host IN
(SELECT * FROM
TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('www.us.oracle.com'))) and
lower_port IS NULL AND upper_port IS NULL
ORDER BY DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL(host) desc;
===========================================================================================================
SQL> /
HOST LOWER_PORT UPPER_PORT ACL
------------------------------------------------------------ ---------- ---------- ------------------------------------------------------------
1.230.241.157 39081 39081 /sys/acls/fox_rule_acl.xml
1.230.240.219 8080 8080 /sys/acls/fox_rule_acl.xml
10.1.10.* /sys/acls/netacl.xml
1.159.17.48 80 80 /sys/acls/netacl.xml
localhost /sys/acls/oracle-sysman-ocm-Resolve-Access.xml
ACL PRINCIPAL PRIVILE IS_GR
------------------------------------------------------------ ------------------------------ ------- -----
/sys/acls/fox_rule_acl.xml fox connect true
/sys/acls/fox_rule_acl.xml fox resolve true
/sys/acls/netacl.xml fox connect true
/sys/acls/netacl.xml fox resolve true
/sys/acls/oracle-sysman-ocm-Resolve-Access.xml ORACLE_OCM resolve true
================================================================================================================================================
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => '/sys/acls/oracle-sysman-ocm-Resolve-Access.xml',
description => 'ORACLE_OCM ACL',
principal => 'ORACLE_OCM',
is_grant => true,
privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => '/sys/acls/oracle-sysman-ocm-Resolve-Access.xml',
principal => 'ORACLE_OCM',
is_grant => true,
privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => '/sys/acls/oracle-sysman-ocm-Resolve-Access.xml',
host => '1.142.70.10');
END;
/
------------------------------------------------------------------------------------
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => '/sys/acls/fox_rule_acl.xml',host => '1.230.240.150');
END;
/
dbms_network_acl_admin.assign_acl (
acl => 'utl_http.xml',
host => 'www.tiger.com',
lower_port => 39081,
upper_port => 39081
);
==============================================================================================================*****************************************************
begin
dbms_network_acl_admin.unassign_acl(
acl => '/sys/acls/netacl.xml',
host => '10.1.10.*'
);
end;
begin
dbms_network_acl_admin.delete_privilege(
'/sys/acls/fox_rule_acl.xml', 'fox', NULL, 'connect'
);
end;
begin
dbms_network_acl_admin.drop_acl('/sys/acls/netacl.xml');
end;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
new database;
No comments:
Post a Comment