Oracle Database 12c Security – Oracle Virtual Private Database (VPD)

Oracle Database 12c Security – Oracle Virtual Private Database (VPD)


Oracle Database 12c Security Session 3 – Review Oracle Virtual Private
Database [music]>>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. [pause] 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. [pause] 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. [pause] 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. [pause] I’ll go to the local context, my user end
context, and just extract session user. [pause]>>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. [pause]>>John: Exactly. [pause] 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. [pause] 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. [pause] 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. [pause] 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. [pause] 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. [pause] 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. [pause] 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. [pause] 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.

Danny Hutson

Leave a Reply

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