Restricting and securing your database network – part 2

Posted on , updated on by Yannick Jaquier
Oracle ,

Table of contents

Preamble

Encrypting network traffic and controlling what access to what is not already a standard implemented everywhere but it starts to be more and more a classical request from security teams and audit companies. Starting with Oracle 10gR2 the Oracle Advanced Security enterprise edition option is no longer required to encrypt and authenticate network:

Network encryption (native network encryption and SSL/TLS) and strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of Oracle Advanced Security and are available in all licensed editions of all supported releases of the Oracle database.

In below my database server is server1.domain.com (192.168.56.101) running Oracle database enterprise edition 12cR1 (12.1.0.2). Server2.domain.com (192.168.56.102) will be my Oracle client (12cR1, 12.1.0.2) and server3.domain.com (192.168.56.103) will be the node I will use to monitor network traffic. All virtual machines are running with Oracle Linux Server release 7.3 64 bits.

You can access to part 1 where we have introduced on how to isolate your database servers from unwanted connections…

Network encryption

The first thing I have asked myself is how to see that information are going in clear between my client and my server ? I have started to use world famous wireshark for the pretty graphical interface (even if product has been tough to configure) but you can also use well known tcpdump for simpler usage and its command line usage nice to copy/paste in a blog. Tcpdump is available in standard Oracle linux repository, so installation is as simple as:

[root@server3 ~]# yum install tcpdump

To be able to monitor traffic between my database server and my client I have been obliged to change promiscuous mode of the Host-only Adapter lan card:

secure_database_network15
secure_database_network15

I create in my database below test table containing confidential sales figures:

SQL> DROP TABLE sales;
 
TABLE dropped.
 
SQL> CREATE TABLE sales
  2  (region VARCHAR2(20),
  3  val NUMBER)
  4  TABLESPACE users;
 
TABLE created.
 
SQL> INSERT INTO sales VALUES('Europe',15587496);
 
1 ROW created.
 
SQL> INSERT INTO sales VALUES('Asia/Pacific',25587425);
 
1 ROW created.
 
SQL> INSERT INTO sales VALUES('US',12584789);
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT * FROM sales;
 
REGION                      VAL
-------------------- ----------
Europe                 15587496
Asia/Pacific           25587425
US                     12584789

If you use tcpdump to monitor traffic you will see something like (I have also edited /etc/services to comment out 1531 port). I monitor traffic between my server (server1.domain.com) and my client (server2.domain.com) using a third node called server3.domain.com:

