Oracle Database configure TCP/IP with SSL and TLS for Database Connections

    Encryption is the method to convert information into secret codes to protect from the bad guys. The real meaning of the information is hidden and the process of encrypting and decrypting data is called cryptography. The formulas used for cryptography are called encryption algorithms, or ciphers.

    Oracle supports two network encryption: Oracle Native Network Encryption and TCP/IP with SSL and TLS.

    Oracle native network encryption is very simple and needs to change a parameter in sqlnet.ora to enable encryption.  All you need is to configure the server to use network encryption. The advantage of native network encryption over TLS/SSL is easy configuration. However, the downside of this method is that to a certain extent, a man-in-the-middle attack is still possible. If an attacker can get into the network and redirect clients to use a malicious database, clients will not be knowing about this although all the communications will be encrypted.

    In a nutshell, if you want easy configuration, oracle Native network encryption works for you. For maximum security, use TLS/SSL. The only downside of the TLS/SSL is harder to set up and maintain.

Here are the step-by-step guide to setup and maintaining TLS/SSL.

Server hostname: ora19c

Client hostname: oraclient

1. Execute the commands below in the server.

a. Create a directory in the client and server to store the wallet. In my case, I am planning to use /u01/app/oracle/wallet as my wallet directory in both the Oracle database client and the server.

mkdir -p /u01/app/oracle/wallet

b. Create auto login local wallet with the command below. 

orapki wallet create -wallet "/u01/app/oracle/wallet" -pwd myWallet_321 -auto_login_local

c. Create a self-signed certificate with 10 years of validity and load it into the recently created wallet.

orapki wallet add -wallet "/u01/app/oracle/wallet" -pwd myWallet_321 \

  -dn "CN=$(hostname)" -keysize 1024 -self_signed -validity 3650

d. Check wallet contents. You will notice one user certificate and one trusted certificate with CN exactly the same as the server hostname.

orapki wallet display -wallet "/u01/app/oracle/wallet" -pwd myWallet_321

e. Export wallet so that it can be loaded to the client end. Ship the server-$(hostname)-certificate.txt file once the export is completed. 

orapki wallet export -wallet "/u01/app/oracle/wallet" -pwd myWallet_321 \

   -dn "CN=$(hostname)" -cert /tmp/server-$(hostname)-certificate.crt

f. Validate the content of the server-certificate.crt file. The file should begin with -----BEGIN CERTIFICATE----- and end with -----END CERTIFICATE-----. The output will be something similar to the screenshot below.

cat /tmp/server-$(hostname)-certificate.crt

2. Execute the command below at the client end.

a. Create a directory to store the wallet.

mkdir -p /u01/app/oracle/wallet

b. Create auto login local wallet with the command below. 

orapki wallet create -wallet "/u01/app/oracle/wallet" -pwd myWallet_321 -auto_login_local

c. Create a self-signed certificate with 10 years of validity and load it into the recently created wallet.

orapki wallet add -wallet "/u01/app/oracle/wallet" -pwd myWallet_321 \

-dn "CN=$(hostname)" -keysize 1024 -self_signed -validity 3650

d. Display the contents of the wallet.

orapki wallet display -wallet "/u01/app/oracle/wallet" -pwd myWallet_321

e. Export the wallet so that it can be added to the server as a trusted certificate.

orapki wallet export -wallet "/u01/app/oracle/wallet" -pwd myWallet_321 \

-dn "CN=$(hostname)" -cert /tmp/client-$(hostname)-certificate.crt

f. Check exported file contents.

cat /tmp/client-$(hostname)-certificate.crt

3. Ship server-$(hostname)-certificate.crt to the client host and client-$(hostname)-certificate.crt to the server host. In my case /mnt is the shared directory between both the hosts.

cp /tmp/client-oraclient-certificate.crt /mnt/certs/

cp /tmp/server-ora19c-certificate.crt /mnt/certs/

4. Add client certificate to server wallet as trusted cert in the server host.

orapki wallet add -wallet "/u01/app/oracle/wallet" -pwd myWallet_321 \

-trusted_cert -cert /mnt/certs/client-oraclient-certificate.crt

# Before Adding the trusted cert

# Adding the trusted cert

# After adding the trusted cert.

5. Add server certificate to client wallet as a trusted cert

orapki wallet add -wallet "/u01/app/oracle/wallet" -pwd myWallet_321 \

