The following article describes the steps to create the wallet and perform a tablepsace encryption.
# mkdir -p /u01/app/oracle/product/12.1.0/db1/network/admin/wallet # chown oracle:oinstall /u01/app/oracle/product/11.2.0/db1/network/admin/wallet # chmod -Rf 700 /u01/app/oracle/product/12.1.0/db1/network/admin/wallet
2: Specify wallet location using the sqlnet.ora
Add the ENCRYPTION_WALLET_LOCATION attribute
[oracle@oraclehost admin]$ cat sqlnet.ora ENCRYPTION_WALLET_LOCATION (SOURCE= (METHOD=FILE (METHOD_DATA= (DIRECTORY=/u01/app/oracle/product/12.1.0/db1/network/admin/wallet)))
3: Initialize and Create the master
key in sqlplus in CDB$ROOT:
This will create a file ewallet.p12 in the wallet directory
SQL> administer key management create keystore
‘/u01/app/oracle/product/12.1.0/db1/network/admin/wallet’ IDENTIFIED BY some_password;
4. Validate the
status of keystore, which must appear as closed at this step
SQL> select wrl_type,status,wallet_type,wallet_order,fully_backed_up,con_id,wrl_parameter from v$encryption_wallet; WRL_TYPE STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID WRL_PARAMETER -------- ---------- ------------ ----------- --------- -------- ----------------------------------------- FILE CLOSED UNKNOWN SINGLE UNDEFINED 0 /u01/app/oracle/product/12.1.0/db_1/network/admin/wallet
4. Open the wallet now
Once the wallet is open, the master key becomes available to the database. There are 2 ways to achieve this.
SQL> administer key management set keystore open identified by password#here!123 CONTAINER = ALL; OR SQL> administer key management set keystore open identified by password#here!123;
Once the wallet is open, we can now perform below TDE operations.
- Column encryption
- Tablespace encryption
SQL> select wrl_type,status,wallet_type,wallet_order,fully_backed_up,con_id,wrl_parameter from v$encryption_wallet; WRL_TYPE STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID WRL_PARAMETER -------- ------------------ ------------ ----------- --------- -------- ----------------------------------------- FILE OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 0 /u01/app/oracle/product/12.1.0/db_1/network/admin/wallet
5. Auto login key store is created now.
But this lead to an issue (MOS 1944507.1). Hence a master encryption key is created before creating auto login wallet.
SQL> administer key management set key identified by password_here with backup ; keystore altered.
This will now change wallet status from open_no_master_key to open.
SQL> select wrl_type,status,wallet_type,wallet_order,fully_backed_up,con_id,wrl_parameter from v$encryption_wallet; WRL_TYPE STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID WRL_PARAMETER -------- ------------------ ------------ ----------- --------- -------- ----------------------------------------- FILE OPEN PASSWORD SINGLE NO 0 /u01/app/oracle/product/12.1.0/db_1/network/admin/wallet
6. Finally create the auto login key store so that key store is auto opened when the database starts.
SQL> administer key management create local auto_login keystore from keystore '/u01/app/oracle/product/12.1.0/db1/network/admin/wallet' identified by passwordhere999; keystore altered.
This results in creation of this is a key store file (i.e, wallet file) and the ecwallet.sso file, also wallet type will be changed to local_autologin
[oracle@oraclehost wallet]$ lltotal 4 -rw-r--r-- 1 oracle oinstall 4165 Aug 13 20:15 cwallet.sso
-rw-r--r-- 1 oracle oinstall 2408 Aug 13 20:16 ewallet_2021081309590519.p12 -rw-r--r-- 1 oracle oinstall 4112 Aug 13 20:16 ewallet.p12 [oracle@oraclehost wallet]$
Now, verify if TDE is working by performing the following tests
Scope :
- A tablespace is created with encryption enabled.
- Inserts some rows into tables which are residing in those tablespaces.
- Also to flush the buffer cache and performing few manual checkpoints may require to force DBWR process to write to data files.
Create two tablespaces
- data_notEncrypted
- data_encrypted
SQL> create tablespace data_notEncrypted datafile '/u01/app/oracle/oradata/datafiles/data_notEncrypted.dbf' size 1G; Tablespace created. SQL> create tablespace data_encrypted datafile '/u01/app/oracle/oradata/datafiles/data_encrypted.dbf' size 1G ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT); Tablespace created. SQL> select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces; TABLESPACE_NAME ENC -------------------- -- DATA_NOTENCRYPTED NO DATA_ENCRYPTED YES 2 rows selected.
Create tables in both the tablespaces and insert few rows in those tables
create table table_enc (TEXT varchar2(200)) tablespace encrypt_data; create table table_not_enc (TEXT varchar2(200)) tablespace noencrypt_data;
insert into table_enc values ('some text here'); insert into table_not_enc values ('some text here');
SQL> select * from enctable; TEXT ------------------------------ some text here
SQL> select * from table_not_enc; TEXT ------------------------------ some text here
SQL> alter system checkpoint ;
Use the "Strings" command to check the data file contents.
SQL> !strings /u01/app/oracle/oradata/datafiles/data_notEncrypted.dbf }|{z DATAFILES DATA_NOTENCRYPTED some text here
Following by the data file which to the encrypted
tablespace, which demonstrates that
SQL> !strings /u01/app/oracle/oradata/datafiles/data_encrypted.dbf }|{z DATAFILES DATA_ENCRYPTED .ETW DU*&.
Hope you got some idea on tablespace encryption. See you all soon with another good blog.... :)
Read more in Oracle
Datapump Architecture and Master Table
Datapump Frequently Asked scenarios
Read more in Postgres
Configure Streaming Replication in PostgreSQL
Installation and configure Postgres 13 on RHEL
All about Physical Replication and Log shipping in Postgres
Possible ways to recover space from deleted rows with insufficient disk space
Streaming-Replication Sync and Async, benefits of streaming replication over Log-shipping
The person who says it cannot be done should not interrupt the person who is doing it.
–Chinese Proverb
No comments:
Post a Comment