ThatJeffSmith

Defining Redaction Policies for Oracle Database 12c Data Models in Oracle SQL Developer

Quickly ID your redacted columns in your diagrams - they are highlighted in red.

Quickly ID your redacted columns in your diagrams – they are highlighted in red.

Oracle SQL Developer Data Modeler version 4 was released as an Early Adopter just last week. You can go download it here and read about the new features here.

A snippet of those new feature release notes…

Masking Mask templates can be defined and used in definition of columns with sensitive information.
Support for REDACTION policy definition on table and column level.
Support for Transparent Sensitive Data Protection (TSDP) policies:

  • Sensitive types can be created and associated with domains, columns and attributes

TSDP policies can be created and associated with sensitive types

So I wanted to take a quick second to talk about how to go about defining a Redaction Policy for one or more columns in a table.

If your relational model properties are set to RDBMS TYPE of ‘Oracle Database 12c,’ then your Security/Masking properties will be used to define Oracle Database 12c Redaction Policies. Otherwise, you can define how the data should be protected, but it will be up to you on how to physically ‘make that’ happen in the database or in your application.

Let’s get started.

Set your Model to ’12c’

In the Model Browser, right-click on your Relational Model in the tree.

Pick the one that says '12'

Pick the one that says ’12’

Configure The Column(s)

I want to make an employee’s salary and commission rates ‘sensitive.’ Or in other words, I don’t want non-privileged folks to be able to see who much everyone else in the organization makes – whether they be in the database or in our HR system.

So whilst designing my EMPLOYEE table, I can peruse the new ‘Redaction Policy’ page in the properties dialog.

A table can have zero or one redaction policies.

A table can have zero or one redaction policies.

Obviously you’ll want to enable the policy AND have the DDL generated if you want to take full advantage of this feature and push it to your database when you’re ready to deploy the model as a real, live, breathing instance.

The expression determines whether or not to present the real data to the end user. The most frequent example I’ve seen is something like ‘1=1′ which always evaluates to TRUE, but a more real-world example might involve SYS_CONTEXT properties.

Now Let’s Look at the Columns Specifically

You can define the the redaction policy information while you’re defining the columns in the general table editor, or you can go into the individual column properties.

General Table Editor

See everything at once, or...

See everything at once, or…

Individual Column Properties

...or you can look at the security setup for one column at a time.

…or you can look at the security setup for one column at a time.

I think for my particular taste, I’ll use the individual column properties dialog. Less chance of screwing something up here than using the columns grid in the main table editor.

Preview Your DDL

Same as always, right-click on your table in the model and check out the DDL. You’ll want to scroll down to the very bottom to see the Redaction Policy created and altered to contain additional columns.

The policy name is defined in the table properties, where we started in step 1.

The policy name is defined in the table properties, where we started in step 1.

The pink’ish highlighted plus marks in the gutter? That’s another new feature where our code folding feature has been extended to the model DDL previewer.

But Wait, I’m Going to Have the Same Redaction Policy Type Setups For Lots of Tables!

Then you’ll want to check out the Tools > Mast Templates Administration. Once you have a mask type setup, you can use that template in the column security definition instead of writing the same code over and over and over again. Just select it in the ‘Mask Template’ drop-down selector.