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

thatjeffsmith SQL Developer 5 Comments

Tell Others About This Story:
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.

Tell Others About This Story:

Comments 5

  1. Hi,

    I am looking from developer stand-point, if we implement Oracle Data Redaction in our DB, can a developer perform update, insert, delete transactions on the DB?
    If yes, when the data is displayed, will the data that he/she has changed be redacted before display?

    Thanks,
    Nalini

    1. thatjeffsmith Post
      Author

      yes – redaction doesn’t limit the table operations you can do – it only changes the data returned to the application

      so you have to be careful

      if your developer has a screen that looks at an employee record, and salary is redacted with a RANDOM policy, and you update their address, some devs will issue an update with every field supplied – in that scenario, their update would change their salary with the random value vs just their new address

      the data is redacted before it’s fetched to the end user/application

  2. Can you tell us how to define a table as IOT in relational model. I went into the “Table properties” but I don’t know which option to use. can you guide me?

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *