Oracle数据库调用http接口时建立ACL授权
--将userA修改为指定用户
1. Create an Access Control List (ACL)
begin
dbms_network_acl_admin.create_acl (
acl => 'Resolve_Access.xml',
description => 'Resolve Network Access using UTL_INADDR',
principal => 'userA',
is_grant => TRUE,
privilege => 'resolve',
start_date => null,
end_date => null
);
commit;
end;
/2. Additional users or roles are added to the ACL using the ADD_PRIVILEGE procedure
BEGIN
dbms_network_acl_admin.add_privilege(acl => 'Resolve_Access.xml',
principal => 'userA',
is_grant => TRUE,
privilege => 'resolve');
dbms_network_acl_admin.add_privilege(acl => 'Resolve_Access.xml',
principal => 'userA',
is_grant => TRUE,
privilege => 'connect');
END;
/3. Assign an ACL to a Network
begin
dbms_network_acl_admin.assign_acl (
acl => 'Resolve_Access.xml',
host => '*',
lower_port => null,
upper_port => null);
commit;
end;
/参考 https://oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1
- ACL Views
The DBA_NETWORK_ACLS, DBA_NETWORK_ACL_PRIVILEGES and USER_NETWORK_ACL_PRIVILEGES views display the current ACL settings. The expected output below assumes none of the delete/drop/unassign operations have been performed.- The DBA_NETWORK_ACLS view displays information about network and ACL assignments.
COLUMN host FORMAT A30
COLUMN acl FORMAT A30
SELECT host, lower_port, upper_port, acl
FROM dba_network_acls;
HOST LOWER_PORT UPPER_PORT ACL
------------------------------ ---------- ---------- ------------------------------
10.1.10.* /sys/acls/test_acl_file.xml
192.168.2.3 80 80 /sys/acls/test_acl_file.xml
2 rows selected.
SQL>- The DBA_NETWORK_ACL_PRIVILEGES view displays information about privileges associated with the ACL.
COLUMN acl FORMAT A30
COLUMN principal FORMAT A30
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;
ACL PRINCIPAL PRIVILE IS_GR START_DATE END_DATE
------------------------------ ------------------------------ ------- ----- ----------- -----------
/sys/acls/test_acl_file.xml TEST1 connect true 02-APR-2008
/sys/acls/test_acl_file.xml TEST2 connect false
2 rows selected.
SQL>- The USER_NETWORK_ACL_PRIVILEGES view displays the current users network ACL settings.
CONN test1/test1@db11g
COLUMN host FORMAT A30
SELECT host, lower_port, upper_port, privilege, status
FROM user_network_acl_privileges;
HOST LOWER_PORT UPPER_PORT PRIVILE STATUS
------------------------------ ---------- ---------- ------- -------
10.1.10.* connect GRANTED
192.168.2.3 80 80 connect GRANTED
2 rows selected.
SQL>
CONN test2/test2@db11g
COLUMN host FORMAT A30
SELECT host, lower_port, upper_port, privilege, status
FROM user_network_acl_privileges;
HOST LOWER_PORT UPPER_PORT PRIVILE STATUS
------------------------------ ---------- ---------- ------- -------
10.1.10.* connect DENIED
192.168.2.3 80 80 connect DENIED
2 rows selected.
SQL>- Checking Privileges
In addition to the ACL views, privileges can be checked using the CHECK_PRIVILEGE and CHECK_PRIVILEGE_ACLID functions of the DBMS_NETWORK_ACL_ADMIN package.
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>