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


Review: Definer and Invoker’s Rights (We’d bet you didn’t know all this.)

Session 3 – Definer’s and Invoker’s Rights

>> John:  By default, stored PL/SQL runs through the definer’s rights. What does that mean? The code runs with all of the direct privileges of the user who owns the code. So the person who created the code has a certain number of privileges. Those are the privileges with which the code runs and also the privileges used to compile it – compilation as well as run time. If the owner – the definer of the code – references any object on which he does not have permission, the code won’t compile. Furthermore, if the permissions are removed later on it won’t run. 

The invoker therefore means nothing at all other than execute privileges on the code itself. This was how PL/SQL works when it first came in as stored PL/SQL in release 7. Oracle always said that this was a good thing. 

If I was to write a code, when users can be granted minimal privileges, all access to data is controlled through the PL/SQL. Well, okay. But think of it from the other direction. 

From the other direction a low privileged user who’s been granted execute privileges on code is inheriting massive privileges from the developer. Whenever he runs the code the invoker inherits the owner’s privileges during that call, so unless that code that he’s running is absolutely perfectly written there’s the potential for the invoker to abuse those privileges. There are many, many ways to get highly privileged SQL injections through stored PL/SQL if the code is not written in the appropriate way the security in mind. 

Another point to emphasize, generally the privileges is resolving object references. If the code refers to table for use synonyms, all those object references, unless of course that fully qualified to the schema name will be resolved in the definer’s schema, not the invoker’s schema. But the real problem is the invoker has the ability to escalate these privileges. He takes on the privileges of the owner during the call. 

So in 8i Oracle introduced invoker’s rights and this was meant to fix the problem. Within the invoker’s rights model, the developer needs minimal privileges. He still needs privileges direct grants on the object used when compiling, but he doesn’t need more than that. Data object privileges will be resolved in the invoker’s schema and the invoker closely associated with that will require direct privileges – or not even direct privileges – will require privileges on the object. So the invoker no longer inherits privileges from the designer. He’s completely reliant on his own privileges. 

Well, that seems to fix the problem but it introduces the reverse problem. With invoker’s rights code the definer of the code is now inheriting the invoker’s privileges, which reverse its situation. Closely associated with that, object references. Object references will be resolved by the invoker in this case and that’s the potential for massive bugs. 

Imagine the situation where a code refers to say an object which isn’t qualified, perhaps why they’re public synonym. All the invoker has to do is remove that public synonym and then the code will refer to its own object, and the definer will know nothing about that. 

We have these two issues that the definer’s rights model, the invoker inherits excessive privileges from the definer. With the invoker’s rights model, the definer has access to the invoker’s rights at run time. 

Let’s have a quick look at this in action. I’m working in 11g for the time being. 

What I shall do is connect / as sysdba. I’m fully aware of course that using sys is not exactly the best way to run things, but never mind that, it will do for the purposes of demonstration, enable screen output. 

I’m going to create a little procedure. I’ll use this procedure throughout the next 40 minutes or so. I’ll walk through the procedure now so we can see what’s going on. 

Create or replace procedure list emp. Now give it one parameter p_job varchar2. What it’s going to do? A variable. Let’s just restore the string I’m going to use with execute immediate. 

Create a type. The type is ref cursor and the type is simply going to be a list of employee names. Then I construct a statement, select ename from scott.emp. Note a fully qualified name here. I don’t want any problems with where object names are going to be resolved. Select ename scott.emp where job = [05:21 inaudible] is an argument up there. 

I construct that statement, I’ll print it out just to be sure we know what’s going on, open the cursor, put the statement and print it out. A very simple procedure. This should simply list the employees of a given department. 

Let’s check if this actually does work. If I execute list_emp and pass in the string CLARK – Smith, Adams, James, Miller – I’m sure you’re all familiar with scott.emp table so those are the Clarks coming through. That’s all [05:59 inaudible] procedure does. 

How do we use it? I’ll create a user. Create user low – a low privileged user identified by low – and grant him the absolute minimum. I’ll grant create session to low and then grant execute on my list_emp procedure to low. 

Does it work? Connect as my low privileged user, password low, set server output on and run the code. Execute sys.list_emp and retrieve the CLARKS. There we go. Smith, Adams, James, Miller. 

I look straightforward enough and if I’m logged on as user low, if I try to select star from scott.emp, I get nowhere. As my low privileged user, my data is protected from him unless he goes through the correct approved code. Definer’s rights then mean that my user low – while it’s in the code – inherits permission to look at the table. That is in fact can potentially be a major problem. 

Imagine the situation a fairly standard hacking approach. I’m not going to teach people to hack, so I’m using very basic techniques here to hope many people are familiar with. Let us say my low privileged user tries to look at a rather dangerous view. Let’s start from dba, look at the dba view and there’s a very useful view indeed, which is users with defpwd. 