[root@server3 ~]# tcpdump -A host server2.domain.com and server1.domain.com and port 1531
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on enp0s3, link-type EN10MB (Ethernet), capture size 65535 bytes
17:35:41.181023 IP server2.32988 > server1.1531: Flags [P.], seq 2387244669:2387244970, ack 3577557774, win 460, options [nop,nop,TS val 27739139 ecr 27735720], length 301
E..ac.@.@.....8f..8e.....Jv}.=3.....@......
................................................................................................W.......................................................................................select * from sales....................................................
17:35:41.181749 IP server1.1531 > server2.32988: Flags [P.], seq 1:460, ack 301, win 427, options [nop,nop,TS val 27752932 ecr 27739139], length 459
E...*L@.@.....8e..8f.....=3..Jw.....Y......
..y...D.....................n?.S.K....uxu....%*.......Q.............................i..................REGION..............................................................VAL...................xu...$).......................B............=...................................,...Europe...;Ka....aB..................................................... ...............................6................3......................................................................
17:35:41.182025 IP server2.32988 > server1.1531: Flags [.], ack 460, win 483, options [nop,nop,TS val 27739140 ecr 27752932], length 0
E..4c.@.@.....8f..8e.....Jw..=4.....*......
..D...y.
17:35:41.182346 IP server2.32988 > server1.1531: Flags [P.], seq 301:322, ack 460, win 483, options [nop,nop,TS val 27739141 ecr 27752932], length 21
E..Ic.@.@.....8f..8e.....Jw..=4......\.....
..D...y......................
17:35:41.182798 IP server1.1531 > server2.32988: Flags [P.], seq 460:728, ack 322, win 427, options [nop,nop,TS val 27752933 ecr 27739141], length 268
E..@*M@.@..O..8e..8f.....=4..Jw......M.....
;0Z............{........... ...............................6................3..........................................................{............ORA-01403: no data found
 
17:35:41.222776 IP server2.32988 > server1.1531: Flags [.], ack 728, win 505, options [nop,nop,TS val 27739181 ecr 27752933], length 0
E..4c.@.@.....8f..8e.....Jw..=5.....)C.....
..D-..y.

You might also install world famous Wireshark tool to see this graphically. On my Linux virtual machine installation is as simple as:

[root@server3 ~]# yum install wireshark.x86_64
[root@server3 ~]# yum install wireshark-gnome.x86_64

If you hit the display issue install required fonts (wondering why they are not by default):

[root@server3 ~]# yum install dejavu-sans-fonts.noarch dejavu-serif-fonts.noarch

Using the same capture filter as with tcpdump:

secure_database_network16
secure_database_network16

As you can see the decoding for a dummy hacker like myself is not so obvious and, so far, I have not deeply investigated how to decode the number from ASCII characters. Instead of wasting too much time on this and keep my focus on encrypting feature I have changed the query to:

SQL> SELECT region, TO_CHAR(val) AS val FROM sales;
 
REGION               VAL
-------------------- ----------------------------------------
Europe               15587496
Asia/Pacific         25587425
US                   12584789

Doing the same tcpdump I now get:

[root@server3 ~]# tcpdump -A host server2.domain.com AND server1.domain.com AND port 1531
tcpdump: verbose output suppressed, USE -v OR -vv FOR full protocol DECODE
listening ON enp0s3, link-TYPE EN10MB (Ethernet), capture SIZE 65535 bytes
17:36:08.466986 IP server2.32988 > server1.1531: Flags [P.], seq 2387244991:2387245341, ack 3577558501, win 505, options [nop,nop,TS val 27766425 ecr 27752933], LENGTH 350
E...c.@.@..e..8f..8e.....Jw..=5......T.....
................................................................................................W......................................................................................-SELECT region, TO_CHAR(val) AS val FROM sales....................................................
17:36:08.467847 IP server1.1531 > server2.32988: Flags [P.], seq 1:459, ack 350, win 447, options [nop,nop,TS val 27780218 ecr 27766425], LENGTH 458
E...*N@.@.....8e..8f.....=5..Jy............
.'<.......Q.............................i..................REGION...................(.......................i...(..............VAL...................xu...%     ......................"*............=............................X......Europe.15587496....aB.....................................................................................6................3......................................................................
17:36:08.467960 IP server2.32988 > server1.1531: Flags [.], ack 459, win 528, options [nop,nop,TS val 27766426 ecr 27780218], length 0
E..4c.@.@.....8f..8e.....Jy..=7.....Q......
.......z
17:36:08.468222 IP server2.32988 > server1.1531: Flags [P.], seq 350:371, ack 459, win 528, options [nop,nop,TS val 27766426 ecr 27780218], length 21
E..Ic.@.@.....8f..8e.....Jy..=7............
.......z.....................
17:36:08.468622 IP server1.1531 > server2.32988: Flags [P.], seq 459:729, ack 371, win 447, options [nop,nop,TS val 27780219 ecr 27766426], length 270
E..B*O@.@..K..8e..8f.....=7..Jy2...........
...{..................................................................Asia/Pacific.25587425......US.12584789............{........... ...............................6................3..........................................................{............ORA-01403: no data found
 
17:36:08.508216 IP server2.32988 > server1.1531: Flags [.], ack 729, win 550, options [nop,nop,TS val 27766467 ecr 27780219], length 0
E..4c.@.@.....8f..8e.....Jy2.=8....&O......
.......{

Now the display is a bit more clearer, so much clearer that you can read them black on white in TCP/IP frames. No need to say that someone sniffing your network can grab highly confidential information…

You can define the desired encryption at client and server level with below parameters (default value is ACCEPTED):

  • SQLNET.ENCRYPTION_CLIENT = accepted | rejected | requested | required
  • SQLNET.ENCRYPTION_SERVER = accepted | rejected | requested | required

You define the encryption algorithm using below parameters at client and server level (by default all algorithms are selected):

  • SQLNET.ENCRYPTION_TYPES_SERVER = (3des112, 3des168, aes128, aes192, aes256, des, des40, rc4_40, rc4_56, rc4_128, rc4_256)
  • SQLNET.ENCRYPTION_TYPES_CLIENT = (3des112, 3des168, aes128, aes192, aes256, des, des40, rc4_40, rc4_56, rc4_128, rc4_256)

CLient and server must have a common encryption algorithm to be able to initiate the connection or below error message is thrown:

ERROR:
ORA-12650: No common encryption or data integrity algorithm

Means that in the case of multiple clients connecting to a single server, you can configure only the server and all clients will benefit by default from encryption. Below table extracted from official Oracle documentation explain how negotiation is performed:

Client SettingServer SettingEncryption and Data Negotiation
REJECTEDREJECTEDOFF
ACCEPTEDREJECTEDOFF
REQUESTEDREJECTEDOFF
REQUIREDREJECTEDConnection fails
REJECTEDACCEPTEDOFF
ACCEPTEDACCEPTEDOFF (This value defaults to OFF. Cryptography and data integrity are not enabled until the user changes this parameter by using Oracle Net Manager or by modifying the sqlnet.ora file.)>
REQUESTEDACCEPTEDON
REQUIREDACCEPTEDON
REJECTEDREQUESTEDOFF
ACCEPTEDREQUESTEDON
REQUESTEDREQUESTEDON
REQUIREDREQUESTEDON
REJECTEDREQUIREDConnection fails
ACCEPTEDREQUIREDON
REQUESTEDREQUIREDON
REQUIREDREQUIREDON

So in sqlnet.ora file of your database server you only have to set. I have chosen REQUIRED value but REQUESTED would do the job. Here it means that clients that have specifically set REJECTED will not be able to connect:

SQLNET.ENCRYPTION_SERVER = required
 
SQLNET.ENCRYPTION_TYPES_SERVER= (AES256)

You can also use netmgr but the tool will force you to set deprecated SQLNET.CRYPTO_SEED parameter:

secure_database_network17
secure_database_network17

SQLNET.CRYPTO_SEED
This parameter was used to seed a random number generator for Oracle Advanced Security. Starting with Oracle Database 10g, Oracle Advanced Security uses a random number generator that does not to require a user-supplied seed value. Last Supported Release 9.2 (9iR2).

If you set SQLNET.ENCRYPTION_CLIENT = REJECTED in client sqlnet.ora then your client will not be able to connect throwing below error message:

ERROR:
ORA-12660: Encryption or crypto-checksumming parameters incompatible

If you sniff again the network between your client and you newly configured encrypted database server you can see that traffic is much less readable:

[root@server3 ~]# tcpdump -A host server2.domain.com and server1.domain.com and port 1531
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on enp0s3, link-type EN10MB (Ethernet), capture size 65535 bytes
18:01:19.041724 IP server2.32991 > server1.1531: Flags [P.], seq 1900443499:1900443831, ack 372174552, win 488, options [nop,nop,TS val 29277000 ecr 29283750], length 332
E.....@.@.G...8f..8e....qFwk........r1.....
...H.......L. ..........:.D......S..)`0/~..cc...........;.>.x,...,..6.!.&.u.d...X...b....n.()y..F.k..%o..Yc......<Z...Zj......W....Ve..tT.<eB#b7u.A`6..T.Z)...)...p]yX1.{..p.|.O..(........d..Ezb...sX.W.?..%W...r......5lr..zg.$........9.9...M3./'......D...V......ceHg...?+.kZ._......n..i;._.(.5.1.....>....0.H        ReA~...^.+......p.W
E....Z...N..
18:01:19.042807 IP server1.1531 > server2.32991: Flags [P.], seq 1:461, ack 332, win 436, options [nop,nop,TS val 29290793 ecr 29277000], length 460
E....l@.@.?p..8e..8f........qFx.....h......
8.K..2.Q..+..|..v3...g.!{G]...i....4.cx....f..$..09k.=.........cWagr.S..'.....'y.@.;.....J..{..[.~.1.D....<.],...N.....C...qC.O.......I,.!...Y.ds..]...>.....Z.{HE..+.5hy.Mr.>........L...?k.W..-9.O.N.A....9S.L.C..c...]..Tn...   ....6..K...CA......%. .:F&..8&...f:o"Uc9.s...Z.... .Kc...].     .p.N...VLh..3V.wDaP\....G}3..V.c...>....)..n..O./.E...He]..pM5J.........$..S...J...@C....2.\.W...A/+..Z.z]+....
18:01:19.043039 IP server2.32991 > server1.1531: Flags [.], ack 461, win 510, options [nop,nop,TS val 29277001 ecr 29290793], length 0
E..4..@.@.I...8f..8e....qFx.........[......
...I...)
18:01:19.043361 IP server2.32991 > server1.1531: Flags [P.], seq 332:360, ack 461, win 510, options [nop,nop,TS val 29277002 ecr 29290793], length 28
E..P..@.@.H...8f..8e....qFx................
...J...)..... ....h...-..4t.[..b#...
18:01:19.043849 IP server1.1531 > server2.32991: Flags [P.], seq 461:745, ack 360, win 436, options [nop,nop,TS val 29290794 ecr 29277002], length 284
E..P.m@.@.@...8e..8f........qFx.....$......
...*...J..... .....d..0NM.2......3...$..e)ss"..,"B_t.9.I:.&..,.....v.m.Q............V.g.j..K0....@.^.&&L....:,Lz.,.......F..NF..vp.7....?=$D...@.       .m......Z.su9K.v......".........p..........y]^O.[}E.../...\v..X...-...V/.........q.(......'....c..9<...8.X-....f=/......H............('.P....4..
18:01:19.083335 IP server2.32991 > server1.1531: Flags [.], ack 745, win 533, options [nop,nop,TS val 29277042 ecr 29290794], length 0
E..4..@.@.I...8f..8e....qFx.........Y......
...r...*

I have also been asked on the important performance penalty of such security measure. In Oracle official documentation the only reference to performance is a quote about the biger impact of 3DES algorithm:

3DES provides a high degree of message security, but with a performance penalty. The magnitude of the performance penalty depends on the speed of the processor performing the encryption. 3DES typically takes three times as long to encrypt a data block when compared to the standard DES algorithm.

Except this Oracle quote not much about it on except one article from OraFAQ done with Oracle 10gR2. MD5 and SHA-1 columns is a club with Network Integrity parameters that we see in next chapter:

AlgorithmNoneMD5SHA-1
Time%NoneTime%NoneTime%None
None79.6 s 80.5 s101%82.4 s104%
DES104.7 s132%107.1 s135%108.2 s136%
3DES168151.8 s191%153.9 s193%155.6 s196%
AES12888.8 s112%90.5 s114%92.1 s116%
AES25691.8 s115%93.5 s117%94.2 s118%
RC4_12881.6 s103%82.5 s104%85.0 s107%
RC4_25681.7 s103%82.8 s104%85.0 s107%

I would say that nowadays security being a main concern I would personally not bother that much with performance impact of this network encryption… But still if you client and database server exchange a lot of information plan performance test before rushing to production… We can, at least, see that 3DES168 is almost doubling response time…

Network integrity

Network integrity aims at ensuring that data has not been modified between the client and the server (data modification attack). It also aims at ensuring the same transaction will not be send multiple time to compromise your system (data replay attack). Testing it is not as easy as with network encryption unfortunately…

To activate it you set below parameters at client or server level, the possible value at client and server work the same as with network encryption (default value is ACCEPTED):

  • SQLNET.CRYPTO_CHECKSUM_SERVER = accepted | rejected | requested | required
  • SQLNET.CRYPTO_CHECKSUM_CLIENT = accepted | rejected | requested | required

You then choose checksum algorithm, client and server must have one in common to work (by default all algorithms are chosen):

  • SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (MD5, SHA1, SHA256, SHA384, SHA512)
  • SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (MD5, SHA1, SHA256, SHA384, SHA512)

I have chosen to activate it, same as before, only at database server level. By default all is ready on client to proceed:

SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA1)
 
SQLNET.CRYPTO_CHECKSUM_SERVER = required

This can also be done with netmgr:

secure_database_network18
secure_database_network18

Here below the tcpdump result with encryption, we simply see that all TCP/IP frames are a bit longer than the initial output making me think that checksum (network integrity) has been added:

[root@server3 ~]# tcpdump -A host server2.domain.com and server1.domain.com and port 1531
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on enp0s3, link-type EN10MB (Ethernet), capture size 65535 bytes
10:36:50.366041 IP server2.32995 > server1.1531: Flags [P.], seq 2461874204:2461874552, ack 3658538857, win 488, options [nop,nop,TS val 89008324 ecr 89012257], length 348
E...".@.@.$r..8f..8e......8....i...........
.................................................................................................L.....................................................................................-select region, to_char(val) as val from sales....................................................."...yW.T...e....{...
10:36:50.367147 IP server1.1531 > server2.32995: Flags [P.], seq 1:480, ack 348, win 438, options [nop,nop,TS val 89022117 ecr 89008324], length 479
E....<@.@.T...8e..8f.......i..9x....a^.....
........."*.........................................X......Europe.15587496.....C.....................................................................................6.........................................................................................F.#..]...yA.F..x@c.
10:36:50.367404 IP server2.32995 > server1.1531: Flags [.], ack 480, win 510, options [nop,nop,TS val 89008326 ecr 89022117], length 0
E..4".@.@.%...8f..8e......9x...H.....-.....
.N(..N^.
10:36:50.367733 IP server2.32995 > server1.1531: Flags [P.], seq 348:390, ack 480, win 510, options [nop,nop,TS val 89008326 ecr 89022117], length 42
E..^".@.@.%...8f..8e......9x...H...........
.N(..N^....*. ....................&..=.!t.)M.%.my.
10:36:50.368078 IP server1.1531 > server2.32995: Flags [P.], seq 480:771, ack 390, win 438, options [nop,nop,TS val 89022118 ecr 89008326], length 291
E..W.=@.@.UG..8e..8f.......H..9......;.....
.N^..N(....#. ........................................................Asia/Pacific.25587425......US.12584789............{........... ...............................6...........................................................................{............ORA-01403: no data found
J......'l...O..x!".+.
10:36:50.408468 IP server2.32995 > server1.1531: Flags [.], ack 771, win 533, options [nop,nop,TS val 89008367 ecr 89022118], length 0
E..4".@.@.%...8f..8e......9....k...........
.N(..N^.

Network strong authentication

By default database users are authenticated with their passwords (whether it is through network or with Operating System for OS authenticated users). If you want to move a step forward in security you might wish to implement what is called strong authentication using third party authentication services (Kerberos and Radius) or SSL with digital certificates.

As just written there are multiple methods to implement strong authentication:

  • Kerberos
  • Remote Authentication Dial-In User Service (RADIUS)
  • Secure Sockets Layer

The authentication method I plan to test is Secure Sockets Layer. It works with certificates stored in Oracle wallet to authenticate clients and server. I will obviously not worked with certificate signed by an authorized authority (Comodo, Verizon,…) but with self signed certificates that must not obviously be used in production.

My Oracle Support (MOS) has plenty of documentation on how to do it, I tend to say maybe too much as I had to mix multiple notes to reach a working environment.

With orapki

I wanted to use graphical Oracle Wallet Manager (OWM) to do it but after many unsuccessful tries I have realized that this tool cannot be used in the case of self signed certificates. Or maybe my knowledge is too poor…

The first time you execute OWM:

secure_database_network19
secure_database_network19

You will get a popup asking to create the default directory, I have chosen to do it and use this directory to store the wallet I will finally create with command line tools. This way it is more convenient to graphically control all is going well:

secure_database_network20
secure_database_network20

The tool to use is orapki. Start by creating a wallet. The wallet is created with auto_login option to avoid being obliged to supply password to use it (password will be asked only in case of modifications):

[oracle@server1 ~]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/owm/wallets/oracle
[oracle@server1 oracle]$ ll
total 0
[oracle@server1 oracle]$ orapki wallet create -wallet . -auto_login -pwd secure_password
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
[oracle@server1 oracle]$ ll
total 8
-rw------- 1 oracle dba 120 Feb  9 12:04 cwallet.sso
-rw-rw-rw- 1 oracle dba   0 Feb  9 12:04 cwallet.sso.lck
-rw------- 1 oracle dba  75 Feb  9 12:04 ewallet.p12
-rw-rw-rw- 1 oracle dba   0 Feb  9 12:04 ewallet.p12.lck

Add a self-signed certificate to your wallet:

[oracle@server1 oracle]$ orapki wallet add -wallet . -dn 'CN=server1.domain.com,O=MyCompany,L=Geneva,C=CH' -keysize 2048 -self_signed -validity 365 -pwd secure_password
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
[oracle@server1 oracle]$ ll
total 8
-rw------- 1 oracle dba 4085 Feb  9 12:06 cwallet.sso
-rw-rw-rw- 1 oracle dba    0 Feb  9 12:04 cwallet.sso.lck
-rw------- 1 oracle dba 4040 Feb  9 12:06 ewallet.p12
-rw-rw-rw- 1 oracle dba    0 Feb  9 12:04 ewallet.p12.lck
[oracle@server1 oracle]$ orapki wallet display -wallet .
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
Requested Certificates:
User Certificates:
Subject:        CN=server1.domain.com,O=MyCompany,L=Geneva,C=CH
Trusted Certificates:
Subject:        CN=server1.domain.com,O=MyCompany,L=Geneva,C=CH

Export the server certificate to import it in your clients’ wallet:

[oracle@server1 oracle]$ orapki wallet export -wallet . -dn 'CN=server1.domain.com,O=MyCompany,L=Geneva,C=CH' -cert server_ca.cert
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
[oracle@server1 oracle]$ ll
total 12
-rw------- 1 oracle dba 4085 Feb  9 12:06 cwallet.sso
-rw-rw-rw- 1 oracle dba    0 Feb  9 12:04 cwallet.sso.lck
-rw------- 1 oracle dba 4040 Feb  9 12:06 ewallet.p12
-rw-rw-rw- 1 oracle dba    0 Feb  9 12:04 ewallet.p12.lck
-rw------- 1 oracle dba 1123 Feb  9 15:13 server_ca.cert

If you control graphically what has been done you get:

secure_database_network21
secure_database_network21

Do almost the same thing on your client. Start by creating an auto login wallet:

[oracle@server2 ~]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/owm/wallets/oracle
[oracle@server2 oracle]$ ll
total 0
[oracle@server2 oracle]$ orapki wallet create -wallet . -auto_login -pwd secure_password
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
[oracle@server2 oracle]$ ll
total 8
-rw------- 1 oracle dba 120 Feb  9 15:20 cwallet.sso
-rw-rw-rw- 1 oracle dba   0 Feb  9 15:20 cwallet.sso.lck
-rw------- 1 oracle dba  75 Feb  9 15:20 ewallet.p12
-rw-rw-rw- 1 oracle dba   0 Feb  9 15:20 ewallet.p12.lck

Add a self signed certificate to your wallet (here I create a certificate for an user I plan to call yjaquier):

[oracle@server2 oracle]$ orapki wallet add -wallet . -dn 'CN=yjaquier,O=MyCompany,L=Geneva,C=CH' -keysize 2048 -self_signed -validity 365 -pwd secure_password
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Export your client certificate for server wallet import:

[oracle@server2 oracle]$ orapki wallet export -wallet . -dn 'CN=yjaquier,O=MyCompany,L=Geneva,C=CH' -cert client_ca.cert
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
[oracle@server2 oracle]$ ll
total 12
-rw------- 1 oracle dba 1095 Feb  9 15:21 client_ca.cert
-rw------- 1 oracle dba 4037 Feb  9 15:20 cwallet.sso
-rw-rw-rw- 1 oracle dba    0 Feb  9 15:20 cwallet.sso.lck
-rw------- 1 oracle dba 3992 Feb  9 15:20 ewallet.p12
-rw-rw-rw- 1 oracle dba    0 Feb  9 15:20 ewallet.p12.lck

Then transfer on client with ssh server certificated and vice-versa. Then on client import server certificate as a trusted one:

[oracle@server2 oracle]$ orapki wallet add -wallet . -trusted_cert -cert server_ca.cert -pwd secure_password
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Import on server client certificate as a trusted one:

[oracle@server1 oracle]$ orapki wallet add -wallet . -trusted_cert -cert client_ca.cert -pwd secure_password
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

At the end you should get on server:

secure_database_network22
secure_database_network22

Now comes the Oracle network configuration. In listener.ora file I have added the listening on secure TCP port (tcps) and wallet location:

LISTENER_ORCL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server1.domain.com)(PORT = 1531))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = server1.domain.com)(PORT = 1532))
    )
  )
 
