8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Exclude ENCRYPTION Clause on Import in Oracle Database 19c
The OMIT_ENCRYPTION_CLAUSE
option has been added to the import TRANSFORM
parameter in Oracle database 19c. The value of "Y" makes Data Pump supress column encryption clauses for resulting tables. As a result columns that were encrypted in the source table will not be encrypted in the destination table. The default value is "N", making column encryption of the destination table match that of the source table.
- Create a Key Management User
- Wallet Setup
- Create Table With Encrypted Column
- Export Table
- Import Table
- Compare Columns
Related articles.
- Transparent Data Encryption (TDE) in Oracle 10g Database Release 2
- Multitenant : Transparent Data Encryption (TDE) in Pluggable Databases (PDBs) in Oracle Database 12c Release 1 (12.1)
Create a Key Management User
Login as a privileged user and create a new key management user, granting it the SYSKM
administrative privilege.
conn sys/SysPassword1@//localhost:1521/cdb1 as sysdba create user c##km_user identified by MyPassword1; grant syskm to c##km_user container=all;
Wallet Setup
We create a location to hold our wallet.
mkdir -p /u01/wallets/cdb1
Edit the "sqlnet.ora" file, setting the correct location for the wallet.
ENCRYPTION_WALLET_LOCATION = (SOURCE =(METHOD = FILE)(METHOD_DATA = (DIRECTORY = /u01/wallets/cdb1/)))
If you are using a read-only Oracle home, the "sqlnet.ora" file is stored outside the main Oracle home.
-- Normal Oracle home. $ORACLE_HOME/network/admin/sqlnet.ora -- Read-only Oracle home. $(orabasehome)/network/admin/sqlnet.ora
We connect to the root container using the key management user and create a new wallet.
conn c##km_user/MyPassword1@//localhost:1521/cdb1 as syskm administer key management create keystore '/u01/wallets/cdb1' identified by "WalletPassword1"; administer key management set keystore open identified by "WalletPassword1" container=all; administer key management set encryption key identified by "WalletPassword1" with backup container=all;
We can see the wallet listed in the V$ENCRYPTION_WALLET
view.
column wrl_parameter format a20 select wallet_type, wrl_parameter, status from v$encryption_wallet; WALLET_TYPE WRL_PARAMETER STATUS -------------------- -------------------- ------------------------------ PASSWORD /u01/wallets/cdb1/ OPEN SQL>
Create Table With Encrypted Column
Create and populate a table with an encrypted column.
conn testuser1/testuser1@pdb1 create table t1 ( id number(10), data varchar2(50) encrypt ); insert into t1 values (1, 'This is a secret!'); commit;
Export Table
First we create a directory object and grant access to our test user.
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba create or replace directory tmp_dir as '/tmp/'; grant read, write on directory tmp_dir to testuser1;
Next we export the table.
expdp testuser1/testuser1@pdb1 \ tables=t1 \ directory=tmp_dir \ logfile=t1-exp.log \ dumpfile=t1.dmp
Import Table
We create the T2
table by importing the dump file and remapping the T1
table to the T2
table. We use the OMIT_ENCRYPTION_CLAUSE
option in the TRANSFORM
parameter.
impdp testuser1/testuser1@pdb1 \ tables=t1 \ directory=tmp_dir \ logfile=t1-imp.log \ dumpfile=t1.dmp \ remap_table=testuser1.t1:t2 \ transform=omit_encryption_clause:y
Compare Columns
When we compare the descriptions of the two tables we can see the DATA
column in the T1
table is encrypted, but the DATA
column in the T2
table is not encrypted.
SQL> desc t1 Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(10) DATA VARCHAR2(50) ENCRYPT SQL> desc t2 Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(10) DATA VARCHAR2(50) SQL>
As a result, only the T1.DATA
column is listed in the USER_ENCRYPTED_COLUMNS
view.
column table_name format a20 column column_name format a20 select table_name, column_name from user_encrypted_columns; TABLE_NAME COLUMN_NAME -------------------- -------------------- T1 DATA SQL>
We can see the data is still consistent in both the T1
and T2
tables.
select * from t1; ID DATA ---------- -------------------------------------------------- 1 This is a secret! SQL> select * from t2; ID DATA ---------- -------------------------------------------------- 1 This is a secret! SQL>
For more information see:
- TRANSFORM
- Transparent Data Encryption (TDE) in Oracle 10g Database Release 2
- Multitenant : Transparent Data Encryption (TDE) in Pluggable Databases (PDBs) in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...