3 Configuring Transparent Data Encryption
You can configure software or external keystores, for use on both individual table columns or entire tablespaces.
- Configuring a Software Keystore
A software keystore is a container for the TDE master encryption key, and it resides in the software file system. - Configuring an External Keystore
There are two types of external keystores that Oracle Database supports: Oracle Key Vault keystores and OCI Vault - Key Management keystores. - Encrypting Columns in Tables
You can use Transparent Data Encryption to encrypt individual columns in database tables. - Encryption Conversions for Tablespaces and Databases
You can perform encryption operations on both offline and online tablespaces and databases. - Transparent Data Encryption Data Dynamic and Data Dictionary Views
You can query a set of dynamic and data dictionary views to find more information about Transparent Data Encryption (TDE) data.
Parent topic: Using Transparent Data Encryption
3.1 Configuring a Software Keystore
A software keystore is a container for the TDE master encryption key, and it resides in the software file system.
- About Configuring a Software Keystore
A software keystore is a container that stores the Transparent Data Encryption master encryption key. - Step 1: Set the Keystore Location in the sqlnet.ora File
The first step you must take to configure a software keystore is to designate a location for it in thesqlnet.ora
file. - Step 2: Create the Software Keystore
After you have specified a directory location for the software keystore, you can create the keystore. - Step 3: Open the Software Keystore
Depending on the type of keystore you create, you must manually open the keystore before you can use it. - Step 4: Set the Software TDE Master Encryption Key
Once the keystore is open, you can set a TDE master encryption key for it. - Step 5: Encrypt Your Data
After you complete the software keystore configuration, you can begin to encrypt data.
Parent topic: Configuring Transparent Data Encryption
3.1.1 About Configuring a Software Keystore
A software keystore is a container that stores the Transparent Data Encryption master encryption key.
Before you can configure the keystore, you first must define a location for it in the sqlnet.ora
file. There is one keystore per database, and the database locates this keystore by checking the keystore location that you define in the sqlnet.ora
file. You can create other keystores, such as copies of the keystore and export files that contain keys, depending on your needs. However, you must never remove or delete the keystore that you configured in the sqlnet.ora
location, nor replace it with a different keystore.
After you configure the software keystore location in the sqlnet.ora
file, you can log in to the database instance to create and open the keystore, and then set the TDE master encryption key. After you complete these steps, you can begin to encrypt data.
Parent topic: Configuring a Software Keystore
3.1.2 Step 1: Set the Keystore Location in the sqlnet.ora File
The first step you must take to configure a software keystore is to designate a location for it in the sqlnet.ora
file.
- About the Keystore Location in the sqlnet.ora File
Oracle Database checks thesqlnet.ora
file for the directory location of the keystore. - Configuring the sqlnet.ora File for a Software Keystore Location
Use thesqlnet.ora
file to configure the keystore location for a regular file system, for multiple database access, and for use with Oracle Automatic Storage Management (ASM). - Configuring an External Store for a Keystore Password
An external store for a keystore password stores the keystore password in a centrally accessed and managed location. - Example: Configuring a Software Keystore for a Regular File System
You can configure a software keystore for a regular file system. - Example: Configuring a Software Keystore When Multiple Databases Share the sqlnet.ora File
You can configure multiple databases to share thesqlnet.ora
file. - Example: Configuring a Software Keystore for Oracle Automatic Storage Management
You can configuresqlnet.ora
for an Automatic Storage Management (ASM) file system - Example: Configuring a Software Keystore for an Oracle Automatic Storage Management Disk Group
You can configuresqlnet.ora
for an Oracle Automatic Storage Management (ASM) disk group.
Parent topic: Configuring a Software Keystore
3.1.2.1 About the Keystore Location in the sqlnet.ora File
Oracle Database checks the sqlnet.ora
file for the directory location of the keystore.
This applies to whether the keystore is a software keystore (wallet), Oracle Key Vault, or Oracle Cloud Infrastructure (OCI) key management service (KMS). You must edit the sqlnet.ora
file to define a directory location for the keystore that you plan to create. Ensure that this directory exists beforehand. Preferably, this directory should be empty.
Note the following behavior when you must edit the sqlnet.ora
file in an Oracle Real Application Clusters (Oracle RAC) or a multitenant environment:
-
In an Oracle RAC environment: If you are using the
srvctl
utility and if you want to include environment variables in thesqlnet.ora
configuration file, then you must set these environment variables in both the operating system and thesrvctl
environment. In Oracle Real Appications Clusters (Oracle RAC), Oracle Database only supports a shared software keystore (wallet) on a shared file system, such as the following:- Oracle Automatic Storage Management (Oracle ASM)
- Oracle ASM Cluster File System (Oracle ACFS)
- Oracle Key Vault
- OCI KMS
-
In a multitenant environment: If the
sqlnet.ora
file is in the CDB root, then the keystore location will be set for the entire CDB environment.
In the sqlnet.ora
file, you must set the ENCRYPTION_WALLET_LOCATION
parameter to specify the keystore location. When determining which keystore to use, Oracle Database searches for the keystore location in the following places, in this order:
-
It attempts to use the keystore in the location specified by the parameter
ENCRYPTION_WALLET_LOCATION
in thesqlnet.ora
file. -
If the
ENCRYPTION_WALLET_LOCATION
parameter is not set, then Oracle Database looks for a keystore at the default database location, which is${ORACLE_BASE}/admin/${DB_UNIQUE_NAME}/wallet
. (DB_UNIQUE_NAME
is the unique name of the database specified in the initialization parameter file.) You can check the location and status of the keystore in theV$ENCRYPTION_WALLET
dynamic view. When the keystore location is not set in thesqlnet.ora
file, then theV$ENCRYPTION_WALLET
view displays the default location.
By default, the sqlnet.ora
file is located in the ${ORACLE_HOME}/network/admin
directory or in the location set by the TNS_ADMIN
environment variable. Ensure that you have properly set the TNS_ADMIN
environment variable to point to the correct sqlnet.ora
file.
Related Topics
Parent topic: Step 1: Set the Keystore Location in the sqlnet.ora File
3.1.2.2 Configuring the sqlnet.ora File for a Software Keystore Location
Use the sqlnet.ora
file to configure the keystore location for a regular file system, for multiple database access, and for use with Oracle Automatic Storage Management (ASM).
-
To create a software keystore on a regular file system, use the following format when you edit the
sqlnet.ora
file:ENCRYPTION_WALLET_LOCATION= (SOURCE= (METHOD=FILE) (METHOD_DATA= (DIRECTORY=path_to_keystore)))
If the
path_to_keystore
will contain an environment variable, then set this variable in the environment where the database instance is started and before you start the database. If you are using thesrvctl
utility to start the database, then set the environment variable in thesrvctl
environment as well, using the following command:srvctl setenv database -db database_name -env "environment_variable_name=environment_variable_value"
-
To create a software keystore on a shared file system for Oracle Real Application Clusters (Oracle RAC), use the following format when you edit the
sqlnet.ora
file:ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = +DATA/$ORACLE_UNQNAME/tde)))
If the
DIRECTORY
setting will include an environment variable, then set this variable in the environment where the database instance is started and before you start the database. If you are using thesrvctl
utility to start the database, then set the environment variable in thesrvctl
environment as well, using a command similar to the following, assuming the name of the Oracle RAC instance isFINRAC
:srvctl setenv database -db FINRAC -t "ORACLE_UNQNAME=FINRAC, ORACLE_BASE=/u01/opt/oracle"
Parent topic: Step 1: Set the Keystore Location in the sqlnet.ora File
3.1.2.3 Configuring an External Store for a Keystore Password
An external store for a keystore password stores the keystore password in a centrally accessed and managed location.
ADMINISTER KEY MANAGEMENT
commands that do not change the TDE configuration can use this hidden password by replacing it in the SQL*Plus command line with the EXTERNAL STORE
clause. An external store for a password is useful for situations in which you use automated tools to perform Transparent Data Encryption operations that require a password, when the scripts that run the automated tools include hard-coded passwords. To avoid hard-coding the password in a script, you can store this password in an external store on the database server. In a multitenant environment, all PDBs use the hidden password of the root container.
IDENTIFIED BY EXTERNAL STORE
clause in the ADMINISTER KEY MANAGEMENT
statement.
Afterward, you must use the EXTERNAL STORE
clause in the ADMINISTER KEY MANAGEMENT
statement for the following operations: opening, closing, backing up the keystore; adding, updating, or deleting a secret keystore; creating, using, rekeying, tagging, importing, exporting encryption keys.
For example:
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY EXTERNAL STORE;
You can change or delete external keystore passwords by using the ADMINISTER KEY MANAGEMENT UPDATE CLIENT SECRET
statement or the ADMINISTER KEY MANAGEMENT DELETE CLIENT SECRET
statement.
Related Topics
Parent topic: Step 1: Set the Keystore Location in the sqlnet.ora File
3.1.2.4 Example: Configuring a Software Keystore for a Regular File System
You can configure a software keystore for a regular file system.
The following example shows how to configure a software keystore location in the sqlnet.ora
file for a regular file system in which the database name is orcl
.
ENCRYPTION_WALLET_LOCATION= (SOURCE= (METHOD=FILE) (METHOD_DATA= (DIRECTORY=/etc/ORACLE/WALLETS/orcl)))
Parent topic: Step 1: Set the Keystore Location in the sqlnet.ora File
3.1.2.5 Example: Configuring a Software Keystore When Multiple Databases Share the sqlnet.ora File
You can configure multiple databases to share the sqlnet.ora
file.
The following example shows how to configure a software keystore location when multiple databases share the sqlnet.ora
file.
ENCRYPTION_WALLET_LOCATION= (SOURCE= (METHOD=FILE) (METHOD_DATA= (DIRECTORY=/etc/ORACLE/WALLETS/$ORACLE_SID/)))
Parent topic: Step 1: Set the Keystore Location in the sqlnet.ora File
3.1.2.6 Example: Configuring a Software Keystore for Oracle Automatic Storage Management
You can configure sqlnet.ora
for an Automatic Storage Management (ASM) file system
The following example shows how to configure a software keystore location in the sqlnet.ora
file for an ASM file system:
ENCRYPTION_WALLET_LOCATION= (SOURCE= (METHOD=FILE) (METHOD_DATA= (DIRECTORY=+disk1/mydb/wallet)))
Parent topic: Step 1: Set the Keystore Location in the sqlnet.ora File
3.1.2.7 Example: Configuring a Software Keystore for an Oracle Automatic Storage Management Disk Group
You can configure sqlnet.ora
for an Oracle Automatic Storage Management (ASM) disk group.
The following format shows how to configure a software keystore if you want to create a software keystore location on an ASM disk group:
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=+ASM_file_path_of_the_diskgroup)))
Parent topic: Step 1: Set the Keystore Location in the sqlnet.ora File
3.1.3 Step 2: Create the Software Keystore
After you have specified a directory location for the software keystore, you can create the keystore.
- About Creating Software Keystores
There are three different types of software keystores. - Creating a Password-Based Software Keystore
A password-protected software keystore requires a password, which is used to protect the TDE master keys. - Creating an Auto-Login or a Local Auto-Login Software Keystore
As an alternative to password-based keystores, you can create either an auto-login or local auto-login software keystore.
Parent topic: Configuring a Software Keystore
3.1.3.1 About Creating Software Keystores
There are three different types of software keystores.
You can create password-based software keystores, auto-login software keystores, and local auto-login software keystores.
Be aware that executing the query SELECT * FROM V$ENCRYPTION_WALLET
will automatically open an auto-login software keystore. For example, suppose you have a password-based keystore and an auto-login keystore. If the password-based keystore is open and you close the password-based keystore and then query the V$ENCRYPTION_WALLET
view, then the output will indicate that a keystore is open. However, this is because V$ENCRYPTION_WALLET
opened up the auto-login software keystore and then displayed the status of the auto-login keystore.
In a multitenant environment, you can create a secure external store for the software keystore. This feature enables you to hide the password from the operating system: it removes the need for storing clear-text keystore passwords in scripts or other tools that can access the database without user intervention, such as overnight batch scripts. The location for this keystore is set by the EXTERNAL_KEYSTORE_CREDENTIAL_LOCATION
initialization parameter. In a multitenant environment, different PDBs can access this external store location when you run the ADMINISTER KEY MANAGEMENT
statement using the IDENTIFIED BY EXTERNAL STORE
clause. This way, you can centrally locate the password and then update it only once in the external store.
Related Topics
Parent topic: Step 2: Create the Software Keystore
3.1.3.2 Creating a Password-Based Software Keystore
A password-protected software keystore requires a password, which is used to protect the TDE master keys.
ADMINISTER KEY MANAGEMENT statement
.
-
Log in to the database instance as a user who has been granted the
ADMINISTER KEY MANAGEMENT
orSYSKM
privilege.In a multitenant environment, log in to the root. For example:
sqlplus c##sec_admin as syskm Enter password: password Connected.
If SQL*Plus is already open and you had modified the
sqlnet.ora
file during this time, then reconnect to SQL*Plus. The database session must be changed before thesqlnet.ora
changes can take effect. -
Run the
ADMINISTER KEY MANAGEMENT
SQL statement to create the keystore.The syntax is as follows:
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'keystore_location' IDENTIFIED BY software_keystore_password;
In this specification:
-
keystore_location
is the path to the keystore directory location of the password-based keystore for which you want to create the auto-login keystore (for example,/etc/ORACLE/WALLETS/orcl
). Enclose thekeystore_location
setting in single quotation marks (' '). To find this location, you can query theWRL_PARAMETER
column of theV$ENCRYPTION_WALLET
view. (If the keystore was not created in the default location, then theSTATUS
column of theV$ENCRYPTION_WALLET
view isNOT_AVAILABLE
.) -
software_keystore_password
is the password of the keystore that you, the security administrator, creates.
For example, to create the keystore in the
/etc/ORACLE/WALLETS/orcl
directory:ADMINISTER KEY MANAGEMENT CREATE KEYSTORE
'/etc/ORACLE/WALLETS/orcl
' IDENTIFIED BY password; keystore altered.After you run this statement, the
ewallet.p12
file, which is the keystore, appears in the keystore location. -
Parent topic: Step 2: Create the Software Keystore
3.1.3.3 Creating an Auto-Login or a Local Auto-Login Software Keystore
As an alternative to password-based keystores, you can create either an auto-login or local auto-login software keystore.
Both of these keystores have system-generated passwords. They are also PKCS#12-based files. The auto-login software keystore can be opened from different computers from the computer where this keystore resides, but the local auto-login software keystore can only be opened from the computer on which it was created. Both the auto-login and local auto-login keystores are created from the password-protected software keystores. Creating any of them does not require database downtime.
Before you begin this procedure, ensure that you complete the procedure described in Step 1: Set the Keystore Location in the sqlnet.ora File.
-
Log in to the database instance as a user who has been granted the
ADMINISTER KEY MANAGEMENT
orSYSKM
privilege.In a multitenant environment, log in to the root. For example:
sqlplus c##sec_admin as syskm Enter password: password Connected.
If SQL*Plus is already open and you had modified the
sqlnet.ora
file during this time, then reconnect to SQL*Plus. The database session must be changed before thesqlnet.ora
changes can take effect. -
Create a password-based software keystore.
For example:
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/orcl' IDENTIFIED BY keystore_password;
-
Run the
ADMINISTER KEY MANAGEMENT
SQL statement to create the keystore.The syntax is as follows:
ADMINISTER KEY MANAGEMENT CREATE [LOCAL] AUTO_LOGIN KEYSTORE FROM KEYSTORE 'keystore_location' IDENTIFIED BY software_keystore_password;
In this specification:
-
LOCAL
enables you to create a local auto-login software keystore. Otherwise, omit this clause if you want the keystore to be accessible by other computers.LOCAL
creates a local auto-login wallet file,cwallet.sso
, and this wallet will be tied to the host on which it was created. For an Oracle Real Application Clusters (Oracle RAC) environment, omit theLOCAL
keyword, because each Oracle RAC node has a different host name. If you configure a local auto-login wallet for the Oracle RAC instance, then only the first Oracle RAC node, where thecwallet.sso
file was created, would be able to access the software keystore. If you try to open the keystore from another node instead of from that first node, there would be a problem auto-openingcwallet.sso
, and so it would result in a failure to auto-open the software keystore. This restriction applies if you are using a shared location to hold thecwallet.sso
file for the Oracle RAC cluster, because usingLOCAL
only works if you have a separatecwallet.sso
file (containing the same credentials) on each node of the Oracle RAC environment. -
keystore_location
is the path to the directory location of the password-based keystore for which you want to create the auto-login keystore (for example,/etc/ORACLE/WALLETS/orcl
). Enclose this setting in single quotation marks (' '). To find this location, query theWRL_PARAMETER
column of theV$ENCRYPTION_WALLET
view. -
software_keystore_password
is the password-based keystore for which you want to create the auto-login keystore.
For example, to create an auto-login software keystore of the password-based keystore that is located in the
/etc/ORACLE/WALLETS/orcl
directory:ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE
FROM KEYSTORE '/etc/ORACLE/WALLETS/orcl
' IDENTIFIED BY password; keystore altered.After you run this statement, the
cwallet.sso
file appears in the keystore location. Theewallet.p12
file is the password-based wallet. -
Note:
Do not remove the PKCS#12
wallet (ewallet.p12
file) after you create the auto login keystore (.sso
file). You must have the PKCS#12
wallet to regenerate or rekey the TDE master encryption key in the future. By default, this file is located in the $ORACLE_HOME/admin/
ORACLE_SID
/wallet
directory.
Transparent Data Encryption uses an auto login keystore only if it is available at the correct location (ENCRYPTION_WALLET_LOCATION
,
WALLET_LOCATION
,
or the default keystore location), and the SQL statement to open an encrypted keystore has not already been executed. (Note that auto-login keystores are encrypted, because they have system-generated passwords.)
Related Topics
Parent topic: Step 2: Create the Software Keystore
3.1.4 Step 3: Open the Software Keystore
Depending on the type of keystore you create, you must manually open the keystore before you can use it.
- About Opening Software Keystores
A password-based software keystore must be open before any TDE master encryption keys can be created or accessed in the keystore. - Opening a Software Keystore
To open a software keystore, you must use theADMINISTER KEY MANAGEMENT
statement with theSET KEYSTORE OPEN
clause.
Parent topic: Configuring a Software Keystore
3.1.4.1 About Opening Software Keystores
A password-based software keystore must be open before any TDE master encryption keys can be created or accessed in the keystore.
You can either manually open a software keystore or, when you perform certain ADMINISTER KEY MANAGEMENT
operations, have the keystore open temporarily during the course of the operation itself. You do not need to manually open auto-login or local auto-login software keystores. These keystore are automatically opened when it is required, that is, when an encryption operation must access the key. If necessary, you can explicitly close any of these types of keystores. Keystores can be in the following states: open, closed, open but with no master key, open but with an unknown master key, undefined, or not available (that is, not present in the sqlnet.ora
location).
You can check the status of whether a keystore is open or not by querying the STATUS
column of the V$ENCRYPTION_WALLET
view.
After you open a keystore, it remains open until you manually close it. Each time you restart a database instance, you must manually open the password keystore to reenable encryption and decryption operations.
3.1.4.2 Opening a Software Keystore
To open a software keystore, you must use the ADMINISTER KEY MANAGEMENT
statement with the SET KEYSTORE OPEN
clause.
Note that if the keystore is open but you have not created a TDE master encryption key yet (described next), the STATUS
column of the V$ENCRYPTION_WALLET
view reminds you with an OPEN_NO_MASTER_KEY
status.
Parent topic: Step 3: Open the Software Keystore
3.1.5 Step 4: Set the Software TDE Master Encryption Key
Once the keystore is open, you can set a TDE master encryption key for it.
- About Setting the Software TDE Master Encryption Key
The TDE master encryption key is stored in the keystore. - Setting the TDE Master Encryption Key in the Software Keystore
To set the TDE master encryption key in a software keystore, use theADMINISTER KEY MANAGEMENT
statement with theSET KEY
clause.
Parent topic: Configuring a Software Keystore
3.1.5.1 About Setting the Software TDE Master Encryption Key
The TDE master encryption key is stored in the keystore.
This key protects the TDE table keys and tablespace encryption keys. By default, the TDE master encryption key is a key that Transparent Data Encryption (TDE) generates. You can find if a keystore has no master key set or an unknown master key by querying the STATUS
column of the V$ENCRYPTION_WALLET
view.
In a multitenant environment, you can create and manage the TDE master encryption key from either the root or the PDB.
You also can create TDE master encryption keys for use later on, and then manually activate them.
Related Topics
Parent topic: Step 4: Set the Software TDE Master Encryption Key
3.1.5.2 Setting the TDE Master Encryption Key in the Software Keystore
To set the TDE master encryption key in a software keystore, use the ADMINISTER KEY MANAGEMENT
statement with the SET KEY
clause.
-
For password software keystores, ensure that you complete the procedure described in Step 3: Open the Software Keystore to open the key.
Auto-login or local auto-login software keys are opened automatically after you create them. Password-based software keystores must be open before you can set the TDE master encryption key. If the auto-login software keystore is open, then you must close it and open the password-based software keystore. If both the password-based keystore and auto-login keystores are present in the configured location and the password-based keystore is open, then the TDE master encryption key is automatically written to the auto-login keystore as well.
-
Log in to the database instance as a user who has been granted the
ADMINISTER KEY MANAGEMENT
orSYSKM
privilege.In a multitenant environment, log in to the root or to the PDB. For example, to log in to a PDB:
sqlplus sec_admin@hrpdb as syskm Enter password: password Connected.
To find the available PDBs, query the
DBA_PDBS
data dictionary view. To check the current PDB, run theshow con_name
command. -
Ensure that the database is open in
READ WRITE
mode.You can set the TDE master encryption key if
OPEN_MODE
is set toREAD WRITE
. To find the status, for a non-multitenant environment, query theOPEN_MODE
column of theV$DATABASE
dynamic view. If you are using a multitenant environment, then query theV$PDBS
view. (If you cannot access these views, then connect asSYSDBA
and try the query again. In order to connect asSYSKM
for this type of query, you must create a password file for it. -
Connect using the
SYSKM
administrative privilege and then run theADMINISTER KEY MANAGEMENT
SQL statement to set the software management keystore.ADMINISTER KEY MANAGEMENT SET KEY [USING TAG 'tag'] [FORCE KEYSTORE] IDENTIFIED BY [EXTERNAL STORE] | keystore_password [WITH BACKUP [USING 'backup_identifier']] [CONTAINER = ALL | CURRENT];
In this specification:
-
tag
is the associated attributes and information that you define. Enclose this setting in single quotation marks (' '). -
FORCE KEYSTORE
enables the keystore operation if the auto-login keystore is in use, or the keystore is closed. -
IDENTIFIED BY
can be one of the following settings:-
EXTERNAL STORE
uses the keystore password stored in the external store to perform the keystore operation. -
keystore_password
is the mandatory keystore password that you created when you created the keystore in Step 2: Create the Software Keystore.
-
-
WITH BACKUP
creates a backup of the keystore. You must use this option for password-based keystores. Optionally, you can use theUSING
clause to add a brief description of the backup. Enclose this description in single quotation marks (' '). This identifier is appended to the named keystore file (for example,ewallet_
time_stamp
_emp_key_backup.p12
, withemp_key_backup
being the backup identifier). Follow the file naming conventions that your operating system uses. -
CONTAINER
is for use in a multitenant environment. EnterALL
to set the key in all of the PDBs in this CDB, orCURRENT
for the current PDB.
For example, if the password-based keystore is open:
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY keystore_password WITH BACKUP USING 'emp_key_backup'; keystore altered.
If the auto-login keystore is open or if the keystore is closed:
ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY keystore_password WITH BACKUP USING 'emp_key_backup'; keystore altered.
-
Related Topics
Parent topic: Step 4: Set the Software TDE Master Encryption Key
3.1.6 Step 5: Encrypt Your Data
After you complete the software keystore configuration, you can begin to encrypt data.
You can encrypt data in individual table columns or in entire tablespaces or databases.
Parent topic: Configuring a Software Keystore
3.2 Configuring an External Keystore
There are two types of external keystores that Oracle Database supports: Oracle Key Vault keystores and OCI Vault - Key Management keystores.
- About Configuring an External Keystore
An external keystore is a separate server or device that provides security storage for encryption keys. - Step 1: Configure Isolated PDBs for Oracle Key Vault
You can configure isolated mode PDBs for Oracle Key Vault by setting theTDE_CONFIGURATION
parameter. - Step 2: Open the External Keystore
After you have configured the external keystore, you must open it before it can be used. - Step 3: Set the First TDE Master Encryption Key in the External Keystore
After you have opened the external keystore, you are ready to set the first TDE master encryption key. - Step 4: Encrypt Your Data
Now that you have completed the configuration for an external keystore, you can begin to encrypt data.
Parent topic: Configuring Transparent Data Encryption
3.2.1 About Configuring an External Keystore
An external keystore is a separate server or device that provides security storage for encryption keys.
External keystores are external to an Oracle database. Oracle Database can interface with external keystores but cannot manipulate them outside of the Oracle interface. The Oracle database can request the external keystore to create a key but it cannot define how this key is stored in an external database. (Conversely, for software keystores that are created using TDE, Oracle Database has full control: that is, you can use SQL statements to manipulate this type of keystore.) Examples of external keystores are Oracle Key Vault keystores. Supported external keystores are Oracle Key Vault and the Oracle Cloud Infrastructure Vault.
To configure an external keystore, you must first include the keystore type in the sqlnet.ora
file, open the external keystore, and then set the external keystore TDE master encryption key.
For Oracle Key Vault:
IDENTIFIED BY "Oracle_Key_Vault_password"
After you configure the external keystore, you are ready to begin encrypting your data.
Parent topic: Configuring an External Keystore
3.2.2 Step 1: Configure Isolated PDBs for Oracle Key Vault
You can configure isolated mode PDBs for Oracle Key Vault by setting the TDE_CONFIGURATION
parameter.
Parent topic: Configuring an External Keystore
3.2.3 Step 2: Open the External Keystore
After you have configured the external keystore, you must open it before it can be used.
- About Opening External Keystores
You must open the external keystore so that it is accessible to the database before you can perform any encryption or decryption. - Opening an External Keystore
To open an external keystore, use theADMINISTER KEY MANAGEMENT
statement with theSET KEYSTORE OPEN
clause.
Parent topic: Configuring an External Keystore
3.2.3.1 About Opening External Keystores
You must open the external keystore so that it is accessible to the database before you can perform any encryption or decryption.
If a recovery operation is needed on your database (for example, if the database was not cleanly shut down, and has an encrypted tablespace that needs recovery), then you must open the external keystore before you can open the database itself.
There are two ways that you can open the external keystore:
-
Manually open the keystore by issuing the
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN
statement. Afterward, you can perform the operation. -
Include the
FORCE KEYSTORE
clause in theADMINISTER KEY MANAGEMENT
statement.FORCE KEYSTORE
temporarily opens the keystore for the duration of the operation, and when the operation completes, the keystore is closed again.FORCE KEYSTORE
is useful for situations when the database is heavily loaded. In this scenario, because of concurrent access to encrypted objects in the database, the auto-login keystore continues to open immediately after it has been closed but before a user has had a chance to open the password-based keystore.
To check the status of the keystore, query the STATUS
column of the V$ENCRYPTION_WALLET
view. Keystores can be in the following states: CLOSED
, NOT_AVAILABLE
(that is, not present in the WALLET_ROOT
location), OPEN
, OPEN_NO_MASTER_KEY
, OPEN_UNKNOWN_MASTER_KEY_STATUS
.
Be aware that for external keystores, if the database is in the mounted state, then it cannot check if the master key is set because the data dictionary is not available. In this situation, the status will be OPEN_UNKNOWN_MASTER_KEY_STATUS
.
Parent topic: Step 2: Open the External Keystore
3.2.3.2 Opening an External Keystore
To open an external keystore, use the ADMINISTER KEY MANAGEMENT
statement with the SET KEYSTORE OPEN
clause.
Parent topic: Step 2: Open the External Keystore
3.2.4 Step 3: Set the First TDE Master Encryption Key in the External Keystore
After you have opened the external keystore, you are ready to set the first TDE master encryption key.
- About Setting the External Keystore TDE Master Encryption Key
You must create a TDE master encryption key that is stored inside the external keystore. - Setting a New TDE Master Encryption Key
You should complete this procedure if you have not previously configured an external keystore for Transparent Data Encryption. - Migration of a Previously Configured TDE Master Encryption Key
You must migrate the previously configured TDE master encryption key if you previously configured a software keystore.
Parent topic: Configuring an External Keystore
3.2.4.1 About Setting the External Keystore TDE Master Encryption Key
You must create a TDE master encryption key that is stored inside the external keystore.
If you have not previously configured a software keystore for TDE, then you can set the first TDE master encryption key in the external keystore. If you have already configured a software keystore for TDE, then you must migrate the database to use the external keystore.
Along with the current master encryption key, Oracle wallets maintain historical master encryption keys that are generated after every re-key operation that rekeys the master encryption key. These historical master keys help to restore Oracle database backups that were taken previously using one of the historical master encryption keys.
3.2.4.2 Setting a New TDE Master Encryption Key
You should complete this procedure if you have not previously configured an external keystore for Transparent Data Encryption.
3.2.4.3 Migration of a Previously Configured TDE Master Encryption Key
You must migrate the previously configured TDE master encryption key if you previously configured a software keystore.
Tools such as Oracle Data Pump and Oracle Recovery Manager require access to the old software keystore to perform decryption and encryption operations on data exported or backed up using the software keystore. You can migrate from the software to the external keystore by following the instructions in Migrating Between a Software Password Keystore and an External Keystore.
Along with the current TDE master key, Oracle wallets maintain historical TDE master keys that are generated after every re-key operation that rotates the TDE master key. These historical TDE master keys help to restore Oracle database backups that were taken previously using one of the historical TDE master keys.
3.2.5 Step 4: Encrypt Your Data
Now that you have completed the configuration for an external keystore, you can begin to encrypt data.
Oracle Key Vault Administrator's Guide describes how to configure Oracle Key Vault keystores.
You can encrypt individual columns in a table or entire tablespaces.
Parent topic: Configuring an External Keystore
3.3 Encrypting Columns in Tables
You can use Transparent Data Encryption to encrypt individual columns in database tables.
- About Encrypting Columns in Tables
You can encrypt individual columns in tables. - Data Types That Can Be Encrypted with TDE Column Encryption
Oracle Database supports a specific set of data types that can be used with TDE column encryption. - Restrictions on Using Transparent Data Encryption Column Encryption
TDE column encryption is performed at the SQL layer. Oracle Database utilities that bypass the SQL layer cannot use TDE column encryption services. - Creating Tables with Encrypted Columns
Oracle Database provides a selection of different algorithms that you can use to define the encryption used in encrypted columns. - Encrypting Columns in Existing Tables
You can encrypt columns in existing tables. As with new tables, you have a choice of different algorithms to use to definite the encryption. - Creating an Index on an Encrypted Column
You can create an index on an encrypted column. - Adding Salt to an Encrypted Column
Salt, which is a random string added to data before encryption, is a way to strengthen the security of encrypted data. . - Removing Salt from an Encrypted Column
You can use the ALTER TABLE SQL statement to remove salt from an encrypted column. - Changing the Encryption Key or Algorithm for Tables with Encrypted Columns
You can use theALTER TABLE
SQL statement to change the encryption key or algorithm used in encrypted columns.
Parent topic: Configuring Transparent Data Encryption
3.3.1 About Encrypting Columns in Tables
You can encrypt individual columns in tables.
Whether you choose to encrypt individual columns or entire tablespaces depends on the data types that the table has. There are also several features that do not support TDE column encryption.
3.3.2 Data Types That Can Be Encrypted with TDE Column Encryption
Oracle Database supports a specific set of data types that can be used with TDE column encryption.
You can encrypt data columns that use a variety of different data types.
Supported data types are as follows:
-
BINARY_DOUBLE
-
BINARY_FLOAT
-
CHAR
-
DATE
-
INTERVAL DAY TO SECOND
-
INTERVAL YEAR TO MONTH
-
NCHAR
-
NUMBER
-
NVARCHAR2
-
RAW
(legacy or extended) -
TIMESTAMP
(includesTIMESTAMP WITH TIME ZONE
andTIMESTAMP WITH LOCAL TIME ZONE
) -
VARCHAR2
(legacy or extended)
You cannot encrypt a column if the encrypted column size is greater than the size allowed by the data type of the column.
Table 3-1 shows the maximum allowable sizes for various data types.
Table 3-1 Maximum Allowable Size for Data Types
Data Type | Maximum Size |
---|---|
|
1932 bytes |
|
3932 bytes |
|
32,699 bytes |
|
1966 bytes |
|
16,315 bytes |
|
966 bytes |
|
32,699 bytes |
Note:
TDE tablespace encryption does not have these data type restrictions.
Parent topic: Encrypting Columns in Tables
3.3.3 Restrictions on Using Transparent Data Encryption Column Encryption
TDE column encryption is performed at the SQL layer. Oracle Database utilities that bypass the SQL layer cannot use TDE column encryption services.
Do not use TDE column encryption with the following database features:
-
Index types other than B-tree
-
Range scan search through an index
-
Synchronous change data capture
-
Transportable tablespaces
-
Columns that have been created as identity columns
In addition, you cannot use TDE column encryption to encrypt columns used in foreign key constraints.
Applications that must use these unsupported features can use the DBMS_CRYPTO
PL/SQL package for their encryption needs.
Transparent Data Encryption protects data stored on a disk or other media. It does not protect data in transit. Use the network encryption solutions discussed in Oracle Database Security Guide to encrypt data over the network.
3.3.4 Creating Tables with Encrypted Columns
Oracle Database provides a selection of different algorithms that you can use to define the encryption used in encrypted columns.
- About Creating Tables with Encrypted Columns
You can use theCREATE TABLE
SQL statement to create a table with an encrypted column. - Creating a Table with an Encrypted Column Using the Default Algorithm
By default, TDE uses theAES
encryption algorithm with a 192-bit key length (AES192
). - Creating a Table with an Encrypted Column Using No Algorithm or a Non-Default Algorithm
You an use theCREATE TABLE
SQL statement to create a table with an encrypted column. - Using the NOMAC Parameter to Save Disk Space and Improve Performance
You can bypass checks that TDE performs. This can save up to 20 bytes of disk space per encrypted value. - Example: Using the NOMAC Parameter in a CREATE TABLE Statement
You can use the CREATE TABLE SQL statement to encrypt a table column using the NOMAC parameter. - Example: Changing the Integrity Algorithm for a Table
You can use the ALTER TABLE SQL statement to change the integrity algorithm for a database table. - Creating an Encrypted Column in an External Table
The external table feature enables you to access data in external sources as if the data were in a database table.
Parent topic: Encrypting Columns in Tables
3.3.4.1 About Creating Tables with Encrypted Columns
You can use the CREATE TABLE
SQL statement to create a table with an encrypted column.
To create relational tables with encrypted columns, you can specify the SQL ENCRYPT
clause when you define database columns with the CREATE TABLE
SQL statement.
Parent topic: Creating Tables with Encrypted Columns
3.3.4.2 Creating a Table with an Encrypted Column Using the Default Algorithm
By default, TDE uses the AES
encryption algorithm with a 192-bit key length (AES192
).
If you encrypt a table column without specifying an algorithm, then the column is encrypted using the AES192
algorithm.
TDE adds salt to plaintext before encrypting it. Adding salt makes it harder for attackers to steal data through a brute force attack. TDE also adds a Message Authentication Code (MAC) to the data for integrity checking. The SHA-1
integrity algorithm is used by default.
-
To create a table that encrypts a column, use the
CREATE TABLE
SQL statement with theENCRYPT
clause.For example, to encrypt a table column using the default algorithm:
CREATE TABLE employee ( first_name VARCHAR2(128), last_name VARCHAR2(128), empID NUMBER, salary NUMBER(6) ENCRYPT);
This example creates a new table with an encrypted column (
salary
). The column is encrypted using the default encryption algorithm (AES192
). Salt and MAC are added by default. This example assumes that the wallet is open and a master key is set.
Note:
If there are multiple encrypted columns in a table, then all of these columns must use the same pair of encryption and integrity algorithms.
Salt is specified at the column level. This means that an encrypted column in a table can choose not to use salt irrespective of whether or not other encrypted columns in the table use salt.
Parent topic: Creating Tables with Encrypted Columns
3.3.4.3 Creating a Table with an Encrypted Column Using No Algorithm or a Non-Default Algorithm
You an use the CREATE TABLE
SQL statement to create a table with an encrypted column.
By default, TDE adds salt to plaintext before encrypting it. Adding salt makes it harder for attackers to steal data through a brute force attack. However, if you plan to index the encrypted column, then you must use the NO SALT
parameter.
-
To create a table that uses an encrypted column that is a non-default algorithm or no algorithm, run the
CREATE TABLE
SQL statement as follows:-
If you do not want to use any algorithm, then include the
ENCRYPT NO SALT
clause. -
If you want to use a non-default algorithm, then use the
ENCRYPT USING
clause, followed by one of the following algorithms enclosed in single quotation marks:-
3DES168
-
AES128
-
AES192
(default) -
AES256
-
-
The following example shows how to specify encryption settings for the empID
and salary
columns.
CREATE TABLE employee ( first_name VARCHAR2(128), last_name VARCHAR2(128), empID NUMBER ENCRYPT NO SALT, salary NUMBER(6) ENCRYPT USING '3DES168');
In this example:
-
The
empID
column is encrypted and does not use salt. Both theempID
andsalary
columns will use the3DES168
encryption algorithm, because all of the encrypted columns in a table must use the same encryption algorithm. -
The
salary
column is encrypted using the3DES168
encryption algorithm. Note that the string that specifies the algorithm must be enclosed in single quotation marks (' '). Thesalary
column uses salt by default.
Parent topic: Creating Tables with Encrypted Columns
3.3.4.4 Using the NOMAC Parameter to Save Disk Space and Improve Performance
You can bypass checks that TDE performs. This can save up to 20 bytes of disk space per encrypted value.
If the number of rows and encrypted columns in the table is large, then bypassing TDE checks can add up to a significant amount of disk space. In addition, this saves processing cycles and reduces the performance overhead associated with TDE.
TDE uses the SHA-1
integrity algorithm by default. All of the encrypted columns in a table must use the same integrity algorithm. If you already have a table column using the SHA-1
algorithm, then you cannot use the NOMAC
parameter to encrypt another column in the same table.
-
To bypass the integrity check during encryption and decryption operations, use the
NOMAC
parameter in theCREATE TABLE
andALTER TABLE
statements.
Parent topic: Creating Tables with Encrypted Columns
3.3.4.5 Example: Using the NOMAC Parameter in a CREATE TABLE Statement
You can use the CREATE TABLE SQL statement to encrypt a table column using the NOMAC parameter.
Example 3-1 creates a table with an encrypted column. The empID
column is encrypted using the NOMAC
parameter.
Example 3-1 Using the NOMAC parameter in a CREATE TABLE statement
CREATE TABLE employee (
first_name VARCHAR2(128),
last_name VARCHAR2(128),
empID NUMBER ENCRYPT 'NOMAC' ,
salary NUMBER(6));
Parent topic: Creating Tables with Encrypted Columns
3.3.4.6 Example: Changing the Integrity Algorithm for a Table
You can use the ALTER TABLE SQL statement to change the integrity algorithm for a database table.
Example 3-2 shows how to change the integrity algorithm for encrypted columns in a table. The encryption algorithm is set to 3DES168
and the integrity algorithm is set to SHA-1
. The second ALTER TABLE
statement sets the integrity algorithm to NOMAC
.
Example 3-2 Changing the Integrity Algorithm for a Table
ALTER TABLE EMPLOYEE REKEY USING '3DES168' 'SHA-1'; ALTER TABLE EMPLOYEE REKEY USING '3DES168' 'NOMAC';
Parent topic: Creating Tables with Encrypted Columns
3.3.4.7 Creating an Encrypted Column in an External Table
The external table feature enables you to access data in external sources as if the data were in a database table.
External tables can be updated using the ORACLE_DATAPUMP
access driver.
-
To encrypt specific columns in an external table, use the
ENCRYPT
clause when you define those columns:A system-generated key encrypts the columns. For example, the following
CREATE TABLE
SQL statement encrypts thessn
column using the3DES168
algorithm:CREATE TABLE emp_ext ( first_name, .... ssn ENCRYPT USING '3DES168', ....
If you plan to move an external table to a new location, then you cannot use a randomly generated key to encrypt the columns. This is because the randomly generated key will not be available at the new location.
For such scenarios, you should specify a password while you encrypt the columns. After you move the data, you can use the same password to regenerate the key required to access the encrypted column data at the new location.
Table partition exchange also requires a password-based TDE table key.
Example 3-3 creates an external table using a password to create the TDE table key.
Example 3-3 Creating a New External Table with a Password-Generated TDE Table Key
CREATE TABLE emp_ext ( first_name, last_name, empID, salary, ssn ENCRYPT IDENTIFIED BY password ) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY "D_DIR" LOCATION('emp_ext.dat') ) REJECT LIMIT UNLIMITED AS SELECT * FROM EMPLOYEE;
Parent topic: Creating Tables with Encrypted Columns
3.3.5 Encrypting Columns in Existing Tables
You can encrypt columns in existing tables. As with new tables, you have a choice of different algorithms to use to definite the encryption.
- About Encrypting Columns in Existing Tables
TheALTER TABLE
SQL statement enables you to encrypt columns in an existing table. - Adding an Encrypted Column to an Existing Table
You can encrypt columns in existing tables, use a different algorithm, and useNO SALT
to index the column. - Encrypting an Unencrypted Column
You can use theALTER TABLE MODIFY
statement to encrypt an existing unencrypted column. - Disabling Encryption on a Column
You may want to disable encryption for reasons of compatibility or performance.
Parent topic: Encrypting Columns in Tables
3.3.5.1 About Encrypting Columns in Existing Tables
The ALTER TABLE
SQL statement enables you to encrypt columns in an existing table.
To add an encrypted column to an existing table, or to encrypt or decrypt an existing column, you use the ALTER TABLE
SQL statement with the ADD
or MODIFY
clause.
Parent topic: Encrypting Columns in Existing Tables
3.3.5.2 Adding an Encrypted Column to an Existing Table
You can encrypt columns in existing tables, use a different algorithm, and use NO SALT
to index the column.
-
To add an encrypted column to an existing table, use the
ALTER TABLE ADD
statement, specifying the new column with theENCRYPT
clause.
Example 3-4 adds an encrypted column, ssn
, to an existing table, called employee
. The ssn
column is encrypted with the default AES192
algorithm. Salt and MAC are added by default.
Example 3-4 Adding an Encrypted Column to an Existing Table
ALTER TABLE employee ADD (ssn VARCHAR2(11) ENCRYPT);
Parent topic: Encrypting Columns in Existing Tables
3.3.5.3 Encrypting an Unencrypted Column
You can use the ALTER TABLE MODIFY
statement to encrypt an existing unencrypted column.
-
To encrypt an existing unencrypted column, use the
ALTER TABLE MODIFY
statement, specifying the unencrypted column with theENCRYPT
clause.
The following example encrypts the first_name
column in the employee
table. The first_name
column is encrypted with the default AES192
algorithm. Salt is added to the data, by default. You can encrypt the column using a different algorithm. If you want to index a column, then you must specify NO SALT
. You can also bypass integrity checks by using the NOMAC
parameter.
ALTER TABLE employee MODIFY (first_name ENCRYPT);
The following example encrypts the first_name
column in the employee table using the NOMAC
parameter.
ALTER TABLE employee MODIFY (first_name ENCRYPT 'NOMAC');
Parent topic: Encrypting Columns in Existing Tables
3.3.5.4 Disabling Encryption on a Column
You may want to disable encryption for reasons of compatibility or performance.
-
To disable column encryption, use the
ALTER TABLE MODIFY
command with theDECRYPT
clause.
Example 3-5 decrypts the first_name
column in the employee
table.
Example 3-5 Turning Off Column Encryption
ALTER TABLE employee MODIFY (first_name DECRYPT);
Parent topic: Encrypting Columns in Existing Tables
3.3.6 Creating an Index on an Encrypted Column
You can create an index on an encrypted column.
The column being indexed must be encrypted without salt. If the column is encrypted with salt, then the ORA-28338: cannot encrypt indexed column(s) with salt
error is raised.
-
To create an index on an encrypted column, use the
CREATE INDEX
statement with theENCRYPT NO SALT
clause.
Example 3-6 shows how to create an index on a column that has been encrypted without salt.
Example 3-6 Creating Index on a Column Encrypted Without Salt
CREATE TABLE employee (
first_name VARCHAR2(128),
last_name VARCHAR2(128),
empID NUMBER ENCRYPT NO SALT,
salary NUMBER(6) ENCRYPT USING '3DES168');
CREATE INDEX employee_idx on employee (empID);
Parent topic: Encrypting Columns in Tables
3.3.7 Adding Salt to an Encrypted Column
Salt, which is a random string added to data before encryption, is a way to strengthen the security of encrypted data. .
Salt ensures that the same plaintext data does not always translate to the same encrypted text. Salt removes the one common method that intruders use to steal data, namely, matching patterns of encrypted text. Adding salt requires an additional 16 bytes of storage per encrypted data value.
-
To add or remove salt from encrypted columns, use the
ALTER TABLE MODIFY
SQL statement.
For example, suppose you want to encrypt the first_name
column using salt. If the first_name
column was encrypted without salt earlier, then the ALTER TABLE MODIFY
statement reencrypts it using salt.
ALTER TABLE employee MODIFY (first_name ENCRYPT SALT);
Parent topic: Encrypting Columns in Tables
3.3.8 Removing Salt from an Encrypted Column
You can use the ALTER TABLE SQL statement to remove salt from an encrypted column.
-
To remove salt from an encrypted column, use the
ENCRYPT NO SALT
clause in the ALTER TABLE SQL statement.
For example, suppose you wanted to remove salt from the first_name
column. If you must index a column that was encrypted using salt, then you can use this statement to remove the salt before indexing
ALTER TABLE employee MODIFY (first_name ENCRYPT NO SALT);
Parent topic: Encrypting Columns in Tables
3.3.9 Changing the Encryption Key or Algorithm for Tables with Encrypted Columns
You can use the ALTER TABLE
SQL statement to change the encryption key or algorithm used in encrypted columns.
Each table can have only one TDE table key for its columns. You can regenerate the TDE table key with the ALTER TABLE
statement. This process generates a new key, decrypts the data in the table using the previous key, reencrypts the data using the new key, and then updates the table metadata with the new key information. You can also use a different encryption algorithm for the new TDE table key.
-
To change the encryption key or algorithm for tables that contain encrypted columns, use the
ALTER TABLE
SQL statement with theREKEY
orREKEY USING
clause.
For example:
ALTER TABLE employee REKEY;
Example 3-7 regenerates the TDE table key for the employee
table by using the 3DES168
algorithm.
Example 3-7 Changing an Encrypted Table Column Encryption Key and Algorithm
ALTER TABLE employee REKEY USING '3DES168';
Parent topic: Encrypting Columns in Tables
3.4 Encryption Conversions for Tablespaces and Databases
You can perform encryption operations on both offline and online tablespaces and databases.
- About Encryption Conversions for Tablespaces and Databases
TheCREATE TABLESPACE
SQL statement can be used to encrypt new tablespaces.ALTER TABLESPACE
can encrypt existing tablespaces. - Restrictions on Using Transparent Data Encryption Tablespace Encryption
You should be aware of restrictions on using Transparent Data Encryption when you encrypt a tablespace. - Creating an Encrypted New Tablespace
When you create a new tablespace, you can configure its encryption settings during the creation process. - Setting the Tablespace Encryption Default Algorithm
TheTABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM
applies to specific encryption scenarios. - Encrypting Future Tablespaces
You can configure Oracle Database to automatically encrypt future tablespaces that you will create. - Encryption Conversions for Existing Offline Tablespaces
You can perform offline encryption conversions by using theALTER TABLESPACE
SQL statementOFFLINE
,ENCRYPT
, andDECRYPT
clauses. - Encryption Conversions for Existing Online Tablespaces
You can encrypt and decrypt an online existing tablespace by using theALTER TABLESPACE
SQL statement with theOFFLINE
andENCRYPT
orDECRYPT
clauses. - Encryption Conversions for Existing Databases
You can encrypt both offline and online databases.
Parent topic: Configuring Transparent Data Encryption
3.4.1 About Encryption Conversions for Tablespaces and Databases
The CREATE TABLESPACE
SQL statement can be used to encrypt new tablespaces. ALTER TABLESPACE
can encrypt existing tablespaces.
In addition to encrypting new and existing tablespaces, you can encrypt full databases, which entails the encryption of the Oracle-supplied SYS
, SYSAUX
, TEMP
, and UNDO
tablespaces. To encrypt a full database, you use the ALTER TABLESPACE
statement, not ALTER DATABASE
, to encrypt the Oracle-supplied tablespaces.
The following table compares the differences between an offline and an online encryption conversion of tablespaces and databases.
Table 3-2 Offline and Online Tablespace and Database Encryption Conversions
Functionality | Offline Conversion | Online Conversion |
---|---|---|
Release with minimum conversion capability |
Oracle Database 11g release 1 (11.1) |
Oracle Database 12c release 2 (12.2) |
What can be backported? |
The ability to encrypt or decrypt a data file with the
AES128 algorithm (using or DECRYPT ) can be used in Oracle Database releases 12.1.0.2 and 11.2.0.4.
|
No |
Algorithms supported |
|
All symmetric encryption algorithm that TDE supports. See About Encryption Conversions for Existing Online Tablespaces for a list of the supported algorithms. |
When can the conversion be run? |
When the tablespace is offline or the database is in the mount stage. |
When the tablespace is online and database is open in read/write mode. |
Is auxiliary space required for the conversion? |
No |
Yes. See Encrypting an Existing Tablespace with Online Conversion for guidelines. |
Oracle Data Guard conversion guidelines |
Convert both the primary and standby manually. Convert the standby first and then switch over to minimum downtime |
After you convert the primary, the standby conversion takes place automatically. You cannot perform an online conversion directly on the standby. |
Encrypt the |
Oracle Database 12c release 2 (12.2) only. You must set |
Oracle Database 12c release 2 (12.2) only. You must set |
Can an existing |
No, but you can create an encrypted |
No, but you can create an encrypted |
Can an existing tablespace be decrypted? |
You only can decrypt a tablespace or data file that was previously encrypted by an offline encrypt operation. Oracle does not recommend that you decrypt the |
Yes, but Oracle does not recommend that you decrypt the |
Can encryption keys be rekeyed or rotated? |
No, but after the tablespace is encrypted, you can then use online conversion to rekey in Oracle Database 12c release 2 (12.2) compatibility. |
Yes |
Can encryption operations be run in parallel? |
You can run parallel encryption conversions at the data file level with multiple user sessions running. |
You can run parallel encryption conversions at the tablespace level with multiple user sessions running. |
What to do if an encryption conversion SQL statement fails to complete? |
Re-issue the encryption or decryption SQL statement to ensure that all the data files within the tablespace are consistently either encrypted or decrypted. |
Rerun the SQL statement but use the |
Parent topic: Encryption Conversions for Tablespaces and Databases
3.4.2 Restrictions on Using Transparent Data Encryption Tablespace Encryption
You should be aware of restrictions on using Transparent Data Encryption when you encrypt a tablespace.
Note the following restrictions:
-
Transparent Data Encryption (TDE) tablespace encryption encrypts or decrypts data during read and write operations, as opposed to TDE column encryption, which encrypts and decrypts data at the SQL layer. This means that most restrictions that apply to TDE column encryption, such as data type restrictions and index type restrictions, do not apply to TDE tablespace encryption.
-
To perform import and export operations, use Oracle Data Pump.
-
If you encrypt the
SYSTEM
,SYSAUX
,TEMP
, orUNDO
tablespace, then never close the keystore manually, even if you later decrypt the tablespace by using theALTER TABLESPACE
SQL statement.
3.4.3 Creating an Encrypted New Tablespace
When you create a new tablespace, you can configure its encryption settings during the creation process.
- Step 1: Set the COMPATIBLE Initialization Parameter for Tablespace Encryption
You must set theCOMPATIBLE
initialization parameter before creating an encrypted tablespace. - Step 2: Set the Tablespace TDE Master Encryption Key
You should ensure that you have configured the TDE master encryption key. - Step 3: Create the Encrypted Tablespace
After you have set theCOMPATIBLE
initialization parameter, you are ready to create the encrypted tablespace.
Parent topic: Encryption Conversions for Tablespaces and Databases
3.4.3.1 Step 1: Set the COMPATIBLE Initialization Parameter for Tablespace Encryption
You must set the COMPATIBLE
initialization parameter before creating an encrypted tablespace.
- About Setting the COMPATIBLE Initialization Parameter for Tablespace Encryption
A minimumCOMPATIBLE
initialization parameter setting of11.2.0.0
enables the full set of tablespace encryption features. - Setting the COMPATIBLE Initialization Parameter for Tablespace Encryption
To set theCOMPATIBLE
initialization parameter, you must edit the initialization parameter file for the database instance.
Parent topic: Creating an Encrypted New Tablespace
3.4.3.1.1 About Setting the COMPATIBLE Initialization Parameter for Tablespace Encryption
A minimum COMPATIBLE
initialization parameter setting of 11.2.0.0
enables the full set of tablespace encryption features.
Setting the compatibility to 11.2.0.0
enables the following functionality:
-
The
11.2.0.0
setting enables the database to use any of the four supported algorithms for data encryption (3DES168
,AES128
,AES192
, andAES256
). -
The
11.2.0.0
setting enables the migration of a key from a software keystore to an external keystore (ensure that the TDE master encryption key was configured for the external keystore) -
The
11.2.0.0
setting enables resetting and rotating the TDE master encryption key
Be aware that once you set the COMPATIBLE
parameter to 11.2.0.0
, the change is irreversible. To use tablespace encryption, ensure that the compatibility setting is at the minimum, which is 11.2.0.0
.
See Also:
-
Oracle Database SQL Language Reference for more information about the
COMPATIBLE
parameter -
Oracle Database Administrator’s Guide for more information about initialization parameter files
3.4.3.1.2 Setting the COMPATIBLE Initialization Parameter for Tablespace Encryption
To set the COMPATIBLE
initialization parameter, you must edit the initialization parameter file for the database instance.
-
Log in to the database instance.
In a multitenant environment, log in to the PDB. For example:
sqlplus sec_admin@hrpdb Enter password: password Connected.
To find the available PDBs, query the
DBA_PDBS
data dictionary view. To check the current PDB, run theshow con_name
command. -
Check the current setting of the
COMPATIBLE
parameter.For example:
SHOW PARAMETER COMPATIBLE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 11.2.0.0 noncdbcompatible BOOLEAN FALSE
-
If you must change the
COMPATIBLE
parameter, then complete the remaining steps in this procedure.The value should be 11.2.0.0 or higher.
-
From the command line, locate the initialization parameter file for the database instance.
-
UNIX systems: This file is in the
ORACLE_HOME
/dbs
directory and is namedinit
ORACLE_SID
.ora
(for example,initmydb.ora
). -
Windows systems: This file is in the
ORACLE_HOME
\database
directory and is namedinit
ORACLE_SID
.ora
(for example,initmydb.ora
).
-
-
Edit the initialization parameter file to use the new
COMPATIBLE
setting.For example:
compatible=12.2.0.0.0
-
In SQL*Plus, connect as a user who has the
SYSDBA
administrative privilege, and then restart the database.For example:
CONNECT /AS SYSDBA SHUTDOWN STARTUP
If tablespace encryption is in use, then open the keystore at the database mount. The keystore must be open before you can access data in an encrypted tablespace.
For example:
STARTUP MOUNT; ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY keystore_password; ALTER DATABASE OPEN;
3.4.3.2 Step 2: Set the Tablespace TDE Master Encryption Key
You should ensure that you have configured the TDE master encryption key.
-
Set the TDE master encryption key as follows:
-
For software TDE master encryption keys, see Step 4: Set the Software TDE Master Encryption Key.
-
For hardware TDE master encryption keys, see Step 3: Set the First TDE Master Encryption Key in the External Keystore.
-
Parent topic: Creating an Encrypted New Tablespace
3.4.3.3 Step 3: Create the Encrypted Tablespace
After you have set the COMPATIBLE
initialization parameter, you are ready to create the encrypted tablespace.
- About Creating Encrypted Tablespaces
To create an encrypted tablespace, you can use theCREATE TABLESPACE
SQL statement. - Creating an Encrypted Tablespace
To create an encrypted tablespace, you must use theCREATE TABLESPACE
statement with theENCRYPTION USING
clause. - Example: Creating an Encrypted Tablespace That Uses AES192
You can use theCREATE TABLESPACE
SQL statement to create an encrypted tablespace. - Example: Creating an Encrypted Tablespace That Uses the Default Algorithm
You can use theCREATE TABLESPACE
SQL statement to create an encrypted tablespace that uses the default algorithm.
Parent topic: Creating an Encrypted New Tablespace
3.4.3.3.1 About Creating Encrypted Tablespaces
To create an encrypted tablespace, you can use the CREATE TABLESPACE
SQL statement.
You must have the CREATE TABLESPACE
system privilege to create an encrypted tablespace.
You can import data into an encrypted tablespace by using Oracle Data Pump. You can also use a SQL statement such as CREATE TABLE...AS SELECT...
or ALTER TABLE...MOVE...
to move data into an encrypted tablespace. The CREATE TABLE...AS SELECT...
statement creates a table from an existing table. The ALTER TABLE...MOVE...
statement moves a table into the encrypted tablespace.
For security reasons, you cannot encrypt a tablespace with the NO SALT
option.
You can query the ENCRYPTED
column of the DBA_TABLESPACES
and USER_TABLESPACES
data dictionary views to verify if a tablespace was encrypted.
See Also:
Oracle Database Reference for more information about the DBA_TABLESPACES
and USER_TABLESPACES
data dictionary views
Parent topic: Step 3: Create the Encrypted Tablespace
3.4.3.3.2 Creating an Encrypted Tablespace
To create an encrypted tablespace, you must use the CREATE TABLESPACE
statement with the ENCRYPTION USING
clause.
-
Log in to the database instance as a user who has been granted the
CREATE TABLESPACE
system privilege.In a multitenant environment, log in to the PDB. For example:
sqlplus sec_admin@hrpdb as syskm Enter password: password Connected.
To find the available PDBs, query the
DBA_PDBS
data dictionary view. To check the current PDB, run theshow con_name
command. -
Run the
CREATE TABLESPACE
statement, using its encryption clauses.For example:
CREATE TABLESPACE encrypt_ts DATAFILE '$ORACLE_HOME/dbs/encrypt_df.dbf' SIZE 1M ENCRYPTION USING 'AES256' ENCRYPT;
In this specification:
-
ENCRYPTION USING 'AES256' ENCRYPT
specifies the encryption algorithm and the key length for the encryption. TheENCRYPT
clause encrypts the tablespace. Enclose this setting in single quotation marks (' '). The key lengths are included in the names of the algorithms. If you do not specify an encryption algorithm, then the default encryption algorithm,AES128
, is used.
-
3.4.3.3.3 Example: Creating an Encrypted Tablespace That Uses AES192
You can use the CREATE TABLESPACE
SQL statement to create an encrypted tablespace.
Example 3-8 creates a tablespace called securespace_1
that is encrypted using the 3DES
algorithm. The key length is 168 bits.
Example 3-8 Creating an Encrypted Tablespace That Uses AES192
CREATE TABLESPACE securespace_1 DATAFILE '/home/user/oradata/secure01.dbf' SIZE 150M ENCRYPTION USING 'AES192' ENCRYPT;
Parent topic: Step 3: Create the Encrypted Tablespace
3.4.3.3.4 Example: Creating an Encrypted Tablespace That Uses the Default Algorithm
You can use the CREATE TABLESPACE
SQL statement to create an encrypted tablespace that uses the default algorithm.
Example 3-9 creates a tablespace called securespace_2
. Because no encryption algorithm is specified, the default encryption algorithm (AES128
) is used. The key length is 128 bits.
You cannot encrypt an existing tablespace.
Example 3-9 Creating an Encrypted Tablespace That Uses the Default Algorithm
CREATE TABLESPACE securespace_2 DATAFILE '/home/user/oradata/secure01.dbf' SIZE 150M ENCRYPTION ENCRYPT;
Parent topic: Step 3: Create the Encrypted Tablespace
3.4.4 Setting the Tablespace Encryption Default Algorithm
The TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM
applies to specific encryption scenarios.
- Encryption commands that do not allow to specify the encryption algorithm
- New tablespaces that are created without the encryption syntax
- The encryption algorithm for the
SYSTEM
tablespace
TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM
only becomes effective if it is set before the first SET KEY
operation with Oracle Key Vault, or the CREATE KEYSTORE
command for software keystore-based TDE configuration.
TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM
applies to both offline and online tablespace encryption operations. It also applies to future encrypted tablespaces, if TABLESPACE_ENCRYPTION
has been set appropriately. In a multitenant environment, you can set TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM
in the CDB root or in individual PDBs.
Parent topic: Encryption Conversions for Tablespaces and Databases
3.4.5 Encrypting Future Tablespaces
You can configure Oracle Database to automatically encrypt future tablespaces that you will create.
- About Encrypting Future Tablespaces
TheENCRYPT_NEW_TABLESPACES
dynamic database initialization parameter controls if future tablespaces are encrypted. - Setting Future Tablespaces to be Encrypted
You can set theENCRYPT_NEW_TABLESPACES
database initialization parameter to automatically encrypt future tablespaces that you create.
Parent topic: Encryption Conversions for Tablespaces and Databases
3.4.5.1 About Encrypting Future Tablespaces
The ENCRYPT_NEW_TABLESPACES
dynamic database initialization parameter controls if future tablespaces are encrypted.
By default, all Oracle Cloud databases are encrypted. If you install an off-the-shelf application into such a database, its installation scripts most likely do not have the encryption syntax. In this case, because ENCRYPT_NEW_TABLESPACES
is set to CLOUD_ONLY
, those tablespaces would be created encrypted regardless.
In an Oracle Cloud environment, the following scenarios may occur when you create encrypted tablespaces in Oracle Cloud and on-premises environments:
-
You create a test database in Oracle Cloud and the tablespaces were encrypted by using when the
ENCRYPT_NEW_TABLESPACE
parameter has been set to automatically create the Cloud database as encrypted. However, you may not have the intention or even an Advanced Security Option license to bring the encrypted database back on premises. For this use case, Oracle Recovery Manager (Oracle RMAN) provides the option to duplicate or restoreAS DECRYPTED
. -
In this case, you either need an Advanced Security license (which includes Transparent Data Encryption and Data Redaction) for the on-premises standby database, or you cannot use the standby database. If a new tablespace is transparently encrypted, then a similar scenario to the first item in this list may occur. For example, suppose you do not have an Advanced Security Option (ASO) license, and you have an automatically encrypted tablespace in the Oracle Cloud. The standby database on premises is also automatically encrypted. In this case, you either need an Advanced Security license (which includes Transparent Data Encryption and Data Redaction) for the on-premises standby database, or you cannot use the standby database.
Parent topic: Encrypting Future Tablespaces
3.4.5.2 Setting Future Tablespaces to be Encrypted
You can set the ENCRYPT_NEW_TABLESPACES
database initialization parameter to automatically encrypt future tablespaces that you create.
Related Topics
Parent topic: Encrypting Future Tablespaces
3.4.6 Encryption Conversions for Existing Offline Tablespaces
You can perform offline encryption conversions by using the ALTER TABLESPACE
SQL statement OFFLINE
, ENCRYPT
, and DECRYPT
clauses.
- About Encryption Conversions for Existing Offline Tablespaces
You can encrypt or decrypt an existing data file of a user tablespace when the tablespace is offline or when the database is not open. - Encrypting an Existing User-Defined Tablespace with Offline Conversion
To encrypt an existing tablespace with offline conversion, you can use theALTER TABLESPACE
SQL statement with theOFFLINE
andENCRYPT
clauses. - Decrypting an Existing Tablespace with Offline Conversion
To decrypt an existing tablespace with offline conversion, you can use theALTER TABLESPACE
SQL statement with theOFFLINE
andDECRYPT
clauses.
Parent topic: Encryption Conversions for Tablespaces and Databases
3.4.6.1 About Encryption Conversions for Existing Offline Tablespaces
You can encrypt or decrypt an existing data file of a user tablespace when the tablespace is offline or when the database is not open.
Use the offline encryption method if you do not plan to change the compatibility of your databases from Oracle Database 11g release 2 (11.2) or Oracle Database 12c release 1 (12.1) to release 12.2, which is irreversible. The offline encryption method is also useful if you want to quickly make use of Transparent Data Encryption before you upgrade this database to release 12.2. You can both encrypt and decrypt offline tablespaces.
Note the following:
-
If you want to encrypt the Oracle Database-supplied tablespaces (
SYSTEM
,SYSAUX
, andUNDO
) using the offline conversion method, then you must use the method that is described in Encrypting an Existing Database with Offline Conversion. -
You can use the online method to rekey, change the encryption algorithm, or decrypt a tablespace that was previously encrypted with the offline method.
-
If you have configured Oracle Data Guard, then you can minimize downtime by encrypting the tablespaces on the standby first, switching over to the primary, and then performing an offline encryption the tablespaces on the new standby database. Offline encryption (both on the data file and tablespace level) are is performed on the standby first. Online encryption is an Oracle Data Guard transaction, and as such, it is replayed on the standby database.
-
You cannot specify the encryption algorithm in an offline conversion. In an offline conversion, all data files and tablespaces are encrypted using the
AES128
encryption key. You can check the encryption key by querying theENCRYPTIONALG
column in theV$DATABASE_KEY_INFO
view. -
You can convert offline tablespaces in parallel by using multiple foreground sessions to encrypt different data files.
-
If you are using Oracle Data Guard, you can minimize the downtime by encrypting the tablespaces on the standby first, switching over, and then encrypting the tablespaces on the original primary next.
-
For Oracle Database 11g release 2 (11.2.0.4) and Oracle Database 12c release 1 (12.1.0.2), you cannot perform an offline encryption of the
SYSTEM
andSYSAUX
tablespaces. Also, Oracle does not recommend encrypting offline theUNDO
tablespace in these releases. Doing so prevents the keystore from being closed, and this prevents the database from functioning. In addition, encrypting theUNDO
tablespace while the database is offline is not necessary because all undo records that are associated with any encrypted tablespaces are already automatically encrypted in theUNDO
tablespace. If you want to encrypt theTEMP
tablespace, you must drop and then recreate it as encrypted.
Parent topic: Encryption Conversions for Existing Offline Tablespaces
3.4.6.2 Encrypting an Existing User-Defined Tablespace with Offline Conversion
To encrypt an existing tablespace with offline conversion, you can use the ALTER TABLESPACE
SQL statement with the OFFLINE
and ENCRYPT
clauses.
SYSTEM
and UNDO
tablespaces cannot be brought offline. If you want to encrypt the tablespaces offline, then you must use the method that is described in Encrypting an Existing Database with Offline Conversion.
Related Topics
Parent topic: Encryption Conversions for Existing Offline Tablespaces
3.4.6.3 Decrypting an Existing Tablespace with Offline Conversion
To decrypt an existing tablespace with offline conversion, you can use the ALTER TABLESPACE
SQL statement with the OFFLINE
and DECRYPT
clauses.
Related Topics
Parent topic: Encryption Conversions for Existing Offline Tablespaces
3.4.7 Encryption Conversions for Existing Online Tablespaces
You can encrypt and decrypt an online existing tablespace by using the ALTER TABLESPACE
SQL statement with the OFFLINE
and ENCRYPT
or DECRYPT
clauses.
- About Encryption Conversions for Existing Online Tablespaces
You can encrypt, decrypt, or rekey existing user tablespaces, and theSYSTEM
,SYSAUX
, andUNDO
tablespace when they are online. - Encrypting an Existing Tablespace with Online Conversion
To encrypt an existing tablespace with online conversion, useALTER TABLESPACE
with theONLINE
andENCRYPT
clauses. - Rekeying an Existing Tablespace with Online Conversion
To rekey an existing tablespace that is online, you can use theREKEY
clause of theALTER TABLESPACE
SQL statement. - Decrypting an Existing Tablespace with Online Conversion
To decrypt an existing tablespace with online conversion, you can use theALTER TABLESPACE
SQL statement withDECRYPT
clause. - Finishing an Interrupted Online Encryption Conversion
If an online encryption process is interrupted, then you can complete the conversion by rerunning theALTER TABLESPACE
statement using theFINISH
clause.
Parent topic: Encryption Conversions for Tablespaces and Databases
3.4.7.1 About Encryption Conversions for Existing Online Tablespaces
You can encrypt, decrypt, or rekey existing user tablespaces, and the SYSTEM
, SYSAUX
, and UNDO
tablespace when they are online.
However, you cannot encrypt, decrypt, or rekey a temporary tablespace online.
An online tablespace can be created by using the ONLINE
clause of the CREATE TABLESPACE
SQL statement. When you encrypt or rekey a tablespace online, the tablespace will have its own independent encryption keys and algorithms.
Note the following:
-
If an offline tablespace has been encrypted, then you can rekey it online to use a different algorithm.
-
You can encrypt multiple tablespaces online in parallel by using multiple foreground sessions to encrypt different tablespaces. Within each tablespace, the data files are encrypted sequentially.
-
If the conversion is interrupted, then you can resume the process by issuing the
FINISH
clause of theALTER TABLESPACE
SQL statement. -
A redo log is generated for each online tablespace conversion.
-
Do not encrypt the
SYSTEM
andUNDO
tablespaces concurrently with other tablespaces. -
You cannot use the transportable tablespace feature with Oracle Data Pump while you are encrypting a tablespace.
-
You cannot run the
ALTER TABLESPACE
statement concurrently with the following features:-
ADMINISTER KEY MANAGEMENT SET KEY
SQL statement -
FLASHBACK DATABASE
SQL statement
-
-
If you are using Oracle-managed files for the data files, then the encryption process rekeys the data files that are associated with the tablespace and then copies or moves them to the default Oracle-managed files location.
-
You can add new files to the tablespace after you have encrypted it. Oracle Database reformats the new file with the new encryption key. Blocks will be encrypted using the new key.
-
Previous operations that took place in the root or the PDB may require the control files to be cross-checked against the data dictionary before you can begin the online conversion process. An
ORA-241 operation disallowed: control file is not yet checked against data dictionary
error may occur. To resolve this problem, restart the root or PDB, and then try issuing the online conversion commands again. - For security reasons, once online conversion processes a data file, Oracle will zero out the original data file before deletion. This prevents the database from leaving ghost data on disk sectors. However, there is a known limitation that can occur if you are performing an online tablespace conversion at the same time that Oracle Recovery Manager (Oracle RMAN) is validating files. The online tablespace conversion processes each file one at a time. If Oracle RMAN is validating a file at the same time that it is being processed by the online tablespace conversion, then Oracle RMAN could report a corruption problem (
ORA-01578: ORACLE data block corrupted (file # , block # )
). It does this because it sees the blocks that comprise the file as zero. This is a false alarm and you can ignore the error. If this occurs, then try running the Oracle RMAN validation process again.
Related Topics
Parent topic: Encryption Conversions for Existing Online Tablespaces
3.4.7.2 Encrypting an Existing Tablespace with Online Conversion
To encrypt an existing tablespace with online conversion, use ALTER TABLESPACE
with the ONLINE
and ENCRYPT
clauses.
STATUS
column of the V$ENCRYPTED_TABLESPACES
dynamic view. The ENCRYPTIONALG
column of this view shows the encryption algorithm that is used. If the conversion process was interrupted, then you can resume it by running ALTER TABLESPACE
with the FINISH
clause. For example, if the primary data file converts but the standby data file does not, then you can run ALTER TABLESPACE ... FINISH
on the standby database for the standby data files.
3.4.7.3 Rekeying an Existing Tablespace with Online Conversion
To rekey an existing tablespace that is online, you can use the REKEY
clause of the ALTER TABLESPACE
SQL statement.
-
You cannot rekey the
TEMP
tablespace. If you want to assign a different encryption algorithm to aTEMP
tablespace, then dropTEMP
and recreate it with the correct encryption algorithm. -
Do not perform an online tablespace rekey operation with a master key operation concurrently. To find if any tablespaces are currently being rekeyed, issue the following query to find the rekey status of encrypted tablespaces:
SELECT TS#,ENCRYPTIONALG,STATUS FROM V$ENCRYPTED_TABLESPACES;
A status of
REKEYING
means that the corresponding tablespace is still being rekeyed. Do not rekey the master key while this status is in effect.
To rekey an existing tablespace with online conversion:
ALTER TABLESPACE
with the FINISH
clause.
3.4.7.4 Decrypting an Existing Tablespace with Online Conversion
To decrypt an existing tablespace with online conversion, you can use the ALTER TABLESPACE
SQL statement with DECRYPT
clause.
ALTER TABLESPACE
with the FINISH
clause.
3.4.7.5 Finishing an Interrupted Online Encryption Conversion
If an online encryption process is interrupted, then you can complete the conversion by rerunning the ALTER TABLESPACE
statement using the FINISH
clause.
ORA-28425: missing a valid FILE_NAME_CONVERT clause
error in the FILE_NAME_CONVERT
clause of the ALTER TABLESPACE
SQL statement. Other examples of interrupted processes are if the conversion skips a data file, which can happen if there is an error when an Oracle DataBase WRiter (DBWR) process offlines a data file, or if there is not enough space for the auxiliary file. The tablespace should be operational even if you do not rerun the ALTER TABLESPACE
statement with the FINISH
clause.
Parent topic: Encryption Conversions for Existing Online Tablespaces
3.4.8 Encryption Conversions for Existing Databases
You can encrypt both offline and online databases.
- About Encryption Conversions for Existing Databases
The encryption conversion of an entire database encrypts all tablespaces, including the Oracle-suppliedSYSTEM
,SYSAUX
,UNDO
, andTEMP
tablespaces. - Encrypting an Existing Database with Offline Conversion
When you encrypt an existing database with offline conversion, you do not specify an encryption algorithm. - Encrypting an Existing Database with Online Conversion
When you encrypt an existing database with online conversion, you do not specify an encryption algorithm.
Parent topic: Encryption Conversions for Tablespaces and Databases
3.4.8.1 About Encryption Conversions for Existing Databases
The encryption conversion of an entire database encrypts all tablespaces, including the Oracle-supplied SYSTEM
, SYSAUX
, UNDO
, and TEMP
tablespaces.
Note the following:
-
To perform the encryption, you can use the offline and online functionality of the tablespace encryption conversions.
-
You can encrypt any or all of the Oracle-supplied tablespaces, and in any order. The encryption of the Oracle-supplied tablespaces has no impact on the encryption of user-created tablespaces.
-
When you encrypt the Oracle-supplied tablespaces, Oracle Database prevents the keystore from being closed.
-
You cannot encrypt an existing temporary tablespace, but you can drop the existing temporary tablespace and then recreate it as encrypted.
-
The
UNDO
andTEMP
metadata that is generated from sensitive data in an encrypted tablespace is already automatically encrypted. Therefore, encryptingUNDO
andTEMP
is optional. -
Oracle recommends that you encrypt the Oracle-supplied tablespaces by using the default tablespace encryption algorithm,
AES128
. However, you can rekey any of these tablespaces afterwards to use a different encryption algorithm if you want. (To find the current encryption key for the current database, you can query theV$DATABASE_KEY_INFO
dynamic view.) -
The performance effect of encrypting all the tablespaces in a database depends on the workload and platform. Many modern CPUs provide built-in hardware acceleration, which results in a minimal performance impact.
-
In a multitenant environment, you can encrypt any tablespaces in any pluggable databases (PDBs), including the Oracle-supplied tablespaces. However, the keystore in the CDB root must be open at all times so that a PDB can open its keystore. You can check the status of whether a keystore is open by querying the
STATUS
column of theV$ENCRYPTION_WALLET
view
Parent topic: Encryption Conversions for Existing Databases
3.4.8.2 Encrypting an Existing Database with Offline Conversion
When you encrypt an existing database with offline conversion, you do not specify an encryption algorithm.
See Rotating the TDE Master Encryption Key for a Tablespace if you want to change the encryption algorithm of the tablespace.
Parent topic: Encryption Conversions for Existing Databases
3.4.8.3 Encrypting an Existing Database with Online Conversion
When you encrypt an existing database with online conversion, you do not specify an encryption algorithm.
ALTER TABLESPACE ENCRYPTION REKEY
SQL statement after the initial encryption.
Related Topics
Parent topic: Encryption Conversions for Existing Databases
3.5 Transparent Data Encryption Data Dynamic and Data Dictionary Views
You can query a set of dynamic and data dictionary views to find more information about Transparent Data Encryption (TDE) data.
Table 3-3 describes these dynamic and data dictionary views.
Table 3-3 Transparent Data Encryption Related Views
View | Description |
---|---|
|
Displays encryption information about encrypted columns in the tables accessible to the current user |
|
Displays encryption information for all of the encrypted columns in the database |
|
Displays encryption information for encrypted table columns in the current user's schema |
|
Describes tablespace usage metrics for all types of tablespaces, including permanent, temporary, and undo tablespaces |
|
Lists the properties of the strings (secrets) that were stored in the keystore for various features (clients). In a multitenant environment, when you query this view in a PDB, then it displays information about keys that were created or activated for the current PDB. If you query this view in the root, then it displays this information about keys for all of the PDBs. |
|
Displays information about the default encryption key that is used for the current database. The default is |
|
Displays information about the tablespaces that are encrypted |
|
When used with keys that have been rotated with the In a multitenant environment, when you query this view in a PDB, it displays information about keys that were created or activated for the current PDB. If you query this view in the root, it displays this information about keys for all of the PDBs. |
|
Displays information on the status of the keystore and the keystore location for TDE |
|
Displays supported encryption algorithms in the current PDB and is used by Oracle Recovery Manager (Oracle RMAN) to validate user-requested algorithms |
See Also:
Oracle Database Reference for detailed information about these views
Parent topic: Configuring Transparent Data Encryption