WALLET_LOCATION =
  (SOURCE=
    (METHOD=file)
    (METHOD_DATA=
        (DIRECTORY=/u01/app/oracle/product/12.1.0/dbhome_1/owm/wallets/oracle)
     )
   )

in sqlnet.ora of server I also add wallet location:

WALLET_LOCATION =
  (SOURCE=
    (METHOD=file)
    (METHOD_DATA=
        (DIRECTORY=/u01/app/oracle/product/12.1.0/dbhome_1/owm/wallets/oracle)
     )
   )

I had to stop and restart the listener to make it working and you can control it is activated with:

[oracle@server1 admin]$ lsnrctl status listener_orcl
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-MAR-2017 15:08:48
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.domain.com)(PORT=1531)))
STATUS of the LISTENER
------------------------
Alias                     listener_orcl
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                09-MAR-2017 12:52:53
Uptime                    0 days 2 hr. 15 min. 55 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/server1/listener_orcl/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1531)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=server1)(PORT=1532)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

Few parameters must be set at instance level:

SQL> show parameter authent
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$
remote_os_authent                    BOOLEAN     FALSE
SQL> ALTER SYSTEM SET os_authent_prefix='' scope=spfile;
 
SYSTEM altered.
 
SQL> show parameter authent
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$
remote_os_authent                    BOOLEAN     FALSE
SQL> shutdown IMMEDIATE
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total SYSTEM Global Area 1073741824 bytes
Fixed SIZE                  2932632 bytes
Variable SIZE             796917864 bytes
DATABASE Buffers          268435456 bytes
Redo Buffers                5455872 bytes
DATABASE mounted.
DATABASE opened.
SQL> show parameter authent
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string
remote_os_authent                    BOOLEAN     FALSE
 
