Pete Finnigan's Oracle Security Weblog

In my last blog on Extreme PL/SQL I mentioned pointers in PL/SQL.

PL/SQL does not support pointers or dynamic memory management in the same way that we can write in C code.

In C code we can define a variable as a pointer, allocate some memory and then store a value at the location allocated and pointed to by the pointer. We can then do memory arithmetic or store a different value or free the memory after use so that it can be used elsewhere. Parts of a C program illustrating this are here:

...
int *p;   /* define a pointer */
p=(int*)malloc(sizeof(int)*1); /* allocate one integer and assign to the pointer */
*p=7;  /* assign a value of 7 stored in the memory located allocated */
...

C has syntax to define a variable as a pointer and access the "contents" of that variable or access the address stored in the variable.

I want to do a similar thing with PL/SQL in my CPU virtual machine implementation so that I can store the programs and use the memory in a similar way to a CPU; to do that in PL/SQL I wanted to access that memory using pointers. I want to show you an earlier version of this idea here to show that we can add and use pointers in PL/SQL.

If we consider what C is doing we can see that the same is easily possible in PL/SQL. In C we allocated blocks of memory from the HEAP or the STACK and we use pointers to perform address arithmetic and then use these pointers to store data at the locations "pointed to" by the pointer.

So here is a sample program that uses a HEAP and pointers to access and manage that memory:

declare
    -- -------------------------------------------------------------
    -- types
    -- -------------------------------------------------------------
    type heap_t is table of integer index by binary_integer;
    subtype "* number" is integer;
    -- -------------------------------------------------------------
    -- variables
    -- -------------------------------------------------------------
    ptr "* number";
    val integer;
    "*" heap_t;
    -- -------------------------------------------------------------
    -- functions
    -- -------------------------------------------------------------
    function malloc(s in number) return "* number"
    is
        n binary_integer:=1;
    begin
        if("*".exists(n)) then
            n:="*"(n);
            while("*".exists(n)) loop
               n:="*"(n); 
            end loop;
            return(n+1);
        else
            "*"(n):=3;
            return(n);
        end if;
    end;
    --
    function "^"(p in binary_integer) return integer
    is
    begin
        return("*"(p));
    end;
    --
begin
    -- ---------------------------------------------------------------
    -- Malloc 1 integer on the heap, assign 7 to it, retrieve its 
    -- value from the heap
    -- ---------------------------------------------------------------
    ptr:=malloc(1);
    "*"(ptr):=7;
    val:="^"(ptr);
    dbms_output.put_line('val=['||val||']');    
    -- ---------------------------------------------------------------
    -- Malloc another 1 integer on the heap, assign 9 to it, retrieve 
    -- its value from the heap
    -- ---------------------------------------------------------------
    ptr:=malloc(1);
    "*"(ptr):=9;
    val:="^"(ptr);
    dbms_output.put_line('val=['||val||']');
end;
/

We can see that I use syntax similar to C. First we create a HEAP (an area of memory) . We declare a new subtype to be "* integer" - a pointer to an integer. We create an instance of our HEAP and call it "*" so that we can access it in a similar syntax to C. Our Malloc function manages the HEAP. It keeps a simple linked list to point to the next free location of memory and allocate a pointer to that memory for the caller and pass it back. The "^" function allows us to use similar syntax to C to access the contents of the pointer. We can then use the pointers, HEAP and functions to write pointer based code as we would in C.

The simple demo in the begin/end block shows that we use our pointer "ptr" declared above as type "* integer", i.e. a pointer to an integer. We call malloc() to get one byte of storage and assign that one byte of storage address to "ptr". We can then store the digit "7" at the location pointed to by "ptr" in a similar way as we would in C. Finally we can retrieve the contents of the location pointed at by "ptr" and print it out. Then we repeat for a second location and store 9 at that location.

Of course we "deliberately" made a C programmers error and overwrote the "ptr" with a new location and lost the pointer to the first location.

We also need to implement free() to give back the memory to the HEAP after we finished using it.

The storage is not efficient for single integers as we need two integers for each integer used so that the memory management linked list works.

Is there a security issue if we introduce pointers and memory management to PL/SQL, Not in the same sense as a C program as any heap based overflow would not overflow beyond the PL/SQL implementation of the HEAP. If we implement a virtual CPU and store programs in the PL/SQL HEAP then yes those programs could overflow their own system but not escape back to overflow PL/SQL

The simple demo I have shown here has been improved already a lot by me for use in my compiler/CPU and I will take about it again in a later blog.

The C like syntax is of course not necessary; I used it here to illustrate that we can do C like programming and hence system level programming in PL/SQL. We can just use normal PL/SQL variables BUT it would be harder to see the beauty of pointers

