Monday, June 25, 2007

Database 11g and Apex by default

I've spent a few weeks working with Apex. Or more accurately, it was the older HTMLDB version with an upgrade not expected for another few months.

I was intrigued by the security model, that the apparently lowly privileged session on the database was able to handle all the database inserts, updates and so on without any special grants.

There is a super version of DBMS_SQL called DBMS_SYS_SQL with a very powerful PARSE_AS_USER function. That much is common knowledge to anyone interested in Oracle security (black or white hat). A user with execute access to DBMS_SYS_SQL is anyone they want to be. It is a scary privilege. It is granted directly to the FLOWS_nnnnn schema, which means that invoker rights procedures owned by that schema run with that privilege. FLOWS_nnnnn procedures are executable by the database user used for running Apex applications. That's the magic that allows the lowly account used for Apex to ignore the lack of specific grants.

It is a very scary privilege when you have sessions routinely logged onto the database with that level of access and using it (so you can't even AUDIT it usefully).

There's a whole bunch of settings that tell Apex which schemas can, should and should not be used for all the SQL and PL/SQL coded into the Apex application so that applications, developers and even Apex administrators don't get all high and mighty. Also the FLOWS_nnnnn user is locked and bolted. But if you have ever been worried about the SQL injection flaws fixed in Oracle's Quarterly Patches, you should be thinking harder about this schema. Those flaws are often where SQL can get executed where Oracle didn't expect SQL. Oracle can (and does) use tools to help identify those. Apex is all about executing your application's SQL that Oracle don't even know about.

I'm not saying that your Apex applications are vulnerable to SQL injection in ways that other Oracle applications aren't. Maybe I am. Apex developers do need to realise that, in someway, every PL/SQL and SQL they write in Apex is being executed dynamically, and use of substitution variables needs to be carefully considered, reviewed, monitored and preferably shredded.

But my main concern is that the FLOWS_nnnn API is a whole new front on the SQL injection war. According to AMIS a default 11g database install will include Apex. I'd prefer the other way. If a business plans on using Apex, they can install it. If they don't plan on using Apex (or even know what it is), then don't give it to them.

Some of the beta-talk about 11g has mentioned that the security of some of the database packages are being beefed up to allow fine-grained grants (eg allowing UTL_HTTP only to specified IP addresses). I hope that DBMS_SYS_SQL is given similar functionality. Security would definately be improved by allowing the DBA to limit the FLOWS_nnnnn to running SQL as pre-defined users. Of course the Apex code may find a different security mechanism for its SQL.

I like Apex. I agree with Noons that it is a very exciting development from Oracle. But in the words of the not-so-ancient not-so-Chinese curse, it may lead us to live in Interesting Times.

1 comment:

Jeffrey Kemp said...

Thanks very much for the note. It's good to know how Apex works with Oracle's privilege system. Also good to know NOT to unlock the FLOWS_nnnnn schema! :)

I wonder, if Apex had come with the database from the start would we care about it being enabled by default? There are other packages in Oracle which have known SQL injection weaknesses, why are they not disabled by default?

Certainly this is one good place to start to look for security flaws but until they're found, suggesting Apex should be disabled by default seems to be no more than FUD.

I would go further than you and suggest that it is very likely there are security flaws, most probably SQL injection flaws, in Apex. And anyone who's worried about it and are not using Apex should disable it, just the same as any other feature of Oracle they're worried about.

I'm not a DBA, however, and probably not paranoid enough.