Loading....

In Oracle 12c Release 2, tablespaces can now be encrypted while they are online and in read-write mode.  In earlier releases, the tablespace had to be taken offline first or the database had to be in the mount state and not open.

In addition, now in 12.2 the SYSTEM, SYSAUX and UNDO tablespaces can also be encrypted as well.

We can also configure the Oracle database to automatically encrypt any new tablespaces that will be created in the future without having to specify any ENCRYPTION clause.

However for this online tablespace encryption, we do need some auxiliary disk space for the staging area where the conversion will occur.

New tablespaces can be encrypted as well as existing tablespaces.
 

Create a directory for the wallet

 
mkdir /u02/app/oracle/admin/salesdb/wallet

 

Create the keystore and encryption master key

 

SQL>  administer key management create keystore '/u02/app/oracle/admin/salesdb/wallet/' identified by oracle;

keystore altered.

SQL> !ls /u02/app/oracle/admin/salesdb/wallet
ewallet.p12

SQL> administer key management set keystore open identified by oracle;

keystore altered.

SQL> administer key management set key identified by oracle with backup;

keystore altered.

 
Note: Oracle performs the file conversion/encryption one at a time – so if we are encrypting the full database we need to have free space at least equal to size of largest datafile in the database.

 

Encrypt the tablespace

 

SQL>  alter tablespace users encryption online using 'AES192' encrypt file_name_convert=('/u03/app/oradata/salesdb/','/u03/app/oradata/salesdb/encr/');

Tablespace altered.


SQL> SELECT TS# , ENCRYPTIONALG,STATUS FROM v$encrypted_tablespaces;

       TS# ENCRYPT STATUS
---------- ------- ----------
	 6 AES192  ENCRYPTING
	

SQL> alter tablespace apex_data encryption online finish encrypt file_name_convert=('/u03/app/oradata/salesdb/','/u03/app/oradata/salesdb/encr/');

Tablespace altered.

SQL> SELECT TS# , ENCRYPTIONALG,STATUS FROM v$encrypted_tablespaces;

       TS# ENCRYPT STATUS
---------- ------- ----------
	 6 AES192  NORMAL


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u03/app/oradata/salesdb/system01.dbf
/u03/app/oradata/salesdb/archive_data01.dbf
/u03/app/oradata/salesdb/sysaux01.dbf
/u03/app/oradata/salesdb/undotbs01.dbf
/u03/app/oradata/salesdb/encr/apex_data01.dbf
/u03/app/oradata/salesdb/users01.dbf

 
 
We can also configure Oracle to automatically encrypt future tablespaces that will be created.

The ENCRYPT_NEW_TABLESPACES database initialization parameter controls how tablespaces created in the future will be encrypted.

Keeping an Oracle Cloud environment in mind, we would like to ensure that sensitive customer data hosted in the cloud is encrypted as far as possible.

The ENCRYPT_NEW_TABLESPACES parameter has three possible values – CLOUD_ONLY, ALWAYS and DDL.

CLOUD_ONLY transparently encrypts the tablespaces hosted in the Cloud using the AES128 algorithm if we do not specify the ENCRYPTION clause when we issue the CREATE TABLESPACE SQL statement. Note that this only applies to an Oracle Cloud environment and not to tablespaces created in an on premise database.

ALWAYS will automatically encrypt the tablespace using the AES128 algorithm if we omit the ENCRYPTION clause of CREATE TABLESPACE SQL statement, for both the Oracle Cloud and on-premise environments.

DDL will encrypt the tablespace using whatever we have specified in the ENCRYPTION clause of CREATE TABLESPACE SQL statement, for both Oracle Cloud and on-premise environments. So tablespaces will not be encrypted automatically and could be the ideal setting for hybrid Cloud environments with data located both in the Cloud as well as on-premise.

Let us see how tablespaces are automatically encrypted when we use the value ALWAYS for the parameter ENCRYPT_NEW_TABLESPACES.
 

SQL> show parameter encrypt

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
encrypt_new_tablespaces 	     string	 CLOUD_ONLY


SQL> alter system set encrypt_new_tablespaces=ALWAYS scope=both;

System altered.


SQL> create tablespace new_data datafile '/u03/app/oradata/salesdb/new_data01.dbf' size 100m;

Tablespace created.


SQL> SELECT TS# , ENCRYPTIONALG,STATUS FROM v$encrypted_tablespaces;

       TS# ENCRYPT STATUS
---------- ------- ----------
	 6 AES192  NORMAL
       8 AES128  NORMAL

 
 
if we like, we can change the encryption algorithm from AES128 to AES192.

Note that since we are encrypting the tablespace online, we need to create the encrypted data files in a new location where the conversion and encryption will occur.
 

SQL> alter tablespace new_data encryption online using 'AES192' rekey file_name_convert=('/u03/app/oradata/salesdb/','/u03/app/oradata/salesdb/encr/');

Tablespace altered.

SQL> SELECT TS# , ENCRYPTIONALG,STATUS FROM v$encrypted_tablespaces;

       TS# ENCRYPT STATUS
---------- ------- ----------
	 6 AES192  NORMAL
	 8 AES192  NORMAL

 
 
Let us now create a table in the encrypted tablespace. If we close the wallet, the table cannot be accessed which indicates that encryption has been enabled.
 

SQL> create table system.test_data tablespace new_data as select * from dba_tables;

Table created.


SQL> select count(*) from system.test_data;

  COUNT(*)
----------
      2595


SQL> administer key management set keystore close identified by oracle;

keystore altered.


SQL> select count(*) from system.test_data;
select count(*) from system.test_data
                            *
ERROR at line 1:
ORA-28365: wallet is not open

Please follow and like us:

Last Update: June 22, 2020  

June 22, 2017 196 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , , , , , ,

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?