Securing access and data in Oracle Environment Best Practices June 2014

Índex 1.

Scope........................................................................................... 3

2.

System Requirements ................................................................. 4 2.1. Database Server 2.2. Client Server

3.

Encrypt the data stored in the DB server .................................... 4 3.1. 3.2. 3.3. 3.4.

4.

Prerequisites Setting the Master Encryption Key Opening and Closing the Encrypted Wallet How to Encrypt Tablespaces

7 11 13

Verify Data Encryption ............................................................ 14 5.1. DB Server 5.2. Client – Server Communication

6.

4 5 5 6

Encrypt communication between client and database server ..... 7 4.1. How to install Oracle Advanced Security 4.2. How to configure the client 4.3. How to configure the database Server

5.

4 4

14 15

Dictionary Views associated to the Encryption system ........... 18 6.1. V$ENCRYPTION_WALLET 6.2. V$ENCRYPTED_TABLESPACES 6.3. DBA_ENCRYPTED_COLUMNS

18 18 19

1. Scope The objective of this document is to certify a secure Oracle environment in which to use the Visure Requirements application. This environment follows the Oracle standard configuration to encrypt data in a database as well as communications between the client and the database server, following the best practices recommended by Oracle.

For the database encryption functionality it is needed to use Oracle TDE (Transparent Data Encryption).

Transparent Data Encryption (TDE) enables to encrypt sensitive data -such as credit card numbers- stored in tables and tablespaces. Encrypted data is decrypted transparently for a database user or application that has access to data. TDE helps to protect data stored on media in the event that the storage media or data file gets stolen.

Oracle Database uses authentication, authorization, and auditing mechanisms to secure data in the database, but not in the operating system data files where the data is stored. To protect these data files, Oracle Database provides Transparent Data Encryption (TDE). TDE encrypts sensitive data stored in data files.

There are two types of Transparent Data Encryption.  Column Encryption: TDE column encryption is used to protect confidential data, such as credit card and social security numbers, stored in table columns.  Tablespace Encryption: TDE tablespace encryption enables to encrypt an entire tablespace. All objects created in the encrypted tablespace are automatically encrypted. TDE tablespace encryption is useful to secure sensitive data in tables. It is not needed to perform a granular analysis of each table column to determine the columns that need encryption. In the environment that has been certified, TDE tablespace encryption has been used to secure the data. All data that is stored in this tablespace will be encrypted automatically. TDE tablespace encryption is a good alternative to TDE column encryption if tables contain sensitive data in multiple columns, or to protect the entire table and not just individual columns.

Página 3 de 19

© Logicalis 2014

18/07/14

To start using TDE, the security administrator must create a wallet and set a master key. The wallet can be the default database wallet shared with other Oracle Database components, or a separate wallet specifically used by TDE. Oracle strongly recommends to use a separate wallet to store the master encryption key.

2. System Requirements To certify the environment, an environment has been defined with the two roles required to get this secure configuration: a DB server and a client with the Visure Requirements application installed.

Each server has the following configuration:

2.1. Database Server The software versions installed in this server are: 

Windows Server 2008 R2 Enterprise SP1.



Oracle Database 11gR2 Enterprise Edition (11.2.0.4) with TDE and Oracle Advanced Security component.

2.2. Client Server The software versions installed in this server are: 

Windows Server 2008 R2 Enterprise SP1.



Java 7 Update 55.



Microsoft Office 2010.



Oracle Client 11gR2 (11.2.0.4) with Oracle Advanced Security component.



Oracle Client 11gR2 (11.2.0.4) with ODBC driver.



Visure Requirements 4.5.7.

3. Encrypt the data stored in the DB server 3.1. Prerequisites To use the Oracle TDE functionality you must install Oracle Enterprise Edition. In order to use TDE tablespace encryption, you must be running Oracle Database 11g release 1 (11.1) or higher. If you have upgraded from an earlier release, the compatibility for the database must have been set to 11.0.0 or higher.

