August | 2014 | Spot on Oracle

This post is about configuring SQL*Net to support native SQL*Net encription and SSL authentication for different users at the same time. Imagine your security offices comes along and demands that every connection to the database must be encrypted and it must be enforced by the database. The application server (e.g. Weblogic) already authenticates itself to the database using SSL certificates, so the network traffic is already encrypted. All other connects use username/password authentication and must be encrypted by native SQL*Net encryption. The database server enforces encryption by setting SQLNET.ENCRYPTION_SERVER = REQUIRED. From a security point of view this is paramount.

Going ahead and configure everything according docs and white papers I bet you’ll end up with “ORA-12696 Double Encryption Turned On, login disallowed”.

[side note]
If SQLNET.ENCRYPTION_SERVER is set to REQUIRED then the client gets “ORA-28865: SSL connection closed”, but once you start digging in the SQL*Net trace file of the server process you’ll find ORA-12696.
Funnily, if SQLNET.ENCRYPTION_SERVER is set to REQUESTED then the client actually gets ORA-12696 reported.
[/side note]

The basic problem is, that there is only one $TNS_ADMIN location with one sqlnet.ora containing all the configuration. Ultimately, we need to find a way to use multiple $TNS_ADMIN locations and separate the sqlnet.ora configurations for the different listening end points/services.
One solution would be to fire up two listeners with different environment settings. Note that the server processes spawned by the listener inherit the properties from it, that being permissions and environment variables.
Following I’m going to show a solution with one single listener using a little known feature: specifying ENVS parameter in the static listener SID list.

But first let’s have a look a the current configuration which is giving us some trouble. There’s a listener configuration with two listening end points (one for TCP and one for TCPS) and a mixed configuration in sqlnet.ora.
My $TNS_ADMIN is pointing to /u01/app/oracle/network/admin which is the default location set in the oracle’s user profile and is used when starting up the listener.

$ cat /u01/app/oracle/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel6ora12dev1.localdomain)(PORT = 2483))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = oel6ora12dev1.localdomain)(PORT = 2484))
    )
  )
SSL_CLIENT_AUTHENTICATION=FALSE
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY=/u01/app/oracle/admin/DEV1/wallet/auth)))

$ cat /u01/app/oracle/network/admin/sqlnet.ora

SQLNET.ENCRYPTION_SERVER=requested
SQLNET.ENCRYPTION_TYPES_SERVER=(aes256)
SQLNET.AUTHENTICATION_SERVICES=(BEQ,TCPS)
SSL_CLIENT_AUTHENTICATION=TRUE
SSL_CIPHER_SUITES=(SSL_RSA_WITH_AES_256_CBC_SHA)
WALLET_LOCATION = (SOURCE = (METHOD=FILE)(METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/DEV1/wallet/auth)))

If we try to connect from a client using SSL authentication it errors with “ORA-12696 Double Encryption Turned On, login disallowed”. Connecting using username/password works just fine and the network traffic is properly encrypted.

To be able to have different SQL*Net configurations we first need to create a separate directory structure. In this case I just create it next to the current network/admin directory and separate the configuration into two sqlnet.ora files:
$ mkdir /u01/app/oracle/network/adminssl
$ vi /u01/app/oracle/network/admin/sqlnet.ora

SQLNET.ENCRYPTION_SERVER=requested
SQLNET.ENCRYPTION_TYPES_SERVER=(aes256)

$ vi /u01/app/oracle/network/adminssl/sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES=(BEQ,TCPS)
SSL_CLIENT_AUTHENTICATION=TRUE
SSL_CIPHER_SUITES=(SSL_RSA_WITH_AES_256_CBC_SHA)
WALLET_LOCATION = (SOURCE = (METHOD=FILE)(METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/DEV1/wallet/auth)))

Now, I need to tell the listener to set the proper environment when spawning server processes for incomming connection requests. This is done by specifying the TNS_ADMIN location in the ENVS parameter:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DEV1_SSL.localdomain)
      (ORACLE_HOME = /u01/app/oracle/product/ora12101)
      (SID_NAME = DEV1)
      (ENVS="TNS_ADMIN=/u01/app/oracle/network/adminssl")
    )
  )

Now, whenever a connection is established via service DEV1_SSL.localdomain the listener exports the environment varialbe TNS_ADMIN before spawning the server process. The server process then looks up the environment variable and reads the sqlnet.ora file in /u01/app/oracle/network/adminssl.
As you can see, next to using different listening end points I also present an addtitional service on the listener.
Depending on which service and end point the client uses it gets either native SQL*Net encryption or SSL authentication including SSL encryption.

