This was first published on https://blog.dbi-services.com/12c-access-control-lists (2017-09-10)
Republishing here for new followers. The content is related to the the versions available at the publication date
There is already enough information about the new simplified 12c way to define Access Control Lists, such as in oracle-base. I’m just posting my example here to show how it is easy.
If, as a non-SYS user you want to connect to a host with TCP, you get an error:
SQL> connect DEMO1/demo@//localhost/PDB1 Connected. SQL> SQL> SQL> declare 2 c utl_tcp.connection; 3 n number:=0; 4 begin 5 c:=utl_tcp.open_connection(remote_host=>'towel.blinkenlights.nl',remote_port=>23); 6 end; 7 / Error starting at line : 27 File @ /media/sf_share/122/blogs/12cacl.sql In command - declare c utl_tcp.connection; n number:=0; begin c:=utl_tcp.open_connection(remote_host=>'towel.blinkenlights.nl',remote_port=>23); end; Error report - ORA-24247: network access denied by access control list (ACL) ORA-06512: at "SYS.UTL_TCP", line 19 ORA-06512: at "SYS.UTL_TCP", line 284 ORA-06512: at line 5 24247. 00000 - "network access denied by access control list (ACL)" *Cause: No access control list (ACL) has been assigned to the target host or the privilege necessary to access the target host has not been granted to the user in the access control list. *Action: Ensure that an access control list (ACL) has been assigned to the target host and the privilege necessary to access the target host has been granted to the user. SQL>
Here are the ACLs defined by default:
SQL> connect sys/oracle@//localhost/PDB1 as sysdba Connected. SQL> select * from dba_host_acls; HOST LOWER_PORT UPPER_PORT ACL ACLID ACL_OWNER ---- ---------- ---------- --- ----- --------- * NETWORK_ACL_4700D2108291557EE05387E5E50A8899 0000000080002724 SYS SQL> select * from dba_host_aces; HOST LOWER_PORT UPPER_PORT ACE_ORDER START_DATE END_DATE GRANT_TYPE INVERTED_PRINCIPAL PRINCIPAL PRINCIPAL_TYPE PRIVILEGE ---- ---------- ---------- --------- ---------- -------- ---------- ------------------ --------- -------------- --------- * 1 GRANT NO GSMADMIN_INTERNAL DATABASE RESOLVE * 2 GRANT NO GGSYS DATABASE RESOLVE
So, I add an ACL to access to towel.blinkenlights.nl on telnet port (23) for my user DEMO1:
SQL> exec dbms_network_acl_admin.append_host_ace(host=>'towel.blinkenlights.nl',lower_port=>23,upper_port=>23,ace=>xs$ace_type(privilege_list =>xs$name_list('connect'),principal_name=>'DEMO1',principal_type =>xs_acl.ptype_db)); PL/SQL procedure successfully completed. SQL> select * from dba_host_acls; HOST LOWER_PORT UPPER_PORT ACL ACLID ACL_OWNER ---- ---------- ---------- --- ----- --------- towel.blinkenlights.nl 23 23 NETWORK_ACL_5876ADC67B6635CEE053684EA8C0F378 000000008000281F SYS * NETWORK_ACL_4700D2108291557EE05387E5E50A8899 0000000080002724 SYS SQL> select * from dba_host_aces; HOST LOWER_PORT UPPER_PORT ACE_ORDER START_DATE END_DATE GRANT_TYPE INVERTED_PRINCIPAL PRINCIPAL PRINCIPAL_TYPE PRIVILEGE ---- ---------- ---------- --------- ---------- -------- ---------- ------------------ --------- -------------- --------- * 1 GRANT NO GSMADMIN_INTERNAL DATABASE RESOLVE * 2 GRANT NO GGSYS DATABASE RESOLVE towel.blinkenlights.nl 23 23 1 GRANT NO DEMO1 DATABASE CONNECT
Now I can connect from my user:
SQL> connect DEMO1/demo@//localhost/PDB1 Connected. SQL> declare 2 c utl_tcp.connection; 3 n number:=0; 4 begin 5 c:=utl_tcp.open_connection(remote_host=>'towel.blinkenlights.nl',remote_port=>23); 6 end; 7 / PL/SQL procedure successfully completed.
If you don’t know why I used towel.blinkenlights.nl, then just try to telnet to it and have fun…