Transparent sensitive data protection let me have a look at the chance I missed a couple of things yes the policy the name of the policy I just gave it I just created us when I run my code all I said have I just gave the policy a name at pour Ce o– policy name I chose the name there right yes mouth in its advanced security and that’s the bad news but I seriously believe that the first time you use it is going to pay for itself I really do think that now because it to work that if you have a change in requirements all of a sudden you have to mask some data type it’s going to be a nightmare job you’re going to have to edit all your source code for all your applications and you still have to remember that if there’s some application you missed or maybe you buy a new application all of a sudden you start using Discoverer is like going to Mars things nowhere’s rejection declarative technique in the database wonderful because we used EPA’s do it and supplies to all users of all applications.
Even applications that don’t exist yet I must say though before I should have said a bit more for move on it isn’t perfect I’ll disable I’ve enabled so now I’m hiding it I can’t see it but it’s not perfect look at this select star form M where cell greater than 3,000 interesting select stars from amp where cell greater than 6,000 are a select star from amp where cell equals five thousand got him Oracle of themselves for this they say that this is not intended to protect data from against from users who have access to ad hoc query tools to remember that you can work around this and the reason is the reduction occurs right at the end of the sequel execution cycle so you go through the normal row selection where the predicate is applied you join your tables you do your column projection and you do your sorting you can even sort so if I select star from amp order by Sal we find King at the top so it’s right at the end of the fetch as you send the results back to the user process.
That’s when the reduction occurs right at the end of the sequel execution even after the order by and however I think it’s going to pay for advanced security the first time you use it because the force of having to write code to do this is just incredible it’s an awful job and remember you’ll probably miss a few columns every time we deploy a new application will you be able to trust it probably not in the exercise I’ll just glance the sheet now you’ll see what a fully worked example of taking this to extremes well putting in to ask you to do for the exercise is quite complex we set up privileged analysis and the idea is we’ve got Pete the programmer we’ve got Andy the auditor and we’ve got Dave the data entry clock so Pete Andy and Dave and they’ve all got different privileges Pete the programmer he’s got clenched resource and so on and you can create things and leave the auditor really high privileges including orders admin which will look at tomorrow morning.
Here’s a highly privileged user and Dave the data entry Clark all he’s got it a couple of selects so it creates some users and then we’ll create a privilege capture and what the privilege capture will do is capture every privilege used by Pete or Andy or Dave enable it then I’m going to ask you to configure data redaction to let the privilege run throughout the whole exercise and the reduction we’ve got a quite complex example here we’re going to create a policy that will mark make cell and that’s thought I just did unless you’ve got that role will do partial reduction I’m going to add another column to the policy the name column we went to morskie name and what this will do the posh reduction is going to replace everything but the first character with a lowercase X you’ll see the effect of it when you do it so we’re going to partial reduction replaced mean this is how you recreate Accord numbers with 12 X’s and then four digits another policy use of regular expressions.
I’m not very good with ranking expressions, but this will do if we’re indexing the lock column of depth and we’re changing every uppercase letter to a lowercase X so if you look at the select star from depths what you’ll find is this instead of New York you will see 3 x’s space and 4 x’s there are simple use of a regular expression we replace uppercase letters with lowercase X and we’re going to do a random reduction of the D name going to replace department name with random letters and the expression 1 equal to 1 that means always do this no matter who gets the data whereas the other ones who are saying only redact if you don’t have that role and see what happens run a few queries let me move on to transplant sensitive data protection now in the exercise I’ve had you configure it action well believe me reduction is going to be a mission to set up cloud control helps a lot the graphical interface helps a lot that interval and columns things out it’s still going to be a huge job.
Virtual private database is the same is horrendous to assess up is virtual private database and TS DP is it supposed to be a simple declarative technique for managing vpd and data redaction and whether you think it’s simple you’ll make you our minds up if you’ve got cloud control is click click click in our exercise we do it with PL sequel right we invoke it all the hard way the only dear is that you define your policies once and apply many times let’s say you have to mask names you’ve got a mask names hide the personal identifiers well your name might occur in two thousand tables, so that means you’re going to have to create a data redaction policy for two thousand columns in two thousand tables it’s an appalling amount of work TS DP makes easier you define the policy once and then associate it with many columns so it’s meant to make life much easier and if you add a new table with more columns and the same policy applies you associate the new columns in the new tables with the existing policy to TS DP.
Lets you set up a situation where one policy can be rolled out to many columns you can even do it in many databases if you got cloud control so you can set up all your policies in one database save them as an XML file number correctly and then apply them to loads of other databases which is a pretty nice capability so it automates the rollout of these facilities on a large scale it’s not easy to do but we’ve got a worked example you create a type and associate columns with the type to the type is just an arbitrary label you just create a type just like label associate columns with the type then you associate a policy with the type, so the policy sits in between sorry the type sits in between your policies and your columns and in the example we have here craggy policies we’re going to mask the salary and cult the Sal and calm columns of Scott door damp so create a type and just call it Finn for financial.
And that’s a simple call to create the type is just a label then you add your columns its core temp cell to the type let’s call temp to come to the type so you might be doing this a thousand times create type once associate all the relevant columns with it then you create the policy to that call from declare to end it’s creating a policy and what this policy says is unless you have the MGR role do full redaction so unless you have that role relax the data and that’s the name of the policy redact full Finn and after that, you associate the policy with the type and subsequently you can just add new columns to the type and they’ll automatically pick up the policy, so this is supposed to make life easy right okay dots you don’t pay for this but of course you must have a page so we’ve got the redaction and that you don’t pay for vpd it works the VPD as well vpd is just part of enterprise edition and thence the guts of this chapter but the really complicated bits comes now.