SQL> ALTER pluggable DATABASE pdb1 OPEN;
 
Pluggable DATABASE altered.

I also create the test user (yaquier, same as in client certificate) for network strong authentication:

SQL> alter session set container=pdb1;
 
Session altered.
 
SQL> create user yjaquier identified externally as 'CN=yjaquier,O=MyCompany,L=Geneva,C=CH';
 
User created.
 
SQL> grant dba to yjaquier;
 
Grant succeeded.

In sqlnet.ora of client I also add wallet location:

WALLET_LOCATION =
  (SOURCE=
    (METHOD=file)
    (METHOD_DATA=
        (DIRECTORY=/u01/app/oracle/product/12.1.0/dbhome_1/owm/wallets/oracle)
     )
   )

If you control with netmgr you get this on server:

secure_database_network23
secure_database_network23

And on client:

secure_database_network24
secure_database_network24

Remark
The documentation on MOS is asking you to set plenty of other parameters but for most of them the default value is more than sufficient. In fact I have been able to make the feature working without setting them.

Then you can test your listener is answering on both secure and unsecure ports:

[oracle@server2 ~]$ tnsping "(ADDRESS=(PROTOCOL=tcp)(HOST=server1.domain.com)(PORT=1531))"
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 09-MAR-2017 15:32:25
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Attempting to contact (ADDRESS=(PROTOCOL=tcp)(HOST=server1.domain.com)(PORT=1531))
OK (0 msec)
[oracle@server2 ~]$ tnsping "(ADDRESS=(PROTOCOL=tcps)(HOST=server1.domain.com)(PORT=1532))"
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 09-MAR-2017 15:32:30
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Attempting to contact (ADDRESS=(PROTOCOL=tcps)(HOST=server1.domain.com)(PORT=1532))
OK (30 msec)

