eMarcel.com

Data Masking with Oracle Enterprise Manager 12c Cloud Control (Grid Conrol)

This tutorial shows you how to identify sensitive data, create Application Data Model, select appropriate mask formats for the sensitive fields, and apply the mask.

First of all I would like to mention that I don't want to reinvent the wheel. I use Oracle 11g/10g tutorial as a reference; Replacing Sensitive Data By Using the Data Masking Pack and I’m going to adopt this tutorial to 12c and its features.

Oracle Data Masking pack is integrated in OEM 12c and can be found in Enterprise > Quality Management top left menu of the OEM 12c.

Prerequisites:

  1. Readers are familiar with Data masking concepts

  2. Oracle EM 12c has been installed

  3. Staging Oracle database 11g has been installed on Linux OS and added to targets in OEM 12c

  4. OEM 12c user  has sufficient privileges to develop and deploy data masking definitions:

o   EM_USER, PUBLIC, EM_ALL_OPERATOR

  5. Database user has sufficient privileges in test database to use data masking packages and execute data masking script. Minimum db privileges required for data masking;

o   SELECT_CATALOG_ROLE

o   SELECT ANY DICTIONARY

o   EXECUTE on DBMS_CRYPTO, DBMS_RANDOM packages

o   ALTER SYSTEM

o   CREATE, DROP, ALTER, SELECT, INSERT, and COMPILE the objects being masked

  6. The predefined masking formats use functions defined in the DM_FMTLIB package. To use the predefined masking formats on a staging database, you must manually install the DM_FMTLIB package on that database. To install the DM_FMTLIB package, see "Installing the DM_FMTLIB Package" .

  7. Download and unzip the 12c_DM_setup.zip file into your working directory. In this tutorial the working directory is /tmp/datamask. This SQL script will create HR_TEST schema with password welcome1 and populate schema with objects and test data. It also provides sql script to create mask_data table with 10k rows of fake data.

The Application Data Model

One of the cool features in OEM 12c is the Application Data Model. ADM stores the list of applications, tables and relationships between table columns that are either declared in the data dictionary, imported from application metadata or user-specified. The Application Data Model maintains sensitive data types and their associated columns and is used by Secure Test Data Management features such as Data Masking and Data Subsetting, to produce test data securely. In Cloud Control 12c a database schema is being called as an application. Application Data Model can hold multiple schemas for example HR, HR_TEST, OE, OE_TEST.

Scenario

In this tutorial, we review the HR_TEST schema in a staging environment to identify sensitive data and then select appropriate mask formats for the sensitive data. After creating the mask, we then proceed to apply the mask and replace the sensitive fields in the production copy with realistic but scrubbed data.

In this guide we are going to cover Oracle Database 11g R2 installation on Linux with ASM.

Automatic Storage Management (ASM) is a feature that has be introduced in Oracle 10g to simplify the storage of Oracle datafiles, controlfiles, logfiles and backups.

ASM will be used as the file system and volume manager for Oracle Database files (data, online redo logs, control files, archived redo logs), and the Fast Recovery Area.

NOTE: ASMLib in the Unbreakable Enterprise Kernel.

ASMLib - Linux specific ASM library and its associated driver.

The Oracle ASMLib kernel driver is now included in the Unbreakable Enterprise Kernel. No driver package needs to be installed when using this kernel.

The oracleasm-support and oracleasmlib packages still need to be installed.

The oracleasm-support  can be installed from Oracle Linux ISO image. For other Linux flavors could be found here – (Download section for particular Linux distribution)

The oracleasmlib can be downloaded from ULN (Unbreakable Linux Network), subscription is required. Non-subscribers are free to use the similar package built for RHEL on their Enterprise Linux machines, which we will use in this tutorial.

What we are going to show:

1. Installing Oracle Enterprise Linux and creating virtual hard disks in VMware for ASM disks

2. Creating recommended OS groups and user for Grid Infrastructure

3. Creating login scripts for grid and oracle users

4. Installing Oracle ASM packages

5. Creating ASM disk volumes

6. Downloading and installing Oracle Grid Infrastructure software

7. Creating ASM disk groups

8. Installing Oracle database software and create database instance 

1. Installing Oracle Enterprise Linux and creating virtual hard disks in VMware for ASM disks

Installation of the Oracle Linux in VMware environment has been described in the following article: Installing Oracle Linux in VMware virtual machine

Once we have Oracle Linux up and running we have to shut it down and create virtual hard disks which will be used for ASM disk groups.

Normally it would be a kind of SAN storage for example, attached to the Linux OS, sometimes several disks visible as multipath devices or raw devices.

However in the end it’s always a set of available disks mounted and accessible for Linux OS like a normal physical disk drives.

We are going to create three virtual hard disks with the following names:

1. CRSDISK1 (100 MB) – for CRSVOL1 disk where Cluster Ready Services (CRS) files will be stored. CRS provides many system management services and interacts with the vendor clusterware to coordinate cluster membership information.

2. DATADISK1 (10 GB) – disk for DATAVOL1 disk. Here database will keep all datafiles, control files, log files …

