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.
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
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';
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)!!
Simply connect PFCLScan to your Oracle database and
it will automatically discover the security issues that could make your Oracle
database vulnerable to attack and to the potential loss of your data.
PFCLObfuscate is the only tool available that can automatically add license controls to your PL/SQL code. PFCLObfuscate
protects your Intellectual Property invested in your PL/SQL database code.
PFCLCode is a tool to allow you to analyse your PL/SQL code for many different types of security issues.
PFCLCode gives you a detailed review and reports and includes a powerful colour syntax highlighting code editor
PFCLForensics is the only tool available to allow you to do a detailed live response of a breached Oracle database and
to then go on and do a detailed forensic analysis of the data gathered.
PFCLATK is a toolkit that allows detailed pre-defined policy driven audit trails for your Oracle
database. The toolkit also provides for a centralised audit trail and centralised activity reporting
We offer a number of web based services such as cookie audits, improving website ranking in search engines,
locating broken links and hosting email and websites
PFCLTraining is a set of expert training classes for you, aimed at teaching how to audit your own Oracle database,
design audit trails, secure code in PL/SQL and secure and lock down your Oracle database.
Choose PFCLServices to add PeteFinnigan.com Ltd to your team for your Oracle Security needs. We are
experts in performing detailed security audits, data security design work and policy creation
PFCLUserRights allows you to create a very detailed view of database users rights. The focus of
the reports is to allow you to decide what privileges and accounts to keep and which to remove.
PFCLSTK is a toolkit application that allows you to provide database security easily
to an existing database. PFCLSTK is a policy driven toolkit of PL/SQL that creates your security
PFCLSFTK is a toolkit that solves the problem of securing third party applications written
in PL/SQL. It does this by creating a thin layer between the application and database and this traps SQL Injection
attempts. This is a static firewall.