Página 4 de 19

© Logicalis 2014

18/07/14

To use the enhanced tablespace encryption features in Oracle Database 11g Release 2 (11.2), the compatibility for the database must be set to 11.2 or higher.

3.2. Setting the Master Encryption Key If you wish to use a wallet specifically for TDE, then you must specify a wallet location in the sqlnet.ora file by using the ENCRYPTION_WALLET_LOCATION parameter. Oracle recommends to use the ENCRYPTION_WALLET_LOCATION parameter to specify a wallet location for TDE.

#sqlnet.ora Network Configuration File: C:\Oracle\app\product\11.2.0\NETWORK\ADMIN\sqlnet.ora # Generated by Oracle configuration tools. ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = C:\Oracle\app\product\base\admin\VISURE\wallet) ) )

Before you can encrypt or decrypt database columns or tablespaces, you must generate a master encryption key. Oracle Database 11g Release 2 (11.2) uses the same master encryption key for both TDE column encryption and TDE tablespace encryption. To set the master encryption key, use the following command. ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "password"

If no wallet location is specified in the sqlnet.ora file, then the default database wallet location is used. The default database wallet location is ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet or ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet, where DB_UNIQUE_NAME is the unique name of the database specified in the initialization parameter file.

3.3. Opening and Closing the Encrypted Wallet The database must load the master encryption key into memory before it can encrypt or decrypt columns/tablespaces. Opening the wallet allows the database to access the master encryption key. Use the following ALTER SYSTEM command to explicitly open the wallet: ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password"

Página 5 de 19

© Logicalis 2014

18/07/14

Use the following ALTER SYSTEM command to explicitly close the wallet ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "password" Closing the wallet disables all encryption and decryption operations. Any attempt to encrypt/decrypt data or access encrypted data results in the following error: ORA-28365: wallet is not open

3.4. How to Encrypt Tablespaces The CREATE TABLESPACE command enables to create an encrypted tablespace. The permanent_tablespace_clause enables choosing the encryption algorithm and the key length for encryption. The ENCRYPT keyword in the storage_clause encrypts the tablespace. The following syntax illustrates this: CREATE TABLESPACE VR457_T_Encrypt DATAFILE 'C:\ORACLE\BBDD\VISURE\vr457_t_encrypt.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ENCRYPTION USING '3DES168' DEFAULT STORAGE (ENCRYPT); The algorithm can have one of the following values:  3DES168  AES128  AES192  AES256 The key lengths are included in the names of the algorithms themselves. If no encryption algorithm is specified, the default encryption algorithm is used. The default encryption algorithm is AES128 .

You cannot encrypt an already existing tablespace. However, you can import data into an encrypted tablespace using the Oracle Data Pump utility. You can also use SQL commands like CREATE TABLE...AS SELECT...or ALTER TABLE...MOVE... to move data into an encrypted tablespace. The CREATE TABLE...AS SELECT... command enables creating a table from an existing table. The ALTER TABLE...MOVE... command enables you to move a table into the encrypted tablespace.

Página 6 de 19

© Logicalis 2014

18/07/14

4. Encrypt communication between client and database server To encrypt the information in the communication between client and server it is necessary to install the functionality "Oracle Advanced Security" in the Oracle client.

4.1. How to install Oracle Advanced Security To install this feature you must perform the following steps:

1. Download the client software for Oracle Database 11gR2 (11.2.0.4), To download the Oracle client software go to the following link (a user and password of the Oracle Support site is required): https://updates.oracle.com/Orion/Services/download/p13390677_112040_WINNT_3of6.z ip?aru=16919969&patch_file=p13390677_112040_WINNT_3of6.zip

2. Unzip de file p13390677_112040_WINNT_3of6.zip and execute the setup.exe file.

3.

Página 7 de 19

Select ‘Custom’.

© Logicalis 2014

18/07/14

4. Select ‘Skip software updates’ and click the ‘Next’ button.

5. Choose a language.

Página 8 de 19

