Oracle Cloud (DBaaS) Security: Move PDB with exporting the TDE master Key in Oracle 12.2

ByJoel Pérez ( Oracle ACE Director )

Oracle Cloud (DBaaS) Security: Move PDB with exporting the TDE master Key in Oracle 12.2

Oracle Cloud (DBaaS) Security: Move PDB with exporting the TDE master Key in Oracle 12.2

Oracle 12.2 中通过导出TDE主密钥实现PDB迁移
PDB迁移:Oracle 12.2 中通过导出TDE主密钥快速实现

 

By Joel Peréz Skant Gupta

 

This article is brought by en.enmotech.com

Subscribe to our Oracle Cloud Blog: blog.enmotech.com

 

Hi Readers

 

Security is one of the main issues that customers think at the time of moving or having the databases, applications and more into the Cloud. Security has many layers, areas, sections and more. When we work with databases “On-Prem”, security in many tasks and configurations is optional but when we work in Cloud, many of them are mandatory.

 

In this article our focused will be based on Oracle Advanced Security TDE and PDB working in DBCS (Database Cloud Service).

 

Oracle Advanced Security TDE provides the ability to encrypt sensitive application data on storage media completely transparent to the application itself. TDE addresses encryption requirements associated with public and private privacy

and security mandates such as PCI and California SB1386. Oracle Advanced Security TDE column encryption was introduced in Oracle Database 10g Release 2, enabling encryption of application table columns, containing credit card or social security numbers. Oracle Advanced Security TDE tablespace encryption was introduced with Oracle Database 11gR1, being the main focus of our article.

Oracle Multitenant Architecture is one of the key points for having a great scalability for moving and upgrades databases into the Cloud. Moving, creating PDB databases in “On-prem” does not have too much complexity if we are not working with security features but if we are.. we have to take into account additional things.

When you create your CDB database using DBCS is mandatory to have at least a PDB in the minimal configuration, that PDB has already a TDE Master Key created to fulfil conditions related to secure our data but when we create a new PDB into that CDB the TDE Master Key is not created by default, however we can create it without no problem. Later when you will create the first user-defined tablespace is when you will receive an error if the TDE Master Key is not already created for that new PDB.

 

The first time We were creating a new PDB and a new tablespace within it We got this problem, this article shows how to solve it and the procedure to administer TDE Master Keys working with PDB.

 

This article shows how to move the PDB in different DBCS Multitenant environment wit exporting TDE master key.

Steps:

1. Create the Database Cloud Service database.

2. Create New PDB in Multitenant environment.

3. Create new master key and create demo table

4. Unplug the PDB whilst exporting the TDE master key

5. Move the data unplugged PDB to different DBCS

6. Plug-in the unplugged PDB and show TDE master key

 

Create the new Cloud Database Service

a) Login to your Oracle cloud services account, go to the “Oracle Database Cloud Service” page and create a new service.

 

  • For Service Name, select PDB-Security.
  • From the Service Level list, select Oracle Database Cloud Service.
  • From the Metering Frequency list, select whatever frequency is appropriate for your environment.
  • From the Software Release list, select Oracle Database 12c Release2.
  • From the Software Edition list, select Enterprise Edition.
  • From the Database Type list, select Single Instance.

 

Then click Next to continue.

 

 

 

 

 

 

 

 

 

 

 

Figure1. Creating a new service (PDB-Security)

 

b) In the Service Details screen, do the following:

  • For DB Name (SID), enter ORCL.
  • For PDB Name, enter PDB1.
  • Set an administrative password of your choice and confirm the password (this will be your sys password and master key password).
  • For Usable Database Storage (GB), enter 25.
  • From the Compute Shape list, select OC3 -1 OCPU, 7.5GB RAM (this is the bare minimum required).
  • For SSH Public Key, enter rsa-key-20170111.pub.

 

Then click Next to continue.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Figure 2. Specifying the service details

 

c) Finally, review the configuration and click Create to create your cloud database.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Figure 3. Creating the cloud database instance

 

c) After a few minutes, the cloud database instance has been created successfully.

 

 

 

 

 

 

 

 

 

 

Figure 4. The cloud database has been created

 

d) Click the service name (PDB-Security) to open the main page of the database.

 

 

 

 

 

 

 

 

 

 

 

 

Figure 5. Main page of the cloud database

 

Connect the Cloud Database

1. Open the instance of the PuTTY executable and connect to the machine using SSH public key.

Figure 6. Connecting to machine using PuTTY

 

 

Create New PDB in Multitenant environment

 

a. On the cloud database host, invoke SQL*Plus and log in to the cloud database as the SYS user.

