Introduction to Oracle Database 12c Data redaction

What is Data redaction

The data redaction is a new feature of Oracle Database 12c's Advanced security options, and Oracle China translates it as a "compilation" When it comes to this feature, and in the Chinese interface of em12c R3, this function is translated into "data writing", I think the latter translation is more appropriate.

In the 12c before the release, a "database security" for the topic of technical exchanges, one customer asked us, Oracle database can be implemented such a function: eligible users can see all the data in the table, while others (low-business users) can query the table, However, some sensitive data is all replaced with asterisks. What I was thinking was that there was no feature in 11g that fully satisfied this customer's needs.

Now 12c data redaction can fully meet the requirements of the above mentioned customers. So, if you summarize it in one sentence: Data redaction is based on the policy (condition) in situ (Oracle uses the word on-the-fly) to overwrite the information to protect sensitive data.

Both data redaction and database vault protect sensitive data, and the difference is that in environments where database vault is configured, if access is not compliant, an error is made, prompting that there is insufficient permissions. In the data redaction environment, the policy-compliant access will see the rewritten (asterisks obscured, randomly converted) data. In addition, Database Vault can restrict privileged users, and data redaction is not valid for DBA users.

Data redaction Configuration

The configuration of data redaction is implemented by the system's PL/SQL package, which simply means using these PL/SQL packages to define the "data overwrite" policy, and the content to be defined in the policy is:

• Schema, table, column for sensitive data

L Conditions for the effective preparation of data

L How the data is written and the corresponding parameters

These can all be done using the PL/SQL package, or you can use EM12C to configure it through a Web page. Let's take a em12c example.

If you use the graphical interface to configure data rewriting, you need to use em12c R3 (Oracle Enterprise Manager Cloud Control 12c Release 3), EM There is no corresponding function in Express (11g Dbcontrol in the 12c name into EM Express, the interface is newly implemented with ADF, but the function seems to be less).

1. Log in to em12c and go to the home page of your DB instance. I am using a PDB (pluggable database), because there is sample data, the method configured in the CDB (Container database) is the same, need to go to the CDB's home page to operate.

2. Click "Manage"-〉 "security"-〉 "Oracle Data redaction", if there is no identification of the configuration database, the system will prompt for identification, I use the SYS user here.

3. Go to the Data rewriting page to create a policy

Selection Scheme (schema), the table that you want to overwrite, the policy name:

Modify the policy expression, which is the effective condition of the policy, where I select the database session user as Scott.

Before we proceed to the next step, we will first confirm the format of the sensitive data. In this example, we want to overwrite the Phone_number column in the Employees table under the HR user. The data form for this column is as follows:

Let's say we want to replace all the phone numbers with the * number, leaving only the first 3 digits.

Click "Add" in the "Object columns" area at the bottom of the page to add sensitive data columns and how to write in the pop-up window, as well as a simple example on the interface:

Because you want to keep the first 3 phone numbers, you start with the 4th character rewrite, all the characters that need to be rewritten are 10, the middle "." Not counted, so "until" the 10th character.

4. Test strategy:

Use Sql*plus to log in to HR users to view all data.

Log in with the Scott user and look at this:

If we change the condition of the policy to "session user as HR", then even if HR is the owner of the table, you can only see the rewritten data:

Other types of policy expressions

In the real world, many applications are logged in using a database user, so the data overwrite can be judged based on client-side information or application information. Of course, in an application, you have to set the relevant context. Note that if the user of the app has DBA authority, the data overwrite is not valid.

We use the Sql*plus simulation application to use the same database user to implement data rewriting using the context. (actually different application users, using different context.) ）

We use HR login to manually set the module to "HRMS".

The data overwrite policy does not take effect when our module is set to a different value (simulates switching between the various types of app users).

Examples in APEX environments

In a database with Apex environments installed, it is also very easy to use data rewriting with apex, using the apex's PL/SQL expression, referencing the application username, here is an example:

Other User login app effects:

Effects of Test_user1 Login app:

Introduction to Oracle Database 12c Data redaction