8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Service-Level Access Control Lists (ACLs) - Database Service Firewall in Oracle Database 12c Release 2 (12.2)
Service-Level Access Control Lists (ACLs) allow you to control access to specific services, including those associated with individual pluggable databases (PDBs). This functionality is part of the Database Service Firewall, which isn't specifically a multitenant feature, but it is useful for controlling access to PDBs.
Related articles.
Setup
The LOCAL_REGISTRATION_ADDRESS_lsnr_alias
setting must be added to the "listener.ora" file. It should either specify a protocol and group or be set to "ON", which defaults to "IPC" and "oinstall".
# LOCAL_REGISTRATION_ADDRESS_lsnr_alias = (address=(protocol=ipc)(group=oninstall)) # LOCAL_REGISTRATION_ADDRESS_lsnr_alias = ON LOCAL_REGISTRATION_ADDRESS_LISTENER = ON
The FIREWALL
attribute can be added to the listener endpoint to control the action of the database firewall.
- Unset : If an ACL is present for the service it is enforced. If no ACL is present for the service, all connections are considered valid.
FIREWALL=ON
: Only connections matching an ACL are considered valid. All other connections are rejected.FIREWALL=OFF
: The firewall functionality is disabled, so all connections are considered valid.
If we wanted to force the firewall functionality we might amend the default listener configuration as follows. Remember, the FIREWALL
attribute is optional.
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-122.localdomain)(PORT = 1521)(FIREWALL=ON)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) LOCAL_REGISTRATION_ADDRESS_LISTENER = ON
The DBSFWUSER
user owns the DBMS_SFW_ACL_ADMIN
package, which provides an API to manage service-level access control lists (ACLs). We will be using this API in the following examples.
Service-Level Access Control Lists (ACLs)
Service-level ACLs can limit access to any named service handled by the listener, including those for a PDB.
Create and start a test service.
CONN / AS SYSDBA BEGIN DBMS_SERVICE.create_service('my_cdb_service','my_cdb_service'); DBMS_SERVICE.start_service('my_cdb_service'); END; / COLUMN name FORMAT A30 COLUMN network_name FORMAT A30 SELECT name, network_name FROM cdb_services ORDER BY 1; NAME NETWORK_NAME ------------------------------ ------------------------------ SYS$BACKGROUND SYS$USERS cdb1 cdb1 cdb1XDB cdb1XDB my_cdb_service my_cdb_service pdb1 pdb1 SQL>
The IP_ADD_ACE
procedure accepts a service name and a host parameter. The host parameter can be IPv4 or IPv6, and wildcards are allowed. Once the ACL is built it is saved using the COMMIT_ACL
procedure.
CONN / AS SYSDBA BEGIN dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_add_ace('my_cdb_service','ol7-122.localdomain'); dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_add_ace('my_cdb_service','192.168.56.136'); dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_add_ace('pdb1','ol7-122.localdomain'); dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_add_ace('pdb1','192.168.56.136'); dbsfwuser.DBMS_SFW_ACL_ADMIN.commit_acl; END; /
The IP_ACL
table holds all the saved ACLs, while the V$IP_ACL
view lists the active ACLs.
-- Display the saved ACLs. COLUMN service_name FORMAT A30 COLUMN host FORMAT A30 SELECT service_name, host FROM dbsfwuser.ip_acl ORDER BY 1, 2; SERVICE_NAME HOST ------------------------------ ------------------------------ "MY_CDB_SERVICE" 192.168.56.136 "MY_CDB_SERVICE" OL7-122.LOCALDOMAIN "PDB1" 192.168.56.136 "PDB1" OL7-122.LOCALDOMAIN SQL> -- Display the active ACLs. SELECT service_name, host, con_id FROM v$ip_acl ORDER BY 1, 2; SERVICE_NAME HOST CON_ID ------------------------------ ------------------------------ ---------- MY_CDB_SERVICE 192.168.56.136 1 MY_CDB_SERVICE OL7-122.LOCALDOMAIN 1 PDB1 192.168.56.136 3 PDB1 OL7-122.LOCALDOMAIN 3 SQL>
At the time of writing the V$IP_ACL
view seems to have an issue such that the data doesn't respond correctly to the format command of SQL*Plus.
With the ACL in place we can connect to the services from the database server, but not from any other machine. In the example below the SQL*Plus connections from the server works fine, but the SQLcl connections from a PC fails with a "IO Error: Undefined Error" error.
$ sqlplus sys/OraPasswd1@ol7-122.localdomain:1521/my_cdb_service as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Sep 19 18:50:20 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> CONN test/test@ol7-122.localdomain:1521/pdb1 Connected. SQL> $ ./sql sys/OraPasswd1@ol7-122.localdomain:1521/my_cdb_service as sysdba SQLcl: Release 17.2.0 Production on Tue Sep 19 18:54:35 2017 Copyright (c) 1982, 2017, Oracle. All rights reserved. USER = sys URL = jdbc:oracle:thin:@ol7-122.localdomain:1521/my_cdb_service Error Message = IO Error: Undefined Error Username? (RETRYING) ('sys/*********@ol7-122.localdomain:1521/my_cdb_service as sysdba'?) $ ./sql test/test@ol7-122.localdomain:1521/pdb1 SQLcl: Release 17.2.0 Production on Tue Sep 19 19:20:07 2017 Copyright (c) 1982, 2017, Oracle. All rights reserved. USER = test URL = jdbc:oracle:thin:@ol7-122.localdomain:1521/pdb1 Error Message = IO Error: Undefined Error Username? (RETRYING) ('test/*********@ol7-122.localdomain:1521/pdb1'?)
We can add an entry for the PC to allow it to connect.
CONN / AS SYSDBA BEGIN dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_add_ace('my_cdb_service','192.168.56.1'); dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_add_ace('pdb1','192.168.56.1'); dbsfwuser.DBMS_SFW_ACL_ADMIN.commit_acl; END; /
The SQLcl connections from the PC now work as expected.
$ ./sql sys/OraPasswd1@ol7-122.localdomain:1521/my_cdb_service as sysdba SQLcl: Release 17.2.0 Production on Tue Sep 19 18:59:53 2017 Copyright (c) 1982, 2017, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> CONN test/test@ol7-122.localdomain:1521/pdb1 Connected. SQL>
The IP_REMOVE_ACE
procedure is used to remove service-level ACL entries. The following removes all the service-level ACLs created for this example.
CONN / AS SYSDBA BEGIN dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_remove_ace('my_cdb_service','ol7-122.localdomain'); dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_remove_ace('my_cdb_service','192.168.56.136'); dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_remove_ace('my_cdb_service','192.168.56.1'); dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_remove_ace('pdb1','ol7-122.localdomain'); dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_remove_ace('pdb1','192.168.56.136'); dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_remove_ace('pdb1','192.168.56.1'); dbsfwuser.DBMS_SFW_ACL_ADMIN.commit_acl; END; / -- Display the saved ACLs. COLUMN service_name FORMAT A30 COLUMN host FORMAT A30 SELECT service_name, host FROM dbsfwuser.ip_acl ORDER BY 1, 2; no rows selected SQL>
We can stop and remove the test service using the following code.
CONN / AS SYSDBA BEGIN DBMS_SERVICE.stop_service('my_cdb_service'); DBMS_SERVICE.delete_service('my_cdb_service'); END; /
PDB-Level Access Control Lists (ACLs)
PDB-level ACLs allow us to manage access to all services for a PDB, rather than having to name them individually.
Create and start a test service in the PDB.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; BEGIN DBMS_SERVICE.create_service('my_pdb_service','my_pdb_service'); DBMS_SERVICE.start_service('my_pdb_service'); END; / COLUMN name FORMAT A30 COLUMN network_name FORMAT A30 SELECT name, network_name FROM dba_services ORDER BY 1; NAME NETWORK_NAME ------------------------------ ------------------------------ my_pdb_service my_pdb_service pdb1 pdb1 SQL>
The IP_ADD_PDB_ACE
procedure accepts a PDB name and a host parameter. The host parameter can be IPv4 or IPv6, and wildcards are allowed. Once the ACL is built it is saved using the COMMIT_ACL
procedure in the normal way.
CONN / AS SYSDBA BEGIN dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_add_pdb_ace('pdb1','ol7-122.localdomain'); dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_add_pdb_ace('pdb1','192.168.56.136'); dbsfwuser.DBMS_SFW_ACL_ADMIN.commit_acl; END; /
The IP_ACL
table holds all the saved ACLs, while the V$IP_ACL
view lists the active ACLs.
-- Display the saved ACLs. COLUMN service_name FORMAT A35 COLUMN host FORMAT A30 SELECT service_name, host FROM dbsfwuser.ip_acl ORDER BY 1, 2; SERVICE_NAME HOST ------------------------------ ------------------------------ "566C59261E6B2CA6E0538838A8C001B3" 192.168.56.136 "566C59261E6B2CA6E0538838A8C001B3" OL7-122.LOCALDOMAIN "MY_PDB_SERVICE" 192.168.56.136 "MY_PDB_SERVICE" OL7-122.LOCALDOMAIN "PDB1" 192.168.56.136 "PDB1" OL7-122.LOCALDOMAIN SQL> -- Display the active ACLs. SELECT service_name, host, con_id FROM v$ip_acl ORDER BY 1, 2; SERVICE_NAME HOST CON_ID ----------------------------------- ------------------------------ ---------- MY_PDB_SERVICE 192.168.56.136 3 MY_PDB_SERVICE OL7-122.LOCALDOMAIN 3 PDB1 192.168.56.136 3 PDB1 OL7-122.LOCALDOMAIN 3 SQL>
With the ACL in place we can connect to the services from the database server, but not from any other machine. In the example below the SQL*Plus connections from the server works fine, but the SQLcl connections from a PC fails with a "IO Error: Undefined Error" error.
$ sqlplus sys/OraPasswd1@ol7-122.localdomain:1521/my_pdb_service as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Sep 19 20:26:15 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> CONN test/test@ol7-122.localdomain:1521/pdb1 Connected. SQL> $ ./sql sys/OraPasswd1@ol7-122.localdomain:1521/my_pdb_service as sysdba SQLcl: Release 17.2.0 Production on Tue Sep 19 20:26:36 2017 Copyright (c) 1982, 2017, Oracle. All rights reserved. USER = sys URL = jdbc:oracle:thin:@ol7-122.localdomain:1521/my_pdb_service Error Message = IO Error: Undefined Error Username? (RETRYING) ('sys/*********@ol7-122.localdomain:1521/my_pdb_service as sysdba'?) $ ./sql test/test@ol7-122.localdomain:1521/pdb1 SQLcl: Release 17.2.0 Production on Tue Sep 19 20:27:51 2017 Copyright (c) 1982, 2017, Oracle. All rights reserved. USER = test URL = jdbc:oracle:thin:@ol7-122.localdomain:1521/pdb1 Error Message = IO Error: Undefined Error Username? (RETRYING) ('test/*********@ol7-122.localdomain:1521/pdb1'?)
We can add an entry for the PC to allow it to connect.
CONN / AS SYSDBA BEGIN dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_add_pdb_ace('pdb1','192.168.56.1'); dbsfwuser.DBMS_SFW_ACL_ADMIN.commit_acl; END; /
The SQLcl connections from the PC now work as expected.
$ ./sql sys/OraPasswd1@ol7-122.localdomain:1521/my_pdb_service as sysdba SQLcl: Release 17.2.0 Production on Tue Sep 19 20:29:35 2017 Copyright (c) 1982, 2017, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> CONN test/test@ol7-122.localdomain:1521/pdb1 Connected. SQL>
The IP_REMOVE_PDB_ACE
procedure is used to remove PDB-level ACL entries. The following removes all the PDB-level ACLs created for this example.
CONN / AS SYSDBA BEGIN dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_remove_pdb_ace('pdb1','ol7-122.localdomain'); dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_remove_pdb_ace('pdb1','192.168.56.136'); dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_remove_pdb_ace('pdb1','192.168.56.1'); dbsfwuser.DBMS_SFW_ACL_ADMIN.commit_acl; END; / -- Display the saved ACLs. COLUMN service_name FORMAT A30 COLUMN host FORMAT A30 SELECT service_name, host FROM dbsfwuser.ip_acl ORDER BY 1, 2; no rows selected SQL>
We can stop and remove the test service using the following code.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; BEGIN DBMS_SERVICE.stop_service('my_pdb_service'); DBMS_SERVICE.delete_service('my_pdb_service'); END; /
For more information see:
Hope this helps. Regards Tim...