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

Transcript

Review: Roles and PL/SQL

Session 4 – Roles and PL SQL

>> John:  We have the business of roles – roles and PL/SQL.

The point here is that roles are invisible to PL/SQL. When you compile your code, only direct privileges are available. When you run your code, only direct privileges are available. This is for definer’s rights, I should say. When you run the code, only direct privileges are available to grant to the definer.

In effect, the definer’s rights code, all roles are disabled within the stored procedure. And certainly roles are disabled when compiling the code.

Functionally, it’s equivalent to having issued the command SET ROLE NONE. Why is this? Why is it you can’t use your privileges? It’s perfectly straightforward. It’s a technology limitation. Quickly to see what’s going on.

If I go in and say – let me connect or create a clear schema.

 

I’ll create myself, grant dba to jw identified by jw. I’ve created a user and given him ridiculously high privileges. Connect [01:36 inaudible].

What can you do? You can do pretty much anything. If he wants to delete from scott.emp, he can do it. Let’s roll back, because along with his dba role, he has the ability to pretty much anything to use the data.

Now we’ll create a procedure. If I create a procedure, delemp as begin, and in there I delete from scott.emp, end, compilation errors. What are they? Show error. At line 2 table of view doesn’t exist. That’s line 2 delete from scott.emp.

I remember way back in version 7, this cause so much confusion before we realized what was going on. It’s quite simple that when we are within the code, as the definer or the invoker, because this is definer’s rights code, when we are within the definer’s rights code block or indeed when we are compiling a definer’s rights code block, the roles are disabled and that’s why we cannot see it. But why is this and why do I say it’s a technology limitation?

It’s because roles can be enabled and disabled. They can be enabled or disabled. That makes it logically impossible to consider a role when executing a stored procedure because I might have the role enabled while I compile it, but how do I know that it’s going to be enabled when I run it? If I grant execute on the code to a third party, he doesn’t have the role at all and that he must not even be logged on.

So it becomes a logical impossibility because of the way roles are used because roles are enabled or disabled. You can dynamically switch them on and off. It’s a logical impossibility to use role within definer’s rights program module.

Invoker’s rights code, however, is very different. That infuses some interesting issues. Invoker’s rights code can use any roles that happen to be enabled for the user at the time that he invokes it in exactly the same way that an anonymous PL/SQL can use any enabled roles.

In the security point of view, the definer can in effect grab any roles within his code that the invoker happens to have.

>> Dave:  I’m sorry, John, pardon. How do you enable or disable a role?

>> John:  Right now there’s several techniques. I won’t spend too much time demonstrating. The easiest technique will be alter – I can set role none. And that will have disabled all my roles.

Now if you look at this, I’ve got nothing. If I try to run my code and query – let’s see.

 

Yes. If I try to delete from scott.emp I can’t see it. So I’ll set role dba and now of course I can. That’s the easiest way. In fact, there are much more [05:07 inaudible] ways to switch roles on and off. We can do it with operating system authentication and I can do it with proxy authentication. We have a persistent session through application server as well as a user when we pass that session out to many of the users that can switch roles on and off. That’s the persistent session switch identities.

Perhaps, best of all, I can create what I call secure application roles well it protect the role with a package and that can contest any number of issues – environment variables, maybe the time, maybe your IP address, perhaps the program you’re using and switch roles on and off accordingly. So there are many ways for controlling roles and you should do it. Blindly granting roles and letting them be enabled all the time is not good security. The end result for this, however, you can get some pretty unexpected results within your code.

I’ve tried to run through in our first half hour some of the issues that we see with PL/SQL in release 11g and earlier. After a short break, we’ll move on to the 12c features.

Copyright SkillBuilders.com 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!

 

×