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:
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.
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.
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
Individual Column Properties
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 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.
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?
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
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?
You don’t do it in the relational model, you do it in the physical model. See this.
Trying to do my best.