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

Secure Linux server 3

Keep /boot as read-only

/boot contains the kernel, ramdisk images as well as bootloader configuration file and bootloader stages. This partition is not required for normal system operation, but read is required while boot and read and write required while kernel upgrades. It will be safer to mount this partition as read only on production system. We can remount this partition with read write whenever kernel upgrades are required.

By default /boot is mount with read and write option while installation. We can modify /etc/fstab to change the mount option. Execute mount command to view all the partitions mounted.

mount

Modify fstab entry so that boot partition will be mounted read only.

vi /etc/fstab
replace default with default,ro on mount entry.

Remount /boot partition with read only option and verify that boot partition is mount to read only mode.

sudo mount -o remount,ro /boot

mount

Temporary remount /boot on read write mode whenever required(in case of kernel upgrade or boot option changes).

sudo mount -o remount,rw /boot

Mount /tmp and data partition With nodev, nosuid, and noexec Options

By default /tmp directory will be accessible to everyone. That is the reason most of hacker and crackers use /tmp as the storage area to store the malicious code and execute them. We can mount /tmp with nodev, nosuid and noexec to avoid such attempts.

Any user can create file and execute it as below.

cd /tmp/

cat > hello.sh

echo "This is hello message from tmp"

^C

chmod 755 hello.sh 

/tmp/hello.sh 

nosuid: Do not set SUID/SGID access on this partition.

nodev: Do not set character or special devices access on this partition.

noexec: Do not allow direct execution of any binaries on the mounted filesystem.

/dev/sdb1               /tmp                    ext4    defaults,nosuid,nodev,noexec       0 0

noexec can be used on the partition with datafile and execution of the binary file is not required. Lets say we can mount data partition of database with noexec Option. In the case below executable file created on /tmp is not directly executed. We can use sh to execute the binary file.

Ignore ICMP or Broadcast Request

ICMP packets are used to verify Network connectivity. Sometime it will be used in indirect ICMP flooding, also known as smurfing resulting DoS (Denial of Service) attack. To prevent Linux server from such attack we need to modify some kernel parameters. Add following kernel parameters to /etc/sysctl.conf file.

net.ipv4.icmp_echo_ignore_all = 1
net.ipv4.icmp_echo_ignore_broadcasts = 1

Execute sysctl to configure kernel parameters at runtime.

sysctl -p

Implement denyhost to Ban Malicious IP Addresses

Configure denyhost on Linux

Avoid Using FTP, Telnet, And Rlogin / Rsh Services

Verify these services are not running with ps ax command. If any of the services are running and are not required then disable it or remove it.

ps ax | grep -i 'ftp\|telnet\|rsh\|rlogin'

Use sudo instead of root access direct.

sudo(su do) permits user to execute some or all command as super users. Though we can directly modify /etc/sudoers file it is recommended to modify sudo settings with visudo command. 

Alias represent the groups and we have following alias. Host Alias specifies the host names the cmd_alias is valid for. Unless you are sharing a sudoers file among different hosts, this alias does not comes in proper use. Use ALL or  hostname of the server or IP address of the server where sudoers file is located if sudoers file is not shared.

Host Aliases (Also known as host list)

Host_Alias DCLAN = 192.168.0.1/28: DBSERVERS = 192.168.1.250, mylinux

User Aliases (Also known as user list)

User_Alias ADMINS = dilli : BCKADMIN = raju, kamal

Command Aliases (Also known as command list)

Cmnd_Alias CMD_ADMIN = /sbin/poweroff : CMD_BCKADMIN = /bin/rsync : CMD_ORCLBCK = /home/oracle/rman

Runas_Alias (Also known as Operator list)

Runas_Alias RLIST1 = oracle

tag list

Tag_Spec ::= (NOPASSWD: | PASSWD: | NOEXEC: | EXEC: |

                   SETENV: | NOSETENV: | LOG_INPUT: | NOLOG_INPUT: |

                   LOG_OUTPUT: | NOLOG_OUTPUT:)

User Specifications are where the sudoers file sets who can run what as who.
syntax:

ADMINS ALL=(ALL) NOPASSWD:CMD_ADMIN

All user listed on the ADMINS User_Alias are allowed to execute command listed on CMD_ADMIN alias on any server with privileges of any user. In addition to that password will not be prompted

when executing the command listed on the command alias CMD_ADMIN.

sudo /sbin/poweroff

User dilli can execute sudo /sbin/poweroff without password prompt.

BCKADMIN DBSERVERS=(RLIST1) PASSWD:CMD_ORCLBCK

User listed on User Alias BCKADMIN that is raju,kamal are allowed to execute command listed on CMD_ORCLBCK command alias with password prompt for 

hosts defined on the host alias DBA. Users are allowed to run command as user oracle only. Host configuration works if the sudoers file is shared among the servers. Otherwise ALL or hostname of the server where sudoers are applied can be used.

sudo  /home/oracle/rman

This command failed with user raju is not allowed to execute command as user root. 

In this case we have to use option -u and specify the user.
sudo -u oracle /home/oracle/rman

Read More