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)
  6. Oracle12c Data Redaction FAQ (1:11)
  7. Oracle 12c Transparent Sensitive Data Protection TSDP (9:51) (click on video below)
  8. Oracle 12c Security Tutorial Summary (1:41)

Date: Sep 18, 2013

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


Oracle 12c Transparent Sensitive Data Protection TSDP

Oracle Database 12c Security


Session 7 – Oracle 12c Transparent Sensitive Data Protection TSDP




>> John:  It’s a simple mechanism for protecting a data – the declarative technique with an easy to use PL/SQL interface. It leverages the existing technologies of Virtual Private Database and data redaction as I’ve already demonstrated. 


In general technique, you define your policies just once. Define them once and apply them many, many times. You associate the policies with as many clones or tables as you wish. You can even deploy them to multiple databases. 


There’s a facility whereby you can develop your entire TSDP environment based on VPD and data redaction on development system and then roll it out to a whole set of databases. You can roll it out to your production database, to your data warehouse, to your testing systems, your QA systems, and so on. Define once, apply many times. But it is based on the existing technology. 


So what’s the technique? You create a sensitive data type. That’s a little more than a logical [1:10 inaudible]. You associate your tables and your columns with the type. You associate the policy with the type, and the policy connects you to the users. So the policy manages the users and their access to a particular type. The type controls the data they can actually see based either on redaction or VPD and in many cases I think when this takes off it would be based on both. 




How do we actually do it? The PL/SQL interface is refreshingly straightforward. I’ll continue working in the sys. 




I work on my Window system here and I work in the sys schema. 


To begin with, first step we create a type. Execute dbms tsdp manage is the first API that we’re looking at. Then we add a sensitive type. The sensitive type is just a label. I shall call it comp for compensation. I’m going to hide anything to do with money in the start scott schema. 


So we generate the type then we create a policy. Now I’m going to paste in a pre-prepared statement on this because I’m tired of making typing errors and I’ll walk through it line by line. The critical procedure call is here, dbms_tsdp_protect.add_policy. When you add a policy – you in fact create a policy – give the policy a name then specify which security feature is this policy going to be used for. The options are redaction or VPD. On this occasion I’m going to use a redaction TSPD policy because redaction is a new feature and I want to look at the new features more than the old ones. So the policy has a name and it’s going to either redact data or filter access through VPD. 




What’s the policy going to do? That depends on the options and the options are predefined data type that we have to populate. I’ve taken the most basic idea I can think of here. I’m going to specify an expression and the function. 




My expression sys_context, sys_session_roles, MGR and to simulate pretty much what I was doing before. If you do not have the MGR role enabled, we will do full redaction. 




The policy has a name, it’s applied, it’s enabled one feature or the other, and then what it’s going to do in this case? Full redaction unless you are an MGR. Done. 


Then we have to associate the policy with the type. I’ve created the type, associated the policy with the type. Execute dbms_tsdp_protect and use the procedure associate policy. And I shall associate it which policy? The one I just created, redact full, and I’ll associate it with the type that I created here. 




That’s a warm-up exercise. Create the type, create the policy, connect them together. Then on demand, you associate the policy or the type with sensitive columns. At this point you might have identified 500 sensitive columns, a number of tables throughout the database. That will be repeated calls but there are ways around this to execute dbms_tsdp_manage and we add sensitive column. 




We associate the sensitive column with the type. Which column? I’ll have scott.emp_sal. Associate the salary column with the sensitive type comp. Underscore there. 




To repeat its call to this procedure we’ll setup the environment rather than what we saw before at the break, which is a much more complex environment. It had to be configured individually for each column of each table. Now, connect. 




Connect miller/miller. 




Set role to none, disable his role. Another redaction comes in. 


What have I achieved? So far, nothing that I couldn’t have really done before. I’ve gone through almost the same exercise. I have to create a type. I’ve created a policy with the function, with an expression of in the function. It does reduction. If you are not in that group, if you do not have that role enabled, you won’t see the data. 


I’ve associated it with only one table, but the beauty of it is I can simply add more columns as I wish. If I want to protect another column, all I need to do is associate a new column with it. For example, maybe I want to protect the com column – the commission. Add sensitive column. 




Add the new column and then re-enable the policy. 




Then we connect miller/miller. Set the role, get rid of the role, run the query. Now we should see that too is now been zeroed out. The beauty of this then is that you can roll it out in a large scale. Define the policy once and then apply it as often as you wish. 


I’m running a bit short on time but let me move on.


What can we do with all these? The end result, it really is very nice indeed. We define the policy just once and then we can apply the same set of policies to many columns and many tables across many databases – VPD policies and redaction policies to a single point of control. It is I should mention nicely integrated with cloud control. If you have cloud control setup with application data models then you can specify your sensitive columns there and there’s a discovery mechanism whereby you can point cloud control at your databases, and in effect generate list of sensitive columns to which the policies need to be applied. 


What can you use it for? It can help you conform to changing requirements of SOX or PCI or GAAP (generally accepted accounting principle). Every time these regulations change and you have to apply different rules to how you redact data with redaction or how you conceal data completely with VPD through TSDP, it gives you a central point of control for all these. 


Then never forget you can retrofit this technique to existing applications. Redaction with 12c or VPD with 11g, I as DBA go behind the back of the application software. No need for patches. No need for vast amounts of programming. I do it declaratively and the programmers and users really have no idea what’s going on. 


I think this is going to be a very powerful facility already. It’s already used in version 11 but it was difficult to use. Moving forward to 12c, I think it’s going to be very useful indeed.


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!