To ease connection I create in tnsnames.ora of my clients the below entries (with my configuration it is still possible to connect on classic TCP port while at the end you should remove it):

PDB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server1.domain.com)(PORT = 1531))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pdb1)
    )
  )
 
PDB1S =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = server1.domain.com)(PORT = 1532))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pdb1)
    )
  )
[oracle@server2 admin]$ sqlplus /@pdb1s
 
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 9 15:30:21 2017
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
 
SQL> show user
USER is "YJAQUIER"
SQL> select sys_context('userenv','network_protocol') from dual;
 
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps

It is anyway still possible to connect in standard way supplying the password (not on command line obviously !!):

[oracle@server2 ~]$ sqlplus test/secure_password@pdb1s
 
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 9 15:42:45 2017
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
 
SQL> select sys_context('userenv','network_protocol') from dual;
 
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps

If you test this from a third client where certificate has not been inserted in server wallet you should be unable to connect:

[oracle@server3 ~]$ tnsping "(ADDRESS=(PROTOCOL=tcp)(HOST=server1.domain.com)(PORT=1531))"
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 09-MAR-2017 15:32:44
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Attempting to contact (ADDRESS=(PROTOCOL=tcp)(HOST=server1.domain.com)(PORT=1531))
OK (50 msec)
[oracle@server3 ~]$ tnsping "(ADDRESS=(PROTOCOL=tcps)(HOST=server1.domain.com)(PORT=1532))"
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 09-MAR-2017 15:32:50
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Attempting to contact (ADDRESS=(PROTOCOL=tcps)(HOST=server1.domain.com)(PORT=1532))
TNS-12560: TNS:protocol adapter error
[oracle@server3 ~]$ sqlplus test/secure_password@pdb1s
 
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 9 16:04:06 2017
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
ERROR:
ORA-28759: failure to open file
 
 
Enter user-name:

