In this article, we will see activating Encryption at pdb level. Before activating activation at pdb level we must open keystore at ROOT level else you will get error “ORA-46661: keystore not open in root container”.

Step 1: We need to set ENCRYPTION_WALLET_LOCATION in sqlnet.ora file

[oracle@localhost ~]$ mkdir -p /u01/oracle/wallet
[oracle@localhost admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/oracle/product/12.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
ENCRYPTION_WALLET_LOCATION=
               (SOURCE=
               (METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/oracle/wallet))
               )

Step 2: Reload listener

[oracle@localhost admin]$ lsnrctl reload

Step 3: User who is invoking this operation must have  ADMINISTER KEY MANAGEMENT privilege. Now connect to cdb$root.

[oracle@localhost admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Sep 12 10:06:24 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>  ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/oracle/wallet' identified by password;

keystore altered.

Step 4: Open keystore in the cdb$root

SQL>  conn system/oracle@updb
Connected.
SQL>  ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password CONTAINER=CURRENT;

keystore altered.

Step 5: Connect to pdb and grant ADMINISTER KEY MANAGEMENT privilege

SQL> conn system/oracle@pdb1
Connected.
SQL> grant ADMINISTER KEY MANAGEMENT to system; 
Grant succeeded.

Step 6: Open keystore at pdb level

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password CONTAINER=CURRENT; 
keystore altered.

Step 7: Let’s create one table with column encryption

SQL>  create table jagruti.details(no number,name varchar2(100) encrypt);

Table created.

Step 8: Insert records in this table

SQL> insert into jagruti.details values(1,'jagruti');

1 row created.

SQL> insert into jagruti.details values(2,'mamta');

1 row created.

SQL> insert into jagruti.details values(3,'zarana');

1 row created.

SQL> commit;

Commit complete.

Step 9: Select record from this table

SQL> select * from jagruti.details;

        NO
----------
NAME
--------------------------------------------------------------------------------
         1
jagruti

         2
mamta

         3
zarana

Step 10: Let’s simulate a scenario where I will close the wallet manually and will try to read encrypt column values

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE close IDENTIFIED BY password CONTAINER=CURRENT
  2  ;

keystore altered.

Step 11: Select record from details table

SQL>  select * from jagruti.details;
 select * from jagruti.details
                       *
ERROR at line 1:
ORA-28365: wallet is not open

Step 12: Let’s reopen wallet and check the same operation

SQL>  ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password CONTAINER=CURRENT
  2  ;

keystore altered.

SQL> select * from jagruti.details;

        NO
----------
NAME
--------------------------------------------------------------------------------
         1
jagruti

         2
mamta

         3
zarana


If you want to be updated with all our articles send us an Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.