Summary:
This document will share the steps to implement TDE (transparent Database encryption) in the database in a Cluster. Required Steps that are needed to implement it:
- have the requirement to have an ACFS file system in place with proper setup there (see below).
- Have the requirement to have an adapted sqlnet.ora in a centralized location.
Details:
- Setup will require preparations. In our case, the first database was MYDB. Steps below will be identical for each database in scope. (Of course you need to change the database name according to the database in your scope).
Mysrv[3-4]dr is holding the MYDB database.
- Create a Diskgroup in normal Redundancy and call it TDE_KEYS.
- Prepare the acfs created mountpoint by creating a TDE_VOL.
- Mount the TDE_VOL as /app/oracle/admin/WALLET
- On the acfs mount (/app/oracle/admin/WALLET
- cd /app/oracle/admin/WALLET
mkdir MYDB,
ln -sf MYDB MYDB <1,2>
This acfs filesystem is shared between the clusternode.
With the mkdir you wil create an entry for the database you are about to encrypt , with the 2 links you will point both instances to the database directory , to keep all in a central place.
- Make sure you have a centralized Sqlnet.ora and adapt this one. As you can see the ORACLE_SID ( the instance name offers a flexible input in this way):
ENCRYPTION_WALLET_LOCATION =
(SOURCE =(METHOD = FILE)(METHOD_DATA =
(DIRECTORY = /app/oracle/admin/WALLET/$ORACLE_SID/ )
)
)
- In the database first check:
SQL> select * from v$encryption_wallet;
WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/app/oracle/admin/WALLET/MYDB1/
NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED
0
- With the below command you will create the keystore and you will give the keystore a password.
SQL> administer key management create keystore ‘/app/oracle/admin/WALLET/MYDB/’ identified by “mypwd19!”;
## Once that command is given Sqlplus will report:
keystore altered.
## TIP Be sure the correct sqlnet is read (links?)
- run below command to open your keystore:
SQL> administer key management set keystore open identified by “mypwd19!”;
## this will report in sqlplus:
keystore altered.
- Now is a good time to Check the status of your wallet:
SQL> select * from v$encryption_wallet;
WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/app/oracle/admin/WALLET/MYDB1/
OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED
0
- Check your encryption keys.
SQL> select key_id,activation_time from v$encryption_keys;
## sql will 1st time report:
no rows selected
## On Os you should already see the wallet similar to below:
SQL> host
oracle@Mysrv3dr:/app/oracle/admin/WALLET/MYDB []# ls -lisa
77 4 -rw——-. 1 oracle dba 2555 Sep 13 05:51 ewallet.p12
- In Sqlplus now it is time to create the key:
SQL> administer key management create key identified by “mypwd19!” with backup;
- When you check your encryption keys again now:
SQL> select key_id,activation_time from v$encryption_keys;
KEY_ID
——————————————————————————
ACTIVATION_TIME
—————————————————————————
NcC1701D+mbkK+6v92xdM/qIxcXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
- In sqlplus give below command:
SQL> administer key management use key ‘ NcC1701D+mbkK+6v92xdM/qIxcXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
‘ identified by “mypwd19!” with backup;
## sqlplus will report:
keystore altered.
- Check again your encryption keys again to get something similar to below:
SQL> select key_id,activation_time from v$encryption_keys;
## now you will see an activation_time Too:
KEY_ID
——————————————————————————
ACTIVATION_TIME
—————————————————————————
NcC1701D+mbkK+6v92xdM/qIxcXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
13-SEP-19 06.01.49.995963 AM +02:00
- As a test create a small tablspace:
SQL> create tablespace ENC datafile ‘+MYDB_DATA’ size 10M encryption using ‘AES256’ default storage(encrypt);
## Check this with:
select tablespace_name,encrypted from dba_tablespaces order by 1;
## Sqlplus should report something like this:
SQL> select tablespace_name,encrypted from dba_tablespaces order by 1;
TABLESPACE_NAME ENC
—————————— —
ENC YES
Now you are all set to create all required application tablespaces with these extra parameters encryption using ‘AES256’ default storage(encrypt).
Automatically open the wallet
Note: Basically a wallet needs to be open before you can access the database. This however can and should be altered: When opening your database this should automatically open the wallet:
- In sqlplus check:
SQL> select * from v$encryption_wallet;
WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/app/oracle/admin/WALLET/MYDB1/
OPEN PASSWORD SINGLE NO
0
- Give this command:
SQL> administer key management create auto_login keystore from keystore ‘/app/oracle/admin/WALLET/MYDB/’ identified by “mypwd19!”;
## sqlplus will report:
keystore altered.
## On OS you will see :
oracle@Mysrv3dr:/app/oracle/admin/WALLET/MYDB [MYDB1]# ls -lisa
total 48
74 4 drwxr-xr-x. 2 oracle dba 4096 Sep 13 06:37 .
2 4 drwxrwxr-x. 5 oracle dba 4096 Sep 13 05:44 ..
82 12 -rw——-. 1 oracle dba 5304 Sep 13 06:37 cwallet.sso
78 4 -rw——-. 1 oracle dba 2555 Sep 13 05:55 ewallet_2019091303553484.p12
80 4 -rw——-. 1 oracle dba 3803 Sep 13 05:57 ewallet_2019091303572233.p12
81 8 -rw——-. 1 oracle dba 5067 Sep 13 06:01 ewallet_2019091304014993.p12
77 12 -rw——-. 1 oracle dba 5259 Sep 13 06:01 ewallet.p12
- And in Sqlplus too you will see a change.
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/app/oracle/admin/WALLET/MYDB1/
OPEN AUTOLOGIN SINGLE NO
0
Appendix 1 Create an acfs file system:
Idea was to setup acfs via asmca. This however did not work in first implementation due to error because ora.proxy_advm is offline on some nodes.
It should be:
crsctl stat res ora.proxy_advm -t
——————————————————————————–
Name Target State Server State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.proxy_advm
ONLINE ONLINE Mysrv3dr STABLE
ONLINE ONLINE Mysrv4dr STABLE
If the status is offline check if the acfsmodule are loaded: lsmod | grep acfs
oracleacfs 4840664 2
oracleoks 663240 2 oracleacfs,oracleadvm
if they are not loaded on the local node: log in as root and perform
$GRID_HOME/bin/csrctl stop crs
$GRID_HOME/bin/acfstool install
$GRID_HOME/bin/crsctl start crs
Modules should be loaded now. Volume can be created in asmca (high protection, TDE_VOL1, 1GB)
The volume will show up under /dev/asm (/dev/asm/tde_vol1-301 in this case).
Mkdir -p /app/oracle/admin/wallet
Cd /app/oracle
Chown –R oracle:dba admin
/sbin/mkfs -t acfs /dev/asm/tde_vol1-302
Register in clusterware:
/sbin/acfsutil registry -a /dev/asm/tde_vol1-302 /app/oracle/admin/WALLET
Mathijs Bruggink 