The above error is the one you will get if the wallet is not configured, if you configure it (without server certificate) you would get:

[oracle@server3 admin]$ sqlplus test/secure_password@pdb1s
 
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 9 16:57:51 2017
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
ERROR:
ORA-29024: Certificate validation failure
 
 
Enter user-name:

With openssl

Instead of using orapki I have tried to use openssl command to generate certificates and to signed them but it did not worked as expected…

First I have asked myself if I should use DSA or RSA and found the answer in Archlinux:

OpenSSH 7.0 deprecated and disabled support for DSA keys due to discovered vulnerabilities, therefore the choice of cryptosystem lies within RSA or one of the two types of ECC.

I have started be generating a root RSA 2048 bits key (I’m working in ~oracle/openssl directory):

[oracle@server1 openssl]$ openssl genrsa -out root.key 2048
Generating RSA private key, 2048 bit long modulus
...............+++
......................................+++
e is 65537 (0x10001)
[oracle@server1 openssl]$ ll
total 4
-rw-r--r-- 1 oracle dba 1766 Feb  9 11:46 root.key

Self-signed the root certificate with:

[oracle@server1 openssl]$ openssl req -x509 -new -nodes -key root.key -sha256 -days 1024 -out self-root.pem
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CH
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:Geneva
Organization Name (eg, company) [Default Company Ltd]:MyCompany
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:server1.domain.com
Email Address []:
[oracle@server1 openssl]$ ll
total 8
-rw-r--r-- 1 oracle dba 1675 Mar  8 15:33 root.key
-rw-r--r-- 1 oracle dba 1253 Mar  8 15:38 self-root.pem

