The Federal Information Processing Standard (FIPS) is a government standard (140-2) for identifying cryptographic security requirements to protect data at rest and transit over the network. FIPS has multiple levels ranging from 1 (lowest) to 4 (highest). The 140-2 represents level 2: this applies to the operational environment for a software module which is the highest level for software. For those that wonder how to get to Level 4 on the database, it only applies to hardware components.
FIPS is designed to ensure confidentiality and non-repudiation of data access, no matter the state of the data. If your database is not FIPS-compliant, your data is more vulnerable to threats and potential security breaches. Given the number of security breaches that seem to happen each year, this is a good thing to implement. And the good news is that it’s not too challenging to setup.
This example assumes a two node Oracle RAC database. If you can follow these steps for two nodes, the same concepts will apply to a larger RAC configuration or a standalone. All steps should be performed as the Oracle software owner. With that being said, let’s get started.
DB FIPS Implementation Steps Using Third Party Certificates
1) Setup another port in the local listener to use TCPS.
To do so, modify local listener with our case adding port 1522 to use TCPS. This example assumes 1521 is currently the TCP listener port and 1522 is not in use:
srvctl modify listener -listener listener1 -p "TCP:1521/TCPS:1522"
2) Create wallet on each node.
In this case we will use /home/oracle/dbfips_wallet as the location to store the wallet. Welcome1 will be the wallet password used in this example, but would certainly recommend doing something more complex for your own implementation...
a. Perform these commands on each node.
cd /home/oracle
mkdir dbfips_wallet
orapki wallet create -wallet /home/oracle/dbfips_wallet -auto_login -pwd Welcome1
b. Create the certificate request on node1.
The CN entry should be customized to your organization as you see fit
orapki wallet add -wallet /home/oracle/dbfips_wallet -dn "CN=host1.com,OU=EM,O=Organization,L=City,ST=State,C=US" -keysize 1024 -sign_alg sha256 -pwd Welcome1
c. Create the certificate request on node2. The CN entry should be customized to your organization as you see fit.
orapki wallet add -wallet /home/oracle/dbfips_wallet -dn "CN=host2.com,OU=EM,O=Organization,L=City,ST=State,C=US" -keysize 1024 -sign_alg sha256 -pwd Welcome1
d. Create the certificate request on node1.
orapki wallet export -wallet /home/oracle/dbfips_wallet -dn "CN=host1.com,OU=EM,O=Organization,L=City,ST=State,C=US" -request node1csr.txt
e. Create the certificate request on node2.
orapki wallet export -wallet /home/oracle/dbfips_wallet -dn "CN=host2.com,OU=EM,O=Organization,L=City,ST=State,C=US" -request node2csr.txt
f. Add certificates to wallet.
Repeat these commands on each node.
./orapki wallet add -wallet /home/oracle/dbfips_wallet -trusted_cert -cert /home/oracle/dbfips_wallet/Root.crt -pwd Welcome1
./orapki wallet add -wallet /home/oracle/dbfips_wallet -trusted_cert -cert /home/oracle/dbfips_wallet/Intermediate.crt -pwd Welcome1
./orapki wallet add -wallet /home/oracle/dbfips_wallet -user_cert -cert /home/oracle/dbfips_wallet/ServerCertificate.crt -pwd Welcome1
3) Update SQLNET.ora / Listener.ora in the GI Home with the below settings.
SQLNET.AUTHENTICATION_SERVICES= (TCPS, BEQ, IPC)
SSL_CLIENT_AUTHENTICATION = TRUE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_3DES_EDE_CBC_SHA)
SSL_VERSION = 1.2 or 1.1
SSL_SERVER_DN_MATCH=TRUE
WALLET_LOCATION =
(SOURCE=
(METHOD=File)
(METHOD_DATA=
(DIRECTORY=/home/oracle/dbfips_wallet)
)
)
4) Update the initialization parameters.
a. The value for the HOST entry can be obtained by doing a lsnrctl status <listener name> and seeing which IP Address is associated with TCP and TCPS entries. Below is an example with the appropriate entry in bold…
lsnrctl status listener1
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 08-FEB-2018 11:51:08
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(RATE_LIMIT=yes)(KEY=LISTENER1)))
STATUS of the LISTENER
------------------------
Alias LISTENER1
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 07-DEC-2017 10:25:13
Uptime 63 days 1 hr. 25 min. 55 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/node1/listener1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=180.170.50.12)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=180.170.70.17)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=180.170.50.12)(PORT=1521)))
b. The values for the cluster interconnects can be found typically by looking at the contents of /etc/hosts. Please see below with the appropriate items in bold…
#### BEGIN Added by Configuration Utility ####
180.170.10.1 xx02db01-priv1.example.com xx02db01-priv1
180.170.10.2 xx02db01-priv2.example.com xx02db01-priv2
180.170.10.3 xx02db02-priv1.example.com xx02db02-priv1
180.170.10.4 xx02db02-priv2.example.com xx02db02-priv2
c. Replace db1 with appropriate ORACLE_SID
Node 1:
alter system set DBFIPS_140=TRUE scope=spfile;
alter system set local_listener="(ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER1))(ADDRESS=(PROTOCOL=TCP)(HOST=180.170.50.12)(PORT=1521))(ADDRESS=(PROTOCOL=TCPS)(HOST=180.170.50.12)(PORT=1522)))" sid='db1';
alter system set cluster_interconnects=’180.170.10.1: 180.170.10.2' scope=spfile sid='db1';
d. The value for the HOST entry can be obtained by doing a lsnrctl status <listener name> and seeing which IP Address is associated with TCP and TCPS entries. Below is an example with the appropriate entry in bold…
lsnrctl status listener1
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 08-FEB-2018 11:51:08
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(RATE_LIMIT=yes)(KEY=LISTENER1)))
STATUS of the LISTENER
------------------------
Alias LISTENER1
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 07-DEC-2017 10:25:13
Uptime 63 days 1 hr. 25 min. 55 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/node2/listener1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=180.170.50.14)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=180.170.70.18)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=180.170.50.14)(PORT=1521)))
e. The values for the cluster interconnects can be found typically by looking at the contents of /etc/hosts
f. Replace db2 with appropriate ORACLE_SID
Node 2:
alter system set local_listener="(ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER1))(ADDRESS=(PROTOCOL=TCP)(HOST=180.170.50.14)(PORT=1521))(ADDRESS=(PROTOCOL=TCPS)(HOST=180.170.50.14)(PORT=1522)))" sid='db2';
alter system set cluster_interconnects=’180.170.10.3: 180.170.10.4' scope=spfile sid='db2';
5) Restart the database where the changes were made.
. oraenv <- Specify ORACLE_SID
srvctl stop database -d <database name>
srvctl start database -d <database name>
6) Setup the fips.ora file
. oraenv <- specify ORACLE_SID
cd $ORACLE_HOME/ldap/admin
vi fips.ora <- add the below entry
SSLFIPS_140=TRUE
7) Restart the database where the changes were made.
. oraenv <- Specify ORACLE_SID
srvctl stop database -d <database name>
srvctl start database -d <database name>
Verify that DBFIPS is set up.
1) Set up additional tracing/logging.
Add following lines to sqlnet. ora to trace the FIPS:
DIAG_ADR_ENABLED=off
trace_directory_server=/home/oracle/dbfips_wallet/trace
trace_file_server=fips_trace_single.trc
trace_level_server=16
TRACE_FILE_CLIENT = cli
TRACE_DIRECTORY_CLIENT =/home/oracle/dbfips_wallet/trace
TRACE_UNIQUE_CLIENT = ON
TRACE_TIMESTAMP_CLIENT = ON
trace_filelen_client = 100
trace_fileno_client = 2
log_file_client = cli
log_directory_client=/home/oracle/dbfips_wallet/trace
tnsping.trace_directory =/home/oracle/dbfips_wallet/trace
tnsping.trace_level = admin
trace_level_client = 16
trace_file_client = client.trc
2) Set up a tnsnames.ora entry (in this example, on node1).
Below is an example based on the previously provided information.
DBS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = 180.170.50.12)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db.world)
)
)
3) Connect using the secure TNS Entry.
sqlplus scott/tiger@DBS
4) Check log files.
grep -i "Final Negotiated SSL Cipher Suite" *
client.trc1_1674.trc:(1) [000003 03-MAR-2017 14:45:54:261] The Final Negotiated SSL Cipher Suite is: SSL_RSA_WITH_AES_256_GCM_SHA384
client.trc2_1674.trc:(1) [000002 03-MAR-2017 14:45:23:828] The Final Negotiated SSL Cipher Suite is: SSL_RSA_WITH_AES_256_GCM_SHA384
fips_trace_single_1364.trc:[03-MAR-2017 14:43:19:270] The Final Negotiated SSL Cipher Suite is: SSL_RSA_WITH_AES_256_GCM_SHA384
fips_trace_single_1705.trc:[03-MAR-2017 14:45:23:839] ntzcontrol: The Final Negotiated SSL Cipher Suite is: SSL_RSA_WITH_AES_256_GCM
5) If using TDE, an error can occur that will cause the database to not start once in FIPS mode.This is caused by the certificate request in the TDE wallet using the wrong algorithm. This can be fixed by running the below:
## The TDE wallet location in this example is /u01/app/WALLETS/db
## Also, used a self-signed certificate in this example
orapki wallet display -wallet /u01/app/WALLETS/db
orapki wallet remove -wallet /u01/app/WALLETS/db -cert_req -dn "CN=oracle" -pwd "password"
orapki wallet add -wallet /u01/app/WALLETS/db -self_signed -dn "CN=Oracle" -keysize 1024 -validity 3650 -sign_alg sha256 -pwd "Welcome1"
orapki wallet display -wallet /u01/app/WALLETS/db
Hope you found this helpful. If you have any questions or would like to discuss this further, please feel free to reach out.
