Oracle Advanced Security, client access control, and SSH tunnelling

Contents

Overview

The Oracle Advanced Security database option helps customers address security, privacy, and regulatory compliance requirements. Oracle Advanced Security provides industry standards-based data privacy, integrity, authentication, single sign-on, and access authorisation in a variety of ways. For example, you can configure either Oracle Net native encryption or Secure Sockets Layer (SSL) for data privacy. Oracle Advanced Security also provides the choice of several strong authentication methods, including Kerberos, Remote Authentication Dial-In User Service (RADIUS), smart cards, and digital certificates.

Oracle Advanced Security is only available to products that interface with Oracle Net Services, such as the Easysoft Oracle ODBC driver. Drivers that do not interface with Oracle Net Services (because they do not use Oracle Client software) are unable to use this option.

This article describes some of the network encryption and Oracle Advanced Security features that are available to Easysoft Oracle ODBC driver users. In addition, the article describes the Oracle Net node validation feature that lets you restrict access to the Oracle listener by client IP address and how to use Secure Shell (SSH) tunnelling as an alternative way of securing Oracle network traffic.

Oracle Net native encryption

Oracle Net native encryption protects the confidentiality of Oracle data as it is transmitted across the network. Encrypting Oracle network traffic safeguards sensitive data such as social security numbers, credit card numbers, and other personally identifiable information against packet sniffing. Packet sniffing is where an attacker tries to capture unencrypted data by using a network sniffer. This sniffing takes place without the knowledge of either the client machine or database server.

To illustrate how Oracle Net native encryption safeguards data privacy, we used a packet sniffer to capture both unencrypted and encrypted data as it was transmitted across the network. The following extract shows some unencrypted Oracle Net traffic, retrieved from an Oracle XE database by using unixODBC's isql with the Easysoft Oracle ODBC driver:

06/27-10:22:49.978242 0:3:FF:3A:42:A3 -> 0:11:11:38:42:A3 type:0x800
len:0x122 192.168.0.137:4836 -> 192.168.0.234:1521 TCP TTL:64 TOS:0x0
16 FB 08 08 A4 13 09 08 00 00 00 00 3F 73 65 6C  ............?sel
65 63 74 20 46 49 52 53 54 5F 4E 41 4D 45 2C 20  ect FIRST_NAME, 
4C 41 53 54 5F 4E 41 4D 45 2C 20 4E 41 54 49 4F  LAST_NAME, NATIO
4E 41 4C 5F 49 44 5F 4E 55 4D 42 45 52 20 66 72  NAL_ID_NUMBER fr
6F 6D 20 65 6D 70 6C 6F 79 65 65 73 01 00 00 00  om EMPLOYEES....

0A 00 00 00 00 00 00 00 00 00 00 00 07 06 53 74  ..............St
65 76 65 6E 04 4B 69 6E 67 08 31 34 34 31 37 38  even.King.144178
30 37 15 03 00 07 07 05 4E 65 65 6E 61 07 4B 6F  07......Neena.Ko
63 68 68 61 72 09 32 35 33 30 32 32 38 37 36 15  chhar.253022876.
03 00 07 07 03 4C 65 78 07 44 65 20 48 61 61 6E  .....Lex.De Haan
09 35 30 39 36 34 37 31 37 34 15 03 00 07 07 09  .509647174......
41 6C 65 78 61 6E 64 65 72 06 48 75 6E 6F 6C 64  Alexander.Hunold
09 31 31 32 34 35 37 38 39 31 15 03 00 07 07 05  .112457891......

The names and social security numbers of these employee records have been captured in plain text by the sniffer.

We then activated Oracle Net encryption on the client machine and database server. To do this, we created sqlnet.ora on the client machine and added these lines to the file:

# Activate encryption. For a complete list of supported encryption
# algorithms, refer to the Oracle Database Advanced Security
# Administrator's Guide.
SQLNET.ENCRYPTION_TYPES_CLIENT = RC4_256
SQLNET.ENCRYPTION_CLIENT = required

Because we were using the Easysoft Oracle ODBC driver with the Instant Client, we used the TNS_ADMIN environment variable to point to the directory where sqlnet.ora was located.

These lines were added to sqlnet.ora on the database server:

SQLNET.ENCRYPTION_TYPES_SERVER = RC4_256
SQLNET.ENCRYPTION_SERVER = required

