Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

Can we Hack an Oracle APEX Application?

I talked recently about securing APEX and the different security angles that should be considered when securing data in application that is written using APEX and hosted in an Oracle database. There are multiple attack vectors from a web based attack using SQL Injection through to abusing the APEX security or indeed just accessing the data directly in the schema tables in the database. A lot of the possible attack vectors are possible often due to design or configuration issues.

Let us be clear the security necessary to secure data in your database whether the application is written using APEX or any other tool is up to you. You must apply security for data at these levels (we will use APEX as an example here) and we will discuss some possible high level attacks against data:

  • Operating System security: If an attacker can access the OS directly then they could access database data files and steal data

  • Network Security: If an attacker can access the network un-encrypted then the attacker could sniff data

  • Database Configuration (hardening): If the database is not hardened and security configured then an internal DBA, developer, support person or end user with a database account could exploit settings and hardening to gain access to data

  • Patches: If the OS, network or database is not patched then there could be vulnerabilities that can be exploited that could allow access to data

  • Data Security in the database: This is the core issue for security of data. There are limitless ways to attack the data and this depends on the design and permissions. For instance if the designer granted SELECT or READ on business data then no clever exploit is needed and any database account can read the data

  • APEX application hardening and security: If the instance permissions are set incorrectly then many types of attacks are possible

  • APEX workspace security: If all pages are public the data can simply be accessed via the web application


Using APEX as an example we know that Oracle internally uses a package SYS.DBMS_SYS_SQL that allows its processing to build a web page from the APEX. This package allows code to be accessed as any database user when used directly.

This is not about APEX per-se, its just an example that could apply to any application

How is this DBMS_SYS_SQL package exposed in the database?

SQL> set serveroutput on
SQL> @sc_who_can_access
Enter value for output_method: S
old 206: lv_file_or_screen:= upper('&&output_method');
new 206: lv_file_or_screen:= upper('S');
Enter value for owner_to_find: SYS
Enter value for object_to_find: DBMS_SYS_SQL
old 207: write_op('Checking object => '||upper('&&owner_to_find')||'.'||upper('&&object_to_find'));
new 207: write_op('Checking object => '||upper('SYS')||'.'||upper('DBMS_SYS_SQL'));
old 209: get_obj(upper('&&object_to_find'),upper('&&owner_to_find'));
new 209: get_obj(upper('DBMS_SYS_SQL'),upper('SYS'));
Checking object => SYS.DBMS_SYS_SQL
====================================================================



PL/SQL procedure successfully completed.

SQL>

The dangerous procedure in this package is PARSE_AS_USER() as that can be used to execute code as any other user including SYS or SYSTEM or any other DBA or powerful account such as a schema owner; therefore allowing access to any data.

We can check now what other database objects are using DBMS_SYS_SQL:

SQL> set lines 220
SQL> col owner for a30
SQL> col name for a30
SQL> col type for a30
SQL> l
1* select owner,name,type from dba_dependencies where referenced_name='DBMS_SYS_SQL'
SQL> /

OWNER NAME TYPE
------------------------------ ------------------------------ ------------------------------
SYS DBMS_STATS_INTERNAL PACKAGE
SYS DBMS_LOGREP_UTIL PACKAGE
SYS DBMS_SQL PACKAGE BODY
SYS DBMS_SYS_SQL PACKAGE BODY
SYS DBMS_SNAPSHOT_UTL PACKAGE BODY
SYS DBMS_IREFRESH PACKAGE BODY
SYS DBMS_SNAP_INTERNAL PACKAGE BODY
SYS DBMS_RECO_SCRIPT_INVOK PACKAGE BODY
SYS DBMS_STREAMS_ADM_UTL PACKAGE BODY
SYS DBMS_LOGREP_UTIL PACKAGE BODY
SYS DBMS_FILE_GROUP_UTL PACKAGE BODY

OWNER NAME TYPE
------------------------------ ------------------------------ ------------------------------
SYS DBMS_DATA_GUARD_INTERNAL PACKAGE BODY
SYS DBMS_RESOURCE_MANAGER PACKAGE BODY
SYS DBMS_AQADM_SYS PACKAGE BODY
SYS DBMS_STATS PACKAGE BODY
SYS DBMS_STATS_INTERNAL PACKAGE BODY
SYS DBMS_DDL PACKAGE BODY
SYS DBMS_GSM_FIXED PACKAGE BODY
SYS DBMS_GSM_GSMUSER PACKAGE BODY
SYS DBMS_TRANSACTION PACKAGE BODY
SYS DBMS_EXPORT_EXTENSION PACKAGE BODY
SYS OLS_ENFORCEMENT PACKAGE BODY

OWNER NAME TYPE
------------------------------ ------------------------------ ------------------------------
SYS DBMS_PRVTAQIP PACKAGE BODY
SYS LOGMNR_DICT_CACHE PACKAGE BODY
SYS DBMS_LOGMNR_LOGREP_DICT PACKAGE BODY
SYS KUPD$DATA PACKAGE BODY
SYS DBMS_REDEFINITION_INTERNAL PACKAGE BODY
SYS DBMS_REDEFINITION PACKAGE BODY
SYS DBMS_SPACE PACKAGE BODY
SYS DBMS_DST PACKAGE BODY
SYS XS_DATA_SECURITY_UTIL PACKAGE BODY
SYS DBMS_SQL_TRANSLATOR_EXPORT PACKAGE BODY
SYS WWV_DBMS_SQL_APEX_220200 PACKAGE BODY

33 rows selected.

SQL>

Wow, that's a lot of packages in a 23 version database that use this dangerous package.

DBMS_SQL uses this package, Label security uses it, Real Application Security uses it, Log Miner uses it, Advanced Queuing uses it, SQL Translation and more...

So, the use of this package is extensive and used by SYS in a lot of cases.

There are other ways to achieve the same result as an attacker; i.e. there are other ways in the database to execute code as another user.

The package SYS.WWV_DBMS_SQL_APEX_220200 is clearly the APEX package that is used by APEX to access DBMS_SYS_SQL so we should look deeper at this. This package also has a PARSE_AS_USER() function. We do not know if this includes any protection to prevent misuse or is a thin wrapper. The package description includes:

SQL> desc WWV_DBMS_SQL_APEX_220200
PROCEDURE CLEAR_ERROR_BACKTRACE
...
PROCEDURE PARSE_AS_USER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_CURSOR NUMBER(38) IN
P_QUERY VARCHAR2 IN
P_USERNAME VARCHAR2 IN
P_USE_ROLES BOOLEAN IN DEFAULT
PROCEDURE PARSE_AS_USER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_CURSOR NUMBER(38) IN
ERROR:
ORA-24328: illegal attribute value


P_STATEMENT TABLE OF IN
P_USERNAME VARCHAR2 IN
P_LFFLG BOOLEAN IN DEFAULT
P_USE_ROLES BOOLEAN IN DEFAULT

SQL>

