Performing Cryptographic Operations on Existing Tablespaces

This chapter covers the steps to encrypt and decrypt the existing online and offline tablespaces. It also covers how to perform rekey operation on the existing online tablespaces.

This chapter covers the following topics:

• Overview

• Encrypting Existing Offline Tablespaces

• Decrypting Existing Offline Tablespaces

• Encrypting Existing Online Tablespaces

• Decrypting Existing Online Tablespaces

• Rekeying Existing Online Tablespaces

Overview

Tablespaces are the logical storage units which stores all data of a database. Tablespaces can be either online or offline when the database is open. Online tablespaces means that the data is available for the users.

Database administrator can make the tablespace offline for maintenance or backup purpose.

Encrypting Existing Offline Tablespaces

To encrypt the existing offline tablespaces:

1. Create a tablespace.

   CREATE TABLESPACE ENCRYPTED_SPACE DATAFILE '$ORACLE_HOME/dbs/encspace.dbf' SIZE 1M;
   

2. Make the tablespace offline.

   ALTER TABLESPACE ENCRYPTED_SPACE OFFLINE NORMAL;
   

3. Encrypt the tablespace.

   ALTER TABLESPACE ENCRYPTED_SPACE ENCRYPTION OFFLINE ENCRYPT;
   

   Note

   For Oracle version 19c, you can specify desired keysize in this step. Supported keysizes are AES192 and AES256. For example:
   ALTER TABLESPACE ENCRYPTED_SPACE ENCRYPTION OFFLINE USING 'AES256' ENCRYPT;

4. Take the tablespace online again.

   ALTER TABLESPACE ENCRYPTED_SPACE ONLINE;
   

Decrypting Existing Offline Tablespaces‌‌

To decrypt the existing offline tablespaces:

1. Take the tablespace that has been previously encrypted and make the tablespace offline.

   ALTER TABLESPACE ENCRYPTED_SPACE OFFLINE NORMAL;
   

2. Decrypt the tablespace.

   ALTER TABLESPACE ENCRYPTED_SPACE ENCRYPTION OFFLINE DECRYPT;
   

3. Take the tablespace online again.

   ALTER TABLESPACE ENCRYPTED_SPACE ONLINE;
   

Encrypting Existing Online Tablespaces

To encrypt the existing online tablespaces:

1. Connect to the database as a user who has SYSDBA administrative privilege.
   For example: sqlplus sec_admin as sysdba Enter Password: password

   Note

   If you plan to encrypt the SYSTEM and SYSAUX tablespaces, you must have the SYSDBS administrative rights.

2. Make sure that the COMPATIBLE initialization parameter is set to the Oracle version installed on your machine. Use the SHOW PARAMETER command to check the current setting of the initialization parameter.

3. If the database is open, ensure that it is open in the read-write mode, else open it in read-write mode.

   ALTER DATABASE OPEN READ WRITE;
   

   Note

   You can query the STATUS column of the V$INSTANCE dynamic view to find if a database is open and the OPEN_MODE column of the V$DATABASE view to find if it is in read-write mode.

4. Ensure that the auxiliary space is at least the same size as the largest datafile of this tablespace. For example, if the size of the largest datafile of the tablespace is 32 GB, make sure that the auxiliary space is also allocated a space ≥ 32 GB.

   Note

   The size restriction of the auxiliary space is defined to cater the database requirement. To find the space used by the datafile, query the BYTES or BLOCKS column of the V$DATAFILE dynamics performance view.

5. Create a tablespace.

   CREATE TABLESPACE ENCRYPT_SPACE DATAFILE '$ORACLE_HOME/dbs/encspace.dbf' SIZE 1M;
   

6. Create a table CUSTOMERS, and insert values in it.

   CREATE TABLE CUSTOMERS (ID INT, NAME VARCHAR (200), CREDIT_LIMIT INT) TABLESPACE ENCRYPT_SPACE;
   insert into CUSTOMERS values (1,'Bob',60000);
   

7. Encrypt the tablespace.

   ALTER TABLESPACE ENCRYPT_SPACE ENCRYPTION ONLINE USING 'AES256' ENCRYPT FILE_NAME_CONVERT = ('encspace.dbf', 'encspace_enc.dbf');
   

   Note

   At this step, you might encounter an error- ORA-00241: operation disallowed: control file is not yet checked against data dictionary
   As a workaround to this problem, shutdown and restart the database, and then execute this command:ALTER TABLESPACE ENCRYPT_SPACE ENCRYPTION ONLINE FINISH ENCRYPT FILE_NAME_CONVERT = ('encspace.dbf', 'encspace_ enc.dbf');

Decrypting Existing Online Tablespaces

To decrypt the existing online tablespaces, take a tablespace that has been previously encrypted and run the following statement.

ALTER TABLESPACE ENCRYPT_SPACE ENCRYPTION ONLINE DECRYPT FILE_NAME_CONVERT=('encspace_enc.dbf', 'encspace.dbf');

Rekeying Existing Online Tablespaces

Rekeying operation on existing online tablespaces is performed based on the status returned by V$ENCRYPTED_TABLESPACES dynamic view. There can be two cases:

• If the key version status of the tablespace is normal:

  Specify the new algorithm to perform REKEY operation. For example:

  ALTER TABLESPACE ENCRYPT_SPACE ENCRYPTION USING 'AES192' REKEY FILE_NAME_CONVERT = ('encspace_enc.dbf', 'encspace.dbf');
  

• If the key version status of the tablespace is encrypting, decrypting, or rekeying

  Use the FINISH clause. For example:

  ALTER TABLESPACE ENCRYPT_SPACE ENCRYPTION ONLINE FINISH REKEY FILE_NAME_CONVERT = ('encspace_enc.dbf', 'encspace.dbf');