Security

Data in the database is secured by using authentication, authorization and auditing mechanism, but the information stored in the datafiles can be read using any hex-editor tools or other OS level utilities, which is causing threat to sensitive  data. To overcome this limitation TDE is used to secure sensitive data, in database columns, stored in the datafiles. TDE is key-based access control mechanism. For each table, having encrypted column in it, an encryption key is generated which is again encrypted by database server’s master key. The database server’s master key is generated at the time when Wallet is ceated. The master key is stored outside oracle database in a Wallet and the keys for all tables containing encrypted columns are stored in dictionary tables in database.

How to setup TDE:

To use TDE one must have “ALTER SYSTEM” privilege and a valid password for oracle Wallet. 1. Make sure that listener and database is up and running and compatibility level of database is set to 10.2 . Enabling Transparent Data Encryption. Issue following command:

<span style="font-size: small;">S</span>QL &gt; alter system set encryption key identified by "password";

If the password is not given in “” then it will be taken in capital letters. This command will do the following things: – Create a Wallet file (ewallet.p12) – Open the Wallet. – Generate databse server’s master encryption key. By default, above command will create ewallet.p12 file under location $ORACLE_BASE/admin/SID/WALLET directory/folder. So it is mandatory to create a directory/folder with name as “WALLET” under $ORACLE_BASE/admin/SID, if it is not created then following error will occur while creating  Wallet file:

SQL> alter system set encryption key identified by "oracle";
*
ERROR at line 1:
ORA-28368: cannot auto-create wallet

The default location is helpful if someone wants different Wallets for different instances running under same ORACLE_HOME. We can manually specify location of Wallet file by modifying $ORACLE_HOME/network/admin/sqlnet.ora file as:

ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=C:\Oracle\product\10.2.0\db_1\wallet)))

Any valid existing directory can be specified here.

Why Wallet is needed?

The encryption keys are stored in the wallet.Database needs to load master key and column encryption keys into memory from the Wallet before it can start encryption/decryption of columns. Wallet remains open until we shutdown the database. It will be closed after the database is shutdown and needs to be started again after database startup as:

<span style="font-size: small;"><span style="font-family: arial,helvetica,sans-serif;">SQL &gt; alter system set encryption wallet open identified by "password";</span></span>

If the wallet remains closed, we will not be able to access the table which contains encrypted columns and will get following error:

ORA-28365: wallet is not open

— Now TDE can be used for this database.

Example:

SQL > conn scott/tiger

SQL > SQL> l
1  create table sood(
2  first_name varchar2(10),
3  last_name varchar2(10),
4  empid number,
5* salary number ENCRYPT);

SQL> create user saurabh identified by oracle;

User created.

SQL> grant create session to saurabh;

Grant succeeded.

Insert data into scott.sood as:

SQL> insert into sood values ('jonh','wallter',1,30000);

1 row created.

SQL> insert into sood values ('Michael','Clark',2,20000);

1 row created.

