Pete Finnigan's Oracle Security Weblog

I like PL/SQL and I am always playing around with it or writing tools for use in security audits in PL/SQL or trying to do things that are not normal with PL/SQL such as writing an interpreter. One thing I found in some testing of PL/SQL recently is that the shorter the PL/SQL the faster it is parsed and compiled. This is common sense really; it should take 'x' seconds to compile a PL/SQL of 'y' length but if the length is 20% shorter it stands to reason it should parse and compile faster; because its shorter. Less code to read in and process, parse, less DIANA, less AST, less P-Code.

I was also thinking about the C language ternary operator where there is no IF statement but a short cut as follows:

(x==y)?"true":"false";

This is a single line IF/ELSE; without the IF/ELSE keywords. The condition is tested and if true the first part after the "?" is returned as the result of the statement or if the condition is false then the part after the ":"

Can we do similar in PL, no ternary operator in PL/SQL BUT we can as it happens use the condition in other places than an IF statement. So if I have a simple function to test if a character is ALPHA i.e. a-z or A-Z then it looks like this:

    function IsAlpha(pv_c in varchar2) return boolean is
        lv_ret boolean;
    begin    
        if(((ascii(pv_c)>=65) and (ascii(pv_c)<=90)) 
            or ((ascii(pv_c)>=97) and (ascii(pv_c)<=122))) then
            lv_ret:=true;
        else
            lv_ret:=false;
        end if;       
        return(lv_ret);
    exception
        when others then
            dbms_output.put_line('Error');
    end;  

As you can see the simple function uses the ascii function to test the passed in character and then return TRUE or FALSE. I realised that we do not need the IF/ELSE/END IF as we can simply put the test condition in the return() directly and the function will then return TRUE or FALSE:

    function IsAlpha2(pv_c in varchar2) return boolean is
    begin    
        return(((ascii(pv_c)>=65) and (ascii(pv_c)<=90)) 
            or ((ascii(pv_c)>=97) and (ascii(pv_c)<=122)));
    exception
        when others then
            dbms_output.put_line('Error');
    end;

This is now a much shorter version of the function producing the same result BUT without the IF/ELSE/END IF now. Great, we reduced the code, increased complexity slightly but made the code succinct.

Obviously we cannot do this change everywhere in all PL/SQL code. It works because the IF/ELSE/END IF returns just true or false. But it will also work not just in return but also in other places that take a condition.

The whole thing is here to prove it works:

set serveroutput on

declare
    lv_ret boolean;
    --
    function IsAlpha(pv_c in varchar2) return boolean is
        lv_ret boolean;
    begin    
        if(((ascii(pv_c)>=65) and (ascii(pv_c)<=90)) 
            or ((ascii(pv_c)>=97) and (ascii(pv_c)<=122))) then
            lv_ret:=true;
        else
            lv_ret:=false;
        end if;       
        return(lv_ret);
    exception
        when others then
            dbms_output.put_line('Error');
    end;  
    --
    function IsAlpha2(pv_c in varchar2) return boolean is
    begin    
        return(((ascii(pv_c)>=65) and (ascii(pv_c)<=90)) 
            or ((ascii(pv_c)>=97) and (ascii(pv_c)<=122)));
    exception
        when others then
            dbms_output.put_line('Error');
    end;
    --
begin
    lv_ret:=IsAlpha('A');
    dbms_output.put_line(sys.diutil.bool_to_int(lv_ret));
    lv_ret:=IsAlpha('0');
    dbms_output.put_line(sys.diutil.bool_to_int(lv_ret));
    lv_ret:=IsAlpha2('A');
    dbms_output.put_line(sys.diutil.bool_to_int(lv_ret));
    lv_ret:=IsAlpha2('0');
    dbms_output.put_line(sys.diutil.bool_to_int(lv_ret));    
end;
/

And running gives:

SQL> @if
1
0
1
0

PL/SQL procedure successfully completed.

SQL>

The use of diutil.bool_to_int() is just to show the result. In the original use of this function it was embedded in another call so the IF/ELSE/END IF is truly removed

#oracleace #sym42 #plsql #securecode #oracle #security #extremeplsql #23ai #23c

Do you develop PL/SQL? Is your Oracle PL/SQL protected?