© Logicalis 2014

18/07/14

6. Specify an Oracle Base path to place all Oracle software and configuration-related files. Also, specify a location to store Oracle’s software files.

Página 9 de 19

© Logicalis 2014

18/07/14

7. Choose the options defined in the following screenshot.

Página 10 de 19

© Logicalis 2014

18/07/14

8. Press the ‘Install’ button

4.2. How to configure the client Once the Oracle Advanced Security component is installed, edit the sqlnet.ora file located in $ORACLE_HOME / network / admin and include the following lines:

#sqlnet.ora Network Configuration File: #C:\Oracle\app\product\11.2.0\NETWORK\ADMIN\sqlnet.ora # Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS) SQLNET.CRYPTO_SEED = 'Visure Encryption' SQLNET.ENCRYPTION_TYPES_CLIENT= (AES256) ADR_BASE = C:\Oracle\app\client\11.2.0.4\log SQLNET.ENCRYPTION_SERVER = REQUIRED SQLNET.ENCRYPTION_CLIENT = REQUIRED SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUIRED The sqlnet.ora file parameters to be modified are:

Página 11 de 19

© Logicalis 2014

18/07/14

 SQLNET.AUTHENTICATION_SERVICES= (NTS) It allows Windows users to be authenticated using Windows NT native security.  SQLNET.CRYPTO_SEED The encryption seed is a random string of up to 256 characters. It generates the cryptographic keys that encrypt data as it travels across the network.  SQLNET.ENCRYPTION_TYPES_CLIENT This parameter specifies a list of encryption algorithms used by this client or server acting as a client. This list is used to negotiate a mutually acceptable algorithm with the other end of the connection. If an algorithm that is not installed is specified on this side, the connection terminates with the ORA-12650 error message. The list of encryption algorithms is:  AES256: AES (256-bit key size).  AES192: AES (192-bit key size).  3DES168: 3-key Triple-DES (168-bit effective key size).  AES128: AES (128-bit key size).  3DES112: 2-key Triple-DES (112-bit effective key size).  ADR_BASE To specify the base directory into which tracing and logging incidents are stored when ADR is enabled. The default on the server side is ORACLE_BASE, or ORACLE_HOME/log, if ORACLE_BASE is not defined  SQLNET.ENCRYPTION_SERVER This parameter specifies the desired encryption behavior when a client or a server acting as a client connects to this server. The behavior of the server partially depends on the SQLNET.ENCRYPTION_CLIENT setting at the other end of the connection.  SQLNET.ENCRYPTION_CLIENT This parameter specifies the desired encryption behavior when this client or server acting as a client connects to a server. The behavior of the client partially depends on the value set for SQLNET.ENCRYPTION_SERVER at the other end of the connection.  SQLNET.CRYPTO_CHECKSUM_SERVER This parameter specifies the desired data integrity behavior when a client or another server acting as a client connects to this server. The behavior partially depends on the SQLNET.CRYPTO_CHECKSUM_CLIENT setting at the other end of the connection.  SQLNET.CRYPTO_CHECKSUM_CLIENT This parameter specifies the desired data integrity behavior when this client or server acting as a client connects to a server. The behavior partially depends on the SQLNET.CRYPTO_CHECKSUM_SERVER setting at the other end of the connection.

Página 12 de 19

© Logicalis 2014

18/07/14

All clients that will connect to the database must have properly configured the sqlnet.ora file to encrypt communications.

4.3. How to configure the database Server The sqlnet.ora file in the database server must include the following lines:

#sqlnet.ora Network Configuration File: #C:\Oracle\app\product\11.2.0\NETWORK\ADMIN\sqlnet.ora # Generated by Oracle configuration tools. ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = C:\Oracle\app\product\base\admin\VISURE\wallet) ) )