SQL> insert into sood values ('Ricky','Ponting',3,10000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from sood;

FIRST_NAME LAST_NAME       EMPID     SALARY
---------- ---------- ---------- ----------
jonh       wallter             1      30000
Michael    Clark               2      20000
Ricky      Ponting             3      10000

SQL> show user

USER is “SCOTT”

SQL> grant select on sood to saurabh;

Grant succeeded.

As user saurabh has select privilige on sood(table containing encrypted column), it will be able to retrive (decrypt) data without performing any additional step.

SQL> conn saurabh/oracle

Connected.

SQL> select * from scott.sood;

FIRST_NAME LAST_NAME       EMPID     SALARY
---------- ---------- ---------- ----------
jonh       wallter             1      30000
Michael    Clark               2      20000
Ricky      Ponting             3      10000

If we mine the redo logs, the logminer will show Salary column as “Unsupported Type” under SQL_REDO, because the data was encrypted just before it was written to disk.

Export/Import and TDE:

1. exp/imp will not work with tables having encrypted columns, following error will be given:

<span style="font-size: small;">-</span>-----------------------------------------------------------
C:\Documents and Settings\sausood&gt;exp scott/tiger tables=sood
Export: Release 10.2.0.1.0 - Production on Fri Dec 19 13:05:55 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
EXP-00107: Feature (COLUMN ENCRYPTION) of column SALARY in table SCOTT.SOOD is not supported. The table will not be exported.
Export terminated successfully with warnings.
--------------------------------------------------------------

TDE is only compatible with DATAPUMP export/import.

2. Backing up tables containing encrypted columns:

SQL> select * from DBA_ENCRYPTED_COLUMNS;

OWNER  TABLE_NAM COLUMN_NAM ENCRYPTION_ALG                SAL

—— ——— ———- —————————– —

SCOTT  SOOD      SALARY     AES 192 bits key              YES

Use the following command to export TDE enabled tables.

$ expdp scott/tiger directory=dir dumpfile=exptde.dmp tables=sood <strong>encryption_password</strong>="oracle"

Parameter ENCRYPTION_PASSWORD allows encrypted columns to be encrypted while exporting these.The password has nothing to deal with encryption keys, it is to used while importing the tables. If the ENCRYPTION_PASSWORD is not used the the data is exported in clear text and a warning message is displayed at the time of datapump export:

"<strong>ORA-39173</strong>: Encrypted data has been stored unencrypted in dump file set. "

While importing it is mandatory to have the same wallet file for database, otherwise following error will occur:

"<strong>ORA-28362</strong>: master key not found"

Transparent Data Encryption FAQ

Oracle 11g introduces Case-sensitive passwords for database authentication. Along with this if you wish to change the password (temporarily) and reset it back to old , you will find that password field in dba_users is empty. Prior to 11g we could use following technique to change/restore password

SQL> create user amit identified by amit;

User created.

SQL> grant create session to amit;

Grant succeeded.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select username,password from dba_users where username='AMIT';

USERNAME                       PASSWORD
------------------------------ ------------------------------
AMIT                           9DEC0D889E8E9A6B

SQL> alter user amit identified by abc;

User altered.

SQL> conn amit/abc
Connected.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter user amit identified by values '9DEC0D889E8E9A6B';

User altered.

SQL> conn amit/amit
Connected.

In 11g if you query password field, it will return NULL.

SQL> select username,password from dba_users where username='AMIT';

USERNAME                       PASSWORD
------------------------------ ------------------------------
AMIT

Let’s first see Case-sensitive password feature in 11g and then steps to change/restore passwords

SQL> create user amit identified by AMIT;

User created.

SQL> grant connect,resource to amit;

Grant succeeded.

SQL> conn amit/amit
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn amit/AMIT
Connected.

This behavior is controlled by “sec_case_sensitive_logon” initialization paramter. If the value is true then it will enforce case sensitive passwords

SQL> select NAME,VALUE from V$SPPARAMETER where NAME='sec_case_sensitive_logon';

NAME                                     VALUE
---------------------------------------- --------------------
sec_case_sensitive_logon                 TRUE

SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> conn amit/amit
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=true;

System altered.

SQL> conn amit/amit
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn amit/AMIT
Connected.

Now to reset the password in 11g, we need to query spare4 column in user$ table

SQL> select spare4 from user$ where name='AMIT';

SPARE4
--------------------------------------------------------------------------------
S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB

SQL> alter user amit identified by abc12;

User altered.

SQL> conn amit/abc12
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user amit identified by values 'S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB';

User altered.

SQL> conn amit/abc12
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn amit/AMIT
Connected.

As per Metalink Note 429465.1 , view DBA_USERS has new column PASSWORD_VERSIONS rendered as follows:

decode(length(u.password),16,'10G ',NULL)||NVL2(u.spare4, '11G ' ,NULL)
for example:

SQL> select USERNAME, PASSWORD_VERSIONS from dba_users where rownum <5;

USERNAME                       PASSWORD
------------------------------ --------
SYS                            10G 11G
SYSTEM                         10G 11G
OUTLN                          10G 11G
DIP                            10G 11G

In this case it means both old and new-style hash values are available for the users, the new hash value is stored in the USER$.SPARE4 column, as long as this remains NULL it means the password has not been changed since the migration and the user will have the old case insensitive password.

SQL> create user test identified by test;

User created.

SQL> select USERNAME, PASSWORD_VERSIONS from dba_users where username in (‘AMIT’,’TEST’);

USERNAME                       PASSWORD
—————————— ——–
AMIT                           11G
TEST                           10G 11G

As I had reset password using only spare4 string, password will be case -sensitive irrespective of setting for sec_case_sensitive_logon parameter value. i.e why we see value of “11G”  for user Amit.

Update

When resetting the password, we need to also query password column from user$ column if we wish to use case-insensitive feature in future. i.e In my above example I used only spare4 column value to reset the password. Now if I set sec_case_sensitive_logon=false , I will not be able to connect.

SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> conn amit/amit
ERROR:
ORA-01017: invalid username/password; logon denied

In case we wish to use both, we need to set identified by values ‘S:spare4;password’. As I didnot use password field while resetting, I find that password field in user$ is empty. To correct it, I had to change the password again.

SQL> select password,spare4 from user$ where name='AMIT';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
                               S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB

SQL>  alter system set sec_case_sensitive_logon=true;

System altered.

SQL> alter user amit identified by AMIT;

User altered.

SQL> select password,spare4 from user$ where name='AMIT';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
9DEC0D889E8E9A6B               S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE

So to reset the password, following needs to be used.

SQL> select password,spare4 from user$ where name='AMIT';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
9DEC0D889E8E9A6B               S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE

SQL> alter user amit identified by values 'S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE;9DEC0D889E8E9A6B';

User altered.

Thanks to Laurent for pointing this. You can see his article for more information.You can use below code to get the password script

select 'alter user '||name||' identified by values '''||password||''';' from user$ where spare4 is null and password is not null
union
select 'alter user '||name||' identified by values '''||spare4||';'||password||''';' from user$ where spare4 is not null and password is not null;