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) (click on video below)
  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)
  8. Oracle 12c Security Tutorial Summary (1:41)

Date: Sep 18, 2013

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


Review Oracle Virtual Private Database

Oracle Database 12c Security


Session 3 – Review Oracle Virtual Private Database




>> John:  First, Virtual Private Database. What is it? It’s a declarative technique for enforcing security. Typically, it’s based on user application context. That was the first limitation of 8i. Backed with 8i context were session-specific. The context variables were sort in the PGA. That means you simply couldn’t use this in a web environment.


However, global context, with use of 9i, Virtual Private Database became usable for web applications with connection pooling. But whether you’re using it to client server or on a three tier environment, it’s declarative technique rather than a programmatic technique.


I can go behind the back of the application as a DBA and generate predicates to get appended to all SQLs. Select, insert, update, delete the end users. Issue the SQL and I will generate a predicate that filters the access. It’s impossible to bypass. All users will have predicates appended to all their code. It doesn’t matter whether they’re using SQL Plus, Discoverer, Business Objects, Crystal Reports, eBusiness Suites, whatever they’re using, I as DBA can filter their access to roles. 


The end result, different users see different data sets while running the same code and accessing the same tables, hence, the name Virtual Private Database. Different users will think they’re seeing a totally different set of tables, but in fact it’s one column database, one set of tables hosting a single application shared by all the users. 




The mechanism – you can apply VPD to tables, to views, to synonyms, and a declarative technique. Once the technique you write a function that generates the predicate, you associate the function with the table. From then on whenever the user issue SQL against that table the Cost-Based Optimizer rewrites the code. The Cost-Based Optimizer rewrites the user SQL to call the function to generate the predicate. The predicate is then appended to the SQL and it’s the modified SQL that is parsed and executed. 


Let’s see how this actually works. I’m working here by the way. 




This is just a perfectly normal 12.1 database. But what I’m going to demonstrate at this point will in fact work on 11g as well. I’m not going to be using any of the 12c enhancements to VPD for this little demonstration. 


I’ll work in the scott schema. What do we have to do? We write a function to generate the predicate string. Create function. 




And I’ll my function vpdf. The function must conform to a fixed specification. It must take two varchar arguments. One of the arguments is intended to be the schema to which the function will be applied. The other argument is the object within the schema to which the function will be applied. And it must return a varchar2, which is the generated predicate. Let’s begin.


Within this function you can do just about anything. There are some limitations. It’s impossible for the function to query or update the table against which the VPD calls here is going to be applied or we violate the rules of purity that we come across so often with PL/SQL. But apart from that, it can do just about anything – including a number of dreadful side effects can be introduced by writing VPD functions. 


I’m going to do a very simple one on this one. I’ll shall simple return immediately a predicate. What predicate? Ename, which is the ename column from this scott.emp table. Ename should equal sys_context. 




I’ll go to the local context, my user end context, and just extract session user. 




>> Dave:  We can see that John has tested this earlier today and therefore the object already was suggesting and he’s just dropping it now and cleaning up the recreate.




>> John:  Exactly. 




I hope you realize these really are live demonstrations. I’m not just running scripts. 


I’ve created a function. What’s that function going to do? It’s going to return this value here. Test it. Always test it and see what it’s actually going to do. I’ll select vpdf. I have to give it a couple of arguments. This one will do for now. 


That’s what it’s going to generate. Ename = sys_context user, end session user. When I apply my policy to a table, any query that hits that table will have where ename = that added onto it. What that actually going to generate is this context call. 




It just generates and returns that current log on schema. 


Having created the function what do I do next? I associate the function with a table. I’ll do it and then run through the API. So dbms_rls for row level security and add policy. 




The policy will be applied to scott.emp. I’ll give the policy a name, mypolicy, that’s just an arbitrary name. And then the function I’m going to apply which is scott.vpdf. That associates scott.vpdf with the table scott.emp. 


Now test it. Select star from scott.emp and I get one row back. We know very well there are 14 rows in the table, but even as the owner of the table I am logged on as scott, I see only the row where the ename column happens so much I logged on schema. 


To take it a bit further, if I create a user or I just create user miller identified by miller. I’ve already got in, that’s fine. I’ll grant him a couple of privileges to make sure it can log on. Grant create session to miller. I’ll give him select any table to miller. Select any table to miller. And then you can access him. 




What happens when he runs the identical codes? Scott run that code and got that row back. Miller runs that code – identical code – he sees a totally different subset of the data. 


Finally, if I create another user, create user jw, identified by jw. Connect / as sysdba. It exists already. Drop user jw. 




I’ll give him one or two extra privileges. I’ll grant him dba and I’ll also grant him exempt access policy to jw. If I connect as him and run the same code, now I see the entire table. Why? Because of that privileges there. 


Note how good the protection is even if I connect as user system, not even as user system can I see the data. The predicate generated in this case, of course, there is no one in this table whose name is uppercase system. Not even a system with DBA privilege can bypass this. This really does block absolutely everybody from seeing data.


I’m taking extremely simple predicate, by the way. I’ll give some examples more complex ones that would perhaps be of more use in the business environment.


What’s actually happening? With just a very brief bit of reverse engineering, connect to scott. I’ll set autotrace on and get out the execution plans.


I run my query. Select * from scott.emp. There we are. Full table scan, but there we see a filter coming in. “Ename” = sys context.


That’s a simple demonstration of what’s actually going on with the Virtual Private Database. We take the user SQL and in the background, the Cost-Based Optimizer rewrites it to append whatever predicate we happen to want to use.




User issues with SQL. We generate the predicate, add the predicate to the SQL. Those are the modified SQL that’s actually parsed and executed. 




What can you do with this? [10:54 inaudible] clear, it restricts access to data, filters access to rows.


What I haven’t demonstrated – we don’t want to spend too much time on this – is you can also apply it to individual columns and relevant rows as a whole. That means that depending on which columns you project, the filter may or may not be applied. 


There are lots more. The filter itself, the filter can be based on many criteria. I just used my schema logon. Usually one would use something much more sophisticated than that – session attributes, who the user is, perhaps a role in an organization, perhaps smaller values, stored as cookies in the browser. Any session attributes, environmental attributes that let Oracle work out “Who is this guy? What data is he allowed to see?”


If you really want to make it confusing, you can have different policies for different statements. I’ll just show you the relevant package DBMS_RLS. The critical procedure call is add policy. Table of policies is going to be applied to, the function you’re going to generate. Then you can have different policies – select, insert, update, delete – which can make it really confusing. But you might be well be allowed to see certain rows but only update other rows.




This has to do with performance, very important indeed. This has to do with column level VPD rather than simple row level VPD that I worked on.



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!