3. FRADISK1 (10 GB) – disk for FRAVOL1 disk for database Fast Recovery Area (FRA) files. For example: database backup files, copy of database control files.

Open virtual machine properties window and use wizard to create new virtual hard disks:

---

Once virtual hard disks have been created power on virtual machine and let the Oracle Linux start.

Main user for most of the oracle products, especially for Oracle Database installations is an oracle user account. Oracle-validated package does create such account automatically with proper group membership configuration and "random" unknown password. 

In Linux world (following good practice rules) we all have our own, personal and dedicated user accounts which belongs to the particular system groups. For example, employees performing DBA role within an organization would have an account which belongs to "dba", "wheel" and other custom groups. Most of the organizations are dealing with massive OS and database installations which also have "oracle" accounts configured, but always the password of the oracle account should stay unknown to all users. 

But how to switch to oracle user when we don't know the password? Simply – using power of sudo, su in combination with sudoers. 
But first particular account needs to be configured to have sufficient privileges to switch to oracle and other users either with or without providing their own (not root or oracle accounts' passwords) passwords when switching.

Imagine we are a dba within an organization and we have a new account "fred" created in Linux OS.

 
[root@test-host ~]# useradd -m -n fred
[root@test-host ~]# passwd fred
Changing password for user fred.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@test-host ~]# id fred
uid=54322(fred) gid=100(users) groups=100(users)
[root@test-host ~]#
 

As we can see user fred belongs to group "users" by default.

Let's make that user also belong to "dba" group:

 
[root@test-host ~]# usermod -g users -G dba fred 
[root@test-host ~]# id fred
uid=54322(fred) gid=100(users) groups=100(users),54322(dba)
[root@test-host ~]#
 

Now user fred belongs to the following groups: users, dba
Let's try to switch to oracle account using sudo:

In article Installing Oracle Linux in VMware virtual machine we covered installation of Oracle Linux 5U8 including oracle-validated package.

Now, our system is ready for Oracle database installation, and no additional configuration tasks is required at this moment.

Note: Most of the time we work with OS from remote locations and target servers are located in data centers or somewhere else.

In this tutorial we assume that our server is located in a remote location and we use remote access tools in order to connect to the Linux terminal, transfer installation packages, and run Oracle Universal Installer on our local (client) machine.

What we are going to show:

1. Downloading Oracle database 11g installation packages from Oracle websites

2. Installing local X-Server Xming to be able to display GUI applets from a remote server on our local desktop, like we were sitting in front of the server.

3. Connecting to Linux server using WinSCP and transferring installation packages.

4. Connecting to Linux server via SSH protocol using PuTTY  and reset password for oracle user (WinSCP and PuTTY how-to can be found here: Enter the Linux ).

5. Configure PyTTY session with port forwarding enabled to be able to display GUI windows via Xming server

6. Unpacking and running Oracle Universal Installer to install Oracle database software

7. Creating new Oracle database instance with Enterprise Manager Database Control

8. Testing our installation connecting to running database instance using the following tools:

• • SqlPlus (general command line tool)
  • Oracle SQLDeveloper – GUI Oracle database client
  • Enterprise Manager Database Control 11g – web based powerful administration and monitoring tool

Let’s get started…

1. Download Oracle Database software installation package from Oracle Technology Network

Let's download Linux 64bit version Oracle Database 11g Release 2 (11.2.0.1.0) for Linux x86-64 

• linux.x64_11gR2_database_1of2.zip
• linux.x64_11gR2_database_2of2.zip

2. Installing and running Xming X Server on Windows host

---

Xming will start minimized in system tray next to the system clock.

What is Oracle Linux?

Oracle Linux is an open source operating system available under the GNU General Public License (GPL) and is
available for free download .

Oracle Linux includes two kernels:

• Unbreakable Enterprise Kernel, tracks mainline Linux kernel closely, and offers the latest features and
  tested performance and stability
• The Red Hat Compatible Kernel, compiled directly from Red Hat Enterprise Linux source

In this tutorial we are going to install Oracle Linux 5 Update 8 (64 bit) prepared to install Orcale Database 11g on it.

What we are going to cover:

1. New virtual image creation

2. Linux Installation using Linux ISO Image downloaded from Oracle eDelivery website
 

1. cut - Removes sections from each line of files.

Prints selected parts of lines from each file to standard output.
Default separator is a "Tab" sign.

Imagine we've got a file file_numbers.txt, which consist of three columns:

col_1 col_2 col_3
 
one two three
 
four five six
 
seven eight nine
 
ten eleven twelve
 

In the following example cut will return only column (col_2):

cut -f2 file_numbers.txt
 
col_2
two
five
eight
eleven
 

in the next example cut will return columns 2 and 3:

cut -f1,3 file_numbers.txt
 
col_1 col_3
one three
four six
seven nine
ten twelve
 

There os few more options described in man pages. Let's take a look at two of them:

- c --characters=LIST select only these characters

- d --delimiter=DELIM use DELIM instead of TAB for field delimiter

In below example we will use "space" mark as a separator (-d " ")

NOTE: an input delimiter may be specified only when operating on fields