Oracle 12c PL/SQL Security New Features - Session 6 of 8 - Granting Roles to Procedures

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.

[pause]

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.

[pause]

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.

[pause]

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.

[pause]

So connect as dev/dev and create my favorite procedure.

[pause]

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.

[pause]

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.

[pause]

And execute on that procedure.

[pause]

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.

[pause]

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.

[pause]

And nothing more. My low privileged developer dev can't grab anything in his too as well. That tightens things up totally.