12c Access Control Lists

By Franck Pachot

.
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…