By Joel Peréz ACED.gifSkant Gupta

 

This article is brought by en.enmotech.com

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

 

Full Index of Oracle Cloud Articles:

Sir.CloudDBaaSjoelperez

 

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

 

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.

1.JPG

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.

2.JPG

Figure 2. Specifying the service details

 

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

3.JPG

Figure 3. Creating the cloud database instance

 

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

4.JPG

Figure 4. The cloud database has been created

 

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

5.JPG

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.

6.JPG

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 MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                            READ ONLY       NO

         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

 

Full Index of Oracle Cloud Articles:

Sir.CloudDBaaSjoelperez

 

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