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.