1. Add Encryption Wallet in sqlnet.ora Location $ORACLE_HOME/network/admin
[oracle@localhost admin]$ vi sqlnet.ora
ENCRYPTION_WALLET_LOCATION =
(SOURCE =(METHOD = FILE)(METHOD_DATA =
(DIRECTORY = /u01/wallet)))
2. Create encryption wallet location
mkdir -p /u01/wallet
3. Create Keystore and Key. And Open the Wallet
SQL*Plus: Release 12.2.0.1.0 Production on Thu May 24 14:36:10 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/wallet/' identified by Abc1234$#; keystore altered. SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Abc1234$# 2 ; keystore altered. SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY Abc1234$# with backup; keystore altered. SQL> select * FROM v$encryption_keys; -------------------------------------------------------------------------------- USER_ID KEY_USE KEYSTORE_TYPE -------------------------------------------------------------------------------------------------------------------------------- ---------------- ----------------- -------------------------------- ---------- AZeoliwGWU8gv2N8DKilil4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA 24-MAY-18 04.37.19.166968 AM +00:00 24-MAY-18 04.37.19.166972 AM +00:00 SYS 0 SYS 0 TDE SOFTWARE KEYSTORE LOCAL NO orcl 1502594573 orcl 1 4294967295 0 0 00000000000000000000000000000000 orcl 1502594573 orcl 1 4294967295 0 0 00000000000000000000000000000000 0
As we can see the encryption keystore is created and open
4. Lets Create Encrypted Tablespace and Check
CREATE TABLESPACE TEST_ENCRY
datafile '/u01/oradata/orcl/test_encry01.dbf' size 10M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
create user vormetric_enc identified by vormetric123 default tablespace TEST_ENCRY;
grant dba to vormetric_enc;
sqlplus vormetric_enc/vormetric123
CREATE TABLE vormetric_enc.Persons (
PersonID int ENCRYPT,
LastName varchar(255) ENCRYPT,
FirstName varchar(255) ENCRYPT,
Address varchar(255) ENCRYPT,
City varchar(255) ENCRYPT
);
insert into vormetric_enc.Persons values('898899','MOHAMMAD','SHADAB','SURREY HILLS','SYDNEY');
commit;
insert into vormetric_enc.Persons values('898899','CHRIS','MARTIN','SURREY HILLS','SYDNEY');
commit;
insert into vormetric_enc.Persons values('898899','YORKE','THOM','MANSFIELD','MANCHESTER');
commit;
## Test if you can see the data
strings /u01/oradata/orcl/test_encry01.dbf
dp|e
aj"C
W%O*$S
DrCh
O.RY('
nUqn
`odj
@`j4
Wl.8)[
mkpo
HyXr
AhT{K
rGp9Ia
(6R<U
ZoQQ
You will only see garbled character, which means your data is now fully encrypted at rest.
You can drop the test tablespace after testing
drop tablespace TEST_ENCRY including contents and datafiles;
5. Now just to do a proof of concept to check how easy it is to read data from a datafile (tablespace) if it is not encrypted
create tablespace test datafile '/u01/oradata/orcl/test01.dbf'size 10M;
create user vormetric identified by vormetric default tablespace test;
alter user vormetric identified by vormetric123;
grant dba to vormetric;
sqlplus vormetric/vormetric123
CREATE TABLE vormetric.Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
insert into vormetric.Persons values('898899','MOHAMMAD','SHADAB','SURREY HILLS','SYDNEY');
commit;
insert into vormetric.Persons values('898899','CHRIS','MARTIN','SURREY HILLS','SYDNEY');
commit;
insert into vormetric.Persons values('898899','YORKE','THOM','MANSFIELD','MANCHESTER');
commit;
## Test if you can see the data
strings /u01/oradata/orcl/test01.dbf
}|{z
YORCL
|\;:
TEST
AAAAAAAA
V1!
!V1#
YORKE
THOM MANSFIELD
MANCHESTER,
MARTIN
CHRIS
SURREY HILLS
SYDNEY,
MOHAMMAD
SHADAB
SURREY HILLS
SYDNEY
## As you can see data is so clearly visible from the datafile when it is not encrypted
You can drop the test tablespace after testing
drop tablespace test including contents and datafiles;
7. Enable Auto ~Open of wallet so you don’t have to specify the password every-time the database restarts
SQL> SELECT * FROM v$encryption_wallet; STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID ------------------------------ -------------------- --------- --------- ---------- FILE /u01/wallet/ OPEN PASSWORD SINGLE NO 0 The wallet is in Password mode which means each time you restart your database you have to specify the key explicitly to open the wallet. We can change this by creating autologin for the wallet, but make you sure you have you encryption key password safely stored somewhere. SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/wallet/' IDENTIFIED BY Abc1234$#; SQL> SELECT * FROM v$encryption_wallet; STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID ------------------------------ -------------------- --------- --------- ---------- FILE /u01/wallet/ OPEN AUTOLOGIN SINGLE NO 0
As you can see the Wallet is now AutoLogin