We should always be using sisal / this backup site sis km in particular this is going to be great for outsourcing so we can all be fired is that basic, yet your employer can now safely outsource your database administration to some low-cost country and the data will be completely safe because what you will do is you will keep the system be a password you won’t give that to your outsourcing contract so in Russia or whatever Indonesia wherever it might be you’ll keep sisti Ba to yourself we will give your outsourcing partner still / sis backup and sis DG sis km you will keep to yourself and this is an important point for the outsourcing environment if any of you work in high security environments I still work in the health sector and health databases and healthcare sector have to be incredibly secure you have to be so careful with the personal information and so on in the health sector environment and hospitals the general rule cannot outsource their database administration because to administer.
A database you need sits DBA there are things you can only do with sis DBA privileges and if you give someone sis DBA you’ve given him the keys to your universe and for that reason hospitals could very rarely outsource their database happen now they can so they because they can give away back up DG and km s sword back up in DG and upper in total safety, so this is important right um and you’re using it already whether you know it or not many people, of course, will never actually implement these differences if we look at say 1 I’ll just take a look at one of the production databases that we’re just configuring at the moment this isn’t one of mine and I think that what they’ve done on this one so I’m logged on us Oracle oh we have of course all the groups here we have called the groups they’re done this correctly okay, but there’s no reason why you don’t have to use this mechanism if your small environments and don’t need separation of duties you can install everything under one group.
Now the next topic privileged analysis in 11g is really difficult to work out well someone can actually do I’ve tried to write code where I query all these views and of course roles make it really complicated when you have roles granted to roles it can be so difficult to work out how someone can actually see data really difficult and auditing of course something tells you the privilege that you citizen tell you the other privileges they’ve got they haven’t used and 12c introduces this wonderful capability called privilege analysis I’ll show you an example of one of the earlier time too amused it because it was so easy to use what has happened in this case it was the idea is you do what you call creating a capture the clip show instructs or equal to monitor all use of roles and privileges then you run your application and then you get reports out in this particular case it was an application we were developing for a clients and the application the whole project really wasn’t very well managed.
I shouldna the clients didn’t want to pay us to do the project management and they didn’t have a very good project manager themselves so overall the project was not very well managed basically also managed at all the program has managed us and when programmers start managing your own projects heaven help you and what they’re done it was an apex application and they gave themselves far too many privileges in apex you have what you call the parsing schema and the pausing schemer is a schemer onto which the code ROMs and they’d given the opposing schemer ludicrous privileges whenever something didn’t work they just granted another privilege and then he did and when the application was delivered to the customer with installation scripts the customer saw the scripts were creating the pausing schemer including all the grants and he went mad and quite rightly too because there are far too high privileges so we jumped in with 12c privileged analysis and it worked brilliantly.
So this is a work at an example this is real I’m going to edit out any personal information, but this is real you create a privilege capture the DBMS privilege capture crater capture and you give the capture a name and that’s just an arbitrary string all privileges used by ice cream as the application what type of capture and to capture based on context variables in this example and I’m going to capture all privileges used by any session where the session user is him because that was the parsing schemer you’ll see more examples of the syntax here what this will do is capture all privileges used by this guy because I’m picking up the context variable user end-user env session user if you’re not familiar with that is things like selects your CSS contacts call this context to go to my user environments and Who am I actually logged on as session user who am I logged on us and I’m logged on as use assess so we’re picking up everything done.
If you’re logged or mass m and then you enable the capture you create the capture then you enable it then you run your software and we let it run for three weeks because you really do need to make sure you cover the entire lifecycle so unless you run for three weeks then you disable to capture generate results when you generate the results which takes a while it populates a bunch of views and I just got a couple of queries so you haven’t got all of them I didn’t think to keep screenshots and everything but this was DBA you sis preps tells you all the system privileges used during the time the capture was running well the username is the one I’ve got there and he used he used selection dictionary how did he get selection dictionary because he got the DBA role he used create table and he got worse through today to pump export falter device role and you can see how badly designed this account as if he needs to create a table he should have been granted create table no one should be granted DBA.
If the user needs selecting a dictionary grants him to select any dictionary don’t grant him DBA and this is the sort of really valuable information you’ve got it we’re finding out what privileges did he use and how did he get there and in particular clearly he’s been using DBA to get some things which are ridiculous right so invaluable information how would you get this without using this facility I honestly don’t know here’s a query against used obj privileges right so this also populated when you’re on the report and what I’ve done here as I think what role does he used to get to object privileges well apparently he used these roles but he also uses DBA three times to get to an object, so drill down further select friend Eva’s object cruise where the user all was DBA, ah and it was last three times he used the DBA role to query that view well that’s okay but of course we need to grant him explicit privileges on that.
So what we now know with absolute certainty is we can revoke DBA from that user we can revoke TBA from him and as long as we grant him that and create a sequence and create a trigger and create procedure and as long as we grant him selection on that the application will still work how would you do that without this mechanism you’ll be shooting in the dark you would revoke DBA and wait to see what breaks now it’s a wonderful capability is privileged to capture right it’s just unfortunate that it’s part of data vault.