Data column encryption in oracle database
We can refer the below oracle document for TDE security.
Configure the Software Keystore Location
Set the WALLET_ROOT and TDE_CONFIGURATION parameters.
SQL> ALTER SYSTEM SET WALLET_ROOT=’C:\app\orauser\admin\testdb\wallet\TDE’ SCOPE=SPFILE SID=’*’;
System altered.
Restart the database for effective the above parameter
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1073738560 bytes
Fixed Size 9036608 bytes
Variable Size 276824064 bytes
Database Buffers 780140544 bytes
Redo Buffers 7737344 bytes
Database mounted.
Database opened.
SQL> show parameter wallet_root
NAME TYPE VALUE
———————————— ———– ——————————
wallet_root string C:\APP\ORAUSER\ADMIN\TESTDB\WA
LLET\TDE
SQL> ALTER SYSTEM SET TDE_CONFIGURATION=”KEYSTORE_CONFIGURATION=FILE” SCOPE=BOTH SID=’*’;
System altered.
Creating a Password-Protected Software Keystore:
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY Oracle123Ace;
keystore altered.
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Oracle123Ace;
keystore altered.
SQL> SELECT STATUS FROM V$ENCRYPTION_WALLET;
STATUS
——————————
OPEN_NO_MASTER_KEY
Set the master encryption key by executing the following command:
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY Oracle123Ace WITH BACKUP;
keystore altered.
SQL> set linesize 120
SQL> col wrl_parameter format a45
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE
——————– ——————————————— —————————— ——————–
WALLET_OR KEYSTORE FULLY_BAC CON_ID
——— ——– ——— ———-
FILE C:\APP\ORAUSER\ADMIN\TESTDB\WALLET\TDE\tde\ OPEN PASSWORD
SINGLE NONE NO 0
Wallet files location :
Auto login wallet can able to enable. If its enable, it will automatically open the wallet
Data Column Encryption :
We are going to test encrypt the national_idenfier column in HR.PER_ALL_PEOPLE_F table.
Datatype of column in varchar(2).
Before Encryption,
Note : better to take backup the table before encryption.
SQL> ALTER TABLE HR.PER_ALL_PEOPLE_F MODIFY (national_identifier ENCRYPT);
ALTER TABLE HR.PER_ALL_PEOPLE_F MODIFY (national_identifier ENCRYPT)
*
ERROR at line 1:
ORA-28338: Column(s) cannot be both indexed and encrypted with salt
When we encrypt the column getting above error, because that column has an index. So we have to remove the salt while encrypt the column.
Salt, which is a random string added to data before encryption, is a way to strengthen the security of encrypted data.
SQL> ALTER TABLE HR.PER_ALL_PEOPLE_F MODIFY (national_identifier ENCRYPT NO SALT);
Table altered.
SQL> SELECT * FROM DBA_ENCRYPTED_COLUMNS;
OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL INTEGRITY_AL
—————————— —————————— —————————— —————————– — ————
HR PER_ALL_PEOPLE_F NATIONAL_IDENTIFIER AES 192 bits key NO SHA-1
Note:
TDE will only encrypt the data at storage and TDE will not mask the data in the select command output.
TDE is protecting the data at rest. It is encrypting the data in the datafiles so that in case they are obtained by other parties it will not be possible to access the clear text data. TDE cannot be used to obfuscate the data for the users who have privileges to access the tables. In the databases where TDE is configured any user who has access on an encrypted table will be able to see the data in clear text because Oracle will transparently decrypt the data for any user having the necessary privileges.
Reference:
How To Verify a Table Is Encrypted Using Transparent Data Encryption (TDE) and How To Test With the Table? (Doc ID 2132982.1)
Quick TDE Setup and FAQ (Doc ID 1251597.1)
DBA 