8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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.

Database Firewall

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.

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

Back to the Top.