On the client side I use either one of the following TNS entries, depending on which authentication method the user uses.

DEV1_SSL.LOCALDOMAIN =
  (DESCRIPTION =
    (ADDRESS_LIST=
      (ADDRESS = (PROTOCOL = TCPS)(HOST = oel6ora12dev1.localdomain)(PORT = 2484))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DEV1_SSL.localdomain)
      (SECURITY=(SSL_SERVER_CERT_DN="CN=DEV1.localdomain"))
    )
  )

DEV1_NSE.LOCALDOMAIN =
  (DESCRIPTION =
    (ADDRESS_LIST=
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel6ora12dev1.localdomain)(PORT = 2483))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DEV1.localdomain)
    )
  )

$ sqlplus /@DEV1_SSL
or
$ sqlplus system/manager@DEV1_NSE

Setting different values for TNS_ADMIN at the time of process spawning is useful in other situations, too. For instance, this technique can be applied if you have one listener for multiple databases and each database has its wallet.

In Grid Infrastructure environment there is also the possibility to set environment variables in Oracle Clusterware which is probably more comfortable to handle:

srvctl setenv database -d  -t TNS_ADMIN=

Foot note: All test have been run on Oracle 12.1.0.1

Following up on Chris Antognini’s findings regarding deferred segment creation (here and here) I noticed another restriction that still finds no mention even in the latest Oracle documentation. I vaguely remember to have come across some issues in lower 11gR2 releases and some of them have been fixed in later patch sets. The problem went along the line of:

alter session set deferred_segment_creation = true;

create table t2 parallel 4
as 
  select /*+ parallel (t1, 4) */ *
  from t1 where 1 = 2
;

select segment_name, segment_type from user_segments where segment_name = 'T1';
SEGMENT_NAME SEGMENT_TYPE
------------ ------------
T1           TABLE

Note: This test war run against Oracle 11.2.0.1 as this particular issue is fixed in at least 11.2.0.4 and later

Now, why in gods name would you run CTAS in parallel when you know the query does not return any rows you might think.
Well, the problem becomes more subtle when we start combining features, namely partitioning and parallel DDL. Following I have prepared a few test cases to demonstrate the issue.

First let’s create the source table to select from:

create table t1
as
  select
      rownum id
    , round(dbms_random.value(1,4), 0) code
  from dual connect by level <= 100
;

The CODE column contains four distinct number values ranging from 1 to 4.

Next, we create a list partitioned table by using CTAS (create table as select) from our source table. All parts of this SQL run serially:

create table t3
segment creation deferred
partition by list (code) (
    partition code_1 values (1)
  , partition code_2 values (2)
  , partition code_3 values (3)
  , partition code_4 values (4)
)
as
  select *
  from t1
  where code = 1
;

select segment_name, segment_type, partition_name from dba_segments where owner = 'TESTUSER' and segment_name = 'T3' order by segment_name, partition_name;
SEGMENT_NAME SEGMENT_TYPE      PARTITION_NAME
------------ ---------------   --------------
T3           TABLE PARTITION   CODE_1

This works as expected. Only the relevant partition was materialized as a segment to hold the data for CODE value 1.

This time, we create the same table structure using parallel DDL:

create table t4
segment creation deferred
parallel 4
partition by list (code) (
    partition code_1 values (1)
  , partition code_2 values (2)
  , partition code_3 values (3)
  , partition code_4 values (4)
)
as
  select *
  from t1
  where code = 1
;

select segment_name, segment_type, partition_name from dba_segments where owner = 'TESTUSER' and segment_name = 'T4' order by segment_name, partition_name;
SEGMENT_NAME SEGMENT_TYPE      PARTITION_NAME
------------ ---------------   --------------
T4           TABLE PARTITION   CODE_1
T4           TABLE PARTITION   CODE_2
T4           TABLE PARTITION   CODE_3
T4           TABLE PARTITION   CODE_4

Oracle has materialized all four partitions even though only one of them contains data. The same also happens when the source query doesn’t return any rows, e.g. when CODE = 5.

Usually, there’s not much sense in running DDL in parallel while the SELECT providing the data runs serially. I have left out parallel processing of the SELECT part for simplicity as my tests have shown that it doesn’t affect the segment creation in any way.

Foot note: All test have been run on Oracle 12.1.0.2 and 11.2.0.4 except when where otherwise

Spot on Oracle

Hopefully enlightening

Monthly Archives: August 2014

Defeating ORA-12696 Double Encryption Turned On (tricking the ENVS)

Deferred Segement Creation PARALLEL