Oracle-ACL配置

1.创建测试用户并赋权

create user acl_test identified by oracle;
grant connect,resource to acl_test;
grant unlimited tablespace to acl_test;

2.创建测试

conn acl_test/oracle
create table t1(id int,name varchar2(10));
insert into t1 values(1,'aaa');
commit;

3.远程连接测试

 sqlplus  acl_test/oracle@10.220.104.101:1521/prod
 
SQL> select * from t1;

        ID NAME
---------- ----------
         1 aaa

4.创建ACL

SQL> BEGIN
  2    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl        => 'test.xml',
  3    4      description  => 'Example ACL',
  5      principal  => 'ACL_TEST',
  6      is_grant => TRUE,
  7      privilege  => 'connect');
  8    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
  9      acl      => 'test.xml',
    principal  => 'ACL_TEST',
 10   11      is_grant => TRUE,
 12      privilege  => 'resolve');
 13    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
 14      acl => 'test.xml',
 15      host => '10.220.104.101',
 16      lower_port => 1521,
 17      upper_port => 1521);
 18    COMMIT;
 19  END;
 20  /

PL/SQL procedure successfully completed.

5.查看创建的ACL

col host format a30
col acl format a80
set pagesize 999
SELECT host, acl, lower_port, upper_port
FROM DBA_NETWORK_ACLS;

6.删除ACL

begin
DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
	acl=>'test.xml',
	principal   =>'ACL_TEST') ;
end;
/

begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(acl=>'test.xml') ;
end;
/