Let's just focus on the APEX use of this package by looking at the WWV_DBMS_SQL_APEX_220200 package. We can check who can access this package:

SQL> @sc_who_can_access
Enter value for output_method: S
old 206: lv_file_or_screen:= upper('&&output_method');
new 206: lv_file_or_screen:= upper('S');
Enter value for owner_to_find: SYS
Enter value for object_to_find: WWV_DBMS_SQL_APEX_220200
old 207: write_op('Checking object => '||upper('&&owner_to_find')||'.'||upper('&&object_to_find'));
new 207: write_op('Checking object => '||upper('SYS')||'.'||upper('WWV_DBMS_SQL_APEX_220200'));
old 209: get_obj(upper('&&object_to_find'),upper('&&owner_to_find'));
new 209: get_obj(upper('WWV_DBMS_SQL_APEX_220200'),upper('SYS'));
Checking object => SYS.WWV_DBMS_SQL_APEX_220200
====================================================================


Object type is => PACKAGE (TAB)
Privilege => EXECUTE is granted to =>
User => APEX_220200 (ADM = NO)

PL/SQL procedure successfully completed.

SQL>

So the APEX schema APEX_220200 can access this package. We should check who can access each of these packages and see if any are accessible outside of APEX; let's check WWV_FLOW_DYNAMIC_EXEC as that sounds interesting:

SQL> select owner,name,type from dba_dependencies where referenced_name='WWV_DBMS_SQL_APEX_220200';

OWNER NAME TYPE
------------------------------ ------------------------------ ------------------------------
SYS WWV_DBMS_SQL_APEX_220200 PACKAGE BODY
APEX_220200 WWV_FLOW_SESSION_RAS PACKAGE
APEX_220200 WWV_FLOW_DYNAMIC_EXEC PACKAGE BODY
APEX_220200 WWV_FLOW_CODE_EXEC_MLE PACKAGE BODY
APEX_220200 WWV_FLOW_SESSION PACKAGE BODY
APEX_220200 WWV_FLOW_SESSION_RAS PACKAGE BODY

6 rows selected.

SQL>

We can test one of the packages, WWV_FLOW_DYNAMIC_EXEC to see if it is granted to anything:

SQL> @sc_who_can_access
Enter value for output_method: S
old 206: lv_file_or_screen:= upper('&&output_method');
new 206: lv_file_or_screen:= upper('S');
Enter value for owner_to_find: APEX_220200
Enter value for object_to_find: WWV_FLOW_DYNAMIC_EXEC
old 207: write_op('Checking object => '||upper('&&owner_to_find')||'.'||upper('&&object_to_find'));
new 207: write_op('Checking object => '||upper('APEX_220200')||'.'||upper('WWV_FLOW_DYNAMIC_EXEC'));
old 209: get_obj(upper('&&object_to_find'),upper('&&owner_to_find'));
new 209: get_obj(upper('WWV_FLOW_DYNAMIC_EXEC'),upper('APEX_220200'));
Checking object => APEX_220200.WWV_FLOW_DYNAMIC_EXEC
====================================================================



PL/SQL procedure successfully completed.

SQL>

Nothing in this case BUT we should check all packages and then check for dependencies and then dependencies of those and so on. We don't know how DBMS_SYS_SQL is exposed in WWV_FLOW_DYNAMIC_EXEC and we don't know if there is any security checks in this package. Further we do not know if any children of this package (i.e. callers) expose the core functionality of DBMS_SYS_SQL or indeed if they even use WWV_FLOW_DYNAMIC_EXEC or expose anything or have any security embedded. Remember an attack is successful if we can steal data not if we can grant DBA to ourselves. If we check WWV_FLOW_DYNAMIC_EXEC to see what packages call this we can see:

SQL> col owner for a30
SQL> col name for a30
SQL> col type for a30
SQL> set lines 220
SQL> l
1* select owner,name,type from dba_dependencies where referenced_name='WWV_FLOW_DYNAMIC_EXEC'
SQL> /

OWNER NAME TYPE
------------------------------ ------------------------------ ------------------------------
APEX_220200 WWV_FLOW_UTILITIES PACKAGE
APEX_220200 WWV_FLOW PACKAGE BODY
APEX_220200 WWV_FLOW_UPGRADE PACKAGE BODY
APEX_220200 WWV_FLOW_DYNAMIC_EXEC PACKAGE BODY
APEX_220200 WWV_FLOW_CODE_EXEC PACKAGE BODY
APEX_220200 WWV_FLOW_CODE_EXEC_PLSQL PACKAGE BODY
APEX_220200 WWV_FLOW_LANG PACKAGE BODY
APEX_220200 WWV_RENDER_CHART2 PACKAGE BODY
APEX_220200 WWV_FLOW_DISP_PAGE_PLUGS PACKAGE BODY
APEX_220200 WWV_FLOW_SW_UTIL PACKAGE BODY
APEX_220200 WWV_FLOW_SECURITY PACKAGE BODY
APEX_220200 WWV_FLOW_FORMS PACKAGE BODY
APEX_220200 WWV_FLOW_BUILDER PACKAGE BODY
APEX_220200 WWV_RENDER_REPORT3 PACKAGE BODY
APEX_220200 WWV_FLOW_RENDER_QUERY PACKAGE BODY
APEX_220200 WWV_FLOW_PROVISION PACKAGE BODY
APEX_220200 WWV_FLOW_PROVISIONING PACKAGE BODY
APEX_220200 WWV_FLOW_TREE PACKAGE BODY
APEX_220200 WWV_FLOW_COLLECTION PACKAGE BODY
APEX_220200 WWV_FLOW_ITEM PACKAGE BODY
APEX_220200 WWV_FLOW_CUSTOM_AUTH_STD PACKAGE BODY
APEX_220200 WWV_FLOW_SW_API PACKAGE BODY

OWNER NAME TYPE
------------------------------ ------------------------------ ------------------------------
APEX_220200 WWV_FLOW_CONDITIONS PACKAGE BODY
APEX_220200 WWV_FLOW_SVG PACKAGE BODY
APEX_220200 WWV_FLOW_CALENDAR PACKAGE BODY
APEX_220200 WWV_FLOW_APP_INSTALL_INT PACKAGE BODY
APEX_220200 WWV_FLOW_WEB_SERVICES PACKAGE BODY
APEX_220200 WWV_FLOW_FEEDBACK_INT PACKAGE BODY
APEX_220200 WWV_FLOW_DML PACKAGE BODY
APEX_220200 WWV_FLOW_THEME_MANAGER PACKAGE BODY
APEX_220200 WWV_FLOW_UTILITIES PACKAGE BODY
APEX_220200 WWV_FLOW_ERROR PACKAGE BODY
APEX_220200 WWV_FLOW_INSTANCE_ADMIN PACKAGE BODY
APEX_220200 WWV_FLOW_PLUGIN PACKAGE BODY
APEX_220200 WWV_FLOW_INSTALL_WIZARD PACKAGE BODY
APEX_220200 WWV_FLOW_TEAM_FILE PACKAGE BODY
APEX_220200 WWV_FLOW_PLUGIN_UTIL PACKAGE BODY
APEX_220200 WWV_FLOW_VALIDATION PACKAGE BODY
APEX_220200 WWV_FLOW_COMPUTATION PACKAGE BODY
APEX_220200 WWV_FLOW_NATIVE_ITEM PACKAGE BODY
APEX_220200 WWV_FLOW_DATA_UPLOAD PACKAGE BODY
APEX_220200 WWV_FLOW_REGION_NATIVE PACKAGE BODY
APEX_220200 WWV_FLOW_REGION_LIST PACKAGE BODY
APEX_220200 WWV_FLOW_PROCESS_NATIVE PACKAGE BODY

