See www.skillbuilders.com/12c-plsql-security for all free modules in this tutorial.
It is now in Oracle Database 12c possible to grant roles to the stored program units. Remember this didn't apply to anonymous PL/SQL. Anonymous PL/SQL as always executed with the enabled roles of the invoker. But we can now grant role to a stored procedure.
There are a couple of conditions. The role granted must be directly granted to the owner. I'm not sure if this is documented or not or it could've been issues I had during my own testing but certainly the last time I tested this thoroughly I found that if I granted roles to roles to roles to roles as I go down to three, it no longer functions. So that could've been just me or it may be documented. But certainly to be sure, the role granted must be granted directly to the person who's writing the code.
Also and it is documented, the owner still needs direct privileges on the object that the code references. That make perfect sense because the role might be disabled at the time that he happens to be creating the object. So you need the role, you need direct privileges on the object referenced by the code.
The invoker however needs absolutely nothing. The invoker now needs nothing, no roles, no privileges. All he needs is execute on the procedure. The invoker will then take on that role during the course of the call. This will tighten up the definer's rights problem and that our user doesn't have much at all. He needs the bare minimum and then only that role will be available, only the role is available to the invoker during the call. Not everything else that the owner happens to have.
You can combine this as well with invoker's rights and either way we are controlling privilege inheritance. Invoker's rights plus roles restrict the ability of definer's to inherit privileges from invokers and invokers inherits privileges from definers, both of which raise that ghastly possibility of privilege escalation associated typically to SQL injection.
Grant create session, create procedure to dev, and that will give him select on scott.emp to dev. I've given dev the minimum he needs to write code that hits that table. Then create a role.
Create role r1 and that'll grant select on scott.emp to r1. Finally, grant r1 to dev. It has met the requirements. The role is granted to the owner, the owner does have direct privileges.
So connect as dev/dev and create my favorite procedure.
The same procedure has executed definer's rights and query scott.emp. But now what we can do this new is I can grant r1 to procedure list_emp.
I'll create a very low privileged user now. I need to connect as sysdba and create user low identified by low, and all I shall give him is create session.
And execute on that procedure.
Grant execute on dev.list_emp to low. That's all he's got. He can log on and he can run, run one procedure. What actually is going to happen to him?
Let me try to log on. Connect sys low/low set server output on and see if he can run that thing. Just to check, if he tries to select star from scott.emp he is the lowest of the low is my user low. But then execute dev.list_emp, trying to retrieve the CLARKs and it works. And because my user low has virtually no privileges at all, there's no possible danger of the malicious developer being able to inherit dangerous privileges from him.
The final step, that functioned because of the privilege that I mentioned earlier - the privilege that we saw on the previous slide which was inheriting privileges. If I revoke that - and this is what you should be doing in all your systems after upgrade - revoke inherit privileges on user low from public, connect there, and it fails. So the final bit of tightening up the security is to grant the privilege specifically we grant inherit privileges on user low to dev.
Now we have a totally secure system and that my low privilege user dev can do that.
And nothing more. My low privileged developer dev can't grab anything in his too as well. That tightens things up totally.
You all know I have no problem working all night, and after all the apologies, I assured Anastasia it wasnt a problem, and we worked till midnight, with Anastasia emerging as another early candidate for Miss Bald USA. At a minimum, she should win Miss Wisconsin and Miss Congeniality, but probably not Miss Punctuality.
Is Anastasia the BEST BALD CHICK EVER? Shes got my vote, and Im guessing she will win a lot of votes from our community!!
Anastasias hair is long, silky Pantene hair, and its all coming off tonight!!
Her figure is Miss America PERFECT, and soon she will be shaved to silky smooth perfection!! A GODDESS AMONG WOMEN!! What a way to celebrate the Fourth of July!!
She loves her new haircut, and cant wait to show off her new BALD BEAUTY to her friends!!
UPDATE ON THE NEW BETA PLAYER- 1,900 VIDEOS NOW SHOWING.
LOOKING FOR A FEW GOOD MEN (AND WOMEN)
TO TEST THE NEW BETA PLAYER ON THE SUB SITE.
We plan to move over 500 videos from Channel 1, which currently contains 747 videos, to Channels 5, 6, 7, 8 and 9 to speed up loading.
Channels 2, 3 and 4 now have 100-200 videos each with good loading speeds.
Current plans call for Channel 10 to be used for Live Haircut Broadcasts.
BIG SAVINGS ON ONE YEAR AND TWO YEAR SUBSCRIPTIONS, and the one month subscription of $33 is STILL CHEAP, so please help us if you can!!
Zoey smiled as the clippers ran down the middle- later saying that was "the best part!"
In a FIRST EVER, Zoey brought her own razor, and asked to be shaved with it. We had no problem with that, using her 5 blade razor!!
Tune in tomorrow, same Bat Time, same Bat Channel.
BRAVE BRUNETTE BOMBSHELL BECOMES BALD BEAUTY.
Stay tuned, as were negotiating with several models, who are ready to go "SUPER SHORT" for the hottest days of the year!!
Tune in tomorrow, same Bat Time, same Bat Channel.
BOTH OF THESE YOUNG BEAUTIES SIGNED CONTRACTS.
Well sir, we wrote up TWO CONTRACTS, and both girls signed, and then we proceeded until SOMEONE changed her mind.
WHICH ONE SHAVED? NOW SHOWING on the sub site.
QUEEN DANIELLE III- LONG LIVE THE QUEEN!!