Transcript

Introduction

>> John:  Good day to you all! My name is John Watson. I’m an Oracle Certified Master DBA and I work at SkillBuilders.

 

[pause]

 

Today I’m going to present what I hope will be a major driver for the 12c upgrade, one way to plug an awful security issue in the Oracle database in earlier releases. There is a problem with the PL/SQL security model that dates back to release 8i, when the invoker’s rights security model was introduced.

 

[pause]

 

Invoker’s rights code was meant to be a security enhancement because it prevents a malicious user from escalating his privileges to those of the developer. However, it introduces a potentially much worse problem. A malicious developer can escalate his privileges to those of the invoker.

 

In this example, I’m going to demonstrate how just allowing your developer to create an index can let him gain DBA privileges. Now I’ll show you how release 12 gives you a way to control this.

Copyright SkillBuilders.com 2017

×
Transcript

Demonstration: SQL Injection via an invokers rights PL/SQL Function and an INDEX

>> John:  First, I’ll create a user who’s going to own the application.

 

[pause]

 

Create user apps identified by apps. And I’m going to give him lots of privileges, grant all privileges to apps. This is a common security model. It’s basically what you use, for example, in Oracle E-Business Suite, an application owner who’s all powerful.

 

Now I’ll create a table in the schema. Any table will do. Create table apps.t1 as select * for more users.

 

[pause]

 

Now I’ll create my user. He’s the relatively low-privileged developer who will hack the system. Create user jw identified by jw. And the privileges I’ll give him: create session, create any index, create procedure. So you can create procedures in his own schema and indexes on the application tables. That might be reasonable for a developer.

 

And now as my low-privileged user, I’ll do the hack. Connect as jw and create a function that will inject my SQL.

 

Let’s walk through the code. Create or replace function f1. My function takes a parameter p1 number and returns it straight back.

 

[pause]

 

That’s in between. It has to be a deterministic function because I’m going to use it as an index key and it has to run with the invoker’s rights so that I can escalate my privileges to those of the invoker. Because what I’m going to do is execute immediate, grant DBA to jw.

 

[pause]

 

First a quick check. Let’s confirm the privileges I have at the moment. I’ve no roles at all and if I attempt to set a role, set role dba, of course I can do it. Maybe I can. I’ll grant execute on f1 to public. And now use my function in a function-based index. Create index apps.hacki on apps.t1 and invoke my function within that.

 

And what’s happened now? Set role dba. Well, I never. It’s worked. Select * from session roles, and I’ve got a lot. So just being able to create an index has let me escalate my privileges to dba status.

Copyright SkillBuilders.com 2017

×
Transcript

Demonstration – Preventing SQL Injection

>> John:  Release 12c has the answer. There is a privileged, inherit privileges. All users have by default granted this privilege to public. This is so that your existing code will still function following upgrade, but what you should be doing after upgrade to 12 is revoke that privilege like this. Revoke inherit privileges on user apps from public.

 

And now let’s see what happens when I attempt the hack. I’ll drop the index and now try to create it. It doesn’t work anymore.

 

[pause]

 

This change of behavior plugs a security hole that has existed in Oracle databases since release 8i and it should be a major driver for upgrading all your databases to release 12 immediately if you have not already done so.

Copyright SkillBuilders.com 2017

×
Transcript

** NEW AUG 31 ** Demonstration – SQL Injection via invokers rights PL/SQL Function and a VIEW

>> John:  To set up the problem, first I’ll create a schema that is going to own all the application objects. Create user apps identified by apps.

 

And now I’ll give him lots of privileges. Grant all privileges to apps. This is the security used by many applications including, as an example, E-Business Suite. You have an application owner who is all-powerful within the application environment.

 

[pause]

 

Next, I’ll create my developer. Create user jw identified by jw. And I’ll give him very restricted privileges. Grant create session, create procedure, create any view to jw. So he can log on, he can write code in his own schema which will let him manipulate objects on which he’s been granted privileges, and he can create views in the app schema that can be used by the application users.

 

And the developer, I’ll create a function that’s going to do my SQL injection. Create or replace function f1, return virtual 2, alt ID current user. Of course it’s going to run with invoker’s rights. Then within an autonomous transaction, there’s my injection. Execute immediate, grant dba to jw, and then return a value to the user who runs it.

 

[pause]

 

Now I’ll trick the apps user into running that function by creating a view and this view will look just like the dual table that we all use. Create or replace view apps.dual as select jw f1 dummy from all_users rownum=1.

 

And lastly, let’s check what privileges do I have right now? Select * from session roles. As user JW, I’ve got not roles at all. But now all I have to do is wait because eventually there will be code in the application that will do the SQL injection for me. It’s only a matter of time. So I’ll connect as user apps and sooner or later the application is going to do something like this – select * from dual. The application runs that code, back comes dummy x. And as far as the application software is concerned, all it’s done is query the dual table. But now I’ll go back in as user JW. Well, previously I have no roles at all. What roles do I have now? I’ve got a lot.

 

What I’ve just demonstrated has been a problem for decades. The create any views privilege sounds safe enough, but it is in fact phenomenally dangerous. However, release 12 finally fixes it. There is a privilege inherit privileges that is required to allow developers to escalate their privileges to those of the invokers. By default this privilege is granted to public – select * from dba_tab_privs where table_name=’APPS’ and then we see it, the inherit privileges on the app schema has been granted to everyone.

 

This is done by default in 12c databases for backward compatibility so that any code which actually relies on this weakness will continue to function after we upgrade. What we need to do, however, to tighten up the system and prevent ad hoc SQL injections is revoke that privilege. Revoke inherit privileges on user apps from public.

 

And now my apps user when he tries to execute the dangerous code – select * from dual – we get an error and the error gives you enough information to track down what’s going on.

 

What should your action plan be to close this loophole? Because close it you must especially if you’ve outsourced your development to some third party organization. If you’ve done that, you may have no idea what backdoors on these lines have been built into your product.

 

[pause]

 

In release 11 you will have to conduct code reviews, identify all the invoker’s rights code in your application and see what it’s actually doing. Any invoker’s rights code inherently suspect because it might be exploiting loopholes like this.

 

Then once you upgrade to release 12 the problem should be solved. Revoke the inherit privileges privileged that is granted to public and grant it only to a very few trusted developers who really do need it, and then you should be safe. And of course should you require any assistance from me or my colleagues at SkillBuilders with enabling security like this, we will be only too pleased to assist.

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!

 

×