He’s trying nose around the database to see who exist. This view shows all the users with default passwords. Very useful information if you’re trying to break into a database. And of course, he can’t see it. But what he can do instead is manipulate things with the SQL injection. So what he will now do rather than executing the code like that,… put in a crafted parameter. The parameter I’ll give it there, execute sys.list_emp(‘CLERK ‘ ‘ union select username from dba user_with_defpwd where x = x. So what happens if you now run this? 

Oh dear. This is a classic case of SQL injection. What has happened is I fooled the database into running that query which wasn’t intended to run. So that come the user’s the emp table, but [09:08 inaudible] in the database with the default passwords. This is a very simple example of the danger of coded definer’s rights. My low privileged user has inherited the permission [09:22 inaudible] duration of the call and the code is so badly written that it permits an injection like that. 

>> Dave:  Pardon, John, there was a question in the queue came in ago. I think you’re just about to answer it and the question is, “Is it possible to prevent SQL injections?”


>> John:  It most certainly is. I’m very pleased to do it. This code is really badly written and I can do a much clever a SQL injections in this by the way. But it wouldn’t be professional to show them. There are many ways to prevent that injection. 

Firstly, instead of using the concatenation of a string, we will use a bind variable. That is still one would use facilities such as the dbms_assert package to validate what’s being put in here as being decent data. We can certainly tighten that up and I sincerely hope you would let us do that if your systems were vulnerable to such attacks. 

So that’s the danger then. During the process of executing that my low privileged user inherited high privileges. 

So what’s the way around it? Well, the way around it is to use invoker’s rights. 

What I shall do is recreate the procedure. 

Create or replace the procedure, author ID current user. That’s the critical keyword and that means that when we run the code it will not run with my rights, it will run with the rights of the person invoking it. 

Now I do now have to grant my low privileged user access to the relevant table. I need to grant select on emp to my low privileged user. Oops, scott.emp. 

Because remember when low trying to query scott.emp directly he didn’t have permission to do that. I do now have to give him permission to do that so that when he’s within this block of code, he will be able to run that statement. 

Connect low/low and run the code. The official code he’s meant to be running is set server output on. 

And it works. If my user then puts in his crafted URL, his crafted parameter I should say. 

Use a copy/paste and do have S in the buffer. 

The crafted URL, that now fails because the code is executing with the invoker’s rights and the invoker does not have privileges to look at that. 

That thing should fix the problem. Well, it fixed one of the problems. It means that my invoker no longer inherits privileges from the definer but that’s in fact reverses the situation. 

Now what I shall do is connect / as sysdba and I’ll create another user. We shall create a user dev identified by dev. Dev is meant to be my developer. I should qualify that and say malicious developer. So I give him some privileges. 

I’ll give him create session, create procedure, select any table. That’s given my developer – my low grade developer – the privileges he needs to do his job but not to do anything dangerous. He will not for instance be able to mess about in the data dictionary. All he can do is create code and his code can hit tables. 

Connecting this dev, I’ll create a procedure. 

So connect dev/dev and then create my code. Plus, we are now assuming that instead of the user being the hacker, it’s my developer being the hacker. 

What’s my hacker done, he’s creating the procedure, define invoker’s rights, the normal code there and then you add it in at the bottom, execute immediate ground dba to dev. 

If my user dev tries to do anything at all, if he tries to do anything on his dba privileges – drop user scott. He can’t do it. Of course he can’t, because he doesn’t have anything like privileges. But now he’s created this. What happens when it gets executed? 

I shall connect system, password Oracle, set server output on, and now we see a high privileged user executes code created by a low privileged user. What happens? 

He runs the code, execute dev.list_emp and retrieve the CLARKS. 

And on the face of it, it works. I’ve retrieved the data we want him to retrieve according to the parameter I put in. But go back in as dev and select star from session roles and a presto. All of a sudden he’s now got the dba role and everything else. That’s the reverse of the problem. 


To summarize, I’ll return to the slide. 


Privilege escalation – that’s what this is all about. Your basic problem is the code of the definer’s rights escalates the privileges of the invoker – that’s my first SQL injection – whereas invoker’s rights escalate the privileges of the definer. So which is more dangerous? Are you worried about malicious users or malicious developers? 


Historically, it was always malicious users but more and more sites now outsourcing their development, outsourcing the testing, outsourcing all sorts of things, which means malicious developers are becoming increasingly dangerous. That’s where the invoker’s rights model, which for many years was supposed to be a good thing is now possibly extremely dangerous. You must always consider the possibilities of SQL injection one way or the other. 

We can of course fix all these issues for you. We can fix all your code so your definer’s rights code will not allow crafted URLs if it’s a web based application to come in. For invoker’s rights code we can fix that, too. With our techniques we can go through it. But it’s going to take time, it’s going to take skill.


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!