[oracle@PDB-Security ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 11 18:53:09 2017

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

SQL>

 

b. Create the new NEWPDB PDB in the DBaaS CDB

SQL> create pluggable database newpdb admin user admin identified by “Welcome_1”;

 

Pluggable database created.

 

c. Open the new PDB in the DBaaS CDB and check the status of PDB

SQL> alter pluggable database newpdb open read write;

 

Pluggable database altered.

 

SQL> show pdbs;

 

CON_ID CON_NAME OPEN MODERESTRICTED

———- —————————— ———- ———-

2 PDB$SEED READ ONLYNO

3 PDB1 READ WRITE NO

4 NEWPDB READ WRITE NO

 

Create a new master key and dummy table

 

a. Open the wallet for new PDB

SQL>alter session set container=newpdb;

 

Session altered.

 

SQL>administer key management set keystore open force keystore identified by “MyKey#123”;

 

keystore altered.

 

b. Create the new master key of new PDB

SQL> administer key management set key force keystore identified by “MyKey#123” with backup;

 

keystore altered.

 

c. Check the new master created for New PDB.

SQL> select pdb.name, e.key_id, to_char(e.creation_time,’DD-MON-YY HH24:MI:SS’) created from v$encryption_keys e, v$pdbs pdb where pdb.con_id=e.con_id order by pdb.name desc, created;

 

NAME KEY_ID CREATED

——————– —————————————————————————— —————————

PDB1 Abw1aBB/Pk8Cvwxg6bwJKcgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 10-AUG-17 14:17:54

NEWPDB AXkR6FF3pU+xv5FJLzWyerMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 11-AUG-17 19:08:18

 

d. Now create the tablespace in newpdb

SQL>create tablespace test;

 

Tablespace created.

 

e. Now create the new user

SQL>create user demo identified by demo account unlock;

 

User created.

 

SQL>grant connect, resource, unlimited tablespace to demo;

 

Grant succeeded.

 

SQL>alter user demo default tablespace test;

 

User altered.

 

f. Connect with new user and create dummy table and load some data into it.

SQL>connect demo/demo@localhost/newpdb.gbvodafone.oraclecloud.internal

Connected.

 

SQL>show user

USER is “DEMO”

SQL>create table test as select table_name from all_tables;

 

Table created.

 

SQL>select count(*) from test;

 

COUNT(*)

———-

110

 

Unplug the PDB while exporting the TDE master key

 

a. On the cloud database host, invoke SQL*Plus and log in to the cloud database as the SYS user.

[oracle@PDB-Security ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 11 19:36:34 2017

 

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

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

 

SQL>alter session set container=cdb$root;

 

Session altered.

 

b. Close the NEWPDB PDB.

SQL>alter pluggable database newpdb close immediate;

 

Pluggable database altered.

 

c. Unplug the PDB. The following command generates the XML file with TDE master key.

SQL>alter pluggable database newpdb unplug into ‘/tmp/newpdb.xml’ encrypt using “MasterKey”;

 

Pluggable database altered.

 

Move the data unplugged PDB to different DBCS

 

a. On the another Database Cloud Service compute node ,create a directory for XML and database files. We can not repeat the steps for creating new Database Cloud Instance.

[oracle@PDB-Security ~]$echo “mkdir -p /u02/app/oracle/oradata/ORCL/NEWPDB” | ssh -i /home/oracle/key.ssh oracle@141.144.32.70

Enter passphrase for key ‘/home/oracle/key.ssh’:

 

b. Tar the datafile for PDB and use the SCP utility to transfer the XML and datafiles to another instance.

[oracle@PDB-Security ~]$ cd /u02/app/oracle/oradata/ORCL/NEWPDB/datafile

 

[oracle@PDB-Security ~]$ tar -cvzf /tmp/newpdb.tar.gz *

 

[oracle@PDB-Security ~]$ scp -i /home/oracle/key.ssh /tmp/newpdb* oracle@141.144.32.70:/u02/app/oracle/oradata/ORCL/NEWPDB

 

Enter passphrase for key ‘/home/oracle/key.ssh’:

newpdb.tar.gz 100% 244MB 243.7MB/s 00:01

newpdb.xml 100% 13KB 13.2KB/s 00:00

 

c. Untar the file on destination server

[oracle@PDB-Security ~]$ echo “cd /u02/app/oracle/oradata/ORCL/NEWPDB ; tar xvzf newpdb.tar.gz” | ssh -i /home/oracle/key.ssh oracle@141.144.32.70

Enter passphrase for key ‘/home/oracle/key.ssh’:

o1_mf_sysaux_drvzxbhr_.dbf

o1_mf_system_drvzxbhh_.dbf

o1_mf_temp_drvzxbhw_.dbf

o1_mf_test_drw05q9c_.dbf

o1_mf_undotbs1_drvzxbht_.dbf

 

Plug-in the unplugged PDB and show TDE master key

 

a. On the destination cloud database host, invoke SQL*Plus and log in to the cloud database as the SYS user.

[oracle@PDB-Migration~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 11 18:53:09 2017

 

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

 

SQL>

 

b. Create the PDB in the DBaaS CDB with XML and master key.

SQL>create pluggable database newpdb

using ‘/u02/app/oracle/oradata/ORCL/NEWPDB/newpdb.xml’

decrypt using “MasterKey”

keystore identified by “MyKey#123”

source_file_directory=’/u02/app/oracle/oradata/ORCL/NEWPDB’;

 

Pluggable database created.

 

c. Open the PDB in Read Write Mode.

SQL>alter pluggable database newpdb open;

 

Pluggable database altered.

 

d. Connect with demo user and check data in the dummy table

SQL>connect demo/demo@localhost/newpdb.gbvodafone.oraclecloud.internal

Connected.

 

SQL>select count(*) from test;

 

COUNT(*)

———-

110

 

e. Check the master TDE for migrated PDB

SQL>select pdb.name, e.key_id, to_char(e.creation_time,’DD-MON-YY HH24:MI:SS’) created

from v$encryption_keys e, v$pdbs pdb

where pdb.con_id=e.con_id

order by pdb.name desc, created;

 

NAME KEY_ID CREATED

——————– —————————————————————————— —————————

PDB1 Abw1aBB/Pk8Cvwxg6bwJKcgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 10-AUG-17 14:17:54

NEWPDB AXkR6FF3pU+xv5FJLzWyerMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 11-AUG-17 19:08:18

 

Conclusion

So now you know that it is very easy to move the PDB in different DBCS Multitenant environment wit exporting TDE master key.

We hope this article is useful and we invite you to continue reading our next publications focused on Oracle Cloud.

 

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

Joel Perez’s LinkedIn: www.linkedin.com/in/SirDBaaSJoelPerez

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

or Join our LinkedIn group: Oracle Cloud DBaaS

 

Author Bios:

 

Joel Pérez is an Expert DBA ( Oracle ACE Director, Maximum Availability OCM, OCM Cloud Admin. & OCM12c/11g ) with over 17 years of Real World Experience in Oracle Technology, specialised in design and implement solutions of: High Availability, Disaster Recovery, Upgrades, Replication, Cloud and all area related to Oracle Databases. International consultant with duties, conferences & activities in more than 50 countries and countless clients around the world. Habitual and one of leading writers of Technical Oracle articles for: OTN Spanish, Portuguese, English and more. Regular Speaker in worldwide Oracle events like: OTN LAD (Latin America), OTN MENA (Middle East & Africa), OTN APAC ( Asian Pacific), DTCC China, Oracle Code.. . Joel has always been known for being a pioneer in Oracle technology since the early days of his career being the first Latin American awarded as “OTN Expert” at year 2003 by Oracle Corp., one of the first “Oracle ACE” globally in the Oracle ACE Program at year 2004. He was honoured as one of the first “OCM Database Cloud Administrator” & Maximum Availability OCM in the world. Currently Joel works as Senior Cloud Database Architect in “Yunhe Enmo (Beijing) Technology Co.,Ltd”., company located in Beijing, China En.enmotech.com

 

Skant Gupta is an Oracle Certified Cloud Professional in Oracle Database 12c, an Oracle Certified Expert in Oracle Real Application Clusters (Oracle RAC) in Oracle Database 11g and 12c, and an Oracle Exadata Certified and an Oracle Certified Professional in Oracle Database 10g, 11g, and 12c. He works at Vodafone Technology in the UK and formerly worked as a senior DBA at Etisalat in Dubai. He has six years of experience with various Oracle technologies, focusing mainly on Cloud, database, and high availability solutions, Oracle WebLogic Suite, Oracle Exadata and Oracle GoldenGate. He has presented at several Oracle user groups worldwide, most recently in the US, the United Arab Emirates, and the India. Skant website link: oracle-help.com

 

This article is brought by Yunhe Enmo (Beijing) Technology Co.,LTD En.enmotech.com

Follow our Company in Linkedin: https://www.linkedin.com/company/1856106/

Subscribe to our Blog and enjoy our Weekly Cloud article..!

About the author

Joel Pérez ( Oracle ACE Director ) administrator