The Distinguish Name (DN) of your self-signed root certificate is:

CN=server1.domain.com,O=MyCompany,L=Geneva,C=CH

Create the wallet (I have chosen default OWM directory) with orapki (can be done with graphical interface):

[oracle@server1 oracle]$ cd $ORACLE_HOME/owm/wallets/oracle
[oracle@server1 oracle]$ orapki wallet create -wallet . -pwd secure_password -auto_login
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
[oracle@server1 oracle]$ ll
total 8
-rw------- 1 oracle dba 120 Mar  8 15:42 cwallet.sso
-rw-rw-rw- 1 oracle dba   0 Mar  8 15:42 cwallet.sso.lck
-rw------- 1 oracle dba  75 Mar  8 15:42 ewallet.p12
-rw-rw-rw- 1 oracle dba   0 Mar  8 15:42 ewallet.p12.lck

Add the self signed root certificate you have created before:

[oracle@server1 oracle]$ cd $ORACLE_HOME/owm/wallets/oracle
[oracle@server1 oracle]$ orapki wallet add -wallet . -trusted_cert -cert ~oracle/openssl/self-root.pem -pwd secure_password
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
[oracle@server1 oracle]$ ll
total 8
-rw------- 1 oracle dba 1189 Mar  8 15:44 cwallet.sso
-rw-rw-rw- 1 oracle dba    0 Mar  8 15:42 cwallet.sso.lck
-rw------- 1 oracle dba 1144 Mar  8 15:44 ewallet.p12
-rw-rw-rw- 1 oracle dba    0 Mar  8 15:42 ewallet.p12.lck
[oracle@server1 oracle]$ orapki wallet display -wallet .
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        CN=server1.domain.com,O=MyCompany,L=Geneva,C=CH

