Tip:
Highlight text to annotate it
X
Oracle Database 12c Security
Session 7 - Oracle 12c Transparent Sensitive Data Protection TSDP
[music]
>> 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.
[pause]
How do we actually do it? The PL/SQL interface is refreshingly straightforward. I'll continue
working in the sys.
[pause]
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.
[pause]
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.
[pause]
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.
[pause]
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.
[pause]
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.
[pause]
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.
[pause]
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.
[pause]
Connect miller/miller.
[pause]
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.
[pause]
Add the new column and then re-enable the policy.
[pause]
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.