SQLNET.AUTHENTICATION_SERVICES= (NTS) SQLNET.CRYPTO_SEED = 'Visure Encryption' SQLNET.ENCRYPTION_TYPES_SERVER= (AES256) ADR_BASE = C:\Oracle\app\product\11.2.0\log SQLNET.ENCRYPTION_SERVER = REQUIRED SQLNET.ENCRYPTION_CLIENT = REQUIRED SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUIRED The ENCRYPTION_WALLET_LOCATION parameter is defined in paragraph 3.2 of this document. The sqlnet.ora file parameters to be modified are:  SQLNET.AUTHENTICATION_SERVICES= (NTS) Allows Windows users to be authenticated using Windows NT native security.  SQLNET.CRYPTO_SEED The encryption seed is a random string of up to 256 characters. It generates the cryptographic keys that encrypt data as it travels across the network  SQLNET. ENCRYPTION_TYPES_SERVER This parameter specifies a list of encryption algorithms used by this server in the order of intended use. This list is used to negotiate a mutually acceptable algorithm with the client’s end of the connection. Each algorithm is checked against the list of available client algorithm types until a match is found. If an algorithm that is not installed is specified in this side the connection terminates with the error message ORA-12650.

Página 13 de 19

© Logicalis 2014

18/07/14

The list of encryption algorithms is:  AES256: AES (256-bit key size).  AES192: AES (192-bit key size).  3DES168: 3-key Triple-DES (168-bit effective key size).  AES128: AES (128-bit key size).  3DES112: 2-key Triple-DES (112-bit effective key size).  ADR_BASE To specify the base directory into which tracing and logging incidents are stored when ADR (Automatic Diagnostic Repository) is enabled. By default the server side is ORACLE_BASE, or ORACLE_HOME/log, if ORACLE_BASE is not defined.  SQLNET.ENCRYPTION_SERVER This parameter specifies the desired encryption behavior when a client, or a server acting as a client, connects to this server. The behavior of the server partially depends on the SQLNET.ENCRYPTION_CLIENT setting at the other end of the connection.  SQLNET.ENCRYPTION_CLIENT This parameter specifies the desired encryption behavior when this client, or server acting as a client, connects to a server. The behavior of the client partially depends on the value set for SQLNET.ENCRYPTION_SERVER at the other end of the connection.  SQLNET.CRYPTO_CHECKSUM_SERVER This parameter specifies the desired data integrity behavior when a client, or another server acting as a client, connects to this server. The behavior partially depends on the SQLNET.CRYPTO_CHECKSUM_CLIENT setting at the other end of the connection.  SQLNET.CRYPTO_CHECKSUM_CLIENT This parameter specifies the desired data integrity behavior when this client, or server acting as a client, connects to a server. The behavior partially depends on the SQLNET.CRYPTO_CHECKSUM_SERVER setting at the other end of the connection.

5. Verify Data Encryption 5.1. DB Server To verify that the information has been encrypted in the files of the tablespace we created with the encrypt option, simply edit the file and try to look in it for a string such as the Unique Identificator (DNI), or name, for example. If the "encrypted wallet" is closed, the application’s data cannot be accesed, and being the entire repository in encrypted tablespaces the database is not able to decrypt the information. At database level, the error “ORA-28365: wallet is not open” is returned.

Página 14 de 19

© Logicalis 2014

18/07/14

In Unix servers, the command "strings" converts a binary file to text. If for example we run the following command we would not find the requested information: # strings datafile_encripted.dbf | grep “credit card number”

5.2. Client – Server Communication Oracle can trace the session between the client and the database server. The trace will ensure that the encryption is active.

To do this, enter the following lines in the file sqlnet.ora:

#sqlnet.ora Network Configuration File: C:\Oracle\app\product\11.2.0\NETWORK\ADMIN\sqlnet.ora # Generated by Oracle configuration tools. ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = C:\Oracle\app\product\base\admin\VISURE\wallet) ) )

