Introduction:- Today we are going to learn about encryption in Oracle. Oracle database has the unique feature to secure data from the data loss. As we get a hint from the word encryption that means the process of converting information or data into code, especially to prevent unauthorized access. Rising security threats, expanding compliance requirements, consolidation, and cloud computing are just a few of the reasons why data security has become critical. Protecting data requires a defense in depth, multi-layered approach that encompasses controls to evaluate security postures, prevent data loss, detect suspicious activities and apply data access controls at the source through data-driven security.
To create a standard Oracle wallet and then add a master key to it you have to follow few basic steps:
- Configure a standard wallet and then add a master key to it you have to follow few basic steps
- Creating the Software Keystore
- Opening a Software Keystore
- Setting the TDE Master Encryption Key in the Software Keystore
- Encrypt the Data
1) Configure the sqlnet.ora file
Oracle should know where to find the Oracle Wallet so you have to define a directory accessible by the Oracle Software.
In the multi tenant solution, the Oracle Wallet location is valid for the CDB and every PDBs at the same time. Edit your sqlnet.ora file and use the following syntax to let the database know where the software key store is located on file system.
Be sure that the directory exists to avoid the error “ORA-46633: creation of a password-based keystore failed”:
[oracle@vsi08devpom admin]$ pwd /opt/app/oracle/product/12.1.0/db_1/network/admin [oracle@vsi08devpom admin]$ echo "ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet)))" >> sqlnet.ora
[oracle@vsi08devpom admin]$ cat sqlnet.ora # sqlnet.ora Network Configuration File: /app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet)))
2) Create the Oracle Wallet
It’s possible to create the Oracle Wallet using the owm gui utility (as you can read on this post) or from sqlplus with a new set of key management statements (ADMINISTER KEY MANAGEMENT).
The steps to create an Oracle Wallet must be executed from the sqlplus command line with a user who has been granted the new SYSKM administrative privilege:
when in a multitenant environment you have to log in to the root container.
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT
To avoid the error “ORA-46633: creation of a password-based keystore failed”, the directory you are going to specify in the create keystore statement must be already present.
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/app/oracle/product/12.1.0/dbhome_1/admin/CDB001/missing_directory' IDENTIFIED BY "0racle0racle"; ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/app/oracle/product/12.1.0/dbhome_1/admin/CDB001/missing_directory' IDENTIFIED BY "0racle0racle" * ERROR at line 1: ORA-46633: creation of a password-based keystore failed
To create the keystore under the path specified in the sqlnet.ora file use the following statement:
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet' IDENTIFIED BY "0racle0racle"; keystore altered.
Querying the V$ENCRYPTION_WALLET view you can see the location, status and type of the wallet.
SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID -------- -------------------------------------------------------- ------ ----------- --------- --------- ------ FILE /app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet CLOSED UNKNOWN SINGLE UNDEFINED 0
3) Opening a Software Keystore
To setup, configure and use encrypted tablespace or column the Oracle Wallet needs to be open.
The v$encryption_wallet view says the status of the wallet is closed so you need to open it using the following statement:
SQL> administer key management set keystore open identified by "0racle0racle"; keystore altered.
The status is now OPEN_NO_MASTER_KEY. This means that the wallet is open, but still a master key needs to be created.
SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID -------- -------------------------------------------------------- ------------------ ----------- --------- --------- ------ FILE /app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet OPEN_NO_MASTER_KEY UNKNOWN PASSWORD UNDEFINED 0
4) Setting the TDE Master Encryption Key in the Software Keystore
You need to set a master key for the Oracle wallet used in the TDE activities on tables or tablespace.
SQL> ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'tde_mek' IDENTIFIED BY "0racle0racle" WITH BACKUP USING 'tde_mek_backup'; keystore altered.
If you need to change the password of your Oracle Wallet because of your company’s security guidelines, you must use the WITH BACKUP option: in this way you are forced to take a backup of your “old” wallet.
SQL> ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY "0racle0racle" set "0racl30racle3"; ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY "0racle0racle" set "0racl30racle3" * ERROR at line 1: ORA-46631: keystore needs to be backed up
A change of the password doesn’t prevent the normal use of every TDE operations: they continue to work as usual with the new password without interruptions. You need to provide the old password and the new password.
SQL> ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY "0racle0racle" set "0racl30racle3" WITH BACKUP USING 'tde_mek_backup_001'; keystore altered.
5) Encrypt the Data
Once you have created an Oracle Wallet and set a TDE master key in it, you can proceed to encrypt your data. Let’s start creating a new encrypted tablespace first and then a column’s table. My current data files:
SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /app/oracle/oradata/CDB001/system01.dbf /app/oracle/oradata/CDB001/sysaux01.dbf /app/oracle/oradata/CDB001/undotbs01.dbf /app/oracle/oradata/CDB001/users01.dbf
The statement to create an encrypted tablespace:
SQL> create tablespace ts_encrypted datafile '/app/oracle/oradata/CDB001/ts_encrypted01.dbf' size 10M encryption default storage(encrypt); Tablespace created.
Information about the encrypted tablespace available in the database.
SQL> select a.name, b.TS#, b.ENCRYPTEDTS, b.ENCRYPTIONALG, b.CON_ID from V$TABLESPACE A, V$ENCRYPTED_TABLESPACES B 2 where A.ts# = B.ts#; NAME TS# ENCRYPTEDTS ENCRYPTIONALG CON_ID ------------ --- ----------- ------------- ------ TS_ENCRYPTED 5 AES128 YES 1
How is it possible to test if the data is encrypted or not ? I’m going to create a table on the USERS (unencrypted) tablespace and another on the TS_ENCRYPTED tablespace. Because the Oracle Wallet is already open I can create on the encrypted tablespace the t1_encrypted table and insert some rows in it.
SQL> create table t1_not_encrypted (text varchar2(255)) tablespace USERS;
Table created.
SQL> create table t1_encrypted (text varchar2(255)) tablespace TS_ENCRYPTED;
Table created.
SQL> insert into t1_not_encrypted values ('my name is marcov');
1 row created.
SQL> insert into t1_encrypted values ('the secrets of marcov');
1 row created.
SQL> commit;
Commit complete.
Flush the buffer cache to be sure all data is written to the datafiles.
SQL> alter system flush buffer_cache; System altered.
I’m able to grep and see the text on the USERS tablespace, but not that one on the TS_ENCRYPTED tablespace.
[oracle@localhost oracle]$ strings /app/oracle/oradata/CDB001/users01.dbf|grep "my name is" my name is marcov [oracle@localhost oracle]$ strings /app/oracle/oradata/CDB001/ts_encrypted01.dbf|grep "secrets" [oracle@localhost oracle]$
Let’s see what happens when the Oracle Wallet is closed. The following command closes an open Oracle Wallet.
SQL> administer key management set keystore close identified by "0racl30racle3"; keystore altered. SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID -------- -------------------------------------------------------- ------ ----------- --------- --------- ------ FILE /app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet CLOSED UNKNOWN SINGLE UNDEFINED 0 When the Oracle Wallet is closed you can query every tables but those based on an encrypted tablespace. SQL> select * from t1_not_encrypted; TEXT ----------------- my name is marcov SQL> select * from t1_encrypted; select * from t1_encrypted * ERROR at line 1: ORA-28365: wallet is not open
You have to open again the Oracle Wallet to successfully execute the query
SQL> administer key management set keystore open identified by "0racl30racle3"; keystore altered. SQL> select * from t1_encrypted; TEXT --------------------- the secrets of marcov
Let’s see how a closed Oracle Wallet affects an encrypted column of a table. I’m going to create a new table with two columns: one is encrypted and the other is not encrypted.
SQL> create table c##marcov.t2_column_encrypted (text varchar2(255), text_encrypted varchar2(255) encrypt) tablespace USERS;
Table created.
SQL> insert into c##marcov.t2_column_encrypted values ('this column is not encrypted', 'the secrets of marcov');
1 row created.
SQL> commit;
Commit complete.
The Oracle Wallet is closed.
SQL> administer key management set keystore close identified by "0racl30racle3"; keystore altered.
When the Oracle Wallet is closed I can able to query the non-encrypted column.
SQL> select text from c##marcov.t2_column_encrypted; TEXT ---------------------------- this column is not encrypted
But when I try to query the encrypted column it fails:
SQL> select * from c##marcov.t2_column_encrypted; select * from c##marcov.t2_column_encrypted * ERROR at line 1: ORA-28365: wallet is not open
I need first to open the Oracle Wallet
SQL> administer key management set keystore open identified by "0racl30racle3"; keystore altered.
Now I can query again the encrypted column of my table.
SQL> select * from c##marcov.t2_column_encrypted; TEXT TEXT_ENCRYPTED ---------------------------- --------------------- this column is not encrypted the secrets of marcov
