Oracle 12c Security Transparent Sensitive Data Protection Tutorial

What is Oracle 12c Transparent Sensitive Data Protection?

Transparent Sensitive Data Protection (a 12c new feature) leverages the Virtual Private Database facility (available since release 8i) and the the Data Redaction facility (introduced in release 12.1.)  TSPD eases the process of implementing and managing either VPD or Redaction.

This tutorial will go through the old way of doing things with VPD: it was always a mission to set up, and because it operates at the row selection stage, sometimes hard to tune. Then we’ll look at Data Redaction: in some ways simpler than VPD, and because it operates at column projection stage, possibly better performing.

Everything discussed is Enterprise Edition, but no need to licence any additional options.

Presented by Oracle Certified Master John Watson, SkillBuilders’ Director of Oracle Database Services.

Limited Time Complimentary eBook, Securing Oracle Database 12c

Are you an Oracle DBA who wants to protect your databases? Register now for the complimentary eBook and learn about Oracle Database Security from the experts who brought you the #1 database in the world.

This free training is segmented into several separate lessons:

  1. Oracle 12c Security Tutorial Introduction (1:58)
  2. Oracle 12c Security Tutorial-Agenda (4:38)
  3. Review Oracle Virtual Private Database (12:29)
  4. Oracle Virtual Private Database FAQ (4:48)
  5. Oracle12c Data Redaction (6:52) (click on video below)
  6. Oracle12c Data Redaction FAQ (1:11)
  7. Oracle 12c Transparent Sensitive Data Protection TSDP (9:51)
  8. Oracle 12c Security Tutorial Summary (1:41)

Date: Sep 18, 2013

NOTE: Some corporate firewalls will not allow videos hosted by YouTube.


Oracle12c Data Redaction

Oracle Database 12c Security


Session 5 – Oracle 12c Data Redaction




>> John:  Looking on to the new feature there, I’m not going to say VPD is horrible. It is a mission to [00:17 inaudible]. It may well have performance issues and it’s going to require a lot of work. 


Data redaction will also require a lot of work but in some ways it is easier to set up. It adjusts projected columns. As far as I can tell of my reverse engineering of this, it applies a column projection stage. 




So you decide that certain data was sensitive and shouldn’t be seen to certain users in certain circumstances. We have various forms of redaction that can be applied to those columns. As full redaction – full redaction will replace var char for the space, numerics with a zero, dates with 01 01 01. 




Partial redaction substitute a constant for substring and that will be, for example, a credit card number. You might well want to display the first 12 digits as Xs of the last four digits as real data. I see that quite often when using websites like eBay or whatever. 


Partial redaction substitutes a constant or part of the data. You can use regular expressions if you’re used to regular expressions. You can do the most amazing intelligent substitution in which kind of again be tailored to the individual who’s actually hitting the data or there’s random [1:35 inaudible]. 




With the current release it is a bit limited with the current release. For example, it applies only select whereas VPD remember can apply to all statements with indeed different policies or different statements. A redaction is not applied to predicates and I’ll demonstrate that later on as well. 




How does it work? Here’s the flow of control. You need to identify your sensitive columns. It’s probably going to run the hardest bits. This will be the credit card numbers, the personal identifiers. Then you need to work out which users should see the redacted data, which users should see the unredacted data. Based on that you develop your policies and the policies can be as simple as previous slide. It can be as simple as convert numbers to a zero or with extraordinary complex regular expression, if you wish. 


If you develop policies and the policies relate the sensitive columns to the users, enable the policies and away you go. Let’s try it. 




Remember we are now in the region of 12c, we’ve moved away from 11g at this point. 




Connect as user scott. I’ll continue working with the same scott schema. 




Now execute. I’ll go straight into it, dbms_redact – dbms redacted the package – add policy. The policy scott emp. The table name to which I’m going to apply the policy, give the policy a name and I shall call it hidesal. I’ll hide people’s salaries. And then the column to which I’m going to apply the policy, column name can be “sal.” 


I nominate the table, I nominate the column, arbitrary name of the policy. Then what am I going to do to it? 


I shall test with an expression, which is the next argument. Expression, sys_context – I’ll use the same basically. It’s like more complex example and how previously perhaps. I’ll go to a different context. I’ll read the context sys_session_roles. That’s the other of the automatically pre-populated context that everybody has access to. 


MGR will be a role. And then if that’s equals false then I’ll apply the policy. What this means is that whenever I run a query that hits scott.emp, SQL or Oracle will check. Are you a member or have you activated the role MGR? If you have not activated the role MGR then we will redact the data. So unless you are have the role manager MGR enabled you’re not going to see any data. 


If I connect let’s say miller, select star from session roles, I’ve got no session roles granted. So select star from scott.emp. What do I see? All the salaries are reduced to zero or presented as zero. A very, very simple redaction policy. 


Going as scott – and what do it do in your application? I’ll create a role. Create role MRG, grant MRG to miller. And I could even alter miller. Set default role none. Probably well worth doing. 




Miller default role none. I can access miller again and he’s got no roles. So if he connects through SQL Plus, the data is redacted. But perhaps when he goes in through our official application he can set role MGR. Having enabled the role, now he sees the values. 


So my predicate tests what roles were enabled and depending on whether you have a role enabled – not just granted but actually enabled – the data may or may not be redacted.


Copyright 2017

Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!