Where these two goals meet is with APEX where I also get involved in helping secure environments and also applications. One part of this is APEX instance wide parameters and I am particular interested in the security parameters. There is a table and view that its based on that hold the parameters:
SQL> col owner for a30
SQL> col object_name for a30
SQL> col object_type for a30
SQL> set lines 220
SQL> l
1* select owner,object_name,object_type from dba_objects where object_name in ('APEX_INSTANCE_PARAMETERS','WWV_FLOW_PLATFORM_PREFS')
SQL> /
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------
PUBLIC APEX_INSTANCE_PARAMETERS SYNONYM
APEX_220200 WWV_FLOW_PLATFORM_PREFS TABLE
APEX_220200 APEX_INSTANCE_PARAMETERS VIEW
SQL>
My user used to do the audit has these permissions:
find_all_privs: Release 1.0.7.0.0 - Production on Sun Dec 17 20:13:22 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
NAME OF USER TO CHECK [ORCL]: SCANNER
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
User => SCANNER has been granted the following privileges
====================================================================
SYS PRIV => CREATE SESSION grantable => NO
SYS PRIV => SELECT ANY DICTIONARY grantable => NO
TABLE PRIV => SELECT object => SYS.USER$ grantable => NO
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
I have limited rights and I cannot access WWV_FLOW_PLATFORM_PREFS without additional permissions such as SELECT ANY TABLE but I can access APEX_INSTANCE_PARAMETERS. Think at a high level of the APEX_% views like DBA_% and the WWV_FLOW_% as the XXX$ tables. We cannot look at WWV_FLOW_PLATFORM_PREFS:
SQL> col name for a30
SQL> col value for a30
SQL> select name,value from apex_220200.wwv_flow_platform_prefs;
select name,value from apex_220200.wwv_flow_platform_prefs
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
But we can see the parameters in APEX_INSTANCE_PARAMETERS:
SQL> col name for a60
SQL> col value for a100
SQL> set lines 220
SQL> set pages 0
SQL> select name,value from apex_instance_parameters order by name;
ACCOUNT_LIFETIME_DAYS 9999
ALLOW_DB_MONITOR N
ALLOW_LEGACY_THEME_COPY N
ALLOW_PERSISTENT_AUTHENTICATION N
ALLOW_PUBLIC_FILE_UPLOAD N
ALLOW_RAS N
APEX_BUILDER_AUTHENTICATION APEX
APEX_LISTENER_FOP_PATH _/fop2pdf
APPLICATION_ACTIVITY_LOGGING U
APP_EXPORT_TO_DB_ENABLED Y
AUDIT_LOG_RETENTION 90
AUTOEXTEND_TABLESPACES Y
AUTO_SCHEMA_NAME N
AUTO_WORKSPACE_NAME N
BIGFILE_TABLESPACES_ENABLED N
CHECK_FOR_UPDATES Y
CLONE_SESSION_ENABLED Y
CONTENT_CACHE_MAX_FILE_SIZE 1048576
CONTENT_CACHE_SIZE_TARGET 104857600
DEBUG_MESSAGE_PAGE_VIEW_LIMIT 50000
DEFAULT_THEMES 42,51
DELETE_UPLOADED_FILES_AFTER_DAYS 14
DG_ALLOW_FORMULAS Y
DG_ALLOW_JSON_DATA_SOURCES Y
DG_ALLOW_MULTI_VALUE Y
DG_ALLOW_SQL_DATA_SOURCES Y
DG_ALLOW_TABLE_DATA_SOURCES Y
DG_ALLOW_TABLE_DATA_SOURCE_WHERE_CLAUSE Y
DG_MAXIMUM_NUMBER_OF_JSON_ROWS_TO_LOAD_PER_BUILTIN 500000
DG_MAXIMUM_NUMBER_OF_ROWS_TO_RETRIEVE_FROM_REST_DATA_SOURCE 500000
DG_MAXIMUM_ROWS_PER_TABLE 500000
DG_PRIORITIZE_SPEED_OVER_RANDOMNESS Y
DG_USE_POWERSET_IF_AVAILABLE Y
DISABLE_ADMIN_LOGIN N
DISABLE_WORKSPACE_LOGIN N
DISABLE_WS_PROV N
ENABLE_TRANSACTIONAL_SQL N
ENCRYPTED_TABLESPACES_ENABLED N
EXPIRE_FND_USER_ACCOUNTS Y
GALLERY_FILE_URLS https://apex.oracle.com/manifests/v1/#APEX_BASE_VERSION#/manifest.json
GET_STARTED_URL https://apex.oracle.com/go/get_started_apex
HPROF_DIRECTORY -
IGNORED_FRIENDLY_URL_PARAMETERS utm_campaign,utm_source,utm_medium,utm_term,utm_content,fbclid
IMAGE_PREFIX /i/
INSTANCE_HASH B14959B75AC2F04B0E067DCCC70E88D833E1A39C609AEC3F34E13C6D619A6D6A
INSTANCE_ID 2400181437117375
KEEP_SESSIONS_ON_UPGRADE N
KILL_SESSIONS_LAST_CALL_ET 3600
KILL_SESSIONS_WAIT_TIME_SEC 120
LOGIN_THROTTLE_DELAY 5
LOGIN_THROTTLE_METHODS USERNAME_IP
MAX_APPLICATION_BACKUPS 25
MAX_DATA_EXPORT_IMAGES 1000
MAX_LOGIN_FAILURES 4
MAX_SCRIPT_SIZE 500000
MAX_SESSION_IDLE_SEC 3600
MAX_SESSION_LENGTH_SEC 28800
MAX_WEBSERVICE_REQUESTS 1000
MLE_LANGUAGES JAVASCRIPT
PASSWORD_ALPHA_CHARACTERS abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ
PASSWORD_HISTORY_DAYS 0
PASSWORD_NOT_LIKE_WORDS oracle
PASSWORD_PUNCTUATION_CHARACTERS !"`'#$%&()[]{},.*+-/|\:;<=>?_~
PERSISTENT_AUTHENTICATION_MAX_DAYS 30
PLSQL_EDITING Y
PRINT_BIB_LICENSED NONE
REJOIN_EXISTING_SESSIONS P
REQUIRE_HTTPS N
REQ_NEW_SCHEMA N
RESTFUL_SERVICES_ENABLED Y
SELF_SERVICE_SCHEMA_PREFIX WKSP_
SERVICE_REQUESTS_ENABLED Y
SERVICE_REQUEST_FLOW MANUAL
SMTP_HOST_ADDRESS localhost
SMTP_HOST_PORT 25
SMTP_TLS_MODE N
SQL_SCRIPT_MAX_OUTPUT_SIZE 200000
STATS_LAST_SENT 20231215
STRONG_SITE_ADMIN_PASSWORD N
SYSTEM_HELP_URL https://apex.oracle.com/doc222
TASK_RETENTION_PERIOD_DAYS 7
TOP_THEMES 42,51
TRACING_ENABLED Y
UPGRADE_DEFERRED N
UPGRADE_DEFER_DAYS 45
UPGRADE_DEFER_DAYS_MAX 90
USERNAME_VALIDATION *
WEBSERVICE_LOGGING U
WORKSPACE_EMAIL_MAXIMUM 1000
WORKSPACE_ISSUE_FILES_YN Y
WORKSPACE_ISSUE_FS_LIMIT 15728640
WORKSPACE_MAX_OUTPUT_SIZE 2000000
WORKSPACE_PROVISION_DEMO_OBJECTS N
WORKSPACE_TEAM_DEV_FILES_YN N
WORKSPACE_TEAM_DEV_FS_LIMIT 15728640
WS_REQUEST_USAGE_Q N
ZIP_FILE_MAX_EXPANSION_FACTOR 200
97 rows selected.
SQL>
What are the permissions granted to APEX_INSTANCE_PARAMETERS?
SQL> @sc_who_can_access
Enter value for owner_to_find: APEX_220200
Enter value for object_to_find: APEX_INSTANCE_PARAMETERS
Checking object => APEX_220200.APEX_INSTANCE_PARAMETERS
====================================================================
Object type is => VIEW (TAB)
Privilege => READ is granted to =>
Role => PUBLIC (ADM = NO)
PL/SQL procedure successfully completed.
SQL>
This is easy, READ is granted to PUBLIC. What about the access to WWV_FLOW_PLATFORM_PREFS:
SQL> @sc_who_can_access
Enter value for output_method: S
Enter value for owner_to_find: APEX_220200
Enter value for object_to_find: WWV_FLOW_PLATFORM_PREFS
Checking object => APEX_220200.WWV_FLOW_PLATFORM_PREFS
====================================================================
PL/SQL procedure successfully completed.
SQL>
There is no access to WWV_FLOW_PLATFORM_PREFS by default. The APEX_INSTANCE_PARAMETERS also has some limited security built in:
SQL> @sc_print 'select * from dba_views where view_name=''''APEX_INSTANCE_PARAMETERS'''''
Executing Query [select * from dba_views where view_name='APEX_INSTANCE_PARAMETERS']
OWNER : APEX_220200
VIEW_NAME : APEX_INSTANCE_PARAMETERS
TEXT_LENGTH : 292
TEXT : select name,
case
when name in ('WALLET_PWD','SMTP_PASSWORD') and value is not null then '***'
else value
end,
created_on,
last_updated_on
from
wwv_flow_platform_prefs,
wwv_flow_current_sgid sgid
where sgid.has_apex_admin_read_role_yn = 'Y'
TEXT_VC : select name,
case
when name in ('WALLET_PWD','SMTP_PASSWORD') and value is not null then '***'
else value
end,
created_on,
last_updated_on
from
wwv_flow_platform_prefs,
wwv_flow_current_sgid sgid
where sgid.has_apex_admin_read_role_yn = 'Y'
TYPE_TEXT_LENGTH :
TYPE_TEXT :
OID_TEXT_LENGTH :
OID_TEXT :
VIEW_TYPE_OWNER :
VIEW_TYPE :
SUPERVIEW_NAME :
EDITIONING_VIEW : N
READ_ONLY : N
CONTAINER_DATA : N
BEQUEATH : DEFINER
ORIGIN_CON_ID : 3
DEFAULT_COLLATION : USING_NLS_COMP
CONTAINERS_DEFAULT : NO
CONTAINER_MAP : NO
EXTENDED_DATA_LINK : NO
EXTENDED_DATA_LINK_MAP : NO
HAS_SENSITIVE_COLUMN : NO
ADMIT_NULL : NO
PDB_LOCAL_ONLY : NO
DUALITY_VIEW : NO
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
What is this role mentioned in the where clause?
SQL> select role from dba_roles where role like 'APEX%';
APEX_ADMINISTRATOR_READ_ROLE
APEX_ADMINISTRATOR_ROLE
APEX_GRANTS_FOR_NEW_USERS_ROLE
3 rows selected.
SQL>
And lets see the view/table that is used in the where clause of APEX_INSTANCE_PARAMETERS:
SQL> @sc_print 'select * from dba_views where view_name=''''WWV_FLOW_CURRENT_SGID'''''
Executing Query [select * from dba_views where view_name='WWV_FLOW_CURRENT_SGID']
OWNER : APEX_220200
VIEW_NAME : WWV_FLOW_CURRENT_SGID
TEXT_LENGTH : 412
TEXT : select nullif(wwv_flow.get_sgid,0) security_group_id,
cu,
nls_sort,
(select wwv_flow_security.has_apex_admin_read_role_yn(cu) from sys.dual)
has_apex_admin_read_role_yn
from ( select /*+ no_merge cardinality(t 1) */
sys_context('userenv','current_user') cu,
sys_context('userenv','nls_sort') nls_sort
from
sys.dual t ) d
TEXT_VC : select nullif(wwv_flow.get_sgid,0) security_group_id,
cu,
nls_sort,
(select wwv_flow_security.has_apex_admin_read_role_yn(cu) from sys.dual)
has_apex_admin_read_role_yn
from ( select /*+ no_merge cardinality(t 1) */
sys_context('userenv','current_user') cu,
sys_context('userenv','nls_sort') nls_sort
from
sys.dual t ) d
TYPE_TEXT_LENGTH :
TYPE_TEXT :
OID_TEXT_LENGTH :
OID_TEXT :
VIEW_TYPE_OWNER :
VIEW_TYPE :
SUPERVIEW_NAME :
EDITIONING_VIEW : N
READ_ONLY : N
CONTAINER_DATA : N
BEQUEATH : DEFINER
ORIGIN_CON_ID : 3
DEFAULT_COLLATION : USING_NLS_COMP
CONTAINERS_DEFAULT : NO
CONTAINER_MAP : NO
EXTENDED_DATA_LINK : NO
EXTENDED_DATA_LINK_MAP : NO
HAS_SENSITIVE_COLUMN : NO
ADMIT_NULL : NO
PDB_LOCAL_ONLY : NO
DUALITY_VIEW : NO
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
So, you can see that both the SGID view and the WWV_FLOW_PLATFORM_PREFS are BEQUETH DEFINER which is the default for a view, so the SGID view runs the code as APEX_220200 as that is the owner of this view. Who has this read role:
SQL> @sc_who_has_role
Enter value for output_method: S
Enter value for role_to_find: APEX_ADMINISTRATOR_READ_ROLE
Investigating Role => APEX_ADMINISTRATOR_READ_ROLE (PWD = NO) which is granted to =>
====================================================================
User => SYS (ADM = YES)
PL/SQL procedure successfully completed.
SQL>
So, APEX_220200 does not have this role, only SYS has it. Also the check for this role is in a cartesian join with WWV_FLOW_PLATFORM_PREFS. What does this role do in the context of this APEX_INSTANCE_PARAMETERS view?
If we connect as SCANNER who does not have the role and select from the view we get 97 rows. If we connect as SYS who does have the role and select from the view we get 97 rows but remember the view runs as APEX_220200 who does not have the role. If we grant the role to APEX_220200 and select again as SCANNER we get the same 97 rows.
What does this obvious security check do?
If we run the API used in the SGID view as SYS for a current user of SYS we get:
SQL> set serveroutput on
SQL> begin
2 dbms_output.put_line(apex_220200.wwv_flow_security.has_apex_admin_read_role_yn('SYS'));
3 end;
4 /
Y
PL/SQL procedure successfully completed.
And if we run it as current user SCANNER who does not have the role we get:
SQL> begin
2 dbms_output.put_line(apex_220200.wwv_flow_security.has_apex_admin_read_role_yn('SCANNER'));
3 end;
4 /
Y
PL/SQL procedure successfully completed.
SQL>
Also 'Y' is returned. Hmmmm. What does the source of this package say:
SQL> select text from dba_source where name ='WWV_FLOW_SECURITY';
...
TEXT
--------------------------------------------------------------------------------
--
--==============================================================================
-- return Y if p_username should be able to read APEX dictionary views for all
-- workspaces. The user is entitled to query all workspaces if
--
-- - user is wwv_flow.g_flow_schema_owner, SYS, SYSTEM
-- - user has been granted APEX_ADMINISTRATOR_ROLE
-- - user has been granted APEX_ADMINISTRATOR_READ_ROLE
-- - user has been granted SELECT ANY DICTIONARY
TEXT
--------------------------------------------------------------------------------
--==============================================================================
function has_apex_admin_read_role_yn (
p_username in varchar2 default null )
return varchar2;
--
--==============================================================================
...
OK, so now we are getting somewhere with the security check. The comment in the package says you can read this view IF:
- The current user is the schema owner - in this example APEX_220200
- The current user is SYS or SYSTEM
- The current user has the APEX_ADMINISTRATOR_ROLE
- The current user has the APEX_ADMINISTRATOR_READ_ROLE
- The current user has SELECT ANY DICTIONARY
So, in my example above SYS has APEX_ADMINISTRATOR_READ_ROLE and SCANNER has SELECT ANY DICTIONARY. So lets create a new user TESTER with just CREATE SESSION:
SQL> sho user
USER is "SYS"
SQL> grant create session to tester identified by tester;
Grant succeeded.
SQL>
Connect to TESTER and try the view:
SQL> connect tester/tester@//192.168.56.18:1521/freepdb1
Connected.
SQL> select name,value from apex_instance_parameters;
no rows selected
SQL>
So, it works, of course!
This view is PUBLIC and can be accessed by the schema, SYS, SYSTEM, grantees of APEX_ADMINISTRATOR_ROLE and APEX_ADMINISTRATOR_READ_ROLE and if the user has SELECT ANY DICTIONARY.
A simple security mechanism to allow a PUBLIC view and then limited access except to users controlled by roles, privileges or a specific set of users. I have been teaching and advising similar methods for your own code for many many years as its simple to do in a limited way and can be done in Standard Editions where extra tools are not available.
Oracle could have used VPD or Database Vault but this is a simple mechanism based on the APEX dictionary views and code. Can it be "got around?" - of course, but any mechanism can be abused with enough effort and other rights. This is why DV or VPD would be better as they are closer to the Oracle command kernel.
If we connect to SYS and let TESTER have ALTER USER:
C:\> sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 18 11:02:04 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL> grant alter user to tester;
Grant succeeded.
SQL>
Connect to TESTER and abuse:
SQL> connect tester/tester@//192.168.56.18:1521/freepdb1
Connected.
SQL> alter user scanner grant connect through tester;
User altered.
SQL> connect tester[scanner]/tester@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from apex_instance_parameters;
COUNT(*)
----------
97
SQL>
This is not hacking as its not SQL Injection or abusing a flaw in code. Its taking advantage of a user with one privilege (ALTER USER) and using it to bypass security in another user (SCANNER and piggy backing on its SELECT ANY DICTIONARY).
The APEX dictionary view security is fine and simple but will never be 100% if someone has access to other rights. There are many other ways to get around this; I leave it to you to work out what other methods there could be
#oracleace #sym_42 #oracle #apex #security #hacking #database
