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. http://www.mhprofessional.com/dbsec

This free training is segmented into several separate lessons:

  1. Oracle 12c Security Tutorial Introduction (1:58) (click on video below) click to read the Transcript(click to read the Transcript)
  2. Oracle 12c Security Tutorial-Agenda (4:38) click to read the Transcript
  3. Review Oracle Virtual Private Database (12:29) click to read the Transcript
  4. Oracle Virtual Private Database FAQ (4:48) click to read the Transcript
  5. Oracle12c Data Redaction (6:52) click to read the Transcript
  6. Oracle12c Data Redaction FAQ (1:11) click to read the Transcript
  7. Oracle 12c Transparent Sensitive Data Protection TSDP (9:51) click to read the Transcript
  8. Oracle 12c Security Tutorial Summary (1:41) click to read the Transcript

Date: Sep 18, 2013


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


Transcript

Oracle 12c Security Tutorial Introduction

Oracle Database 12c Security

 

Session 1 – Tutorial Introduction

 

[music]

 

>> Dave:  Welcome everyone. Sorry we’re three minutes late. Apologies for that. Welcome and Thank you for joining today’s session. If you’re interested in Oracle database security you’re in the right place at the right time.

 

[pause]

 

Just in case you’ve missed SkillBuilders previous Oracle database trainings, you can access them for free anytime at www.skillbuilders.com/oracle-tutorials. I’ll put that in the chat window for you.

 

[pause]

 

This will be a technical training session including examples and live demonstrations. It has been designed for database administrators and anyone with Oracle database technical background charged with insuring security on Oracle database.

 

[pause]

 

Our instructor today is Oracle Certified Master John Watson. John holds eight Oracle certifications including the all important Security Certified Implementation Specialist. As I mentioned he is a certified master.

 

[pause]

 

After leveling the tables with just a brief review of Virtual Private Database, John will teach us how Oracle 12c redaction and Oracle 12c Transparent Sensitive Data Protection elegantly extends securing data in your database or databases. Note the new acronym TSDP, Transparent Sensitive Data Protection.

 

[pause]

 

My name is Dave Anderson. I’ll moderate today. I’ll be looking for your questions in the chat window. A webcast best practice tip – keep your chat window open.

 

Now I’d like to bring in John Watson for today’s training. Welcome, John.

 

Copyright SkillBuilders.com 2017

×
Transcript

Oracle 12c Security Tutorial-Agenda

Oracle Database 12c Security

 

Session 2 – Tutorial Agenda

 

[music]

 

>> John:  Thank you. Thank you, David. Good afternoon, good morning, depending on the time zone, everybody. I’ll run through now what I want to cover in this short session.

 

First, I’m going to go through the Virtual Private Database. Virtual Private Database also known by several other acronyms, some people actually refer to it as Row-Level Security. Other people use Fine Grained Access Control. So, VPD RLS FGAC.

 

[pause]

 

A powerful facility. It’s also bundled up by the way as Label Security. It was first introduced in release 8i and it’s just about works. But back then it’s had serious performance problems. Furthermore, it wasn’t really suitable at all for a web environment. I think many people – myself included – tried it back with 8i and thought this doesn’t work and gave up. However, in the later releases, particularly with changes that came in with 10g, it’s become a very powerful capability indeed which I strongly advice everybody to look at.

 

VPD – we’ll have a look at VPD – I should point out, it’s Enterprise Edition. Then we’ll move on to a 12c feature, your data redaction newly released 12.1. Positioning data redaction against VPD there is, as far as users are concerned, considerable functional overlap. But the underlying technology is in fact completely different. The protection you get with data redaction is not as comprehensive as that provided by the VPD.

 

In some cases, my attempt to reverse engineer it found it may be possible circumvented in certain circumstances if the user [1:58 inaudible] privilege position. But compared to VPD, it is not simple to implement and I don’t believe they’re only performance issues. Redaction is licensed as part of the advanced security option from 12c onwards.

 

[pause]

 

Thirdly, a brief mention of data masking. I don’t think I’m going to have time to demonstrate data masking but for completeness I do want to mention it, because again there’s an overlap with data redaction, with Virtual Private Databases, all in the same sort of area. But I won’t have time to demonstrate that, I don’t think.

 

The data masking briefly then, unlike the other two, data masking actually changes data. Virtual Private Database restricts the data that people see. Data redaction conceals or hides the data. A subtle difference there. Data masking actually changes the data in the database and it’s a permanent change. That makes it suitable for long production systems. All those clones you’d make.

 

When you clone your databases to test systems, the development systems, the DSS query systems and so on, you have to clean the data. You have to remove all the personal references so that people can’t see any of the personal indicators as you move your data from production to the warehouse for redaction development. That’s where data masking comes in. A permanent change makes the data typically on cloned systems it’s generated from your production boxes.

 

