Encrypting PDB

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