(Photo by Johannes Plenio, on Unsplash)
Conferences are great. Not only for the technical content, also for the people. Recently during DOAG, I had very interesting conversations (yes, several conversations 🙂 ) with Martin Berger about how to control who is connecting to which database in a complex environment. Among other topics, we mentioned that it was possible, starting with Oracle 12.2, to set Access Control Lists to allow connections to a database service (in Non-CDB or PDB) from specific IP addresses.
This new feature Database Service Firewall was introduced with Oracle 12.2. It should not to be confused with Database Firewall, which is a dedicated system used to monitor traffic from and to databases, and is part of Oracle Audit Vault and Database Firewall product.
As I never used Database Service Firewall, I decided to give it a try in a Multitenant environment with RAC.
My lab is a 2-node RAC cluster with Grid Infrastructure 18, a 18.3 RAC Container database called metal, and one pluggable database called opeth.
Service creation
I start by creating a new service called favourite_band on this PDB with 1 preferred instance :
srvctl add service -db metal -service favourite_band -pdb opeth -preferred metal1 srvctl start service -db metal -service favourite_band
Listeners modification
Following My Oracle Support Doc 2360283.1 on Database Firewall Service, I now want to filter all connections to PDB opeth, except those coming from the IP address 192.168.78.51.
First, I modify local listeners to enable firewall functionality :
srvctl modify listener -listener listener -endpoints TCP:1521:FIREWALL=ON
And then, I add LOCAL_REGISTRATION_ADDRESS_LISTENER=ON in listener.ora. This is a mandatory parameter to allow service ACLs to be processed by listeners.
Access Control List creation
The Access Control List we are about to create is handled by the local listeners, as described in the documentation : “Local listeners and server processes validate all inbound client connections against the ACL.”
OK, now let’s try to add an Access Control Entry to prevent any host except IP 192.168.78.51 to connect to PDB opeth, using any service of course :
SQL> connect / as sysdba
Connected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> execute dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_add_pdb_ace('opeth','192.168.78.51') ;
BEGIN dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_add_pdb_ace('opeth','192.168.78.51') ; END;
*
ERROR at line 1:
ORA-20014: Must run as DBSFWUSER user
ORA-06512: at "DBSFWUSER.DBMS_SFW_ACL_ADMIN", line 322
ORA-06512: at "DBSFWUSER.DBMS_SFW_ACL_ADMIN", line 920
ORA-06512: at line 1
Oops, even SYS is not allowed to run this package : “This package is owned by the DBSFWUSER schema. The procedures in this package can be run only by the DBSFWUSER user.”
So I have to unlock this user in order to connect (or use proxy user authentication). It does not work with ALTER SESSION SET CURRENT_SCHEMA either :
SQL> alter user dbsfwuser identified by aggressive_password account unlock ; User altered. SQL> connect dbsfwuser/aggressive_password Connected.
Now that’s much better :
SQL> execute dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_add_pdb_ace('opeth','192.168.78.51') ;
PL/SQL procedure successfully completed.
SQL> execute dbsfwuser.DBMS_SFW_ACL_ADMIN.commit_acl ;
PL/SQL procedure successfully completed.
If I try to connect to service favourite_band, which refers to PDB opeth, from host 192.168.78.52, I get the following error :
# sqlplus mikael@favourite_band
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Dec 12 13:40:42 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-12506: TNS:listener rejected connection based on service ACL filtering
But from server 192.168.78.51, everything is fine :
# sqlplus mikael@favourite_band SQL*Plus: Release 18.0.0.0.0 - Production on Wed Dec 12 13:42:27 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Enter password: Last Successful login time: Fri Dec 07 2018 23:36:31 +01:00 Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0
Useful table and view
There are 2 interesting objects : table dbsfwuser.ip_acl, which contains ACLs that are committed, and view v$ip_acl, which contains ACLs sent to the local listener.
SQL> select * from v$ip_acl ; SERVICE_NAME HOST CON_ID ------------------------------ -------------------- ---------- FAVOURITE_BAND.RACATTACK 192.168.78.51 3 OPETH.RACATTACK 192.168.78.51 3
The first row refers to our newly created service, and the second one refers to the default service name that comes with every PDB creation.
SQL> select * from dbsfwuser.ip_acl ; SERVICE_NAME HOST ---------------------------------------------------------------- ---------------------- "7C18DF5B044067E2E053334EA8C09FD1.RACATTACK" 192.168.78.51 "FAVOURITE_BAND.RACATTACK" 192.168.78.51 "OPETH.RACATTACK" 192.168.78.51
What is this first strange service name ? Thanks to Franck Pachot and this excellent blog post, I found out that it is an internal service named after the GUID of the PDB. It is not supposed to be used.
If you remember, at the beginning of this post, I created service favourite_band on 1 preferred instance of my RAC database. So if I check v$ip_acl on the other instance, I only have the default PDB service :
SQL> select * from v$ip_acl ; SERVICE_NAME HOST CON_ID ------------------------------ -------------------- ---------- OPETH.RACATTACK 192.168.78.51 3
But thanks to gv$ip_acl, I can check what’s active on all instances :
SQL> select * from gv$ip_acl ; INST_ID SERVICE_NAME HOST CON_ID ---------- ------------------------------ -------------------- ---------- 1 FAVOURITE_BAND.RACATTACK 192.168.78.51 3 1 OPETH.RACATTACK 192.168.78.51 3 2 OPETH.RACATTACK 192.168.78.51 3
Now, let’s see what v$ip_acl and dbsfwuser.ip_acl contain in different cases :
What happens when I stop my service ?
- Stop service
favourite_band:
# srvctl stop service -db metal -service favourite_band # srvctl status service -service favourite_band -db metal Service favourite_band is not running.
- Check v$ip_acl :
SQL> select * from v$ip_acl ; SERVICE_NAME HOST CON_ID ------------------------------ -------------------- ---------- FAVOURITE_BAND.RACATTACK 192.168.78.51 3 OPETH.RACATTACK 192.168.78.51 3
Service favourite_band still appears.
- Check
dbsfwuser.ip_acl:
SQL> select * from dbsfwuser.ip_acl ; SERVICE_NAME HOST ---------------------------------------------------------------- ---------------------- "7C18DF5B044067E2E053334EA8C09FD1.RACATTACK" 192.168.78.51 "FAVOURITE_BAND.RACATTACK" 192.168.78.51 "OPETH.RACATTACK" 192.168.78.51
Likewise, service favourite_band still appears.
- Issue a commit :
SQL> execute DBMS_SFW_ACL_ADMIN.commit_acl; PL/SQL procedure successfully completed.
- Check v$ip_acl again :
SQL> select * from v$ip_acl ; SERVICE_NAME HOST CON_ID ------------------------------ -------------------- ---------- FAVOURITE_BAND.RACATTACK 192.168.78.51 3 OPETH.RACATTACK 192.168.78.51 3
Likewise.
- Check
dbsfwuser.ip_aclagain :
SQL> select * from dbsfwuser.ip_acl ; SERVICE_NAME HOST ----------------------------------------------------------------- -------------------- "7C18DF5B044067E2E053334EA8C09FD1.RACATTACK" 192.168.78.51 "FAVOURITE_BAND.RACATTACK" 192.168.78.51 "OPETH.RACATTACK" 192.168.78.51
Likewise. Thus, those 2 objects do not reflect the status of the service.
What happens I, then, remove my service ?
- Remove service
favourite_band:
srvctl remove service -db metal -service favourite_band -f
- Check
v$ip_acl:
SQL> select * from v$ip_acl ; SERVICE_NAME HOST CON_ID ------------------------------ -------------------- ---------- FAVOURITE_BAND.RACATTACK 192.168.78.51 3 OPETH.RACATTACK 192.168.78.51 3
Service favourite_band still appears.
- Check
dbsfwuser.ip_acl:
SQL> select * from dbsfwuser.ip_acl ; SERVICE_NAME HOST ---------------------------------------------------------------- ---------------------- "7C18DF5B044067E2E053334EA8C09FD1.RACATTACK" 192.168.78.51 "FAVOURITE_BAND.RACATTACK" 192.168.78.51 "OPETH.RACATTACK" 192.168.78.51
Likewise.
- Issue a commit :
SQL> execute DBMS_SFW_ACL_ADMIN.commit_acl; PL/SQL procedure successfully completed.
- Check
v$ip_acl:
SQL> select * from v$ip_acl ; SERVICE_NAME HOST CON_ID ------------------------------ -------------------- ---------- OPETH.RACATTACK 192.168.78.51 3
Now that’s interesting : service favourite_band is gone.
- Check
dbsfwuser.ip_acl:
SQL> select * from dbsfwuser.ip_acl ; SERVICE_NAME HOST ---------------------------------------------------------------- ---------------------- "7C18DF5B044067E2E053334EA8C09FD1.RACATTACK" 192.168.78.51 "FAVOURITE_BAND.RACATTACK" 192.168.78.51 "OPETH.RACATTACK" 192.168.78.51
But still, it appears in dbsfwuser.ip_acl even if the commit has been issued. This is not a big problem as the PDB is still protected, but it can be misleading.
Now, how to go back to a clean situation ?
The only solution I have for the moment is to recreate and restart the service, remove the Access Control List, commit the change, check everything disappeared, and then remove the service again … But I am sure there is a better solution.
Remove Access Control List
Finally, to allow all hosts to access again PDB opeth, I remove the corresponding ACL and commit using the following procedures :
SQL> execute DBMS_SFW_ACL_ADMIN.ip_remove_pdb_acl('opeth') ;
PL/SQL procedure successfully completed.
SQL> execute DBMS_SFW_ACL_ADMIN.commit_acl ;
PL/SQL procedure successfully completed.
… and also modify local listeners with (FIREWALL=OFF) :
srvctl modify listener -listener listener -endpoints TCP:1521:FIREWALL=OFF
Be careful not to forget this last step … I did, and then I could not connect from any host anymore, even though dbsfwuser.ip_acl and v$ip_acl returned no rows … But of course, the documentation says “If no ACLs are configured for a service, all connections are rejected for that service.”
Note that you can also remove one specific entry from the ACL with procedure DBMS_SFW_ACL_ADMIN.ip_remove_pdb_ace.
Bonus point
Maybe you noticed that v$ip_acl never contains the GUID service … What if I try to connect to PDB opeth, from an authorized host, with the GUID service ?
# sqlplus mikael@"(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=7c18df5b044067e2e053334ea8c09fd1.racattack))(ADDRESS=(PROTOCOL=TCP)(HOST=racatt-scan.racattack)(PORT=1521)))"
SQL*Plus: Release 18.0.0.0.0 - Production on Sat Dec 15 16:47:39 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-12506: TNS:listener rejected connection based on service ACL filtering
So it is not possible to connect to the PDB through it with an authorized host, even though this host is in dbsfwuser.ip_acl …
Use with Connection Manager (CMAN)
This functionnality could be used with CMAN by authorizing only CMAN’s hosts in ACLs of PDBs, and then, manage clients’ hosts in CMAN configuration instead of managing them in a distributed way accross all PDBs. Thanks to Ludovico Caldara for this idea 🙂
Floo Bar 