SQLNET.AUTHENTICATION_SERVICES= (NTS) SQLNET.CRYPTO_SEED = 'Visure Encryption' SQLNET.ENCRYPTION_TYPES_CLIENT= (AES256) ADR_BASE = C:\Oracle\app\client\11.2.0.4\log SQLNET.ENCRYPTION_SERVER = REQUIRED SQLNET.ENCRYPTION_CLIENT = REQUIRED SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUIRED trace_level_client = 16 trace_file_client = cli trace_directory_client = c:\trace trace_unique_client = on trace_timestamp_client = on trace_filelen_client = 100 trace_fileno_client = 2 log_file_client = cli log_directory_client = c:\trace\log tnsping.trace_directory = c:\trace TRACE_DIRECTORY_CLIENT and TRACE_FILE_CLIENT can have any value you choose.

Página 15 de 19

© Logicalis 2014

18/07/14

Oracle will add numerical digits to the file name you supply for TRACE_FILE_CLIENT. Review the contents of C:\trace. You should not see any clear text for the query you issued, which means that you have successfully encrypted your database connections.

The sqlnet.ora file parameters to be modified are:  trace_level_client If not set to off (or 0, which is the default) it enables tracing. Possible values: USER (4), ADMIN (10) and SUPPORT (16).  trace_file_client Specifies the name of the (client) trace file. The default is sqlnet.trc.  trace_directory_client Specifies the destination directory in which the trace file goes. The default is the current directory in which the executable is started.  trace_unique_client Specifies whether a unique trace file is created for each client trace session or not. When the value is set to on, a process identifier is appended to the name of each trace file, enabling several files to coexist. For example, trace files named sqlnetpid.trc are created if the default trace file namesqlnet.trc is used. When the value is set to off, data from a new client trace session overwrites the existing file. Use this parameter when ADR is not enabled.  trace_timestamp_client Adds a time stamp in the form of dd-mon-yyyy hh:mi:ss:mil in every trace event in the database server trace file, which has the default name “ofsvr_pid.trc”. This parameter is also applicable when non-ADR tracing is used.  trace_filelen_client To specify the size of the client trace files in kilobytes (KB). When the size is reached, the trace information is written to the next file. The number of files is specified with the TRACE_FILENO_CLIENT parameter. Use this parameter when ADR is not enabled.  trace_fileno_client Specifies the number of trace files for client tracing. When this parameter is set with the TRACE_FILELEN_CLIENT parameter, trace files are used in a cyclical fashion. The first file is filled first, then the second file, and so on. When the last file has been filled, the first file is re-used, and so on. The trace file names are distinguished one from another by their sequence number. For example, if the default trace file of sqlnet.trc is used and this parameter is set to 3, then the trace files will be named sqlnet1.trc, sqlnet2.trc and sqlnet3.trc.

 log_file_client

Página 16 de 19

© Logicalis 2014

18/07/14

Specifies the name of the log file for the client. Use this parameter when ADR is not enabled.  log_directory_client Specifies the destination directory for the client trace file. Use this parameter when ADR is not enabled.  tnsping.trace_directory To turn the TNSPING

utility tracing

on

at

a specified level

or

turn it

off.

If the encryption was successfully used, you will see a line saying, “The server chose the 'AES256' encryption algorithm” and another line saying “encryption is active, using AES256”:

[14-MAY-2014 16:04:41:290] naeecom: entry [14-MAY-2014 16:04:41:290] naeecom: The server chose the 'AES256' encryption algorithm [14-MAY-2014 16:04:41:290] naeecom: exit [14-MAY-2014 16:04:41:290] naeccom: entry [14-MAY-2014 16:04:41:290] naeccom: The server chose the 'SHA1' crypto-checksumming algorithm [14-MAY-2014 16:04:41:290] naeccom: exit [14-MAY-2014 16:04:41:290] na_tns: entry [14-MAY-2014 16:04:41:290] na_tns: Secure Network Services is available. [14-MAY-2014 16:04:41:290] nau_adi: entry [14-MAY-2014 16:04:41:290] nau_adi: exit [14-MAY-2014 16:04:41:290] na_tns: authentication is not active [14-MAY-2014 16:04:41:290] na_tns: encryption is active, using AES256 [14-MAY-2014 16:04:41:290] na_tns: crypto-checksumming is active, using SHA1 [14-MAY-2014 16:04:41:290] na_tns: exit