My name is Pete Finnigan and in the next few minutes I will show you how you can protect you PL/SQL investment from theft. We can:

• Stop people stealing your ideas


• Make sure you control your PL/SQL


• Secure your PL/SQL code


• Simple to use

Ask to Purchase a License or see a live demo first

Here is the Perfect Solution to Stop Theft of your PL/SQL

Did you know that if you do not protect your PL/SQL then anyone with access to the database where your code is deployed can steal it.

If you don't protect your PL/SQL before it is deployed then someone can read your code in clear text and understand it and rewrite it as their own. You think your application is fantastic and customers will buy it but if you don't protect it someone will take it for free and use it or access your source code and copy the ideas within it.

Which means that your code could be stolen and deployed to further databases and used without you receiving a penny in license fees that you should be due.

Luckily for you we have an easy to use solution that helps prevent anyone from learning how your PL/SQL application code works and understanding your ideas and prevents the theft of your code from a database where it is deployed. Our solution can also easily add protection to your application to prevent its use even if its copied to another database.

• Obfuscate and compact your PL/SQL code which means your customers cannot understand and steal your intellectual property


• Detailed configuration and customization which means that you can set up and re-use the same rules on a different project


• Control the obfuscation process which means you decide what is protected and what is not


• Add simple date and time based licensing which means you control when your application runs in the customers system


• Add run time controls automatically to your application which means you can activate and control which database it works in


• Command line operation which means you can integrate PFCLObfuscate into your existing build cycle

Using PFCLObfuscate to protect your PL/SQL code

Using PFCLObfuscate is easy. After installing the product you can see that the software is highly configurable at the product level and also down to the individual source code level and anywhere in between.

The above picture shows the main configuration settings for the tool. For each piece of PL/SQL at a schema level or even down to individual pieces of PL/SQL we can easily provide settings. All of the settings are stored at the schema level in files BUT we can also save the configuration per schema and open/close saves configs easily if we need separate settings at a file level or group of files within a schema. The image below shows some of the PFCLObfuscate settings that can be changed

Once we have all the settings configured as we need we can connect to a database and choose a schema to download PL/SQL source code from:

The next step is easy; simply click "refresh" from the file menu to get all the source code for the chosen schema. This is shown next:

When we have a list of PL/SQL packages, headers, procedures and functions then we can simply check the box next to each piece of PL/SQL that we want to obfuscate. This is shown next:

We can display the original clear text as well as the obfuscated text:

The product is much more though. We can easily inject code at any point we choose in the clear text PL/SQL. This means that it is easy to add license type protection to your PL/SQL. This means you can ship PL/SQL to customers for instance where it has a time limit - e.g. it works for 30 days and stops or we can inject locks into your PL/SQL so that you can for instance limit which database the protected code will work in. We can also use the same functionality to add better string obfuscation or indeed anything that you need. The main configuration screen is here:

As you can see we use Lua files to inject code into your PL/SQL. These scripts write PL/SQL that is then automatically added to the obfuscate stream and is obfuscated with the rest of your code.

Not everything in your PL/SQL can be obfuscated. Imagine that you have calls to a specific package procedure such as schema.package.procedure(a,b). If we obfuscate the call then it cannot find it as the original package is not obfuscated. We have two options here; we can either omit this call from the obfuscation by adding it to the omit files OR we can also obfuscate everything else. We have some customers who also obfuscate all table definitions and triggers and more with PFCLObfuscate.

Don't forget we can also use the product completely from the command line and the whole process to protect all of your PL/SQL can be automated and be added into your build processes.

Detailed documentation is available and can be used as a reference when working with the product and we also have email based support where we will answer any questions.

Pete Finnigan is the designer of this software and he has more than 21 years real world experience helping customers secure data in Oracle databases. Pete is an Oracle ACE, a member of the OakTable and also a member of Symposium 42 and is a published author multiple times on the subject of securing data in Oracle databases.

License PFCLObfuscate to easily protect your own PL/SQL source code. A download of the software is built for you and is available as soon as payment is received. The Pro license is £1,095 GBP (+ Taxes if applicable) to install and use to protect all of your PL/SQL. To arrange a purchase Email Sales Now

Buy a Pro license in the next 30 days from this post date and get 25% off our one day live on-line "secure Coding in PL/SQL" class taught by Pete Finnigan. You can choose a date from our on-line course agenda. To arrange a purchase Email Sales Now

