Oracle Database 12c INHERIT PRIVILEGES Privilege (PL/SQL Security) Tutorial

Oracle Database Security Tutorial for PL/SQL

Historically, we had definer’s rights and invoker’s rights. Definer’s rights code lets the invoker escalate his privileges, invoker’s rights code lets the definer escalate his privileges. Big problem! And the use of roles complicated things further. We have all been caught out by misunderstanding how roles are applied (or not) within PL/SQL. Oracle Database 12c tidies this up, with the INHERIT [ANY] PRIVILEGE privilege, and the ability to grant roles to program units. It is enabled by default, so make sure you understand it.

Presented by Oracle Certified Master John Watson, SkillBuilders’ Director of Oracle Database Services.

Be sure to see our free tutorial that shows how the 12c INHERIT PRIVILEGES privilege can prevent SQL injection!

This free Oracle 12c PL/SQL Security tutorial is segmented into several separate lessons:

  1. Introduction (1:20)
  2. Tutorial Agenda (4:11)
  3. Review: Definer and Invoker’s Rights (We’d bet you didn’t know all this.) (16:51)
  4. Review: Roles and PL/SQL (6:21)
  5. 12c INHERIT PRIVILEGES Privilege (6:47)
  6. 12c Granting Roles to Procedures (6:37)
  7. 12c Bequeath Views (1:44)
  8. 12c PL/SQL Security Summary (3:57) (click on video below)

Date: Aug 28, 2013

NOTE: Some corporate firewalls will not allow videos hosted by YouTube.


12c PL/SQL Security Summary

Session 8 – 12c PL/SQL Security , Next Steps

>> John:  You’ve got to revisit all your 11g stored code in your current systems and secure it. It is frighteningly easy to achieve SQL injections. There are a many number of ways if your code is written at all loosely. If you think a third party applications you really trust third party developers who have taken their trouble to tighten up the code or not. 

Maybe you do, maybe you don’t. Whether your [00:38 inaudible] will be happy with is a different matter. You’ve got to pass through and revisit all your stored code and secure it. Remember the dangerous aspects is where the invokers can inherit rights from definers and whether definers can inherit rights from invokers. And both go in both directions. We can fix it. You can fix it yourselves but we can fix it for you. It’s a fair-sized job but it’s got to be done and never forget APEX as well. 

Then you need to start planning your upgrade to 12c. This probably makes sense to you at the same time. As you pass through all your 11g code consider replacing definer’s rights modules. What do you replace them with? You replace them with invoker’s rights modules to which roles are being granted. 

And then think of using inherit privileges. Go away from that default procedure, that default situation when the current release inherit privileges is granted to public for all new users. You want to move away from that. So assess your existing code and fix it and then as part of the upgrade to 12c consider restructuring it on those lines. 

Very briefly, the other topics I was hoping to have time for, but didn’t but they are relevant security access control lists. These are totally re-implemented with version 12. 

Version 11 the critical package…

Let me go to my 11 system. 

Dbms network acl admin. In version 11, dbms network acl has about a dozen procedures. Every one of these procedures is now deprecated. I can’t recall another case where Oracle has made such a change from one release to another. Every procedure is deprecated. 

They still work. If were back were compatibility this will all then still function but in 12c there are new procedures were meant to replace all our network access control with a new mechanism. 

Similarly, external procedures really tightened up at last. External procedures and dbms [02:48 inaudible] external jobs are now properly managed with credentials stored as separate distinct objects. The encryption is tightened up a bit as well. But you can still decrypt stored credentials but external procedures are no longer terrifying as they used to be. 

Advanced security option, the licensing model has changed. We can now setup network encryption for you, I believe even on standard edition at no extra charged and this was always extremely annoying. To use network encryption you have to buy Advanced Security option on top of Enterprise Edition. We can now do it for you just on Standard Edition licenses, I believe certainly on Enterprise Edition licenses. The licensing model has changed and we can now perhaps revisit a few clients and think like can we now do encryption that you couldn’t do previously. 


That concludes what I wanted to say so unless there are any more questions I shall pass it back to you Dave now.


Copyright 2017

Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!