Tip:
Highlight text to annotate it
X
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.