** 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