There are Three ways to configure Oracle Container database (CDB) with Pluggable databases (PDBs) for Network isolation.
they are
1) Using Connection Manager (CMAN) with Access Control rules. ( Oracle Recommended way )
2) using 12.2 new feature Access control layer for PDBs. ( 12.2 New feature )
3) One listener per PDB on a distinct port and have firewall white-list for each PDB. ( works in DBaaS in Oracle cloud. But not practical when there are many PDBS.)
1) CMAN :
Using CMAN is the recommended way. You have a one time configuration of a CMAN server which listens to user connections and forwards to the remote DB listener.
The remote DB listener registers their services when their DBs configure remote_listener parameter in the database.
The Access Control rules in the CMAN configuration file cman.ora works like a firewall whitelist where you configure ipaddress lists that are allowed to access each service. In 12c, since each PDB is a service, the rules can be used to network-isolate each PDB. This way, each PDB tennant is completely isolated from the neighboring PDB and cannot access it even if they know username password and connect string.
Cman lister will listen on a port witn a service_name "cman" registered. CMAN also registers all the PDBs to which connections could be forwarded. This service then forwards the connection to the other registered PDBS if the rule sets are accepted. All DBs that need connection forwarded to them, need to register the services by configuring the remote listeners (remote_listener parameter).
Below is a quick list of steps you need to do to configure a CMAN.
Step a) install CMAN. : you will have to install CMAN from Oracle client Software. Choose custom install and choose Connection manager and sql*net. You can install CMAN on the same server as CDB or on a remote server.
Step b) configure cman.ora in CMAN_HOME/network/admin directory.
Below is the example of configuring cman.ora
cman_test =
(configuration=
(address=(protocol=tcp)(host=list)(port=1999))
(parameter_list =
(aso_authentication_filter=off)
(connection_statistics=yes)
(log_directory=/home/oracle/logs)
(log_level=off)
(max_connections=256)
(idle_timeout=0)
(inbound_connect_timeout=0)
(session_timeout=0)
(outbound_connect_timeout=0)
(max_gateway_processes=16)
(min_gateway_processes=2)
(remote_admin=on)
(trace_directory=/home/oracle/logs)
(trace_level=admin)
(trace_timestamp=off)
(trace_filelen=1000)
(trace_fileno=1)
(max_cmctl_sessions=4)
(event_group=init_and_term,memory_ops)
)
(rule_list=
(rule=
(src=140.86.39.27)(dst=*)(srv=pdb1)(act=accept)
(action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
)
(rule=(src=list)(dst=127.0.0.1)(srv=cmon)(act=accept))
(rule=(src=140.86.39.27)(dst=*)(srv=list)(act=accept))
(rule=(src=140.86.39.63)(dst=*)(srv=pdb2)(act=accept))
)
)
This service rule " (rule=(src=<cman_hostname>)(dst=127.0.0.1)(srv=cmon)(act=accept)" is a mandatory rule that will start a cman listener service CMAN on the given port "(address=(protocol=tcp)(host=<cnamHost>)(port=1999)) ". Without this line, you will get errors like
"(EVENT=CMAN.ORA contains no rule for local CMCTL connection)(Add (rule=(src=list)(dst=127.0.0.1)(srv=cmon)(act=accept)) in rule_list)"
Other rules like "(rule=(src=140.86.39.63)(dst=*)(srv=pdb2)(act=accept))" forwards connection requests from ipaddress 140.86.39.63 to pdb2 only, provided that service is registered.
Step c) start CMAN.
Using, CMCTL, connect to configuration profile in cman.ora. The alias in the above example is cman_test.
[oracle@list ~]$ cmctl
CMCTL> ADMINISTER cman_test
CMCTL:cman_test> .
Step d) Register remote PDBs or services
alter system set remote_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=129.150.66.81)(PORT=1999))';
Note: if you DB listener is on 1521, then the PDBs get autoregistered in local and remote listener.
Step e) connect from client:
SQL> conn system/Saturn_02@//<cman_host>:1999/pdb2
Connected.
or configure tnsname.ora to point to cman host, cman port and service name.
Note, The clients may not have access to the PDBs directly and may not even know the ipaddress. However, as long a the DBs can access the cman host and port, the connection gets forwarded.
2) Database Service Firewall
This option is based new functionality of 12.2 database. It is called Database Service Firewall.
It is part of Oracle Access Control List. (ACL). Each policy is represented by an access control list (ACL) containing hosts that are allowed access to a specific database service. Local listeners and server processes validate all inbound client connections against the ACL.
Below are the steps to configure the DB for PDB isolation using Database service firewall.
Step a) You need a package DBMS_SFW_ACL_ADMIN package. This is installed by running as sysdba. This package is owned by the DBSFWUSER schema. The procedures in this package can be run only by the DBSFWUSER user.
SQL> conn / as sysdba
Connected.
-- Create user and package.
SQL> @$ORACLE_HOME/rdbms/admin/dbmsaclsrv.sql
Step B)
Configure the listener.ora and set LOCAL_REGISTRATION_ADDRESS_LISTENER and firewall=on. Below is the sample listener.ora with modification in red.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = CDB4.compute-dbuser19.oraclecloud.internal)(PORT = 1521) (firewall=on))
)
)
VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON
SSL_VERSION = 1.0
LOCAL_REGISTRATION_ADDRESS_LISTENER= (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
Step C) Start the listener.
[oracle@CDB4 admin]$ lsnrctl start
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 10-MAR-2017 22:35:08
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/CDB4/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CDB4.compute-dbuser19.oraclecloud.internal)(PORT=1521)(FIREWALL=ON)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CDB4.compute-dbuser19.oraclecloud.internal)(PORT=1521)(firewall=on)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 10-MAR-2017 22:35:08
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/CDB4/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CDB4.compute-dbuser19.oraclecloud.internal)(PORT=1521)(FIREWALL=ON)))
The listener supports no services
The command completed successfully
[oracle@CDB4 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Mar 10 22:36:55 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Step D) Now setup the local_listener to point to the listener.
SQL> alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=localhost)(PORT=1521))';
System altered.
SQL> alter system register;
System altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 CDB4PDB1 READ WRITE NO
6 MYLOCAL READ WRITE NO
Step e) Note that you will get errors if you do not include the DBSFWUSER.
Add the ACL service ipaddress for the perticulat PDB and the IP address from which to allow connections.
SQL> execute dbms_sfw_acl_admin.ip_add_pdb_ace('CDB4PDB1','140.86.12.219');
BEGIN dbms_sfw_acl_admin.ip_add_pdb_ace('CDB4PDB1','140.86.12.219'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SFW_ACL_ADMIN.IP_ADD_PDB_ACE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> exec DBSFWUSER.DBMS_SFW_ACL_ADMIn.ip_add_pdb_ace('CDB4PDB1','140.86.12.219');
PL/SQL procedure successfully completed.
SQL> exec DBSFWUSER.dbms_sfw_acl_admin.commit_acl;
PL/SQL procedure successfully completed.
SQL> exec DBSFWUSER.DBMS_SFW_ACL_ADMIn.ip_add_pdb_ace('CDB4PDB1','140.86.39.63');
PL/SQL procedure successfully completed.
SQL> exec DBSFWUSER.dbms_sfw_acl_admin.commit_acl;
PL/SQL procedure successfully completed.
SQL> alter system register;
System altered.
SQL> exec DBSFWUSER.DBMS_SFW_ACL_ADMIn.IP_REMOVE_PDB_ACE('CDB4PDB1','140.86.39.63');
PL/SQL procedure successfully completed.
SQL> alter system register;
System altered.
Step f) Connect from remote client and test. You will either get connected or access denied error depending on the ACL rule.
select * from DBSFWUSER .ip_acl;
SERVICE_NAME HOST
-------------------------------------------- --------------------------------
"48ADC37AB9052039E0536E99C40AD0FD" 140.86.12.219
"48ADC37AB9052039E0536E99C40AD0FD" 140.86.39.63
"CDB4PDB1" 140.86.12.219
"CDB4PDB1" 140.86.39.63
SQL> conn system/Saturn_02@//140.86.39.27/CDB4PDB1
Connected.
if the sqlplus is run from a client ip not in ACL, you the the following error.
SQL> conn system/Saturn_02@//140.86.39.27/CDB4PDB1
ERROR:
ORA-46981: Access to service CDB4PDB1 from 140.86.39.63 was denied.
___________________________________________________________________________________________________________________________________________________
3) Multiple Listener
This is not a Complete Oracle solution, but using a combination of network security and listener configuration, we can achieve network isolation to the PDBs.
Oracle 12.2 onward, the parameter LOCAL_LISTENERS is modifiable. So could create one listener per PDB, one listener for CDB on port 1521, and additional listener per PDB.
All the listeners had each had different names and and ports.
Now you can modify Network firewall rules to allow only certain Ip address per port. It is easier done in Oracle Cloud DBaaS. Here the DB instance access rules can be altered to create ip white lists and accociate the whitelist to a access rule and access rule to the DBaas VM.
While this is not the recommended way, I am only listing this as a option for the sake of documentation and it was the first option i tried out as i had access to DBaaS.
Step 1) create listeners listening for different ports for each PDB.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = list.compute-dbuser19.oraclecloud.internal)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
LISTENER_PDB1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = list.compute-dbuser19.oraclecloud.internal)(PORT = 1531))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1531))
)
)
LISTENER_PDB2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = list.compute-dbuser19.oraclecloud.internal)(PORT = 1532))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1532))
)
)
LISTENER_PDB3 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = list.compute-dbuser19.oraclecloud.internal)(PORT = 1533))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1533))
)
)
step 2)
Modify each PDB to listener on a seperate port.
[oracle@list admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 2 22:46:36 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c EE High Perf Release 12.2.0.1.0 - 64bit Production
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 NEWPDBFROMSEED READ WRITE NO
6 PDB3 READ WRITE NO
SQL> alter session set container=pdb2;
Session altered.
SQL> show parameter local_lis
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string
SQL> alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=localhost)(PORT=1532))';
System altered.
SQL> alter system register;
System altered.
SQL> !
[oracle@list admin]$ lsnrctl status listener_pdb2
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 02-MAR-2017 22:49:01
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=list.compute-dbuser19.oraclecloud.internal)(PORT=1532)))
STATUS of the LISTENER
------------------------
Alias listener_pdb2
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 02-MAR-2017 22:46:05
Uptime 0 days 0 hr. 2 min. 56 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/list/listener_pdb2/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=list.compute-dbuser19.oraclecloud.internal)(PORT=1532)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1532)))
Services Summary...
Service "49ada128d2c73b24e053aa6bc40a78d4" has 1 instance(s).
Instance "list", status READY, has 2 handler(s) for this service...
Service "pdb2" has 1 instance(s).
Instance "list", status READY, has 2 handler(s) for this service...
The command completed successfully
[oracle@list admin]$ exit
exit
Step 3) This is part where i create a separate white-list for each port associated with the DBaaS Vm. I followed the steps from this site.
5051.jpg)