-trusted_cert -cert /mnt/certs/server-ora19c-certificate.crt

# Before adding the server trusted cert.

# Adding the server trusted cert.

# After adding the server trusted cert.

6. Server sqlnet.ora configuration.

WALLET_LOCATION =

   (SOURCE =

     (METHOD = FILE)

     (METHOD_DATA =

       (DIRECTORY = /u01/app/oracle/wallet)

     )

   )

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

7. Client sqlnet.ora configuration.

WALLET_LOCATION =

   (SOURCE =

     (METHOD = FILE)

     (METHOD_DATA =

       (DIRECTORY = /u01/app/oracle/wallet)

     )

   )

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS)

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

8. Server-side Listener configuration

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =

  (SOURCE =

    (METHOD = FILE)

    (METHOD_DATA =

      (DIRECTORY = /u01/app/oracle/wallet)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCPS)(HOST = ora19c)(PORT = 2484))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

# Existing listener status

Bounce listener to reflect the change on listener configuration file: listener.ora

lsnrctl stop

lsnrctl start

Validate listener configuration

lsnrctl status

9. Add the following tnsnames in the tnsnames.ora file of client tnsnames configuration

orclpdb1=

  (DESCRIPTION=

    (ADDRESS=

      (PROTOCOL=TCP)

      (HOST=ora19c)

      (PORT=1521)

    )

    (CONNECT_DATA=

      (SERVER=dedicated)

      (SERVICE_NAME=orclpdb1)

    )

  )

orclpdb1_ssl=

  (DESCRIPTION=

    (ADDRESS=

      (PROTOCOL=TCPS)

      (HOST=ora19c)

      (PORT=2484)

    )

    (CONNECT_DATA=

      (SERVER=dedicated)

      (SERVICE_NAME=orclpdb1)

    )

  )

10. Testing connection.

# Tnsping output

Using TCP connection. The network protocol used will be TCP while executing the select statement.

sqlplus test/test123@orclpdb1

set sqlp "_user @ _connect_identifier >"

SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') as network_protocol FROM dual;

Using TCPS connection. The network protocol used will be TCPS while executing the select statement.

sqlplus test/test123@orclpdb1_ssl

set sqlp "_user @ _connect_identifier >"

SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') as network_protocol FROM dual;

Read More

Oracle Database Native Encryption

    

     Encryption is the method to convert information into secret codes to protect from the bad guys. The real meaning of the information is hidden and the process of encrypting and decrypting data is called cryptography. The formulas used for cryptography are called encryption algorithms, or ciphers.

    Encryption plays a vital role in protecting information. It provides the following primary benefits.

1. Confidentiality encodes the message's content.
2. Authentication verifies the origin of a message.
3. Integrity proves the contents of a message have remained the same since it was sent.
4. Nonrepudiation prevents senders from denying they sent the encrypted message.

    Oracle database provides network encryption to protect data while traveling through the network. It offers native data network encryption and integrity to ensure that is secure from the bad guys. Oracle Native network encryption converts plaintext data into unintelligible ciphertext based on a key. In a symmetric cryptosystem, the same key is used both for encryption and decryption of the same data. Oracle Database provides the Advanced Encryption Standard (AES) symmetric cryptosystem for protecting the confidentiality of Oracle Net Services traffic.

    Oracle Database supports the Federal Information Processing Standard (FIPS) encryption algorithm, Advanced Encryption Standard (AES). AES is a highly supported algorithm all over the works. It defines three standard key lengths of 128-bit, 192-bit, and 256-bit.

    Encrypting network data provides data privacy so that unauthorized parties cannot view plaintext data as it passes over the network.

Oracle database native encryption is easy to deploy and follows the step-by-step guide to deploy it.

All configurations are done in the "sqlnet.ora" files on the client and server. 

Set the following parameters in the sqlnet.ora file of the server.

SQLNET.ENCRYPTION_SERVER

SQLNET.ENCRYPTION_TYPES_SERVER

Set the following parameters in the sqlnet.ora file of the client.

SQLNET.ENCRYPTION_CLIENT

SQLNET.ENCRYPTION_TYPES_CLIENT

Following are the acceptable values for SQLNET.ENCRYPTION_[SERVER|CLIENT]