Generate the certificate signing request (CSR):

[oracle@server1 oracle]$ orapki wallet add -wallet . -dn "CN=server1.domain.com,O=MyCompany,L=Geneva,C=CH" -keysize 1024 -pwd secure_password
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
[oracle@server1 oracle]$ orapki wallet display -wallet .
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
Requested Certificates:
Subject:        CN=server1.domain.com,O=MyCompany,L=Geneva,C=CH
User Certificates:
Trusted Certificates:
Subject:        CN=server1.domain.com,O=MyCompany,L=Geneva,C=CH

Export the CSR using:

[oracle@server1 oracle]$ orapki wallet export -wallet . -dn "CN=server1.domain.com,O=MyCompany,L=Geneva,C=CH" -request self-signed-oracle.csr -pwd secure_password
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
[oracle@server1 oracle]$ ll
total 12
-rw------- 1 oracle dba 2429 Mar  8 15:46 cwallet.sso
-rw-rw-rw- 1 oracle dba    0 Mar  8 15:42 cwallet.sso.lck
-rw------- 1 oracle dba 2384 Mar  8 15:46 ewallet.p12
-rw-rw-rw- 1 oracle dba    0 Mar  8 15:42 ewallet.p12.lck
-rw------- 1 oracle dba  621 Mar  8 15:47 self-signed-oracle.csr

Trying to sign the CSR with the server self-signed certificate did not worked:

[oracle@server1 openssl]$ ll
total 12
-rw-r--r-- 1 oracle dba 1675 Mar  8 15:33 root.key
-rw-r--r-- 1 oracle dba 1253 Mar  8 15:38 self-root.pem
-rw------- 1 oracle dba  621 Mar  8 15:47 self-signed-oracle.csr
[oracle@server1 openssl]$ openssl x509 -req -in self-signed-oracle.csr -CA self-root.pem -CAkey root.key -CAcreateserial -out self-signed-oracle.crt -days 365 -sha256
Signature verification error
140156873422752:error:0D0C50A1:asn1 encoding routines:ASN1_item_verify:unknown message digest algorithm:a_verify.c:191:

My server is perfectly up to date (as of writing of this blog post) and I have unfortunately not found how to overcome this error… If someone has an idea feel free to comment…

With keytool

Keytool is coming with Java and I have seen it used by Tim Hall, this would be by far my last choice but I wanted to see if I get the same issues as with openssl. Create a Java KeyStore (JKS) containing a self-signed certificate:

[oracle@server1 ~]$ cd $ORACLE_HOME/owm/wallets/oracle
[oracle@server1 oracle]$ keytool -genkeypair -keyalg RSA -keysize 2048 -alias selfsigned -keystore keystore.jks -dname 'CN=server1.domain.com,O=MyCompany,L=Geneva,C=CH' -storepass secure_password -validity 365 -keypass secure_password
[oracle@server1 oracle]$ ll
total 4
-rw-r--r-- 1 oracle dba 2190 Feb 10 12:55 keystore.jks

Create an empty wallet:

[oracle@server1 oracle]$ orapki wallet create -wallet . -pwd secure_password -auto_login
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
[oracle@server1 oracle]$ ll
total 12
-rw------- 1 oracle dba  120 Feb 10 12:56 cwallet.sso
-rw-rw-rw- 1 oracle dba    0 Feb 10 12:56 cwallet.sso.lck
-rw------- 1 oracle dba   75 Feb 10 12:56 ewallet.p12
-rw-rw-rw- 1 oracle dba    0 Feb 10 12:56 ewallet.p12.lck
-rw-r--r-- 1 oracle dba 2190 Feb 10 12:55 keystore.jks

Import the Java KeyStore (JKS) into the wallet:

[oracle@server1 oracle]$ orapki wallet jks_to_pkcs12 -wallet . -pwd secure_password -keystore keystore.jks -jkspwd secure_password
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
[oracle@server1 oracle]$ orapki wallet display -wallet .
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
Requested Certificates:
User Certificates:
Subject:        CN=server1.domain.com,O=MyCompany,L=Geneva,C=CH
Trusted Certificates:
Subject:        CN=server1.domain.com,O=MyCompany,L=Geneva,C=CH

All worked fine and I got same result as with orapki, so not encountering any issue !

Securing database network conclusion

Before 12cR1 even if you have found an huge interest in network encryption, checksum and strong authentication the advanced security enterprise edition option price might have been a stopper in its implementation.

This is finally no more true and nothing should prevent you to, at least, implement network encryption and checksum as it require really low effort. You can still implement it from your database server in an optimistic way, means by not forbidding client connection that do not want to implement it…

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>