If the encryption was not successfully enabled, you will see these entries instead of the ones listed above: [14-MAY-2014 16:08:52:637] naeecom: entry [14-MAY-2014 16:08:52:637] naeecom: Encryption inactive [14-MAY-2014 16:08:52:637] naeecom: exit [14-MAY-2014 16:08:52:637] naeccom: entry [14-MAY-2014 16:08:52:637] naeccom: Crypto-Checksumming inactive [14-MAY-2014 16:08:52:637] naeccom: exit [14-MAY-2014 16:08:52:637] na_tns: entry [14-MAY-2014 16:08:52:637] na_tns: Secure Network Services is available. [14-MAY-2014 16:08:52:637] nau_adi: entry [14-MAY-2014 16:08:52:637] nau_adi: exit [14-MAY-2014 16:08:52:637] na_tns: authentication is not active [14-MAY-2014 16:08:52:637] na_tns: encryption is not active [14-MAY-2014 16:08:52:637] na_tns: crypto-checksumming is not active

Página 17 de 19

© Logicalis 2014

18/07/14

[14-MAY-2014 16:08:52:637] na_tns: exit -

6. Dictionary Views associated to the Encryption system 6.1. V$ENCRYPTION_WALLET Displays information on the status of the wallet and the wallet’s location for transparent data encryption.

Column

Datatype

Description

WRL_TYPE

VARCHAR2(20)

Type of the wallet resource locator (for example, FILE)

WRL_PARAMETER

VARCHAR2(4000)

Parameter of the wallet resource locator (for example, absolute filename if WRL_TYPE = FILE)

STATUS

VARCHAR2(9)

Status of the wallet:

   

CLOSED OPEN OPEN_NO_MASTER_KEY UNDEFINED

6.2. V$ENCRYPTED_TABLESPACES Displays information about the tablespaces that are encrypted.

Column

Datatype

Description

TS#

NUMBER

Tablespace number

ENCRYPTIONALG

VARCHAR2(7)

Encryption algorithm:

    

NONE 3DES168 AES128 AES192 AES256

ENCRYPTEDTS

VARCHAR2(3)

Indicates whether the tablespace is encrypted (YES) or not (NO)

ENCRYTPEDKEYFoot 1

RAW(32)

Encrypted version of the tablespace key for the encrypted tablespace

Página 18 de 19

© Logicalis 2014

18/07/14

Column

Datatype

Description

MASTERKEYIDFootref 1

RAW(16)

ID of the master key that was used to encrypt the tablespace key

BLOCKS_ENCRYPTEDFootref 1

NUMBER

Number of tablespace blocks that have been encrypted during the lifetime of this instance

BLOCKS_DECRYPTEDFootref 1

NUMBER

Number of tablespace blocks that have been decrypted during the lifetime of this instance

6.3. DBA_ENCRYPTED_COLUMNS Displays encryption algorithm information for all encrypted columns in the database.

Column

Datatype

NULL

Description

OWNER

VARCHAR2(30)

NOT NULL

Owner of the table

TABLE_NAME

VARCHAR2(30)

NOT NULL

Name of the table

COLUMN_NAME

VARCHAR2(30)

NOT NULL

Name of the column

ENCRYPTION_ALG

VARCHAR2(29)

Encryption algorithm used to protect secrecy of data in this column:

   

3 Key Triple DES 168 bits key AES 128 bits key AES 192 bits key AES 256 bits key

SALT

VARCHAR2(3)

Indicates whether the column is encrypted with SALT (YES) or not (NO)

INTEGRITY_ALG

VARCHAR2(12)

Integrity algorithm used for the column:

 

Página 19 de 19

SHA-1 NOMAC

© Logicalis 2014

18/07/14