Finally, we connected to the database and retrieved the same data. No additional configuration was necessary. Because the Easysoft Oracle ODBC driver uses the Oracle client libraries, Oracle Net features such as encryption are automatically available to the driver.

This extract shows how activating Oracle Net encryption protects the privacy of the data:

06/27-10:33:19.188351 0:3:FF:3A:42:A3 -> 0:11:11:38:42:A3 type:0x800
len:0x124 192.168.0.137:1127 -> 192.168.0.234:1521 TCP TTL:64 TOS:0x0
00 E2 00 00 06 00 00 00 00 00 46 1F D6 42 23 46  ..........F..B#F
37 98 C1 E5 79 F0 4F 2E BD BE E4 7C F6 4E 59 E0  7...y.O....|.NY.
0D 36 F9 29 6E A7 B1 74 04 A1 43 7F B0 42 42 74  .6.)n..t..C..BBt
44 D1 EB BF 5D A5 A0 C4 60 17 9A C4 6D 40 22 24  D...]...`...m@"$
C2 83 BC 75 1D 512 ED A5 51 3C 1C A2 24 AF DA A7  ...u....Q<..$...
99 AB F3 EA 4E 41 2D 65 03 1E CC 74 4E FC 1A 7A  ....NA-e...tN..z
23 31 9E 82 E3 E6 D3 6C 22 6E E4 C3 17 54 95 F3  #1.....l"n...T..

01 52 00 00 06 00 00 00 00 00 41 D1 37 08 36 43  .R........A.7.6C
BA 5D 12 01 2D 39 34 92 76 CD AB 32 E8 DF A9 FF  .]..-94.v..2....
52 69 2A A1 4C 17 DF 32 98 07 C1 8C 30 4D 48 CC  Ri*.L..2....0MH.
86 AF 0B 2D 6C C1 C6 05 1D 09 5F 1D ED D1 E8 16  ...-l....._.....
40 A1 D9 65 6A 0F 05 29 F0 B2 B4 91 01 FF BB 13  @..ej..)........
A3 85 C2 24 D5 DE 1C 09 3D 12 E8 0C 09 A8 AF 20  ...$....=...... 
12 1B 1D 68 88 2F EF E7 E2 F1 A1 91 3D 20 90 6A  ...h./......= .j
60 57 C9 03 70 17 5E 46 66 33 4E 10 C7 BB 97 6E  `W..p.^Ff3N....n
D4 F0 36 43 39 39 69 3C DD 71 B4 3F 94 0A 5C EE  ..6C99i<.q.?..\.
E5 CB 6B DD 27 1D 86 41 20 02 AB FC D0 1F 89 7C  ..k.'..A ......|
02 D9 11 90 C8 DA 55 72 5C 2F B8 95 D8 12 5C 01  ......Ur\/....\.

The confidential employee data is now unreadable.

Oracle Net encryption provides the following encryption algorithms to protect information: Rivest Cipher 4 (RC4), Data Encryption Standard (DES), Triple-DES, and Advanced Encryption Standard (AES). An encryption algorithm transforms data into a form that cannot be deciphered without a decryption key. The native network encryption algorithms provide varying levels of security and performance.

Note that Oracle Net always encrypts passwords before sending them across the network even if encryption is not otherwise activated.

Oracle Advanced Security

This section lists some of the Oracle Advanced Security features that are available to Easysoft Oracle ODBC driver users:

Client access control

Oracle Net valid node checking lets you allow or deny access to an Oracle database server based on the IP address (or host name) of the client machine making the request. You can control access to the database server by specifying either which machines are allowed access or which machines are denied access. The Oracle listener checks the IP address or host name of the client machine and, based on the rules you define, decides to allow or deny the request.

Because node validation works at the listener level, a potential attacker does not get as far as the database, making Denial of Service (DoS) attacks more difficult. DoS is any form of attack on a system that tries to prevent legitimate users from accessing it. The most common form of attack is to overwhelm a server with connection requests that cannot be completed. This causes the server to become so busy attempting to respond to the attack that it ignores legitimate requests for connections.

To use the node validation feature, set the following sqlnet.ora (protocol.ora for Oracle 8) parameters on the database server:

# Enable node validation
tcp.validnode_checking = YES

# Prevent these client IP addresses from
# making connections to the Oracle listener.
tcp.excluded_nodes = {list of IP addresses}

# Allow these IP addresses to connect.
tcp.invited_nodes = {list of IP addresses}

Attempts to connect to the Oracle listener from the excluded IP addresses are blocked. For example:

/usr/local/easysoft/unixodbc/bin/isql -v ORACLE
[S1000][unixODBC][Easysoft][Oracle]ORA-12537: TNS:connection closed

Protecting Oracle network traffic with SSH tunnelling

SSH provides a secure encrypted communications channel between two machines over an insecure network. A client machine can connect to an Oracle database over a secure SSH connection by using port forwarding. SSH port forwarding provides another way to protect data privacy through encryption and safeguard against data interception and alteration.

Port forwarding is sometimes called tunnelling because the SSH connection provides a secure "tunnel" through which another TCP/IP connection may pass. Port forwarding works by mapping a local port on the client to a remote port on the server. All traffic coming to the local port is forwarded to the remote port. When you use SSH port forwarding with the Easysoft Oracle ODBC driver and Oracle client, the SSH client intercepts all Oracle Net traffic coming from the client machine, encrypts it, and transmits it to the SSH daemon running on the Oracle database server. The SSH daemon decrypts the Oracle Net traffic and then forwards the data to the Oracle listener.

Oracle Net (formerly known as Net8) is a software layer present on both the client machine and Oracle database server that is responsible for establishing and maintaining the connection between the client application and server. When Oracle Net is used to communicate with the Oracle database server on a TCP/IP network, Oracle Net traffic can be encrypted by passing it through an SSH tunnel.

Encrypting Oracle Net traffic protects sensitive data such as social security and credit card numbers against packet sniffing.

Creating an SSH tunnel between a client machine and an Oracle database server requires an SSH client to be present on the client machine and an SSH server to be present on the database server. No configuration is necessary on the database server.

Accessing an Oracle database over an SSH connection

The following example shows how to access an Oracle database over an SSH connection:

  1. Do one of the following:
    • If you are using the OCI version of the Oracle ODBC driver (which uses the client libraries), create this data source in /etc/odbc.ini:
      [ORACLE_SSH]
      Driver          = ORACLE
      Database        = //localhost:9901/mydb
      User            = mydbuser
      Password        = mydbpassword

      The Database attribute specifies port 9901 on the client machine rather than the usual port 1521 on the database server.

    • If you are using the WP version of the Oracle ODBC driver (which does not use the client libraries), create this data source in /etc/odbc.ini:
      [ORACLE_SSH]
      Driver          = Easysoft ODBC-Oracle WP
      Server          = localhost
      Port            = 9901
      SID             = mydb
      User            = mydbuser
      Password        = mydbpassword

      The Port attribute specifies port 9901 on the client machine rather than the usual port 1521 on the database server.

  2. Start the SSH server on the database server.
  3. Before connecting to the database, set up port forwarding by initiating an SSH connection on the client machine:
    ssh -L 9901:mydbhost:1521 mydbhost

    The command opens an SSH connection to mydbhost and also securely forwards all network traffic from port 9901 on the client machine to port 1521 on mydbhost.

    Note that the port number on the client machine does not have to be 9901. You can use any port you want, as long as it does not interfere with other services. If you try to use a privileged port, you will need to establish the SSH connection as the root user.

  4. Use isql to test the new data source:
    cd /usr/local/easysoft/unixodbc/bin
    ./isql -v ORACLE_SSH

In addition to being encrypted, data passed through an SSH tunnel is automatically integrity checked and authenticated by using SSH credentials.

Integrity is the guarantee that data has not been tampered with during transmission — the data received therefore is the same as the data that was sent. The underlying transport of SSH, TCP/IP, does have integrity checking to detect alteration that results from network problems. However, TCP/IP's integrity checking is ineffective against deliberate tampering and can be fooled by an attacker. SSH uses cryptographic integrity checking to verify that transmitted data has not been altered.

SSH integrity checking protects against data modification and data replay attacks.

SSH uses cryptographic hash algorithms based on MD5 and SHA-1 for integrity checking: well-known and widely trusted algorithms. (Note that although SSH protocol version 2 (SSH-2) uses MD5 and SHA-1 for integrity, the previous version, SSH-1, uses a comparatively weak method: a 32-bit cyclic redundancy check (CRC-32) on the unencrypted data in each packet.)

When the SSH tunnel is created, the SSH client verifies the identity of the SSH server (server authentication), and the server verifies the identity of the user requesting access (user authentication). Server authentication ensures that the SSH server is genuine and not an impostor masquerading as the SSH server. This protects against attempts by an attacker to redirect the network connection to a different machine.

References