How syntax creates the capture in Database 12c?

Syntax creates the capture and their various types of capture this one I’m calling the capture all and the type of capture G underscore database that’s a constant that means to capture every privilege used by anyone under any circumstances there’s a more focused one we’re going to capture we call it rolls and we’re going to capture every use of DBA or resource these are legacy roles they shouldn’t be granted to anybody so what this capture will do is capture every time anyone uses one so all sorts of things you can do with that more examples this is a privilege based on context variables I’m calling the capture context just an arbitrary string and I’m going to pick up every use of a privilege by this condition anyone logged on with client identifier JW he might be an application server user that could be his application ID we’re also going to capture everything done by database user Scott we’re also going to capture all privileges used by appeal sequel module called updates.


So that will be a package or procedure called updates to capture all the privileges use their so very useful facilities here you can focus quite tightly on what you’re investigating start the capture run the workload disabled to capture generate results then you’ve got used Vuze DBA use and DBA unused so at names oops DBA used percent what object privileges and then you’ve got the path one to show how you got to it because of its roles granted to roles that path could be quite complicated so all the privileges use to all the system privileges object privileges and so on and very interestingly you’ve also got the ones you didn’t use it quite often you’ll find people have got more privileges than they need, and these views will show you the privileges that weren’t you that were granted but not used during the phrase so brilliant stuff if either of you any of you has access to data vault.


This is going to be worth using with this worth buying data vaults just to have this is a bit as a different matter, so that’s our first feature you’ve got to pay for the next one you have to pay for its data redaction I like this a lot but it’s part of advanced security it’s a declarative technique for masking data it doesn’t change anything at all it just masks it and the beauty of it is it’s behind the back of the software you know if you go to a website and you give it a credit card number as a standard when you see the credit card number shown back to you you see 12 X’s and then the last four digits of the number you can bet your life that’s being done programmatically some poor programmer when designing that screen has said credit card number substitutes the first 12 digits with axes and then show the rest it’s being done programmatically and that’s a huge amount of work especially if there’s a change in requirements.

Now maybe you’re showing I don’t know the bank sort code in clear text and all of a sudden there are new financial regulatory requirements that say you have to mollusk assault codes you’ve got to pay a programmer to go through every screen of your application identifying every occurrence of that value and rewriting the screen to mask the code it’s going to be a huge amount of work furthermore you might miss one we’re still if it’s the third-party products you can’t even do anything until the supplier produces a patch for which is going to charge it, so masking data to users is a horrible job it’s unreliable it’s expensive with this technique I do it is the data but with the database, it’s done declaratively within the database so it doesn’t matter whether someone is logging on with sequel plus or we’ve towed or with a form application or an apex application it doesn’t matter how will they get into the database I’m going to mask the data if it that is appropriate.

So it applies to everything then and their various techniques for inaction full reduction partial and so on you are identifying sensitive columns I know you’ve got cloud control at your site’s haven’t you Cloud Control has a very nice user interface for this cloud control has a facility that let’s see that helps you find the columns because in my experience of this sort of thing it’s this business is the first two bullet points here the really difficult bits if you’ve got an application with 50,000 tables there could be thousands of columns with sensitive data somehow or other you’ve got a track up down you’ve got to identify the columns that are meant to be masked then you’ve got the really difficult bits who is allowed to see the data perhaps if it’s a credit card number if someone a member of the public has logged on through the website the credit card should be masked if your internal staff has logged on with the forms application the credit card number should not be masked.


So you have to work out all sorts of rules if someone logs on the sequel plus conceal the data if they log on with crystal reports don’t conceal the data they’re all sorts of issues here you’ve got to work out what sensitive and under what circumstances should data be redacted that’s very difficult having done that the easiest which is the pl sequel right we’ve got a complex example in the exercise I’ll show you a very simple example now that should at least give an idea of what’s going on so here I am logged on to my database well I show user simple example it’s all done with DBMS read acts the U.S. read acts and you create a policy your ad a policy and the policy says that for this column of this table to escort dot amp salary column do this to it and their various things you can do you can apply a function type do this to it and two more detail there and the expression is under what circumstances to do it so here’s an example, so there’s a much more complex.

One in the exercise but in this example here and I simple one add a policy so I’m going to read X Scott amp cell and I’m going to do full reduction which just means put it down to zero it was a numeric column reduced to zero and the expression is one to do it I’m using contacts call so select this context MGR I’ll just have to I’m going to a context called session roles that’s new with release 12 it tells you what roles you have enabled do I have the MGR role enabled from door no I don’t conexus Scott password tiger do I have that rule enabled no do I have DBA enabled yes I gave myself DBA role I’ll crater role create role MGR and I’ll grant it to myself grants MGR to Scott looked I’m not going to be able to do that I pledge nice to do that yes I can right now previously I did not have the MGR role do I have it now no I don’t but if I log on again connect Scott tiger yes I do so that’s a simple test of do I have a role enabled and that’s what I use here and what this says are done full redaction.

If you do not have the MGR role so read acts that column if you don’t have that role right which right now I do have and that basically is it so try it if I will select star from the amp and I see the salaries but if I set roll none disable my use of the roll it’s reduced to zero and it’s that simple so even though I am Scott if I don’t have that role about now set to roll to MGR and now I can see if it’s a wonderfully simple declarative technique, so there’s a much more complicated example in the exercise but it’s really easy and I love it doesn’t matter what tool you use doesn’t matter how you’re connecting it works and isn’t really changed but we mentioned it briefly.

Leave a Comment