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) (click on video below)
  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)

Date: Aug 28, 2013

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


Tutorial Agenda

Session 2 – Tutorial Agenda

>> John:  What I want to talk about today is some security issues with 11g and then moving on to release 12c. 

In general previous releases of the database have had real problems with security particularly with PL/SQL. This does cause a bit of confusion out there when you read third party assessments contrasting Oracle security with say SQL server and so on. Some people will say that Oracle security is rubbish. Other people will say it’s fantastic. 


What causes that paradox? It’s because you can secure your database, but you have to do it. Out of the box the database is stuffed full of marvelous facilities – the developer’s marvelous facilities for users – but many of them are potentially highly dangerous. So make no mistake. Oracle database can and should be totally secure, but if your DBAs and your programmers don’t make it secure it may be wide open to abuse, wide open to hackers. 




This has always been a problem. With later releases 11 and now 12, the situation is improving. There are more and more things that one can do declaratively, more techniques for tightening up those wonderfully powerful facilities that can be open to abuse. So what I’m going to go through is spend some time with 11g. We have to. We have to understand what some of the problems are that are being addressed. Also, of course, 11g will be available to be useful for several years to come. 


I’ll spend some time in 11g environment looking at some of the major issues in the PL/SQL environment. Then we will move on to 12c and see some of the new facilities and see how they tighten up the security issues that we see with 11g. 




If possible – I don’t know if this will come true or not – I’ll maybe have quick look at network access control list I completely re-implemented in 12c and maybe talk a bit about the advanced security option as well as that has changed somewhat too. 


That’s the agenda I intend to follow. Beginning with 11g, we’ll go through what are the definer’s rights, what are invoker’s rights for code, the relationship between roles and PL/SQL. These issues, definer’s rights, invoker’s rights and roles is potentially very useful but cause a lot of confusion. 


I remember when I installed PL/SQL, it first came when it was first introduced in version 7 many, many years ago I found this mind bogglingly confusing. Invoker’s rights came in a couple of releases later and then died. That added to the confusion because invoker’s rights attempted to fix the problem caused by definer’s rights. Roles have always been confusing in the PL/SQL 11g environment. 


We then move on to the 12c techniques. It’s a whole new privilege – inherit privileges or inherit any privileges. That tightens up some of the problems with both views and with PL/SQL. They’re very nice facility indeed. We can now grow up roles to procedures, so the confusion of roles in PL/SQL, that is not removed, but we can use roles in a much more [03:31 inaudible] fashion with PL/SQL in 12c. 




Also, closer [03:35 inaudible], that’s the concept of the bequeath view. Just as PL/SQL stored procedures they execute definer’s rights to invoker’s rights with the privileges of the person who owns the PL/SQL or with the privileges of the person who invokes the PL/SQL. 


It’s the same with views. Historically views are always executed with the privileges of the owner. Now, to a certain extent we can have what I might always call an invoker’s rights view. 

Then if we have time, a couple of other things as well. 


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!