OWNER NAME TYPE
------------------------------ ------------------------------ ------------------------------
APEX_220200 WWV_FLOW_INVOKE_API_PROCESS PACKAGE BODY
APEX_220200 WWV_FLOW_AUTHORIZATION PACKAGE BODY
APEX_220200 WWV_FLOW_AUTHENTICATION PACKAGE BODY
APEX_220200 WWV_FLOW_INTERACTIVE_GRID PACKAGE BODY
APEX_220200 WWV_FLOW_AUTHENTICATION_NATIVE PACKAGE BODY
APEX_220200 WWV_FLOW_MAINT PACKAGE BODY
APEX_220200 WWV_FLOW_DEBUG PACKAGE BODY
APEX_220200 WWV_FLOW_TREE_REGION PACKAGE BODY
APEX_220200 WWV_FLOW_LEGACY_PLUGINS PACKAGE BODY
APEX_220200 WWV_FLOW_EXEC PACKAGE BODY
APEX_220200 WWV_FLOW_EXEC_LOCAL PACKAGE BODY
APEX_220200 WWV_FLOW_SW_PAGE_CALLS PACKAGE BODY
APEX_220200 WWV_FLOW_EXEC_REMOTE PACKAGE BODY
APEX_220200 WWV_FLOW_DATALOAD_XML PACKAGE BODY
APEX_220200 WWV_FLOW_EXEC_WEB_SRC PACKAGE BODY
APEX_220200 WWV_FLOW_ADVISOR_DEV PACKAGE BODY
APEX_220200 WWV_FLOW_EXEC_WEB_SRC_RESTSQL PACKAGE BODY
APEX_220200 WWV_FLOW_WEB_SRC_SYNC PACKAGE BODY
APEX_220200 WWV_FLOW_F4000_PLUGINS PACKAGE BODY
APEX_220200 WWV_FLOW_F4000_UTIL PACKAGE BODY
APEX_220200 WWV_FLOW_PROPERTY_DEV PACKAGE BODY
APEX_220200 WWV_FLOW_DATA_PROFILE_DEV PACKAGE BODY

OWNER NAME TYPE
------------------------------ ------------------------------ ------------------------------
APEX_220200 WWV_FLOW_WEB_SRC_DEV PACKAGE BODY
APEX_220200 WWV_FLOW_IR_API PACKAGE BODY
APEX_220200 WWV_FLOW_REST_WS PACKAGE BODY
APEX_220200 WWV_SAMPLE_DATASET PACKAGE BODY
APEX_220200 WWV_DICTIONARY_CACHE_DEV PACKAGE BODY
APEX_220200 WWV_DBMS_CLOUD PACKAGE BODY
APEX_220200 WWV_FLOW_SODA_DEV PACKAGE BODY
APEX_220200 WWV_FLOW_DATA_LOADER PACKAGE BODY
APEX_220200 WWV_FLOW_APPROVAL PACKAGE BODY
APEX_220200 WWV_DG_BLUEPRINT_UTIL_INT PACKAGE BODY
APEX_220200 WWV_FLOW_DATA_LOADING PACKAGE BODY
APEX_220200 WWV_FLOW_WIZARD_API PACKAGE BODY
APEX_220200 WWV_FLOW_CALENDAR_DEV PACKAGE BODY
APEX_220200 WWV_FLOW_PLUGIN_DEV PACKAGE BODY
APEX_220200 WWV_FLOW_CODE_EXEC_DEV PACKAGE BODY
APEX_220200 WWV_FLOW_CODE_EXEC_MLE_DEV PACKAGE BODY
APEX_220200 WWV_FLOW_PLSQL_EDITOR PACKAGE BODY
APEX_220200 WWV_FLOW_LOAD_DATA PACKAGE BODY
APEX_220200 WWV_FLOW_GENERATE_DDL PACKAGE BODY

85 rows selected.

SQL>

We can go further, we could check all child packages of everything that uses DBMS_SYS_SQL and we would need to ensure that every path is protected. i.e. ensure only the code necessary can be executed. The problem with DBMS_SYS_SQL is that it can run code as other users and run any code. This is a good example of what you must look at in your own code and applications; if you expose a route to read or change data protect that route properly.

So there are many routes possible; exploit a package that exposes something dangerous with things like SQL Injection or gain access to the owner of the package and simply use it or find a child that accesses the package and do the same, use it, exploit it or gain access to the owner.

So lets check the state of APEX_220200 and see if its accessible:

SQL> @find_all_privs



find_all_privs: Release 1.0.7.0.0 - Production on Tue May 28 07:45:23 2024
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

NAME OF USER TO CHECK [ORCL]: APEX_220200
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

