SELECT BLOG;

How APEX executes SQL statements without having permissions?

Denis Savenko -

I don't know about you, but I was shocked when saw how easily APEX executes SQL statements against my schemas for the first time. Seriously, I didn't grant a single permission to any APEX user, I only added a workspace to schema assignment inside the instance administration panel. And after that, all workspace developers started to have the same rights as the owner of the database schema, associated with the workspace. And this raises a set of questions.

How it works?

So, what mechanisms work under the APEX's hood to make it able to execute SQL and PL/SQL statements with the owner rights? And if you google this, you could find some sources telling that it leverages the package called DBMS_SYS_SQL in the SYS schema.

DBMS_SYS_SQL is an undocumented package, which is not meant to be used outside of Oracle, because it gives the invoker an ability to execute anything with rights of anybody. Mostly, it repeats the functionality of DBMS_SQL, but instead of, for instance, parse function, there's the parse_as_user and so forth.

In the recent Database Administration AskTOM Office Hours I asked this question to Connor Mc'Donald, a recognized Oracle Master, and he confirmed the fact that APEX uses DBMS_SYS_SQL to execute statements. And he insisted on not granting EXECUTE permission on the package to anyone. Seriously. Nobody.

But if you go deeper, you will be surprised even more. The thing is that DBMS_SYS_SQL is indeed not granted to be executed by anyone. I tried everything to prove the opposite - from querying system views such as DBA_TAB_PRIVS and ALL_TAB_PRIVS to Pete Finnigan's Who-Can-Access-set-of-scripts, but I failed to find a single user permitted to use the package!

This means that nobody can use DBMS_SYS_SQL directly (except SYS, of course), neither can APEX. But how then does APEX do it? And why did Connor Mc'Donald confirm it?

That's because APEX calls DBMS_SYS_SQL through a broker package, called WWV_DBMS_SQL_APEX_180100 (the suffix depends on the version of APEX installed), which is also owned by the user SYS, and this package is granted to the APEX internal user APEX_180100 (the user name also depends on the version of APEX installed).

So, this literally means everybody who has access to the APEX internal user, has potential to elevate their rights to execute any SQL with the rights of any database user.

Is it a big security concern?

It depends.

The thing is that if we query ALL_TAB_PRIVS to get the users who have grants on the WWV_DBMS_SQL_APEX_180100 package, we'll find out there's nobody, but APEX_180100.

And the user APEX_180100 is locked during APEX installation process. It means that, at least, nobody can connect to your database as this user to leverage its power.

Though, there're other packages which are owned by this user and some of these packages are granted to PUBLIC. Hence, every user in the database can execute subprograms in them. And when they invoke a procedure from one of those packages, this procedure is invoked with the rights of the package owner (read APEX_180100).

To be even more precise, there's a query to retrieve a list of packages dependant on the dangerous WWV_DBMS_SQL_APEX_180100:

select distinct owner||'.'||name from all_dependencies where referenced_owner = 'SYS' and referenced_name = 'WWV_DBMS_SQL_APEX_180100' order by 1;

We see a plenty:

APEX_180100.WWV_FLOW_AUTHENTICATION_NATIVE
APEX_180100.WWV_FLOW_AUTHORIZATION
APEX_180100.WWV_FLOW_CALENDAR
APEX_180100.WWV_FLOW_CUSTOM_AUTH_STD
APEX_180100.WWV_FLOW_DATALOAD_XML
APEX_180100.WWV_FLOW_DATA_UPLOAD
APEX_180100.WWV_FLOW_DISP_PAGE_PLUGS
APEX_180100.WWV_FLOW_DML
APEX_180100.WWV_FLOW_DYNAMIC_EXEC
APEX_180100.WWV_FLOW_EXEC_LOCAL
APEX_180100.WWV_FLOW_EXEC_REMOTE
APEX_180100.WWV_FLOW_F4000_PLUGINS
APEX_180100.WWV_FLOW_FEEDBACK_INT
APEX_180100.WWV_FLOW_INSTANCE_ADMIN
APEX_180100.WWV_FLOW_INSTANCE_REST_ADMIN
APEX_180100.WWV_FLOW_ITEM
APEX_180100.WWV_FLOW_LOAD_DATA
APEX_180100.WWV_FLOW_LOAD_EXCEL_DATA
APEX_180100.WWV_FLOW_PLUGIN
APEX_180100.WWV_FLOW_PLUGIN_DEV
APEX_180100.WWV_FLOW_PLUGIN_UTIL
APEX_180100.WWV_FLOW_PROPERTY_DEV
APEX_180100.WWV_FLOW_REST
APEX_180100.WWV_FLOW_SECURITY
APEX_180100.WWV_FLOW_SESSION
APEX_180100.WWV_FLOW_SESSION_RAS
APEX_180100.WWV_FLOW_SW_API
APEX_180100.WWV_FLOW_TEAM_FILE
APEX_180100.WWV_FLOW_TREE
APEX_180100.WWV_FLOW_UTILITIES
APEX_180100.WWV_FLOW_WEB_SERVICES
APEX_180100.WWV_FLOW_WS_API
APEX_180100.WWV_FLOW_WS_ATTACHMENT
APEX_180100.WWV_FLOW_WS_GEOCODE
APEX_180100.WWV_FLOW_WS_IMPORT
APEX_180100.WWV_FLOW_WS_SETUP
SYS.WWV_DBMS_SQL_APEX_180100

After rewriting our query to get only those which are granted to PUBLIC:

select 
  distinct owner||'.'||name from all_dependencies x
where referenced_owner = 'SYS' 
  and referenced_name = 'WWV_DBMS_SQL_APEX_180100' 
  and exists (select null from all_tab_privs where table_name = x.name and grantee = 'PUBLIC')
order by 1;

It looks like we still see some..

APEX_180100.WWV_FLOW_CUSTOM_AUTH_STD
APEX_180100.WWV_FLOW_DATA_UPLOAD
APEX_180100.WWV_FLOW_ITEM
APEX_180100.WWV_FLOW_PLUGIN_UTIL
APEX_180100.WWV_FLOW_REST
APEX_180100.WWV_FLOW_UTILITIES

But should we start to panic right now? I don't think so. Why? Because I believe that people who developed these packages thought about security and that there's no possibility to abuse subprograms in this set of packages, which can make it possible to elevate rights of a target user to a desired level.

But we should remember that such probability still exists.

What to do?

So what to do now? Nothing. Seriously. There're no what to do's, there're what NOT to do's:

  • Never unlock the internal APEX user (in example, APEX_180100 for the 18.1 version of APEX). It was locked intentionally.
  • Never grant EXECUTE privilege on either DBMS_SYS_SQL or WWV_DBMS_SQL_APEX_180100 to anybody. Seriously. They are not documented and not meant to be used deliberately.
  • Never set a weak password for the APEX instance administrator user. These strong default password complexity rules are there on purpose. Remember, by creating a workspace-schema assignment, the instance administrator automatically gives owner's privileges on the given database schema objects to all workspace developers.
  • Do not associate APEX workspaces with powerful schemas. Always create a specific schema dedicated to a particular APEX workspace. Then give needed permissions on needed database objects to this particular schema user. Otherwise, you risk to end up giving much more power to workspace developers and APEX applications than they need. And giving more than needed is always a bad thing when it comes to security.

Conclusion

I should warn you that despite the fact that I definitely have some experience in working with Oracle Database, I am not an Oracle Ace, neither by title, nor by skills. So, I can surely be wrong in some of my conclusions. But I believe if I'm mistaken somewhere or missing something, I'd just be told so.

The message of this blog post is simple - APEX is a great tool, I loved it from the first sight (c'mon, I'm a database developer who was given a chance to play with desktop and mobile web applications using the tools I'm good at), but still, you should know how the tool works under the hood, regardless you use it for a long time, or only going to try the platform. You should, because it enables you to know what to expect from the framework and how to cook it better. The only point here which makes me sad a bit is the fact that there's very few official guidelines by Oracle on how to cook stuff better in APEX - and we don't have any choice but sort them out ourselves. Maybe, in a some while, we'll see more official whitepapers and other sources of recommendations on the matter.

It happened that we are people who work with the most valuable thing of our customers - data, so, don't let yourself think you can afford not to care about its security.

And remember - with great power comes great responsibility!