#oracleace #sym_42 #oracle #security #database #plsql #c #securecode #bufferoverflow #interpreter #compiler #heap #virtualmachine #vm #cpu

It has been a while since my last blog post here. I have not abandoned blogging. Over the last year and more I have blogged regularly and this is reflected in my Oracle ACE Pro contributions this last year. I have just been accepted as an ACE Pro for another year.

I have not released many blogs recently BUT I have been writing blogs in the background at the same rate as normal to release in the coming weeks as a series of articles around PL/SQL. I wrote back in 2022 about adding a scripting language to PL/SQL and now I have done this. I have created a simple language based on BASIC initially but it will change to be not BASIC as we go along. I have implemented an interpreter in PL/SQL for this simple version of BASIC and tested simple BASIC programs that work and are parsed and executed in PL/SQL. The next step is to complete the implementation of a VM in PL/SQL to simulate a simple CPU that will execute binaries for an assembly language I have designed. The interpreter will change into a compiler for the simple BASIC, it will output assembler for the machine I have created in the VM and assemble that to binary. Finally the binary is executed in the VM/CPU implemented in PL/SQL.

What do I mean with the title of this blog of Extreme PL/SQL?. Well, doing things with PL/SQL that you would not normally do and trying to write systems level code in PL/SQL. I have more things coming up but we will keep that quiet for now!!, ok, one for you now; PL/SQL does not have pointers in the same way that C does. You cannot create a pointer to a type such as char and then malloc memory for that char and access it using its location (pointer) and then retrieve the value. I have been looking at how we might do this in PL/SQL or simulate it more later.

Why? I have always been interested in PL/SQL and also from a security standpoint; there is always a security angle for me. I have some security ideas and things I want to talk about with PL/SQL and maybe with Apex. These ideas will become clearer soon. I might put forward some papers for conferences around this area of extreme PL/SQL and coding things that you would never do.

Watch out soon for the article series on "Extreme PL/SQL" and also compilers, interpreters, VMs, assemblers all in PL/SQL. I have 26 parts already, almost half of them complete and the others in a state of development with code to demonstrate.

Could we write an OS (Operating System) in PL/SQL? not Linux/Windows level of course but maybe at the level of a Monitor that were used to load and run programs in the old 8 bit days. I am focusing on the language layer as compilers, system programming, interpreters and more have interested me since the beginning of the 90s.

Here is a simple example of the interpreter loading and running a simple program with some trace output

-- -----------------------------------------------------------------------------
-- PFCL_int Interpreter - run examples code in a simple harness
-- -----------------------------------------------------------------------------

declare
    lv_prog varchar2(32767):='PRINT var + 3+(7*66) END';
begin
    -- initialise the interpreter
    pfcl_int.init(true,1);
    -- run the code
    pfcl_int.run(lv_prog);
    --
end;
/
sho err

And the output including trace:

TRACE: Trace Start [25-JUN-2024 08:55:25]
TRACE: [1] gv_source=[PRINT var + 3+(7*66) END]
TRACE: [1] gv_len=[24]
TRACE: [1] gv_posn=[0]
TRACE: [1] Token Type = [KEYWORD  ] : Token is = [PRINT]
TRACE: [1] Token Type = [VARIABLE ] : Token is = [var]
TRACE: [1] Token Type = [DELIMETER] : Token is = [+]
TRACE: [1] Token Type = [NUMBER   ] : Token is = [3]
TRACE: [1] Token Type = [DELIMETER] : Token is = [+]
TRACE: [1] Token Type = [DELIMETER] : Token is = [(]
TRACE: [1] Token Type = [NUMBER   ] : Token is = [7]
TRACE: [1] Token Type = [DELIMETER] : Token is = [*]
TRACE: [1] Token Type = [NUMBER   ] : Token is = [66]
TRACE: [1] Token Type = [DELIMETER] : Token is = [)]
TRACE: [1] Token Type = [KEYWORD  ] : Token is = [END]
TRACE: [1] Symbol Table ==>
TRACE: [1] symbol name=[var],type=[NUMBER],value=[0]
TRACE: [1] Keyword table ==>
TRACE: [1] Keyword name=[REM],action=[]
TRACE: [1] Keyword name=[LET],action=[]
TRACE: [1] Keyword name=[PRINT],action=[]
TRACE: [1] Keyword name=[IF],action=[]
TRACE: [1] Keyword name=[THEN],action=[]
TRACE: [1] Keyword name=[GOTO],action=[]
TRACE: [1] Keyword name=[END],action=[]
465
TRACE: Trace End [25-JUN-2024 08:55:25]
===============================================================
No errors.
SQL>

Watch for the series detailing all of this work with PL/SQL

#oracleace #sym_42 #oracle #database #23c #23ai ##securecode #plsql #extreme #interpreter #compiler

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

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

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

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 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