DBA Hub

📋Steps in this guide1/6

Exclude ENCRYPTION Clause on Import in Oracle Database 19c

This article demonstrates how to use the OMIT_ENCRYPTION_CLAUSE of the import TRANSFORM parameter in Oracle database 19c.

oracle 19cconfigurationintermediate
by OracleDba
15 views
1

Create a Key Management User

Login as a privileged user and create a new key management user, granting it the administrative privilege.

Code/Command (click line numbers to comment):

1
2
3
4
conn sys/SysPassword1@//localhost:1521/cdb1 as sysdba

create user c##km_user identified by MyPassword1;
grant syskm to c##km_user container=all;
2

Wallet Setup

We create a location to hold our wallet. Edit the "sqlnet.ora" file, setting the correct location for the wallet. If you are using a read-only Oracle home, the "sqlnet.ora" file is stored outside the main Oracle home. We connect to the root container using the key management user and create a new wallet. We can see the wallet listed in the view.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mkdir -p /u01/wallets/cdb1

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)(METHOD_DATA =
    (DIRECTORY = /u01/wallets/cdb1/)))

-- Normal Oracle home.
$ORACLE_HOME/network/admin/sqlnet.ora

-- Read-only Oracle home.
$(orabasehome)/network/admin/sqlnet.ora

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;

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>
3

Create Table With Encrypted Column

Create and populate a table with an encrypted column.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
conn testuser1/testuser1@pdb1

create table t1 (
  id    number(10),
  data  varchar2(50) encrypt
);

insert into t1 values (1, 'This is a secret!');
commit;
4

Export Table

First we create a directory object and grant access to our test user. Next we export the table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
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;

expdp testuser1/testuser1@pdb1 \
      tables=t1 \
      directory=tmp_dir \
      logfile=t1-exp.log \
      dumpfile=t1.dmp
5

Import Table

We create the table by importing the dump file and remapping the table to the table. We use the option in the parameter.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
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
6

Compare Columns

When we compare the descriptions of the two tables we can see the column in the table is encrypted, but the column in the table is not encrypted. As a result, only the column is listed in the view. We can see the data is still consistent in both the and tables. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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>

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>

select * from t1;

        ID DATA
---------- --------------------------------------------------
         1 This is a secret!

SQL>


select * from t2;

        ID DATA
---------- --------------------------------------------------
         1 This is a secret!

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!