User => APEX_220200 has been granted the following privileges
====================================================================
SYS PRIV => ALTER DATABASE grantable => NO
SYS PRIV => ALTER SESSION grantable => NO
SYS PRIV => ALTER USER grantable => NO
SYS PRIV => CREATE CLUSTER grantable => YES
SYS PRIV => CREATE DIMENSION grantable => YES
SYS PRIV => CREATE INDEXTYPE grantable => YES
SYS PRIV => CREATE JOB grantable => YES
SYS PRIV => CREATE MATERIALIZED VIEW grantable => YES
SYS PRIV => CREATE MLE grantable => YES
SYS PRIV => CREATE OPERATOR grantable => YES
SYS PRIV => CREATE PROCEDURE grantable => YES
SYS PRIV => CREATE PUBLIC SYNONYM grantable => NO
SYS PRIV => CREATE ROLE grantable => NO
SYS PRIV => CREATE SEQUENCE grantable => YES
SYS PRIV => CREATE SESSION grantable => YES
SYS PRIV => CREATE SYNONYM grantable => YES
SYS PRIV => CREATE TABLE grantable => YES
SYS PRIV => CREATE TABLESPACE grantable => NO
SYS PRIV => CREATE TRIGGER grantable => YES
SYS PRIV => CREATE TYPE grantable => YES
SYS PRIV => CREATE USER grantable => NO
SYS PRIV => CREATE VIEW grantable => YES
SYS PRIV => DROP PUBLIC SYNONYM grantable => NO
SYS PRIV => DROP TABLESPACE grantable => NO
SYS PRIV => DROP USER grantable => NO
SYS PRIV => EXECUTE DYNAMIC MLE grantable => YES
SYS PRIV => EXEMPT REDACTION POLICY grantable => NO
SYS PRIV => INHERIT ANY PRIVILEGES grantable => NO
SYS PRIV => UNLIMITED TABLESPACE grantable => NO
TABLE PRIV => DELETE object => MDSYS.SDO_GEOM_METADATA_TABLE grantable => NO
TABLE PRIV => DELETE object => FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ grantable =>
YES
TABLE PRIV => EXECUTE object => SYS.ANYDATA grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_APPLICATION_INFO grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_ASSERT grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_CRYPTO grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_CRYPTO_INTERNAL grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_DB_VERSION grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_FLASHBACK grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_LDAP grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_LDAP_UTL grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_LOCK grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_METADATA grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_MLE grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_OUTPUT grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_PRIV_CAPTURE grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_RANDOM grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_REDACT grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_REGISTRY grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_SCHEDULER grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_SESSION grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_SQL grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_STATS grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_STATS_INTERNAL grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_TYPES grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_UTILITY grantable => NO
TABLE PRIV => EXECUTE object => XDB.DBMS_XMLDOM grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_XMLGEN grantable => NO
TABLE PRIV => EXECUTE object => XDB.DBMS_XMLPARSER grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_XMLSTORE grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_XPLAN grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_XPLAN_TYPE_TABLE grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_XS_NSATTR grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_XS_NSATTRLIST grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_XS_SESSIONS grantable => NO
TABLE PRIV => EXECUTE object => SYS.DIANA grantable => NO
TABLE PRIV => EXECUTE object => SYS.DIUTIL grantable => NO
TABLE PRIV => EXECUTE object => SYS.GETLONG grantable => NO
TABLE PRIV => EXECUTE object => SYS.HTF grantable => NO
TABLE PRIV => EXECUTE object => SYS.HTP grantable => NO
TABLE PRIV => EXECUTE object => SYS.JSON_ARRAY_T grantable => NO
TABLE PRIV => EXECUTE object => SYS.JSON_DATAGUIDE grantable => NO
...
TABLE PRIV => SELECT object => SYS.DUAL grantable => YES
TABLE PRIV => SELECT object => FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ grantable =>
YES
TABLE PRIV => UPDATE object => FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ grantable =>
YES

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

This schema has lots of useful privileges such as ALTER USER; With this an attacked is SYSDBA as if the attacker gets access to APEX_220200 then they ca simply change the SYS password IF they can access the root container of course; if not then gain access to an account that can steal data or do other damage. What about the APEX_220200 account:

SQL> @sc_print 'select * from dba_users where username=''''APEX_220200'''''
Executing Query [select * from dba_users where username='APEX_220200']

USERNAME : APEX_220200
USER_ID : 131
PASSWORD :
ACCOUNT_STATUS : LOCKED
LOCK_DATE : 03-APR-23
EXPIRY_DATE :
DEFAULT_TABLESPACE : SYSAUX
TEMPORARY_TABLESPACE : TEMP
LOCAL_TEMP_TABLESPACE : TEMP
CREATED : 03-APR-23
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS :
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : NONE
PROXY_ONLY_CONNECT : N
COMMON : NO
LAST_LOGIN :
ORACLE_MAINTAINED : Y
INHERITED : NO
DEFAULT_COLLATION : USING_NLS_COMP
IMPLICIT : NO
ALL_SHARD : NO
EXTERNAL_SHARD : NO
PASSWORD_CHANGE_DATE :
MANDATORY_PROFILE_VIOLATION : NO
PROTECTED : NO
READ_ONLY : NO
DICTIONARY_PROTECTED : NO
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

So, APEX_220200 is LOCKED BUT if we have access to an account that has ALTER USER then we could gain access to APEX_220200 or add the ability to proxy to it:

