How APEX executes SQL statements without having permissions?
How APEX executes SQL in against other database schemas? Its internal user wasn't given a single permission! In this post I am trying to investigate this matter and question if it is a security concern or not
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
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
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?
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
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
To be even more precise, there's a query to retrieve a list of packages dependant on the dangerous
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
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_180100for the 18.1 version of APEX). It was locked intentionally.
- Never grant
EXECUTEprivilege on either
WWV_DBMS_SQL_APEX_180100to 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.
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!