1. ACCEPTED: It is the most relaxed and default parameter if the parameter is not set. The client or server will allow both encrypted and non-encrypted connections. 
2. REJECTED: It is the plain-text-only parameter and both client and server will refuse encrypted traffic.
3. REQUESTED: It is a relaxed and somewhat secure way for encryption. The client or server will request encrypted traffic whenever possible but will accept non-encrypted traffic if encryption is not possible.
4. REQUIRED: It is the highly restricted value for the parameter, the client or server will only accept encrypted traffic.

Set the following parameters in sqlnet.ora of the server to encrypt Oracle database network traffic using the AES256 algorithm. 

SQLNET.ENCRYPTION_SERVER=REQUIRED

SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)

Set the following parameters in sqlnet.ora of the client to encrypt Oracle database network traffic using the AES256 algorithm. 

SQLNET.ENCRYPTION_CLIENT=REQUIRED

SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

The above configuration will discard any unencrypted traffic. To get a lit bit of relaxed configuration we can use the following parameters in the sqlnet.ora file of the server. It will prefer a client to use an encrypted connection to the server but will accept a non-encrypted connection too.

SQLNET.ENCRYPTION_SERVER=REQUESTED

SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)

It is highly advised to upgrade the Oracle database client version to support encryption. I have noticed a lot of incidents in the database due to clients not supporting the encryption. The output of the encrypted connection has been attached below. Executed command below to find if encryption is enabled while connecting to the database.

set line 1000

col NETWORK_SERVICE_BANNER for a100

select SID, SERIAL#,NETWORK_SERVICE_BANNER 

from V$SESSION_CONNECT_INFO 

where sid=(select sid from v$mystat where rownum=1);

# Unencrypted connection.

# Added parameters to the server side sqlnet.ora

# Added parameters to the client side sqlnet.ora

# Encrypted connections.

Read More

Decrypt Oracle Weblogic password.

    Oracle WebLogic Server is a Java EE application server currently developed by Oracle Corporation. Oracle acquired WebLogic Server when it purchased BEA Systems in 2008. It is a chance that we may forget the WebLogic admin username and password. Following is the step-by-step guide to recovering the WebLogic admin user and password.

1. Set oms environment variables.

. oraenv <<< oms 

2. Changed directory to the GCDomain/bin

cd  /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/bin/

ls -alh

3. The WebLogic settings are in the setDomainEnv.sh script file(setDomainEnv.cmd for Windows and setDomainEnv.sh for Linux). Execute the command below to set WebLogic environment variables.

./setDomainEnv.sh

4. The username and password are stored in encrypted format in the file boot.properties below. Search the file with the name. There may be multiple boot.properties files, but the required one is inside the security directory.

find /u01 -iname boot.properties

Choose the one with the security/boot.properties as in the screenshot below.

/u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/security/boot.properties

5. WebLogic Scripting Tool (WLST) is a command-line scripting interface system administrators and operators use to monitor and manage WebLogic Server instances and domains. Execute wlst.sh inside the oracle_common/common/bin directory.

${ORACLE_HOME}/oracle_common/common/bin/wlst.sh

Type the following command to fetch the password.

Once you execute the wlst.sh the prompt will be like below. 

wls:/offline>

6. Execute the command one at a time. Make sure you replace the location of the boot.properties noted in the earlier step.

from weblogic.security.internal import BootProperties

BootProperties.load("<boot.properties location>", false)

prop = BootProperties.getBootProperties()

print "username: " + prop.getOneClient()

print "password: " + prop.getTwoClient()

The password was recovered successfully.

Read More

How to find Oracle Enterprise Manager startup time.

 

    Oracle Enterprise Manager(OEM) is an on-premises solution for a comprehensive monitoring and management solution for Oracle products such as Oracle Database and Engineered Systems. A lot of plugins, monitoring Templates, thresholds, and other features make OEM a great tool for monitoring.

    Few days ago, I was asked to find the Oracle Management Service(OMS) startup time. I googled a lot and search for a better way to present the startup time for the OEM process. Finally, after 2 hours of searching I found the solution to find the startup time of the OEM.

    Following is the step-by-step process to find the startup time of the OMS startup time.

1. Click on Setup >> Manage Cloud Control >> Management Servers.

2. Click on the Management Servers >> Monitoring >> Status History.

3. The time value in the Up Since provides the time the OMS server has been started.

4. At the top left side, there is a drop-down menu to check the Overall Availability Duration. We have the option to check availability during the last 24 hours, last 7 days, last 31 days, and custom.

Read More