Managing Oracle Database 12c with Enterprise Manager – Part IX

We are discussing the management of Oracle Database 12c via Oracle Enterprise Manager 12c. In our previous blog post on this topic, we looked at how easy it was to set up Oracle Data Redaction for a 12c database using Enterprise Manager - this capability being used to hide confidential data on the fly as it is being displayed.

We will now look at the Database Resource Manager and its new capabilities to handle PDBs in Database 12c, and how it can be set up via Enterprise Manager.

Drill down to the 12c Database target in Enterprise Manager. From the home page of the database, open the Administration menu and choose Resource Manager. The Resource Manager Home page is displayed.

As can be seen, the Database Resource Manager is now CDB aware and allows you to create CDB Resource plans. This will control resources to Pluggable databases inside the CDB. Drill down to the CDB resource plans, this displays the following screen.

Click on “Edit” to define the CDB Resource Plan.

In the Resource Plan, by default, all the PDBs have a “share” of one (1) . There are three PDBs in this container database, as displayed on this page - “PDB(3)”. Therefore, each PDB has a share of 33%. 

You can add a separate Resource Allocation by clicking on the “Add/Remove” button.

Here we have added PDB3 as a separate entry with 2 shares to the Resource Allocation. The remaining PDBs have 1 share each.  This means 50% is the Resource Allocation of PDB3, and the other two PDBs have 25% each. This can be used to cover a scenario where PDB3 is an important application’s pluggable database, and more resources are required to be allocated to this pluggable database.

We are also activating this plan with a simple tick mark on this page. Click on Apply. And we haven’t written a single line of PL/SQL code (which is normally required to define a resource plan if not using Enterprise Manager).

For more detailed information on the powerful capabilities of the Oracle Database Resource Manager, please refer to the chapter “Managing Resources with Oracle Database Resource Manager” in the 12c Database Admin guide.

In the next blog post, we will look at more management aspects of Oracle database 12c using Enterprise Manager, in particular the ability to self-service the creation of PDBs using “Pluggable database (PDB) as a Service (PDBaaS)” – a form of Database as a Service.

This blog post was originally posted at this link.

Managing Oracle Database 12c with Enterprise Manager – Part VIII

We are discussing the management of Oracle Database 12c in Oracle Enterprise Manager 12c. In our previous blog post on this topic, we started to look at Data Redaction, and created a Policy on the HR user and Salary column. We specified Full Redaction as seen below, to hide all the Salary column figures (a zero will be displayed).

Click on “Show SQL”, this displays the PL/SQL code that is generated to create the Data Redaction policy. Even this simple example demonstrates how Enterprise Manager saves a lot of admin time, since it writes the PL/SQL code for the DBA. Fancy writing this manually?

The policy is created successfully. We can see that the policy is “enabled” and there is one redacted column.

So, the Data Redaction Policy has been created successfully and enabled. But how do we verify this? 

From now on, any select statement issued by a non-SYSDBA should show the salary as zero (0) in each case.  This can be seen below in SQL Plus, logging in as the HR database user:

[oracle@db12c admin]$ sqlplus hr/@pdb3

SQL>  select * from employees;

NAME                                     DEPARTMENT               SALARY             JOINDATE

John Smith                            Science                           0                          01-MAR-13

Simon Pereira                        Commerce                      0                          01-APR-13

Whereas, if you login as sysdba, you can see the salary values  since no redaction is applied.

[oracle@db12c admin]$ sqlplus sys@pdb3 as sysdba

SQL>  select * from hr.employees;

NAME                                     DEPARTMENT               SALARY             JOINDATE

John Smith                            Science                           200000                01-MAR-13

Simon Pereira                        Commerce                     500000                01-APR-13

If the concept of Oracle Data Redaction interests you, then you can read further about it in the documentation here.

In the next blog post, we will look at how the Resource Manager can be set up for 12c Databases, via the powerful and time-saving interface of Enterprise Manager 12c.

This blog post was originally posted at this link.

Managing Oracle Database 12c with Enterprise Manager – Part VII

We are discussing the management of Oracle Database 12c in Oracle Enterprise Manager 12c. In our previous blog post on this topic, we looked at how user administration is performed for a 12c Database using Enterprise Manager, such as the creation of a common user for the CDB$ROOT.

Let us talk now about the capability of Oracle Data Redaction. This is a new Oracle Database 12c feature, which camouflages data on the fly when being displayed. It is a feature of Oracle Advanced Security i.e. it is licensed via theAdvanced Security Option (ASO).

Login to Enterprise Manager 12c Cloud Control, and open the (12c) Database target menu. From this menu, selectAdministration.. Security.. Oracle Data Redaction as seen above. This opens the Data Redaction page displayed in the following screenshot.

There are no Data Redaction Policies currently in this database. Click on “Create” to formulate a new policy. The Create Data Redaction Policy page appears.

Name the Data Redaction Policy as “SAL_POLICY” (any appropriate name), and select the schema as HR and table as EMPLOYEES, and also the policy expression. Here we have used 1=1 signifying that all rows will be used.

Note that the HR user was previously created in the PDB3 Pluggable database.

You can now add an Object column to the policy, by clicking on the “Add” button.

Select the Salary column and a Redaction template. Full Redaction will redact (hide) all the data in the column, i.e. the entire Salary figure will be hidden.  Since it is a Numeric column, it will be redacted with a Zero.

We will continue setting up Data Redaction via Enterprise Manager, in the next blog post. The Redaction policy will also be verified by a login test to the database.

This blog post was originally posted at this link.