#oracleace #sym_42 #oracle #plsql #protection #obfuscation #license #protect #sourcecode #database

I talked at a high level a few weeks ago about Extreme PL/SQL and gave a brief look at an interpreter I have been creating for a simple language based on BASIC.

I have been keeping notes in a Word document currently running over 100 pages and over 35 sections/chapters/articles. I plan to release each of those chapters as separate blogs in a blog series. I have been writing all of these notes as I develop the language and the interpreter and of course the design and tests and content changes. So, i am unsure at this stage whether to release all the articles "as-is" or update them to reflect the changes that have occurred as we progressed.

I will decide soon!

The language is fairly simple and have these features:

• Define any number of numeric variables such as "var" or "x" or...


• Expressions include "+, -, /, *", variables brackets


• KEYWORDS such as IF, THEN, FI, LET, PRINT, GOTO, REM and END

The language is simple and does not include loops or ELSE in the IF statement or procedures - YET!

We can implement all of the above with LET, GOTO and IF anyway. Writing loops, ELSE or procedures with GOTO, IF and LET makes the code more complex and hard to write and understand but can be done.

I wanted to get a simple version of a language working first and then we can extend it and add more language constructs and features.

The original plan was to write a VM for a CPU in PL/SQL and indeed I have written that already in anticipation but I have not written an assembler yet to convert assembly language to binary instructions. This binary stream would then be executed in the VM of the CPU. Finally the interpreter would be converted to a compiler and would emit assembly language instructions for the VM. The tool chain would be BASIC => compiler => assembly language => assemble => binary => execute the binary in the VM CPU.

Currently the simple BASIC language is interpreted and having looked at the speed, I suspect the binary would not run massively faster than the interpreter. The VM is a CPU written in PL/SQL and the interpreter executes the simple BASIC. We compare at run time executing BASIC or binary in a PL/SQL program. There is additional complexity of course in compiling the BASIC to assembler and then assembling the assembly language and running it. If the goal is to run a program then probably there is not a massive difference in speed. I will explain a lot more in the detailed write up. One option is to make the BASIC execute faster by reducing its size so it parses and executes faster. We will explore that also in the detailed write up.

The language is not BASIC and is a simple implementation of some of the original BASIC from the 1960s. It will change as I add features to it so I am going to call it PFCLScript.

A traditional starter program is "hello World". Here it is in PFCLScript:

declare
    lv_prog varchar2(32767):=q'[
                            PRINT "Hello, World!!"
                            ]';
begin
    pfcl_int.init(true,1);
    pfcl_int.run(lv_prog);
end;
/

And here it is running:

SQL> @compiler
Hello, World!!

Start Time       : 17-JUL-24 08.14.09.484629 AM
End Time         : 17-JUL-24 08.14.09.562677 AM
Elapsed Seconds : +000000 00:00:00.078048000
SQL>

OK, lets try another favourite for testing new languages or learning to program in a language; the Fibonacci sequence where the Fibonacci number is less than 20:

declare
    lv_prog varchar2(32767):=q'[ 
                            LET m=20
                            LET x=1
                            LET y=1
                            :30 IF x>m THEN GOTO :20 FI
                            PRINT x
                            LET x=x+y
                            IF y>m THEN GOTO :20 FI
                            PRINT y
                            LET y=x+y
                            GOTO :30
                            :20 END
                            ]';  
begin
    --
    pfcl_int.init(true,1);
    --
    pfcl_int.run(lv_prog);
    --
end;
/

And running shows:

SQL> @compiler
1
1
2
3
5
8
13

Start Time       : 17-JUL-24 10.55.13.109739 AM
End Time         : 17-JUL-24 10.55.16.933322 AM
Elapsed Seconds : +000000 00:00:03.823583000
SQL>

That is the correct answer.

I will decide whether to update the notes I have already created for many articles to reflect the later versions of the interpreter or release them as they were written. I am also going to continue to add some features to the language and the interpreter and also decide whether to convert to a compiler or not.

Watch out for more and commend via social media and also please consider following me on my social media accounts.


#oracleace #sym_42 #oracle #database #23c #23ai #securecode #plsql #extreme #interpreter #compiler #assembler #vm #cpu #pfclscript

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