Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

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

[Previous entry: "Be Careful of What You Include In SQL*Net Security Banners"] [Next entry: "PL/SQL, AST, DIANA, Attributes and IDL"]

PL/SQL Machine Code Trace - event 10928

I have had an interest in PL/SQL for more around 25 years. I have always liked this great language as its powerful and simple and a great tool for writing code in the database.

I wrote my very first PL/SQL program back in the mid to late 90s but I wrote it in Pro*C, Oracles C interface to the database that's a little higher level than OCI. I was tasked with writing and creating test data for system testing and I decided to create some rules in meta tables and then from those meta tables generate PL/SQL code that would create and populate the test data. The PL/SQL was not the same for all tests so I decided to generate that as custom PL/SQL using the meta data and a Pro*C program - I am primarily a C programmer and its my favourite language but I also favour PL/SQL as many can see from all of the free tools and scripts I have written and used and let people download for free from this site.

PL/SQL is based on ADA and is a compiled language. That is Oracle runs a compiler and converts the PL/SQL into machine code (There are of course many intermediate steps but we will come back to those later) that is then run in a PL/SQL virtual machine that exists in the database software. The virtual machine is written in C (I assume because we know Oracle is written in C). This PL/SQL virtual machine executes instructions in the same way as the hardware of your PC executes machine language (1s and 0s). This machine language is usually written for most machines in assembler. Oracle calls its code M-CODE or P-CODE. I have seen both names over the years.

This assembler / machine code is saved in the database for each piece of PL/SQL in the IDL% tables and is then loaded and executed when a procedure, function, package needs to execute. The PL/SQL machine language can be seen and the VM execution traced. I wanted to show this low level detail here. I did cover this event back in 2004 on my site and also in my BlackHat talk on unwrapping PL/SQL in 2006.

Lets do a simple test. First connect to the database and give my ORABLOG schema ALTER SESSION so that it can set the 10928 event:

SQL> sho user
USER is "SYSTEM"
SQL>
SQL> grant alter session to orablog;

Grant succeeded.

SQL>

Now connect to ORABLOG and create a very simple piece of code and run it to prove it works:

SQL> connect orablog/orablog@//192.168.56.85:1521/bfora.localdomain
Connected.
SQL>
SQL> create or replace procedure test_null as
2 begin
3 null;
4 end ;
5 /

Procedure created.

SQL>
SQL> begin
2 test_null;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>

Now run the same piece of code but with the 10928 event set first:

SQL> alter session set events '10928 trace name context forever, level 1';

Session altered.

SQL> begin
2 test_null;
3 end;
4 /

PL/SQL procedure successfully completed.

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

Session altered.

SQL>

Now jump over to the Linux box and have a look at the generated trace file:

[oracle@oel1124 trace]$ cat bfora_ora_16459.trc
Trace file /u01/app/oracle/diag/rdbms/bfora/bfora/trace/bfora_ora_16459.trc
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: oel1124.localdomain
Release: 2.6.39-300.26.1.el5uek
Version: #1 SMP Thu Jan 3 18:31:38 PST 2013
Machine: x86_64
Instance name: bfora
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 16459, image: oracle@oel1124.localdomain


*** 2020-04-02 10:42:36.179
*** SESSION ID:(137.99) 2020-04-02 10:42:36.179
*** CLIENT ID:() 2020-04-02 10:42:36.179
*** SERVICE NAME:(bfora.localdomain) 2020-04-02 10:42:36.179
*** MODULE NAME:(SQL*Plus) 2020-04-02 10:42:36.179
*** ACTION NAME:() 2020-04-02 10:42:36.179

Entry #1

00002: ENTER DS[0]+64 <"__anonymous_block",ept=1,sz=24,lvl=0,prm=0>

00008: XCAL 2, 1
Entry #1
[Line 1] procedure test_null as
ORABLOG.TEST_NULL: 00002: ENTER DS[0]+152 <"TEST_NULL"#980980e97e42f8ec #1,ept=1,sz=16,lvl=0,prm=0>
[Line 3] null;
ORABLOG.TEST_NULL: 00008: RET

00014: RET
[oracle@oel1124 trace]$

Its really quite simple but you can see ASSEMBLER instructions such as RET and XCAL. You can also see PL/SQL source mapped to assembler and also non PL/SQL source also mapped to assembler. For instance the call to enter the procedure TEST_NULL is written as "procedure test_null" when in fact the call is just TEST_NULL, the call is shown as the ENTER to the procedure.

Lets connect again to ORABLOG just to get a new trace file in a different session and enter a slightly more complex (not much) piece of code:

SQL> connect orablog/orablog@//192.168.56.85:1521/bfora.localdomain
Connected.
SQL> alter session set events '10928 trace name context forever, level 1';

Session altered.

SQL> declare
2 lv_1 number;
3 begin
4 lv_1:=lv_1+1;
5 end;
6 /

PL/SQL procedure successfully completed.

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

Session altered.

SQL>

Now jump over to the Linux box and spool the trace file:

[oracle@oel1124 trace]$ cat bfora_ora_16892.trc
Trace file /u01/app/oracle/diag/rdbms/bfora/bfora/trace/bfora_ora_16892.trc
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: oel1124.localdomain
Release: 2.6.39-300.26.1.el5uek
Version: #1 SMP Thu Jan 3 18:31:38 PST 2013
Machine: x86_64
Instance name: bfora
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 16892, image: oracle@oel1124.localdomain


*** 2020-04-02 10:49:54.885
*** SESSION ID:(137.101) 2020-04-02 10:49:54.885
*** CLIENT ID:() 2020-04-02 10:49:54.885
*** SERVICE NAME:(bfora.localdomain) 2020-04-02 10:49:54.885
*** MODULE NAME:(SQL*Plus) 2020-04-02 10:49:54.885
*** ACTION NAME:() 2020-04-02 10:49:54.885

Entry #1

00002: ENTER DS[0]+64 <"__anonymous_block",ept=1,sz=16,lvl=0,prm=0>

00008: RET
[oracle@oel1124 trace]$

This is all an anonymous block and the code is not shown for the simple assignment and addition. Interesting.

Now try a similar example but do it in a procedure:

SQL> connect orablog/orablog@//192.168.56.85:1521/bfora.localdomain
Connected.
SQL> create or replace procedure t(lv_1 in number) is
2 lv_2 number;
3 begin
4 lv_2:=lv_1+1;
5 end;
6 /

Procedure created.

SQL>
SQL> alter session set events '10928 trace name context forever, level 1';

Session altered.

SQL> begin
2 t(12);
3 end;
4 /

PL/SQL procedure successfully completed.

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

Session altered.

SQL>

Now jump over and get the trace file:

[oracle@oel1124 trace]$ cat bfora_ora_17220.trc
Trace file /u01/app/oracle/diag/rdbms/bfora/bfora/trace/bfora_ora_17220.trc
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: oel1124.localdomain
Release: 2.6.39-300.26.1.el5uek
Version: #1 SMP Thu Jan 3 18:31:38 PST 2013
Machine: x86_64
Instance name: bfora
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 17220, image: oracle@oel1124.localdomain


*** 2020-04-02 10:53:10.604
*** SESSION ID:(137.103) 2020-04-02 10:53:10.604
*** CLIENT ID:() 2020-04-02 10:53:10.604
*** SERVICE NAME:(bfora.localdomain) 2020-04-02 10:53:10.604
*** MODULE NAME:(SQL*Plus) 2020-04-02 10:53:10.604
*** ACTION NAME:() 2020-04-02 10:53:10.604

Entry #1

00002: ENTER DS[0]+104 <"__anonymous_block",ept=1,sz=32,lvl=0,prm=0>
Static Address Registers
#0000000 HS+0
#0000001 HS+24

00008: MOVA #1, FP+8
00014: XCAL 3, 1
Entry #1
[Line 1] procedure t(lv_1 in number) is
ORABLOG.T: 00002: ENTER DS[0]+176 <"T"#9689ba467a19cd19 #1,ept=1,sz=64,lvl=0,prm=1>
Static Address Registers
#0000002 HS+0
ORABLOG.T: 00008: INFR DS[0]+232
Frame Desc Version = 2
slot# = 1 start offset = 16
# of locals = 1
TC_SSCALARi: #1, FP+16, d=FP+24
[Line 4] lv_2:=lv_1+1;
ORABLOG.T: 00014: ADDN #0, #2, #1
[Line 5] end;
ORABLOG.T: 00022: RET

00020: RET
[oracle@oel1124 trace]$

This shows the code translated to assembler and machine instructions for the PL/SQL virtual machine. It is interesting to see PL/SQL source for instance "[Line 4] lv_2:=lv_1+1;" and see how that translates to assembler instruction ADDN (Add) with three parameters, two sides to the expression and the location of the result, I assume.

Have a play, it is interesting to see how Oracle compiles its code for PL/SQL and how it actually runs in the database PL/SQL VM

Ok, that's it for todays short post WFH (Working From Home)!!