The reason I won’t have time to demonstrate it is that with 12c it is pretty awkward. One data masking came in with 11g. There was a very nice graphical interface provided with 11g database control and no PL/SQL interface. With release 12c, database control no longer exists and there’s not a data masking interface provided with database express. So to get data masking functioning nowadays, you need either grid control or cloud control. I don’t think I’m going to have time to switch over to that environment. But, remember, it’s there and those overlap with the other two functions.

 

Then lastly, we’ll move on to Transparent Sensitive Data Protection, TSDP.

 

[pause]

 

TSDP is a very good frontend, simplified the pain of implementing VPD or data redaction. So what I’ll run through is VPD, redaction, and then Transparent Sensitive Data Protection, which will make it so much easier to configure.

 

Copyright SkillBuilders.com 2017

×
Transcript

Review Oracle Virtual Private Database

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.

 

 

Copyright SkillBuilders.com 2017

×
Transcript

Oracle Virtual Private Database FAQ

Oracle Database 12c Security

 

Session 4 – Oracle Virtual Private Database FAQ

 

[music]

 

>> Dave:  What about parsing – is SQL with the predicate reusable?

 

[pause]

 

>> John:  It is. This was a major problem with the earlier releases. Back with 8i the workload of parsing and not merely the parsing workload, also the workload of evaluating the function was appalling. But with the current release, if the policy is defined correctly, we can eliminate any workload.

 

It’s really defining policies as static or defining the policy type as being sensitive to variations in context, variations in session. So, yes, there can be dreadful performance issues parsing and false evaluation, but there are ways around them if it’s done correctly.

 

[pause]

 

>> Dave:  Great. Thank you. How about connection pooling? How does it work with connection pooling?

 

[pause]

 

>> John:  You need to use global context. The context I’m going to is just a local context. My predicate, as we see up here, is going to use RAM and that is variable stored in the PGA. That’s useless for connection pooling because then you might have hundreds in application server-users sharing one session and, of course, all the same session user.

 

I don’t have time to demonstrate it now but the keyword is “global context” – a global context that you set up variables in the SGA typically maintained by the application server. Then we can apply different predicates to different users as they come in through the application server on the sequel by sequel basis. In effect, the one persistent database session will generate different predicates for every sequel it hits it.

 

[pause]

 

>> Dave:  John, there’s a great contribution in the chat. I’ll read it to you. You’ve mentioned that you’ve seen CBO performance issues, but perhaps you can speak to one comment about it’s not always transparent. Let me read.

 

“Our experience with VPD isn’t the dynamically added predicates could cause CBO performance issues and it is not always transparent what is happening. Have you experienced this?”

 

[pause]

 

>> John:  Yes, I have. When it first came in with 8i, I had dreadful performance issues as did virtually everybody. With the later releases, major upgrade from 10g, I believe there will probably workarounds for most of the performance issues. One should be able to design the functions in such a way that it will be if not deterministic at least deterministic within the confines of one application server session. And if one can do that, then one can solve the performance issues.

 

To go back to the questioner, yes, I have seen those issues. I’m not surprised you’ve seen those issues. I do believe they can usually be fixed, but that comes on something I was going to say later on that I’ll mention now. Setting up VPD it can be a mission to set up. It is not easy and that I believe is one reason why data redaction may well become very popular.

 

[pause]

 

>> Dave:  You might have shown it John in your demo – I was doing some chatting. Is it possible to see the altered query, the underlying query? Is there a technique for that?

 

[pause]

 

>> John:  There is no technique to see the actual underlying query but what you can do is go to the view.

 

[pause]

 

If you look at all the VPD policy

 

[pause]

 

then to join column. Joining that column to the statement to the row of [4:18 inaudible] sequel will let you identify the predicate that was appended. You have to write code to do that to identify your sequel joined there that’s the predicate. There’s no view that’s going to show you the modified code.

 

>> Dave:  But there’s the knowledge that the students need in order to find the appended predicate.

 

[pause]

 

>> John:  Yes. Good question.

 

>> Dave:  Very good. Thank you very much.

 

Copyright SkillBuilders.com 2017

×
Transcript

Oracle12c Data Redaction

Oracle Database 12c Security

 

Session 5 – Oracle 12c Data Redaction

 

[music]

 

>> John:  Looking on to the new feature there, I’m not going to say VPD is horrible. It is a mission to [00:17 inaudible]. It may well have performance issues and it’s going to require a lot of work.

 

Data redaction will also require a lot of work but in some ways it is easier to set up. It adjusts projected columns. As far as I can tell of my reverse engineering of this, it applies a column projection stage.

 

[pause]

 

So you decide that certain data was sensitive and shouldn’t be seen to certain users in certain circumstances. We have various forms of redaction that can be applied to those columns. As full redaction – full redaction will replace var char for the space, numerics with a zero, dates with 01 01 01.

 

[pause]

 

Partial redaction substitute a constant for substring and that will be, for example, a credit card number. You might well want to display the first 12 digits as Xs of the last four digits as real data. I see that quite often when using websites like eBay or whatever.

 

Partial redaction substitutes a constant or part of the data. You can use regular expressions if you’re used to regular expressions. You can do the most amazing intelligent substitution in which kind of again be tailored to the individual who’s actually hitting the data or there’s random [1:35 inaudible].

 

