Application Express and parsing of SQL

Recently, I worked with Oracle Support who was helping a customer who had a variety of questions about Oracle Application Express. One of the questions was:

"Please provide a detailed explanation of how apex interacts with database security. It appears that apex is manipulating queries prior to presenting them tot he database to be run either by wrapping them in a pl/sql block that prevents role based security from working or doing a pre-security check that doesn't check role level security prior to issuing a query, or some other even that bypasses role based security. "


Since there really isn't any great explanation of this anywhere, I'd like to provide a brief but lucid explanation about how parsing of SQL works in Oracle Application Express.

• The Oracle database ships with a supplied PL/SQL package named DBMS_SQL, to perform the execution of dynamic SQL.
  
  
• The Oracle database also ships with a supplied but undocumented PL/SQL package named SYS.DBMS_SYS_SQL. This package enables the execution of dynamic SQL but it also enables the parsing of a statement as a specific database user and with the privileges of this specific database user. This PL/SQL package is highly privileged, obviously. The EXECUTE privilege on this package is not granted to database user, by default. It should almost never be granted to any database user, ever. When asked to grant execution on this package to someone, a smart DBA will always say "no."
  
  
• Until Database version 10.2.0.3, both DBMS_SQL and DBMS_SYS_SQL would not observe roles when parsing SQL. This was consistent with database object access in PL/SQL itself (database roles are not observed in PL/SQL, in case you didn't know).
  
  
• In Database 10.2.0.3 and later (and XE), SYS.DBMS_SYS_SQL was changed to support a flag which enabled the observation of database roles when parsing SQL.
  
  
• In Application Express 2.1 and later, support was added to Oracle Application Express SQL Workshop on DB versions 10.2.0.3 and later to observe database roles, to be consistent with SQL*Plus. However, the execution of SQL in an Application Express application still does not observe roles when parsing user SQL. This remains true for the recently released Application Express 4.0.

So this explains a few things:

1. If you've ever wondered how Application Express can parse SQL as a specific database user and with the privileges of this database user but without ever connecting as that database user, this is the answer - DBMS_SYS_SQL.
   
   
2. When a new database user/schema is provisioned through Application Express, the discrete system privileges are granted to this new database user and not through any database role.
   
   
3. When you look the underlying database view V$SESSION, it will show that the database sessions associated with Application Express applications are connected as the minimally privileged database user APEX_PUBLIC_USER (or ANONYMOUS, if you're using the embedded PL/SQL gateway). But within that session, the underlying Application Express engine is being invoked and, after determining who the SQL can be parsed as for that specific page view, the SQL is being parsed as a different database user.
   
   
4. This also explains why, in some database versions, it will appear that roles are enabled when you issue DML statements from SQL Commands in SQL Workshop, but in the development of your application and in the execution of your application, it will appear that database roles are not enabled and that direct object privileges are required.