SQL> @sc_who_has_priv
Enter value for priv_to_find: ALTER USER
Privilege => ALTER USER has been granted to =>
====================================================================
User => APEX_220200 (ADM = NO)
User => ORDS_METADATA (ADM = NO)
User => HRREST (ADM = NO)
User => VF (ADM = NO)
User => TESTER (ADM = NO)
User => SYS (ADM = NO)
Role => DBA (ADM = NO) which is granted to =>
User => AV (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
Role => IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DATAPUMP_IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => AV (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
User => SYS (ADM = YES)
User => GSMADMIN_INTERNAL (ADM = NO)
User => SYS (ADM = YES)
Role => DBA (ADM = NO) which is granted to =>
User => AV (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
Role => DATAPUMP_IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => AV (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
User => SYS (ADM = YES)
User => GSMADMIN_INTERNAL (ADM = NO)
Role => DV_ACCTMGR (ADM = NO) which is granted to =>

PL/SQL procedure successfully completed.

SQL>

Plenty of users to investigate. A detailed review of the database and application design should be made.

The idea here is to check for weakness in your data security design and application design and see if its possible to get access to dangerous things in your applications that would then allow you to steal data or change data. Check all access paths and see if its possible. Add protection code to your own code; i.e. use ACCESSIBLE BY clause to ensure that your package or procedure can only be called from where you decide OR go one better and use one of the call stack procedures and check the stack yourself BUT also check all possible access to schemas that leak or expose weakness and check all chains of access. Also check PL/SQL code for security vulnerabilities and check your application security permissions.

As you can see, data security is about layers and understanding all of the possible layers and finding ways to make sure each layer is secure and protections are implemented where necessary.

#oracleace #sym_42 #oracle #database #security #23c #23ai #securecode #plsql #grants #datasecurity #databreach #protect

Can We Add New Language Features to PL/SQL?

This is a thought experiment really but is possible to do with some efforts and in a more targeted way.

I have coded in PL/SQL for around 29 years and it is one of my favourite languages along with C. I also code in Lua more and .net almost daily but PL/SQL and C are my favourite languages.

PL/SQL is well documented to be based on ADA. If you look at ADA you can easily see that PL/SQL has some ADA features missing and of course SQL added.

I was remembering CFront the first C++ compiler. This was not a compiler in the normal sense as it read the C++ user program source and converted it into native C to be complied by the C compiler available on the machine. The CFront compiler was also written in C++ so had a strange bootstrap mechanism to allow some pre-compiled C libraries to be used when CFront was first compiled from C++ on a new machine. This C++ compiler had lexers, AST and parsers etc but it output C code not assembler or native binaries for the machine. CFront died many many years ago at the start of the 90s because it was hard to add more and more new language features and syntax; well not really died as it is still available to download if you wanted to. Actual proper C++ compilers were written before CFront stopped being the de-facto C++ compiler. The C++ language at the time that CFront was used was much simpler than C++ now.

If we look at the PL/SQL DIANA nodes that are available to see in the dictionary code shipped with the database even in 23c / 23ai we can still see DIANA nodes for ADA features that are not in PL/SQL.

The idea of CFront got me thinking. I have talked about and even mentioned here a few times over the years how it would be nice to have some other features in the PL/SQL language. Oracle are not going to add new features to PL/SQL just because we ask them to but could we add new features ourselves if we wanted to.

I started coding C for Oracle a very long time ago and coding with the OCI libraries (the original OCI not the cloud!!) and I also coded in Pro*C where SQL was embedded with some extra syntax into C programs that were then pre-processed and generated a complete C program with shipped Oracle libraries that allowed the C+SQL to work. Pro*C was a good example as a C programmer could write C and embed SQL where they needed database access rather than use C APIs direct. This made the original source easier to read and understand. The original source could not be compiled direct to a binary and had to be pre-processed into C that could be compiled to a binary along with linking Oracles libraries.

APEX is not the same idea that I am talking about above but is similar. You choose, add meta data, write code snippets and a complete application is created and runs in the APEX environment

This thought experiment would allow us to add:

  • language syntax: We could add a syntax "++" to a variable, i.e. instead of writing i:=i+1 we could write i++ as we can in C. I have shown an example of this in the past as a function on this blog

  • Security: We could add security features to the PL/SQL code that is written as a kind of place holder. This means the developer can say "create or replace procedure dummy is begin --++DOSEC" where we say make this a secure procedure BUT the security code is added after development but before deployment. This means the developer can identity procedures that are important and need to be secured BUT even the developer doesn't get to see the security code and its added and managed by the security team. We do this now for customers

  • New Features: Imagine that want multi-threaded PL/SQL and rather than write complex code to use jobs and implement mechanisms to do sync/lock, semaphores and more it would be better to use simple language features to define and add threads rather than use a complex library directly. We can do this now for instance by automatically adding license code

  • Productivity: If we write pattern code, i.e. we have 600 database tables and each table needs red, write, update, delete PL/SQL code. We can just add syntax to PL/SQL that states this, i.e. "create or replace procedure dummy as {{uses access 'where clause' table schema.table}}...". The code would be managed and generated and can then be used in the procedure without the developer writing it specifically

  • Template: We can also use templates in a similar way like inheritance in C++ or maybe as interfaces in vb.net for repeated code or slightly modified code

  • Insecure or not supported : If for instance your code uses DBMS_JOB you should change to DBMS_SCHEDULER. This change is not just a find and replace in an editor - and this is just one example - the change may require some code rewrite as well. We help customers do this now



All of these examples are possible to do by taking new syntax and creating valid PL/SQL in the same way Pro*C creates valid C or CFront creates valid C from C++

The CFront or Pro*C approach could easily be used in PL/SQL using any simple method to process PL/SQL with extra syntax to produce real PL/SQL to compile

We could also change the language so that {} becomes begin/end or as we said i++ becomes i:=i+1 and so on. For fun!!

We are also not limited to PL/SQL, we could do the same ideas on Lua, VB.NET, c#.NET and many more. We do all of these things above now for PL/SQL except adding new syntax but we could do that now as well if needed using our same methods. We do these things for security reasons in most cases in our PL/SQL code using tools we have developed.

#oracleace #sym_42 #oracle #database #plsql #code #securecode #apex #23ai #security

Locate an Error in Wrapped PL/SQL

I had a conversation a few weeks ago with someone who asked me how to find a missing table when you have a wrapped PL/SQL file and cannot see the source code and you install it and it gives an ORA-0942 error - Table or view does not exist. He asked me for advice on how you might find out what table or view cannot be accessed by the wrapped PL/SQL.

This was an interesting question.

Firstly why not just look at the clear text PL/SQL? - well in this example case it was a commercial application he was asked to install into a database and the PL/SQL was wrapped and he could not see the code. He did of course ask the vendor and in the end got an answer that some other scripts that created the tables needed to be run first and problem solved.

So assuming that you do not have access to the clear text PL/SQL what can you do?

I want to create a simple example. First connect to my 23c database as SYS and create a sample user PETE1:

[oracle@localhost ~]$ sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Mon Mar 18 09:58:52 2024
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle. All rights reserved.


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> create user pete1 identified by pete1;

User created.

SQL> grant create session to pete1;

Grant succeeded.

SQL> grant unlimited tablespace to pete1;

Grant succeeded.

SQL> grant create procedure to pete1;

Grant succeeded.

SQL>

Connect to my sample user PETE1 and test whether I can access SYS.USER$. Obviously I know the answer in advance that the error is my PL/SQL cannot access SYS.USER$ but I need to be sure there is an error first simply in SQL*Plus:

SQL> connect pete1/pete1@//192.168.56.18:1521/freepdb1
Connected.
SQL> select name from sys.user$;
select name from sys.user$
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

Now create the PL/SQL and in it access SYS.USER$ - the table that is missing, inaccessible.

SQL> get test.sql
1 -- test PL/SQL for wrapped create
2 create or replace procedure test as
3 lv_password varchar2(4000);
4 begin
5 select password
6 into lv_password
7 from sys.user$
8 where name='SYS';
9* end;
10 .

Now wrap the PL/SQL:

[oracle@localhost ~]$ wrap iname=test.sql oname=test.plb

PL/SQL Wrapper: Release 23.0.0.0.0 - Developer-Release on Mon Mar 18 10:07:41 2024
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle and/or its affiliates. All rights reserved.

Processing test.sql to test.plb
[oracle@localhost ~]$

Show the code to prove it is wrapped:

[oracle@localhost ~]$ cat test.plb
create or replace procedure test wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
81 be
LlzHv6ZvxN4mhihv9rosYR6UAmEwg5nnm7+fMr2ywFwWoWLRzLh0iwbSvW0ouHTLy/4owMzn
x3TAM7h0ZSV8f3x8UKCLwMAy/tKGBnSfgVIyy8yp1YYG0tKZCOpnJfqVKW9duwovdojYH6uK
qxoVlYi57p6glXKzsT0rpBbYSNdD/MHg16ama3fKHg==

/
[oracle@localhost ~]$

Now let us try and install this code into my database:

SQL> @test.plb

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/2 PL/SQL: SQL Statement ignored
6/11 PL/SQL: ORA-00942: table or view does not exist
SQL>

OK, so we know there is a ORA-00942 error but we do not know what table causes it so that we can fix it as the code is wrapped and looking at lines 4 and 6 is meaningless in this context

How to know what table or view does not exist?

Try the ALL_ERRORS view

SQL> set serveroutput on
SQL> @sc_print 'select * from all_errors'
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from all_errors','''','''''');
Executing Query [select * from all_errors]
OWNER : PETE1
NAME : TEST
TYPE : PROCEDURE
SEQUENCE : 1
LINE : 6
POSITION : 11
TEXT : PL/SQL: ORA-00942: table or view does not exist
ATTRIBUTE : ERROR
MESSAGE_NUMBER : 0
-------------------------------------------
OWNER : PETE1
NAME : TEST
TYPE : PROCEDURE
SEQUENCE : 2
LINE : 4
POSITION : 2
TEXT : PL/SQL: SQL Statement ignored
ATTRIBUTE : ERROR
MESSAGE_NUMBER : 0
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

This is not useful as it doesn't tell me what table does not exist and is a repeat of the error we saw trying to compile the wrapped code in SQL*Pus.

We can try dependencies instead:

SQL> @sc_print 'select * from all_dependencies where name=''''TEST'''' and referenced_type=''''TABLE'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from all_dependencies where name=''TEST'' and referenced_type=''TABLE''','''','''''');
Executing Query [select * from all_dependencies where name='TEST' and
referenced_type='TABLE']
OWNER : PETE1
NAME : TEST
TYPE : PROCEDURE
REFERENCED_OWNER : SYS
REFERENCED_NAME : USER$
REFERENCED_TYPE : TABLE
REFERENCED_LINK_NAME :
DEPENDENCY_TYPE : HARD
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

Yes, the issue is there in the dependencies view and shows that it is indeed SYS.USER$ but if there were hundreds of tables then its harder to locate the actual one. Test if we can access it as PETE1 in SQL*Plus:

SQL> desc sys.user$
ERROR:
ORA-04043: Object sys.user$ does not exist.


SQL>

Reconnect as SYS and grant ALTER SESSION to PETE1 to allow the use of trace:

C:\_audit_scripts\scripts>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 18 10:22:48 2024

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 session to pete1;

Grant succeeded.

SQL>

Set trace and install the PLB again:

[oracle@localhost ~]$ sqlplus pete1/pete1@//192.168.56.18:1521/freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Mon Mar 18 10:23:02 2024
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle. All rights reserved.

Last Successful login time: Mon Mar 18 2024 10:22:32 +00:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> @test.plb

Warning: Procedure created with compilation errors.

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL>

locate the trace file:

SQL> sho parameter diag

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /opt/oracle
diagnostics_control string IGNORE
SQL>

Look for the potential trace file:

[oracle@localhost oracle]$ find . -name "*.trc" -print 2>/dev/null | xargs ls -al 2>/dev/null | grep "Mar 18"
-rw-r-----. 1 oracle oinstall 2243134 Mar 18 10:30 ./diag/rdbms/free/FREE/trace/FREE_dbrm_3163.trc
-rw-r-----. 1 oracle oinstall 101670 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr0_184977.trc
-rw-r-----. 1 oracle oinstall 905271 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr1_179252.trc
-rw-r-----. 1 oracle oinstall 42302 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr2_185301.trc
-rw-r-----. 1 oracle oinstall 71822 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr3_185112.trc
-rw-r-----. 1 oracle oinstall 41974 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr4_185336.trc
-rw-r-----. 1 oracle oinstall 1151 Mar 18 09:57 ./diag/rdbms/free/FREE/trace/FREE_j001_183579.trc
-rw-r-----. 1 oracle oinstall 1150 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_j003_184635.trc
-rw-r-----. 1 oracle oinstall 1453 Mar 18 09:55 ./diag/rdbms/free/FREE/trace/FREE_j005_184641.trc
-rw-r-----. 1 oracle oinstall 1449 Mar 18 09:55 ./diag/rdbms/free/FREE/trace/FREE_j006_184644.trc
-rw-r-----. 1 oracle oinstall 943553 Mar 18 09:55 ./diag/rdbms/free/FREE/trace/FREE_lmhb_3199_data.trc
-rw-r--r--. 1 oracle oinstall 31969 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_m000_183247.trc
-rw-r-----. 1 oracle oinstall 72908 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_m000_184934.trc
-rw-r-----. 1 oracle oinstall 35341 Mar 18 10:20 ./diag/rdbms/free/FREE/trace/FREE_m001_184937.trc
-rw-r--r--. 1 oracle oinstall 31938 Mar 18 10:09 ./diag/rdbms/free/FREE/trace/FREE_m002_184940.trc
-rw-r--r--. 1 oracle oinstall 31969 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_m004_184027.trc
-rw-r--r--. 1 oracle oinstall 31969 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_m005_180811.trc
-rw-r-----. 1 oracle oinstall 1744 Mar 18 10:22 ./diag/rdbms/free/FREE/trace/FREE_ora_185518.trc
-rw-r-----. 1 oracle oinstall 537629 Mar 18 10:24 ./diag/rdbms/free/FREE/trace/FREE_ora_185538.trc
[oracle@localhost oracle]$

Looks like the last ones are a potential match so grep now for the PL/SQL procedure:

[oracle@localhost trace]$ grep -i test *.trc
FREE_m000_178093.trc: SQLSTR={alter table WRH$_AWR_TEST_1 split partition WRH$_AWR_TEST_1_1405253007_MXSN at (1405253007,1608) into (partition WRH$_AWR_TEST_1_1405253007_1608, partition WRH$_AWR_TEST_1_1405253007_MXSN tablespace SYSAUX) update indexes}
FREE_m000_178093.trc:Error=12954 prepare storage for table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m000_178093.trc:kewcpspr: Skip split due repair needed table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m000_184934.trc: SQLSTR={alter table WRH$_AWR_TEST_1 split partition WRH$_AWR_TEST_1_1405253007_MXSN at (1405253007,1656) into (partition WRH$_AWR_TEST_1_1405253007_1656, partition WRH$_AWR_TEST_1_1405253007_MXSN tablespace SYSAUX) update indexes}
FREE_m000_184934.trc:Error=12954 prepare storage for table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m000_184934.trc:kewcpspr: Skip split due repair needed table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m001_179164.trc: SQLSTR={alter table WRH$_AWR_TEST_1 split partition WRH$_AWR_TEST_1_1405253007_MXSN at (1405253007,1632) into (partition WRH$_AWR_TEST_1_1405253007_1632, partition WRH$_AWR_TEST_1_1405253007_MXSN tablespace SYSAUX) update indexes}
FREE_m001_179164.trc:Error=12954 prepare storage for table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m001_179164.trc:kewcpspr: Skip split due repair needed table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_ora_185538.trc:create or replace procedure test wrapped
FREE_ora_185538.trc: value="procedure test wrapped
FREE_ora_185538.trc: value="TEST"
[oracle@localhost trace]$

Nothing useful is found in the trace. We can see the ORA-00942 and the insert into error$ but not the SQL that causes the error in the trace file.

There are other things we can do; the first is talk to the vendor and ask why their wrapped code fails to compile or ask the vendor about the line number in the code and what table is missing. We can use simple ideas such as dependencies to try and locate the missing table or permissions when we try and compile wrapped code where we don't have access to the clear text code.

Also remember the 9i wrap.exe and lower was the front end to a PL/SQL compiler, the 10g and higher is a simple obfuscation of the clear text code.

#oracleace #sym_42 #oracle #database #security #plsql #compile #permissions #error #942 #23c

Attention PL/SQL Programmers - is your PL/SQL at risk of breach?

Do you develop software in PL/SQL?

I will show you in the next few minutes how you can learn to find security vulnerabilities in your PL/SQL code

Even if the database that your PL/SQL is deployed to is secure then if you do not program your PL/SQL defensively and securely then it is a matter of when and not if that a breach of your customer or employers data could occur through non-secure PL/SQL applications.

If you are not aware of what security issues in PL/SQL look like and how they could be exploited then it is not easy for you to code your PL/SQL securely. How would you feel if it was your lack of knowledge that caused a data breach to occur?

We have an easy solution for you. We are running two live training events online on the 27th March 2024 on UK hours and the same class is re-run on the 28th March 2024 but this is on USA EST time zones. Some highlights of the class next:

  • The classes are taught by Pete Finnigan live but on-line via webex so you do not need to leave your office or home to attend.

  • You can ask questions at any time during the class day and get access to Pete and learn from him and his over 20 years of experience in this field of secure coding in PL/SQL.

  • The class is one day and is called Secure Coding in PL/SQL and is taught live from 9am to 5pm. The class includes taught lessons and many demonstrations throughout the day.

  • Each student will receive pdfs of full course notes and lessons and also over 100 free SQL and PL/SQL scripts and tools covering the demonstrations and also the many free tools used in the class.

  • You do not need to be a PL/SQL developer to attend although most attendees are usually PL/SQL developers. We have also taught DBA staff and even managers who would like to understand the security risks likely to be found in PL/SQL their developers create.

  • The high level agenda is as follows:

    • Data Theft: This lesson covers why data can be stolen or privilege escalated in a
      database focusing on issues related to privileges assigned to PL/SQL, bad
      programming practices and leakage of data. This section is an overview to allow the student to see how PL/SQL fits into
      the security model intended to protect Data

    • Permissions: We cover permissions of packages and procedures and design decisions that affect security

    • Coding Errors: This section introduces common PL/SQL Security programming issues and
      for each shows the issue in code form and exploitation and then also in terms
      of secure coding and solution. These include: Input validation, Object validation, Open interfaces, SQL and PL/SQL and Other Injection issues, File and external access, Operating system commands, Vulnerable and dangerous package use and more

    • Secure Coding Best Practice: We look at fixing the issues and secure coding best practice

    • Encryption: We use encryption as an example to demonstrate everything we have covered so far

    • Protecting PL/SQL: This section discusses techniques to lock down PL/SQL in terms of Preventing IPR loss, Prevent unauthorised execution both in the host database or if the code is removed, License type features and wrapping and unwrapping

    • Finishing Up: We cover processes to secure code and review coding and also automated checking of your code and finally creating secure coding policies




The course material and demonstrations have recently been extensively updated and cover up to Oracle version 23c. Of course all earlier versions are also covered.

The course fee is just £440 GBP (plus VAT if applicable).

To register your place on our class then simply send an email to securecode@petefinnigan.com and we will help you secure your place

As a bonus if you book more than one place we will include a 10% discount for all places booked by you.

Your PL/SQL code will be more secure after this class so please register your place now.

To register your place on our class then simply send an email to securecode@petefinnigan.com and we will help you secure your place

#oracleace #sym_42 #oracle #plsql #secure #coding #training

How to Secure all of Your Oracle Databases - Part 1

How do you know how secure your Oracle databases are?

How secure should your Oracle databases be?

These are interesting questions that we will cover in this three part post. This first part is going to cover the high level discussions / strategy and issues related to that. The second part will cover the process of reviewing what you have now and then the third part is going to look at the ongoing strategy to fix and secure all databases in the organisation.

You do not need a percentage secure figure or some other artificial measure of data security. These figures can be useful though to trace progress but if a database in your opinion is 75% secure what does that really mean?.

If it is measuring 75% secure this week and 71% last week and 69% the week before then that's useful as an indicator that progress is going in the right direction.

Some companies use standards like the CIS Benchmark to secure their database against. In the absence of anything else this is a starting point. Checklists like CIS focus on defaults and simple hardening. Imagine if someone applied all of the CIS to all of their databases; This would be a large task and in this case as a simple score they could claim 100% or high compliance against CIS and think that they are secure BUT imagine also that passwords are found or guessable or all data is granted SELECT, INSERT, UPDATE and DELETE to PUBLIC on all tables. This means that CIS does not protect the data. Actual data security and design measures are needed. Yes, the databases are hardened BUT the design of the database and application and data model are weak and the data can easily be compromised and hardening of the core database does not protect it.

There are multiple layers that we must consider when securing any Oracle database:

  • Patching We must apply the security patches released by Oracle BUT their application in general does not affect the security of data itself

  • Hardening This is the revokes, defaults etc from documents such as CIS or Stig or NIST. These are useful to harden the base Oracle software BUT they do not in general secure actual data

  • Data Security

    • Access Controls We must design security to allow access to the database to only those authorised and only when needed

    • User Security Each user must have least privileges only and suitable password management and controls

    • Data Access Controls All data must be designed and code and data separated in separate schemas and permissions created between data and code and users and roles

    • Context Based Security The use of context based security can be added to allow more fine grained control to access to data, users and permissions. This can be by using Oracle technologies such as Database Vault or VPD or OLS etc or can be custom coded with triggers, code and more

    • Audit Trails Each layer of the database should also have suitable audit trails designed and enabled to allow use of the database to be properly monitored




Of course other layers should also be considered from a security perspective such as the underlying Operating System and networking also also if necessary application layers

The security of data in Oracle databases is also affected by the number of databases - i.e. if you have 1,000 databases and 200 issues to secure in each that is 200,000 items to secure across the estate. This is excessive. The available budget to secure all data and the time available and number of people available to work on it must also be considered.

Inevitably this means that we need a security policy / design that is layered and is achievable across the estate.

One other factor to take into account is existing processes and working practices. Even if we change the security at a hardening level or re-design the actual data security if staff/users all share the schema password or SYS password that security work is useless.

We need an all-encompassing security design for data in an Oracle database including patching, hardening, data security, other layers and also process.

What is really needed at a high level is a risk assessment to create a valid list of all possible threats to the database and a valid list of possible vulnerabilities in the database and finally a list of counter-measures that can be used to mitigate the threats via exploiting the located vulnerabilities.

Join me soon for part 2 where I will discuss the actual audit process itself

#oracleace #sym_42 #oracle #database #security #audit #hardening #patching #vulnerability

Happy 21st Birthday to PeteFinnigan.com Limited

My company PeteFinnigan.com Limited is 21 years old today!!

It seems that time has gone so fast. When I started the company my oldest son was a baby and now he is almost 22 years old and works here in our offices doing marketing.

I wanted to focus on helping people secure data in their Oracle databases. I think I have achieved this goal very successfully. We (and I) help people in many ways secure data in their Oracle databases and sometimes other databases. We specialise in securing data in Oracle databases but the ideas and techniques and knowledge we use also transcends other databases.

We do:

  • Oracle Security Audits :We do a detailed review of customers Oracle databases and present the best cost effective strategy for them to secure their data

  • Consult in all areas of Oracle security :We have consulted in so many areas of securing Oracle over the years and still do. Anything that relates to Oracle security we have helped with including audit trail designs, encryption in the database, use of HSM, Oracle key Vault, Database Vault, VPD, OLS, Masking and many many more...

  • Specialist consulting; part of your team :We also are the Oracle security specialist in some companies teams. We work on a call off basis so that you can include us as needed in your projects and we bill to the minute. We work with a small number of companies doing this now and we keep it small to be able to fully support the clients. Talk to us if you would like a very cost effective way to have an Oracle security expert as part of your team when needed

  • Securing PL/SQL : We do PL/SQL security code reviews and also help customer protect their PL/SQL with obfuscation

  • Development consulting :We help companies in the development of software in the area of Oracle security with consulting and sometimes development help

  • We have multiple software products :

    • PFCLScan :Scan your database for security issues and vulnerabilities

    • PFCLCode :Review your PL/SQL code for security flaws

    • PFCLObfuscate :Protect your PL/SQL

    • PFCLForensics :Manage a database data breach, perform live response and perform forensic analysis

    • PFCLCookie :Assess a website for cookies used



  • Oracle Security Training :We have over ten days of expert training in all areas of securing data in an Oracle database

  • Blogging, Speaking and presenting :We like to give away expertise for free via blogging, presenting, our website and free scripts and tools



What about the next 21 years of helping people secure data in an Oracle database (or other database)?

The one thing I can say about the last 21 years is that when I started there was literally no one else doing what I did which was specialising in deep detailed help and advice to secure data in Oracle. There was little to no evidence of much Oracle security going on. Security patches had not long started at that point; there were limited hardening advice and most people did not do a deep job on designing and securing databases. I remember cold calling companies back in 2023 and being able to speak to the right person and they were in the most part interested in what I had to say and offer BUT there was no budget to secure Oracle databases; the budgets went on network security and desktop security.

Most databases back then I did get to see had no security and most were the reverse of secure; i.e. everyone used SYS and SYSTEM and schemas, passwords not protected or changed in more than 10 years, no schema level security design, no hardening and ....

There was also often a kick back against security of Oracle often for fear it would break the running system and often because a lot of people didn't want to give away their elevated access. Some didn't want me to see and report on the bad practices as they knew deep down they were bad practice.

One thing back then was there were very few specialists in securing Oracle and amazingly after 21 years there are still not many out there. Why is this?, I have taught a lot through training but I guess security of Oracle is still regarded as the last task to be done in ten minutes before a new database / application goes live? so we are not needed?

I still see databases now that look like they did 21 years ago BUT the attitude and willingness to secure and learn has changed drastically in customers

The world has changed in the last 21 years; much more data theft and identity theft. Data in databases has become the new target; the new gold rush!!


#oracleace #sym_42 #oracle #database #security #databreach #forensics #plsql #securecode #obfuscate

Securing APEX

I have liked APEX for many years and been involved in auditing and securing Oracle databases that include APEX for many years.

What surprises me sometimes is that those deploying and developing an APEX application treat it like a box and do not consider that APEX is written in PL/SQL, uses PL/SQL and is deployed to an Oracle database. Its like they believe that the database security is someone else's problem or they didn't realise that its in a database - with the data!! Sometimes, the Oracle database is a side issue for them; it's not considered in terms of security and the only factor for the customer is that an Oracle database is needed to run APEX so its provided and not secured in any meaningful way

I wanted to do a post now to highlight at a high level the areas that should be considered for securing an APEX application and database when using and deploying APEX in your database. Lets list and discuss briefly:

  • Web Based Security: Your APEX application may use JavaScript to enhance its features and operation but Javascript can be the target of attackers through input manipulation for attacks such as Cross Site Scripting (XSS) and more. This point is not just limited to JS but to any web level technology that can be used from APEX

  • Webserver and network security: The webserver is likely to be ORDS and it provides the web content to the end users. The security of ORDS must be considered and the network design of the database, ORDS and access must be secured

  • APEX Security: The APEX application itself must be secured at the APEX level. This includes user security, profiles, passwords, instance level security, deployment security, APEX database object security, workspace security and more

  • APEX Application security: APEX provides security at the application level and allows security of users, authorisation, authentication and page level security. The application in APEX is written as snippets of PL/SQL that are stored in the APEX repository and these also must be written with security in mind and secure web and database coding techniques used

  • Supporting PL/SQL: Often the bulk of the APEX application will be written in separate PL/SQL packages called from a thin veneer of PL/SQL in the APEX repository. These packages must be also secured and designed with security in mind. The schema that owns them must be limited in rights and access to he rest of the Oracle database. The PL/SQL must be written with secure coding techniques in mind. This includes avoiding SQL Injection but also managing access to resources and features securely from the applications PL/SQL

  • Schema design and security: The schema that owns the PL/SQL of the application must also be designed with security in mind. The data should be held in one schema and application code (PL/SQL) in another (at least) to allow grants to be made between code and data and not a free for all. Least privilege of the schemas must be observed

  • Database Level Security: Database level security is a complex topic of course and I have covered the basic areas here many times. At a high level we must patch and harden. These alone will not secure data. We must also then secure the data within the database. This is the biggest part of the task of securing data in an Oracle database. This part includes user access controls, user level privileges and data level privileges.

  • Context Based Security: An additional layer that can be applied is to use context based security such as Database Vault, Virtual Private Database, TSDP and more. The normal rights at the database level are granular but Oracle provides extra (often cost based) features that allow more controlled and fine grained security to be applied. We can also create context based security of our own using PL/SQL code, triggers and views. Context based security can also be used at the APEX level as well as the database level

  • Audit Trails: There must be well designed and useful audit trails implemented at the database and also at the PL/SQL level and APEX levels so that any potential misuse of the application or data can be captured to make any breach analysis or forensic analysis easier

  • OS Security: The operating system that hosts the database must also be secured. There are many layers that are similar to the database and APEX. We must secure defaults, hardening, users and privileges, audit trails and more

  • Admin and User Access: We also must consider how users and particularly types of admin users (sys admins, DBA, support etc) access the OS, the Oracle database and APEX. We must ensure that data is protected through these types of access and that the access is audited



Attacks are many and varied from the web level to abusing APEX itself or abusing the PL/SQL or database or even OS level. Securing an APEX application is multi-layered and we must consider all layers for security when we deploy an APEX application from OS to web.

In general we can take the same approach with each layer:

  • Review existing security policies

  • Review the actual systems (Database, OS, APEX, PL/SQL)

  • Understand the security requirements and design a new policy, update the existing policy, create a fixing strategy

  • Fix and secure all systems

  • Perform continuous review of all systems (Database, OS, APEX, PL/SQL...)




#oracleace #sym_42 #plsql #apex #oracle #database #security