[pause]

 

With the current release it is a bit limited with the current release. For example, it applies only select whereas VPD remember can apply to all statements with indeed different policies or different statements. A redaction is not applied to predicates and I’ll demonstrate that later on as well.

 

[pause]

 

How does it work? Here’s the flow of control. You need to identify your sensitive columns. It’s probably going to run the hardest bits. This will be the credit card numbers, the personal identifiers. Then you need to work out which users should see the redacted data, which users should see the unredacted data. Based on that you develop your policies and the policies can be as simple as previous slide. It can be as simple as convert numbers to a zero or with extraordinary complex regular expression, if you wish.

 

If you develop policies and the policies relate the sensitive columns to the users, enable the policies and away you go. Let’s try it.

 

[pause]

 

Remember we are now in the region of 12c, we’ve moved away from 11g at this point.

 

[pause]

 

Connect as user scott. I’ll continue working with the same scott schema.

 

[pause]

 

Now execute. I’ll go straight into it, dbms_redact – dbms redacted the package – add policy. The policy scott emp. The table name to which I’m going to apply the policy, give the policy a name and I shall call it hidesal. I’ll hide people’s salaries. And then the column to which I’m going to apply the policy, column name can be “sal.”

 

I nominate the table, I nominate the column, arbitrary name of the policy. Then what am I going to do to it?

 

I shall test with an expression, which is the next argument. Expression, sys_context – I’ll use the same basically. It’s like more complex example and how previously perhaps. I’ll go to a different context. I’ll read the context sys_session_roles. That’s the other of the automatically pre-populated context that everybody has access to.

 

MGR will be a role. And then if that’s equals false then I’ll apply the policy. What this means is that whenever I run a query that hits scott.emp, SQL or Oracle will check. Are you a member or have you activated the role MGR? If you have not activated the role MGR then we will redact the data. So unless you are have the role manager MGR enabled you’re not going to see any data.

 

If I connect let’s say miller, select star from session roles, I’ve got no session roles granted. So select star from scott.emp. What do I see? All the salaries are reduced to zero or presented as zero. A very, very simple redaction policy.

 

Going as scott – and what do it do in your application? I’ll create a role. Create role MRG, grant MRG to miller. And I could even alter miller. Set default role none. Probably well worth doing.

 

[pause]

 

Miller default role none. I can access miller again and he’s got no roles. So if he connects through SQL Plus, the data is redacted. But perhaps when he goes in through our official application he can set role MGR. Having enabled the role, now he sees the values.

 

So my predicate tests what roles were enabled and depending on whether you have a role enabled – not just granted but actually enabled – the data may or may not be redacted.

 

Copyright SkillBuilders.com 2017

×
Transcript

Oracle12c Data Redaction FAQ

Oracle Database 12c Security

 

Session 6 – Oracle 12c Data Redaction FAQ

 

[music]

 

>> Dave:  Would it work for APEX web users, for example, an APEX user identified by APEX? I’m not sure about that. Is that APEX_user public user?

 

[pause]

 

>> John:  It will definitely work with APEX users. What one would have to do is query the appropriate context to find out who the user is. Either one would create one’s own context explicitly or one could identify looking at the APEX security system and identify what security attributes has been setup for the APEX session.

 

The question is perfectly correct. Everybody is going to connect to the same schema in the APEX environment, but APEX will track the individual session by session identifier and that will give me as DBA the information I need to create a policy to contest what is this APEX user allowed to see. That will definitely be possible.

 

Copyright SkillBuilders.com 2017

×
Transcript

Oracle 12c Transparent Sensitive Data Protection TSDP

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.

 

Copyright SkillBuilders.com 2017

×
Transcript

Oracle 12c Security Tutorial Summary

Oracle Database 12c Security

 

Session 8 – Tutorial Summary

 

[music]

 

>> John:  Summary. Virtual Private Database can perform well. It is impossible to bypass under any circumstances no matter who’s using it and by what tool they’re using VPD is impossible to bypass. But it is a lot of work to setup particularly with regard to making it perform well.

 

Data redaction is easier and, as I just mentioned, I do not believe it has a performance hit. In this first release, it is a bit limited so perhaps when it’s considered in conjunction with other access controls.

 

TSDP takes away the pain at the business of the centralized administration is really nice. And never forget and anyone who spends time with me will know that I’m in love with the declarative techniques and this is all declarative. I, as DBA, go behind the back of the software, I can retrofit this, any of these facilities to existing applications that any developer inputs.

 

Alright, back to you Dave.

 

[pause]

 

>> Dave:  Thank you so much, John. A brilliant presentation. I appreciate it very much. So, folks, we’d like for you guys to keep us at SkillBuilders in mind – if you have any consulting needs, be it Oracle database or APEX consulting, perhaps your security issues, perhaps performance tuning, you might even think maybe it’s too small of a need. We handle very small issues. We can also do large projects for you.

 

Copyright SkillBuilders.com 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!

 

×
Podcast
×