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.

Oracle Security Training Presentations

Why not make good use of your stay at home time and get excellent very cost effective training in all areas of securing data in your Oracle databases.

I have just made live a new set of training dates on our website for our Oracle Security Training
that will run in blocks including a teaching of each class by me on USA EST time zone and also on a UK/EU time zone. All of the training is written and taught by Pete Finnigan and is fast paced and very interesting including many demos. All of the new trainings are held on line and suit the current covid restrictions as you don't need to leave your home or desk.

The big advantage of us keeping class sizes small is that you get access to Pete Finnigan for the whole of the trainings and you are free to ask as many questions as you wish and to get the wisdom and experience of Pete on tap as well as devour the actual training materials

The class sizes are kept small to allow everyone to join in.

The Instructor



The instructor for all of the training is Pete Finnigan. Pete has 20 years experience in securing Oracle databases and a vast knowledge and experience in helping customers and conference and training attendees to secure data held in Oracle databases.

Available Classes



The classes are:



We have 6 classes and each are one day classes except the 2 day class "How to perform a security audit of an Oracle database". We also have set up the dates so that you can combine the how to perform an audit 2 day class with the one day hardening and securing Oracle class to create a 3 day class.

The classes are £440 GBP for the one day class and £850 GBP for the two day class (any tax, VAT etc will be added where we must)

Dates Available



We have lots of dates available and to choose from. Please see our Public Training Class Dates page for details of all of the classes to be taught by Pete Finnigan. We have classes to be taught on UK/EU time zone and also USA/EST time zones.

What Do You get?



With every class you get to interact with Pete Finnigan a well known expert in securing Oracle and the data in Oracle databases. He is always happy to interact, answer questions and discuss anything that is relevant to the materials of the class. We do keep to a schedule though so also be sure that we will keep to it.

Each class also comes with a copy of the slides and also a free download of all of the tools and scripts and demos that Pete will explain and work through. These tools and scripts are not toys written for a class and are tools that we use in commercial engagements so the classes are worth it for just the tools alone; so the teaching is a bonus and great value.

Booking



To book your place on any class simply send an email to info@petefinnigan.com and we will do the rest. Its simple, write today and book your place!!

Happy 18th Birthday PeteFinnigan.com Limited

It has been an eventful year last year and 2021 started a bit strange due to lockdown. Last Friday our company PeteFinnigan.com Limited came of age; it was 18 years old. Wow, it has been a long and interesting journey for our company but an interesting and exciting journey!

This corona virus has been with us around one year already. The first cases in the UK (at the time) were here in York where I live. Two people were taken away in an ambulance from a hotel where the staff of the ambulance had full haz mat suits on. At least that part is not as precautionary now with ambulances seen driving around and no sign of full haz mat suits anymore; just staff in more normal PPE.

We were plunged into a lock down in the middle of March 2020 here in the UK and told to work from home; so we did for a few months and then gradually came back to the office after the lock down ended. We then had another short lock down in November. Then came January this year 2021 and the numbers of cases and deaths and people in hospital here in the UK went through the roof. At the start of this current lock down we started to work from home and then I caught covid myself from a family member. The others in my family recovered quickly but I had it for weeks and was laid up unable to do anything for weeks. I managed to get most of the symptoms that are listed by the NHS except severe difficultly breathing; which is the one symptom you don't want to get; in fact only now very recently do I feel much much better. I spoke to someone today who saw me today who remarked that when they saw me 2-3 weeks ago I looked terrible and looked like I was on my way to the morgue as I was not just pale but my skin was grey. Not good to know that, but at least I am much better now.

So how has the company been affected by Covid? well normally we would be travelling all over the world delivering consulting engagements and I would be teaching my Oracle Security training classes. Well, no travel but we have been providing training classes online for many customers on public trainings and also private events and we have been doing consulting engagements online also. We have done security audits, PL/SQL code reviews and provided expert consulting in many areas of Oracle security. I personally miss the face to face contact we get when teaching and also when delivering consulting but for training it is just as good online for the students; our classes are still live and me teaching, students can still see everything in terms of slides and my demos and my screen and of course students can interact as much as they need to and ask questions.

The progress with products was hampered slightly as we planned to release another new product PFCLForensics in January but due to my covid infection and also the lock down that has now been delayed but we will release it soon. This is a tool that can be used to perform immediate "live incident response" on a suspected (or known to be) breached Oracle database and also to then provide "Forensics analysis" within the tool. The forensics is done with a timeline that can be viewed in a number of ways. This allows the data that you collect that you identify as part of the breach to be ordered into this timeline.

Keep an eye out for more news on PFCLForensics coming soon.

During the last 2020 year we have released a number of new interim point release versions of PFCLScan; adding more facilities to it and checks and tools.

More importantly we have also release a completely new product and revamped completely another product.

The new product is PFCLCode a static source code analyser. This tool analyses your PL/SQL code for vulnerabilities but it also goes further than other source code analysers as we also look at the design and deployment of the code into the database; so also looking at things like hierarchy, design choices of the schema and individual pieces of PL/SQL as well as privileges. We, of course also analyse the PL/SQL code itself for issues.

The revamped product is PFCLObfuscate which is our product to protect your PL/SQL that has been deployed to a database. The product has been available for a while as a command line only tool. We have now added a complete GUI interface to it and released it during last year. This makes the tool easier to use as it also has productivity enhancements. This is a great tool and easy to use. Of course the command line operation is also still there if you require that to integrate the tool into any build system.

One of the major design decisions that I made last year whilst we were developing PFCLCode, PFCLObfuscate and PFCLForensics was to leverage the core functionality of PFCLScan.

The core scanner was designed as a framework that can connect to a database, server or whatever and run projects with policies comprised of checks written in many different languages including SQL, PL/SQL, Lua, Unix Shell, DOS scripts, sftp, even questions based and as we can run DOS commands you can actually run checks in any language you wish if you have the language interpreter on your PC. This power is enhanced in that the core engines of PFCLScan can actually be run as checks themselves. This creates powerful recursive checks where a project/policy/check can actually run the scanner itself as a check; ad infinitum - this allows automation and also structured scanning. Similarly with the reporting tool that we have created that sports its own very simple BUT powerful language. This framework allowed us to utilise the scanner within itself; so that development moves to "user" space and is not confined to the core development of the GUI. This lead to plugins that are actually just project/policy/checks in the scanner. You can create a project to do something useful either on the host PC or in a database or... and then convert this project to a plugin. This can then be run from the plugins screen as a tool.

We extended the plugin functionality to allow plugins to be executed in the GUI interface at certain designed "hook points". This means that the interface can be extended easily simply by adding "hook points" - simple code and then a plugin can be created in "user space" and assigned to the "hook point" in the plugins screen.

This extension and reuse of features along with some other changes lead us to design all of our new software products INTO PFCLScan. We create a new GUI screen for each product such as PFCLCode and this can be accessed via the "tools" menu in PFCLScan if you purchases PFCLScan + PFCLCode or if you purchase just PFCLCode then a launcher is presented when the tool starts up and you choose PFCLCode and only see the PFCLCode interface. Although each product such as PFCLCode has its own GUI screen / interface it uses core features of PFCLScan (trace, logging, users, login, plugins of course and more...). This means that we have one source code tree for all products and one build system and we can develop new products much faster as quite a bit is creating plugins and importantly using existing functionality.

All of this means that we can easily sell bundles of products together, add new products to sell in the same design model BUT we reuse the core functionality and not redevelop similar things over and over.

It gets better; well for us anyway. We also created the customer build system and activation system inside of PFCLScan using plugins. So PFCLScan is used to build PFCLScan.

I will expand on the products in a later post and talk more about what they can do to help you secure your Oracle databases and data.

We are also working on adding more training dates to our public training calendar. More dates will be added in the next few days, so please watch out for those. We have many training classes and all focus on how to secure an Oracle database. All of our classes are described on our "Oracle security training course" page. Have a look for details and a 2 page flyer for each class

We are also in discussions with a number of companies to partner with us to resell our software products or to partner with offering our consulting or trainings. We are always open to taking on more partners and resellers. The advantage for you is that you can offer training and services that you do not have the skills in house to offer and also resell ready made security products to help people secure data in their Oracle databases. Please contact me to discuss if you would like to partner with us.

If you would like more information on any of our services please contact me now and I will be happy to help.

If you would like to book a place on a training class or to request a private class then please contact me and I will be happy to help.

If you would like to purchase a license for any of our software products or request a demo, then please contact me and I will be happy to help with that

TCPS Connection With an Oracle Instant Client

All of our products (PFCLScan, PFCLCode, PFCLObfuscate and http://www.petefinnigan.com/products/pfclforensics.htm) can use an Oracle instant client to connect to the target database(s) or even a full client.

It is of course simpler to use an instant client if you don't have a full client on your PC as it is just a case of unzipping the instant client and copying the files and sub-directories (usually vc8, vc9 or vc14) to the place you want to use it.

One issue with an instant client is that until 19c it doesn't natively support TCPS and wallets with the EZCONNECT syntax. When you use 19c there is additional syntax to support this (I will do an additional blog post for this soon).

This blog post is based on making a TCPS connection to the database initially with SQL*Plus to prove the connection works and then with PFCLScan to show that we can connect in PFCLScan with a TNS alias and an instant client but also we can make a TCPS connection to the database/listener.

The following details this process and shows how PFCLScan works with this setup

Introduction


This is a short description to cover connecting from PFCLScan and also SQL*Plus from a client PC to a database that has SSL encryption setup.

This is also a test with an instant client and not a full Oracle client

This will also work with cloud setups and an autonomous database for instance. In this test case we instead made an SSL connection to a 12.2 SE Oracle database with SSL encryption and a self signed certificate and the same certificate in the client/server wallets for ease of setup. But this set up allows TCPS connection to the database and proves that the TCPS connection works.

Server Setup


I am not going to describe the server setup in detail but can provide copies of my sqlnet.ora, listener.ora and orapki commands if needed.

I set up a TCPS connection setup in my 12.2 single instance SE2 database. This uses port 2484 for the TCPS and has a self signed certificate in a wallet on the server. This wallet is then referenced in my sqlnet.ora and listener.ora on the server.

As I am using an instant client on the PC I do not have orapki to set up the client wallet as the instant client does not ship this. So, I created the wallet and installed the certificate on the server and then copied the wallet files to the PC for the client to use.

Client Setup


I created a directory on my PC and copied the 11.2.0.4 32bit instant client into this directory. I also copied the sub-directories vc8 and vc9 into the same directory:

Oracle instant client copied to PC


The above is the basic instant client for 11.2.0.4 and also the sqlplus add on. They are both zip files so just copy the contents to the directory.

I copied the wallet files from the server and copied them into my client 11.2.0.4 instant client directory these are shown highlighted here:

Oracle wallets copied from the server



I then set my TNS_ADMIN environment variable to point to my 11.2.0,4 client folder: c:\_aa\oracle_client:

TNS_ADMIN environment variable



I added the 11.2.0.4 instant client directory to my PATH:

Set The PATH environment variable



This can then be seen in my command prompt:

Show The PATH environment variable



I now created my sqlnet.ora and tnsnames.ora and copied these also to the same 11.2.0.4 client folder these are shown highlighted:

sqlnet.ora and tnsnames.ora setup



My tnsnames.ora has the following content:

ORCL=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCPS)(HOST=192.168.1.95)(PORT=2484))
(CONNECT_DATA=
(SERVICE_NAME=orcl.localdomain)))
My sqlnet.ora has the following content:
SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS)
SSL_CLIENT_AUTHENTICATION=FALSE

SSL_VERSION=1.0
WALLET_LOCATION=
(SOURCE=
(METHOD=file)
(METHOD_DATA=(DIRECTORY=C:\_aa\Oracle_client)))

TRACE_LEVEL_CLIENT = support
TRACE_FILE_CLIENT = client.log
TRACE_DIRECTORY_CLIENT = c:\_aa\Oracle_client
DIAG_ADR_ENABLED = OFF
ADR_BASE = OFF

Testing the Connection
I can now use SQL*Plus to connect to the database. First I start a CMD prompt and CD to the scanner BIN directory (the location of oscan.exe):

C:\>cd _aa\PB\bin

C:\_aa\PB\bin>dir oscan.exe
Volume in drive C is OS
Volume Serial Number is C67F-6487

Directory of C:\_aa\PB\bin

04/11/2020 11:26 1,496,064 oscan.exe
1 File(s) 1,496,064 bytes
0 Dir(s) 76,606,251,008 bytes free

C:\_aa\PB\bin>

Now I run the pfclset.bat script. This moves me to the DATA directory:

C:\_aa\PB\bin>pfclset
pfclset.bat Release 1.0 Copyright 2015 PeteFinnigan.com Limited

C:\_aa\PD>

Now I can connect to my database using SQL*Plus and the TCPS connection to prove the connection works for the client:

C:\_aa\PD>sqlplus system/oracle1@ORCL

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 6 10:21:00 2020

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


Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

SQL>

And confirm its TCPS

SQL> select sys_context('USERENV','network_protocol') from dual;

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps

SQL>

Now I can set up a project in PFCLScan and set the connection details to use my tnsnames.ora alias ORCL:

connection details PFCLScan



I am able to connect from an 11.2.0.4 instant client using tnsnames.ora alias and making a TCPS connection to my 12.2 database
Now I can run the scan:

completed scan with PFCLScan



This works!

PL/SQL, AST, DIANA, Attributes and IDL

I have been wanting to write a detailed post about this subject for a very long time and indeed I have had some notes and screen dumps for some of this for more than 15 years for some parts of this post. I have re-created some elements below in a current Oracle database now to write this; this is a reasonably big post and has taken a couple of evenings work last week and one this week to write it. It is detailed but that's fine. I have been trying to write a post a day during this lockdown but hopefully you will benefit from a bigger post but its taken a few evenings (when I write these posts) to complete and has meant no post last Friday.

I wanted to expand a little on my unwrapping talk from 2006 and also talk about PL/SQL internals BUT more in the direction of the internals and not about unwrapping. I have always been interested in this topic but more so now in some details as i am in the middle of some work currently with PFCLCode our PL/SQL static code analyser for security issues. We have been developing that tool for some time and a new GUI is being added into PFCLScan for release soon. This will allow customers to choose all PL/SQL in the database, a schema of PL/SQL or a single procedure or function or anywhere in between these limits and perform detailed security analysis of that PL/SQL code. We have over 200 check types already built into the engine that does the analysis and more are being added. The GUI allows high level views of all the PL/SQL code or detailed analysis of a single piece of code. We have colour syntax highlighted editor view of each and every issue. Because its built into PFCLScan it uses all of the power of our scanner but also at because of this allows command line running so that this tool; PFCLCode and indeed any of our tools, can be easily integrated into any other tools or workflow such as your favourite database GUI editors.

More on PFCLScan engines, plugins, command line running and integration into other tools soon in a blog post.

I have been collecting details and analysis for years on PL/SQL and I was thinking maybe to write this as an MS Word document and publish as a pdf but i decided to just put it on the blog. I have had an interest in some of these ideas since the mid 90s and some of it early noughties and some more recently as I write and analyse PL/SQL for various reasons and products. So, I decided its time to spend some time and collate and edit and release a succinct version of these ideas and notes.

Enjoy!!

Last week I spoke about the PL/SQL and its machine code in a blog post called "PL/SQL Machine Code Trace - event 10928" and how PL/SQL is a compiled language and it is eventually converted/compiled into machine code and that machine code is executed in a PL/SQL virtual machine. This is a C program that executes PL/SQL machine instructions and this VM is what runs your PL/SQL in the database. This is a concept all of us should be familiar with nowadays because of the rise of virtualisation with tools such as virtualbox or VMware etc. Those are tools that simulate an X86 or X64 or other PC hardware as software allowing copies of Windows or Linux to run in software rather than hardware. These tools are eminently part of the push to cloud with the ideas of provisioning hardware (as software) very quickly and having systems available on demand or moved as needed. Well PL/SQL does the same; it runs in a VM but it runs machine code for PL/SQL.

I did cover some of the details in this post many years ago in my Blackhat talk in 2006 Unwrapping PL/SQL but lets go into some detail again with some new elements and digressions. This post is not about unwrapping but its mention cannot be avoided.

Why am I interested in this low level detail of PL/SQL? well initially I was interested around 20 or more years ago because i loved low level internal details especially of the database. I am a C programmer, I first learned Oracle C and user exits in the mid 90s, then OCI (Oracles C API) and then Pro*C and also in parallel SQL and PL/SQL. I started as a C programmer and always loved low level details. I loved to dig around in the databases of the time (Oracle 6 and 7) and look at the files shipped with the database in the /rdbms/admin directory especially things like the .bsq files, the DIANA, PIDL, DIUTIL, SUBPTXT and more; particularly I liked what was evidently hard core PL/SQL that was written like it was a C programmer writing it; at least to me it did. One example is that I found a procedure SUBPTXT and I looked at it (this below is in 18c XE by the way now, BUT i looked at this originally in Oracle 7):

SQL> select text from dba_source where name='SUBPTXT';

TEXT
--------------------------------------------------------------------------------
procedure subptxt(name varchar2, subname varchar2, usr varchar2,
txt in out varchar2) is
begin
subptxt2(name, subname, usr, null, null, txt);
end;

I then looked at the code of this procedure by selecting it from the database:

SQL> select text from dba_source where name='SUBPTXT2';

TEXT
--------------------------------------------------------------------------------
procedure subptxt2(name varchar2, subname varchar2, usr varchar2,
dbname varchar2, dbowner varchar2,
txt in out varchar2) is
status diutil.ub4;

begin -- main
diutil.subptxt(name, subname, usr, dbname, dbowner, txt, status);
if (status <> diutil.s_ok) then
if (status = diutil.s_subpNotFound) then
txt := '$$$ s_subpNotFound';
elsif (status = diutil.s_stubTooLong) then

TEXT
--------------------------------------------------------------------------------
txt := '$$$ s_stubTooLong';
elsif (status = diutil.s_logic) then
txt := '$$$ s_logic';
elsif (status = diutil.s_notInPackage) then
txt := '$$$ s_notInPackage';
else txt := '$$$ s_other';
end if;
end if;
end subptxt2;

20 rows selected.

SQL>

Look at the comment, "-- main", next to the start of the begin; that's a C programmer, fantastic!!!. As I said I came across these functions in the 90s and also PSTUB as i was involved in C, PL/SQL and some Forms and these were used to generate server side calls for PL/SQL. As i said i loved to dig into the database details. Let's look at a sample PL/SQL function that I have used as a demo for SQL Injection for almost 20 years. This is one iteration of this sample and is of my CUST function; here is the header via a DESCRIBE:

SQL> desc cust
FUNCTION cust RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PV_NAME VARCHAR2 IN

SQL>

What if we now run the SYS.SUBPTXT procedure; what does it do? The source code file pistub.sql says its a "subprogram stub generator". What does that mean? it is from (as far as I know) the old days of Oracle Forms where server side calls needed to generate stubs to call procedures in the database; it did this by generating a "stub". First lets describe the procedure:

SQL> desc subptxt
PROCEDURE subptxt
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
SUBNAME VARCHAR2 IN
USR VARCHAR2 IN
TXT VARCHAR2 IN/OUT

Now lets use it to generate the header / describe for my CUST function shown above:

SQL> var a varchar2(2000);
SQL> exec sys.subptxt('CUST',NULL,NULL,:a);

PL/SQL procedure successfully completed.

SQL> print a

A
--------------------------------------------------------------------------------
function CUST (PV_NAME VARCHAR2) return VARCHAR2;

SQL>

This call clearly generated the same function signature as the actual function in the database. But how does it work, what does it do to generate this signature? The SYS.SUBPTXT() procedure is not wrapped and we can read it and we can also read some of the SYS.DIUTIL package procedure that is called from the --main body shown above. I selected some of the DIUTIL package from my 18cXE database but you can also go and review the source code files in the /rdbms/admin directory on the server or view the complete source code yourself as i have done. Here is a section of the SYS.DIUTIL package (some output cut for brevity; see the ... for the gaps):

SQL> select text from dba_source where name='DIUTIL';

TEXT
--------------------------------------------------------------------------------
PACKAGE diutil IS

e_subpnotfound EXCEPTION;
e_notinpackage EXCEPTION;
e_nopriv EXCEPTION;

...

ELSIF (nkind = diana.d_parm_c) THEN
DECLARE seq pidl.ptseqnd := diana.as_list(diana.as_p_ass(n));
BEGIN
etext(last_elt(seq));
END;

-- arglist
ELSIF (nkind = diana.ds_apply) THEN
DECLARE aseq ptnod := diana.as_list(n); BEGIN
rv := rv || '(';
listtext(aseq, ',');
rv := rv || ')';
END;

-- d_f_call
ELSIF (nkind = diana.d_f_call) THEN
DECLARE args ptnod := diana.as_p_ass(n);
BEGIN
IF (pidl.ptkin(args) <> diana.ds_param) THEN
-- ordinary function call
etext(diana.a_name(n));
etext(args);
ELSE -- operator functions, determine if unary or n-ary
DECLARE s pidl.ptseqnd := diana.as_list(args);
namenode ptnod := diana.a_name(n);
BEGIN
IF (pidl.ptslen(s) = 1) THEN -- unary
etext(namenode);
rv := rv || ' ';
etext(pidl.ptgend(s, 0));
ELSE exprtext(namenode, rv); listtext(s, rv);
END IF;
END;
END IF;
END;

...


This is interesting and back in the last 90s when i first looked at this stuff I could see that this is compiler stuff; follow the sample output of DIUTIL above and you can see its reading the code in some way for FUNCTION parameters. When unwrapping of the wrap file became "a thing" in the early noughties and I realised that some of the data in the wrap file was actually DIANA nodes then I remembered all of these packages and code (DIUTIL, PIDIAN, DIANA...) and realised that this is code (in SYS.DIUTIL) that is walking the AST (What is an AST in a minute). You can clearly see that this is the case above and its nice code and interesting to understand it. We will come back to this in more details shortly.

Why walk the AST - the DIANA? well that is because DIANA ('D'escriptive 'I'ntermediate 'A'ttributed 'N'otation for 'A'da - DIANA) was designed for ADA as the language that described the AST for ADA. Why walk the AST in the SYS.DIUTIL package above to generate the function signature? well that's what one of the founding principals of DIANA was. In the days of low memory and low processing power, it was figured that the DIANA would be stored - as IDL ('I'nterface 'D'efinition 'L'anguage) and that DIANA could then be processed and used rather than the source code. This meant that it was designed into DIANA to re-generate source code as in the SUBPTXT() procedure and also in the 9iR2 and earlier wrap file format. The designers of DIANA thought that source, pretty printers, analysers etc could all be driven from ADA. Nice idea.

I have written a number of parsers and lexers for PL/SQL for all of the language and for some of it. For instance our product PFCLObfuscate features a PL/SQL parser so that PL/SQL code can be parsed and protected to remove IPR (Intellectual Property) and also to lock PL/SQL code to a database through dynamic obfuscation so that it will not work if stolen. This is the only product to protect PL/SQL that can lock your code to the database it runs on; think license protection in games from years ago. As i mentioned earlier we have also written a PL/SQL parser for our PL/SQL static code analyser PFCLCode so that your PL/SQL can be analysed for many different types of security bugs including things like SQL Injection vulnerabilities.

In starting to write PL/SQL parsers I often lamented that wouldn't it be great if Oracle exposed their AST to the database users for PL/SQL that exists in the database. Then we could process Oracles AST and analyses it in any way that we needed to - after all that was the principal for DIANA. Unfortunately, even though you can see above that functions like SYS.SUBPTXT can be used to extract the internally stored AST (DIANA) for a function or procedures signature by parsing the internal AST we cannot use the same mechanisms to parse the whole AST for any piece of PL/SQL in a procedure or function body. I will show why shortly.

Oracle for many years have made it clear that the DIANA (AST) is not stored for package bodies and whilst it does seem that DIANA (AST) is stored for standalone procedures; as we will see this is not exposed to us for use even for procedures/functions bodies either. The procedure and function body exposed as DIANA in the database is not what was written and is intended for use in these stub generators.

PL/Scope - seems like it may be a useful tool to analyse PL/SQL code; well it is of course BUT its not perfect to analyse PL/SQL properly as its really a record of identifiers in the PL/SQL code and where those identifiers are created and used (called, referenced and assigned). We can do quite a lot with PL/Scope and it certainly warrants more investigations but as an outsider who audits and investigates other peoples (customers) databases and PL/SQL then we cannot change their databases or compile their code to get the list of identifiers generated in PL/Scope. So we need tools that can analyse code without changing the databases not ones that do BUT clearly PL/Scope is in some way using the AST / DIANA of the code as its parsed and compiled and the identifiers details are written out to tables for analysis; DBA_IDENTIFIERS for instance.

Before the PL/SQL source code gets converted into M-CODE or P-CODE it starts as PL/SQL source code of course. PL/SQL is well known to be based on the ADA language. Oracle based PL/SQL on ADA but removed some of the ADA elements such as parallel processing including tasks and synchronous message passing etc. Oracle of course also added all of the hooks to embed SQL directly into the language. If you are familiar with PL/SQL and look at ADA code you will recognise a lot of the code as its very similar with similar comments, block structure, packages, types and constructs such as IF, FOR and WHILE loops. ADA has support for task based concurrency and wouldn't it be great if Oracle put that back into PL/SQL !!! - More on that in a future post.

Some, but not all of the main components of this puzzle:

  • PL/SQL: This is the language of course. This is the source code that you work with BUT Oracle doesn't actually execute your source code it compiles it first into Machine code. The machine code is likely an optimised version of the code and perhaps does not reflect the original PL/SQL exactly. The PL/SQL is your original source code

  • AST: Abstract Syntax Tree - This is the tree representation of a PL/SQL program after is it parsed. The parse process will take on a number of phases. The first phase is lexical analysis where each language token is recognised. For instance the keyword PROCEDURE or the keyword FOR or the symbol pair :=. The next phase is the parsing which aims to recognise the language structure or syntax such as PROCEDURE {name} '(' {name} IN|OUT|INOT DATA_TYPE, [...] ')' and also analyse types and context of the code. This analysis / parsing phase constructs a tree called an 'A'bstract 'S'yntax 'T'ree; AST. This tree often in compilers and languages can be two trees or two phases of the same tree. The first phase being the structural tree representation of the language structure BUT as a tree; the second phase is where the tree is populated with symantec or code or lexical attributes - this is where the 'A'ttributed part of the tree comes in. The compiler creates a structured tree to represent the language elements and then adds leaves or tags to the tree. This can be for Lexical elements - i.e. a variable name such as my procedure name 'CUST' or parameter 'PV_NAME'. The attributes can be semantic elements i.e. things that relate to the language, value etc of a node. The main part of the tree are the structural attributes, the elements that point to other structural attributes (other nodes). We will see some examples soon.

  • DIANA: The AST in PL/SQL is made up of a tree of 315 different nodes (at least in 18c XE, less in earlier versions). The Abstract Syntax Tree starts at a root node - normally D_COMP_U in most PL/SQL (we will see this in an example in a moment) but may not always be D_COMP_U for all PL/SQL. These are the main components of the tree that is built hanging off the D_COMP_U DIANA node. Each DIANA node can have none (zero, we will see this shortly) or more attributes. These attributes are in some cases pointers to instances of other DIANA nodes

  • Attributes: Each DIANA node has a set of none (zero) or many attributes. These attributes can start with L_% and these are lexical attributes; a common one is L_SYMREP which is used for the name of a symbol table entry. Another common lexical attribute is L_SRCPOS which shows the row/column position in the original PL/SQL source. Some attributes start S_% and these are semantic attributes that relate to value or settings such as S_SPEC or S_BODY or SS_PRAGM_L. Then there are also C_% attributes. These are code attributes that relate to the generated code (M-CODE / P-CODE). Examples of code attributes are C_FIXUP, C_ENTRY or C_FRAME. Finally the most important attributes are the structural attributes. An example could be A_D_ attribute that points at the DI_FUNC or DI_PROC DIANA nodes that describe the function or procedure including its name. Some structural DIANA nodes are lists such as AS_LIST and these point at a list of DIANA nodes. In effect this allows DIANA nodes to be added to the tree as a set of nodes at the same level. For instance a list of parameters for a procedure or function would be an AS_LIST as each parameter is at the same level in the tree. Each DIANA node is different and has many different attributes that allow trees to be built that represent the PL/SQL

  • IDL: 'I'nterface 'D'efintion 'L'anguage. This is how the DIANA is written down on disk. This is a way to write at DIANA AST out in a flat way.

  • M-Code / P-CODE: As we showed in a previous post PL/SQL is compiled into machine code; M-CODE or P-CODE


How do i know some of the above. Well I started as i said by digging into packages and so on in the database; I also investigated ADA in some depth BUT I also bought a book a very long time ago, 16 or 17 years ago - DIANA An Intermediate Language for ADA - by Goos, Wulf, Evans and Butler:
DIANA Book by Goos, Wulf, Evans and Butler

I even found one of the authors and we engaged in some email correspondence. He told me that DIANA was dropped in ADA quite quickly but he knew Oracle used it originally in PL/SQL. He was very impressed that after all the years since they designed DIANA that someone was interested in it again.

This book is useful as it describes DIANA and its nodes and attributes and sample AST structures for most of ADA (which is also the core and a lot of PL/SQL). This book teaches you a lot of what DIANA and ASTs look like and how they work.

So, now lets see some DIANA and AST in the database for my sample PL/SQL function introduced above. First I needed to create a table so that the PL/SQL code will compile. I then installed my function into the database:

SQL> sho user
USER is "SYS"
SQL> create table credit_card (name_on_card varchar2(30), last_name varchar2(15));

Table created.

SQL> @custa.sql

Function created.

SQL>

Now we can use the DUMPDIANA code from Oracle. This is not installed by default so i need to install this from the ./rdbms/admin directory. This needs to be installed as SYSDBA and i have installed this in a pluggable database in my 18cXE database:

SQL> connect sys/oracle1@//192.168.56.78:1523/xepdb1 as sysdba
Connected.
SQL> @dumpdian

Library created.


Package created.


Package body created.

SQL>

You can describe the package and see its procedures available:

SQL> connect sys/oracle1@//192.168.56.78:1523/xepdb1 as sysdba
Connected.
SQL> desc dumpdiana
PROCEDURE DUMP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ANAME VARCHAR2 IN
LU_TYPE NUMBER IN DEFAULT
PRINT_FORMAT BINARY_INTEGER IN DEFAULT
PROCEDURE DUMP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NOD BINARY_INTEGER IN
PRINT_FORMAT BINARY_INTEGER IN DEFAULT
PROCEDURE NODE_COUNT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ANAME VARCHAR2 IN
LU_TYPE NUMBER IN DEFAULT
PROCEDURE NODE_COUNT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NOD BINARY_INTEGER IN

SQL>

Next we can run the SYS.DUMPDIANA.DUMP procedure for my CUST function and get is DIANA/AST. This is simple to run but it doesn't output anything to the screen. It creates a trace file that shows the AST/DIANA:

SQL> exec dumpdiana.dump(aname => 'CUST');

PL/SQL procedure successfully completed.

SQL>

We can now go to the Linux machine and get the trace file. This trace is the nicest to see, the "structured tree format". You can also play with DUMPDIANA and get a line format and another as well. Here is the DIANA AST for this CUST function - BUT there are some caveats!!

[oracle@oel18cxecrm trace]$ cat XE_ora_15871.trc
Trace file /opt/oracle/diag/rdbms/xe/XE/trace/XE_ora_15871.trc
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Build label: RDBMS_18.4.0.0.0DBRU_LINUX.X64_181017
ORACLE_HOME: /opt/oracle/product/18c/dbhomeXE
System name: Linux
Node name: oel18cxecrm.localdomain
Release: 4.14.35-1818.3.3.el7uek.x86_64
Version: #2 SMP Mon Sep 24 14:45:01 PDT 2018
Machine: x86_64
Instance name: XE
Redo thread mounted by this instance: 1
Oracle process number: 40
Unix process pid: 15871, image: oracle@oel18cxecrm.localdomain


*** 2020-03-16T15:23:50.371519+00:00 (XEPDB1(3))
*** SESSION ID:(31.24495) 2020-03-16T15:23:50.371543+00:00
*** CLIENT ID:() 2020-03-16T15:23:50.371548+00:00
*** SERVICE NAME:(xepdb1) 2020-03-16T15:23:50.371553+00:00
*** MODULE NAME:(sqlplus.exe) 2020-03-16T15:23:50.371558+00:00
*** ACTION NAME:() 2020-03-16T15:23:50.371563+00:00
*** CLIENT DRIVER:(SQL*PLUS ) 2020-03-16T15:23:50.371567+00:00
*** CONTAINER ID:(3) 2020-03-16T15:23:50.371572+00:00

(Using the structured tree format)
PD1(2):D_COMP_U [
L_SRCPOS : row 1 col 1
A_CONTEX :
PD2(2): D_CONTEX [
L_SRCPOS : row 1 col 1
AS_LIST : < >
]
A_UNIT_B :
PD3(2): D_S_BODY [
L_SRCPOS : row 1 col 1
A_D_ :
PD4(2): DI_FUNCT [
L_SRCPOS : row 1 col 10
L_SYMREP : CUST,
S_SPEC : PD5^(2),
S_BODY : PDD^(2),
S_LOCATI : 0,
S_STUB : void,
S_FIRST : PD4^(2),
C_OFFSET : 0,
C_FIXUP : NOT YET,
C_FRAME_ : 0,
C_ENTRY_ : 1,
S_FRAME : void,
A_UP : PD3^(2),
S_LAYER : 1,
L_RESTRICT_REFERENCES : 47,
A_METH_FLAGS : 0,
SS_PRAGM_L : void,
S_INTRO_VERSION : 0,
A_PARALLEL_SPEC : void,
C_VT_INDEX : 0,
C_ENTRY_PT : 1,
S_FG_POS : 0,
S_FG_SIG : S_PLSC_SIG : AS_WHTLST : void,
]
A_HEADER :
PD5(2): D_F_ [
L_SRCPOS : row 1 col 1
AS_P_ :
PD6(2): DS_PARAM [
L_SRCPOS : row 1 col 14
AS_LIST : <
PD7(2): D_IN [
L_SRCPOS : row 1 col 15
AS_ID :
PD8(2): DS_ID [
L_SRCPOS : row 1 col 15
AS_LIST : <
PD9(2): DI_IN [
L_SRCPOS : row 1 col 15
L_SYMREP : PV_NAME,
S_OBJ_TY : PD65^(3),
S_INIT_E : void,
S_FIRST : PD9^(2),
C_OFFSET : 0,
S_FRAME : PD4^(2),
S_ADDRES : 0,
SS_BINDS : A_UP : PD7^(2),
A_FLAGS : 0,
S_PLSC_SIG : ]
>
]
A_NAME :
PDA(2): DI_U_NAM [
L_SRCPOS : row 1 col 26
L_SYMREP : VARCHAR2,
S_DEFN_PRIVATE : PD64^(3),
SS_BUCKE :
L_DEFAUL : 256
]
A_EXP_VO : void,
A_INDICA : void,
S_INTERF : void,
]
>
]
A_NAME_V :
PDB(2): DI_U_NAM [
L_SRCPOS : row 1 col 43
L_SYMREP : VARCHAR2,
S_DEFN_PRIVATE : PD64^(3),
SS_BUCKE :
L_DEFAUL : 256
]
S_OPERAT : void,
A_UP : PD3^(2)
]
A_BLOCK_ :
PDD(2): D_BLOCK [
L_SRCPOS : row 1 col 1
AS_ITEM :
PDE(2): DS_ITEM [
L_SRCPOS : row 1 col 1
AS_LIST : < >
A_UP : PDD^(2)
]
AS_STM :
PD10(2): DS_STM [
L_SRCPOS : row 1 col 0
AS_LIST : <
PD29(2): D_P_CALL [
L_SRCPOS : row 1 col 1
A_NAME :
PD2E(2): D_S_ED [
L_SRCPOS : row 1 col 1
A_NAME :
PD2C(2): D_S_ED [
L_SRCPOS : row 1 col 1
A_NAME :
PD2A(2): DI_U_NAM [
L_SRCPOS : row 1 col 1
L_SYMREP : SYS,
S_DEFN_PRIVATE : void,
SS_BUCKE :
L_DEFAUL : 0
]
A_D_CHAR :
PD2B(2): DI_U_NAM [
L_SRCPOS : row 1 col 1
L_SYMREP : SYS_STUB_FOR_PURITY_ANALYSIS,
S_DEFN_PRIVATE : PD4^(4),
SS_BUCKE :
L_DEFAUL : 0
]
S_EXP_TY : void,
]
A_D_CHAR :
PD2D(2): DI_U_NAM [
L_SRCPOS : row 1 col 1
L_SYMREP : PWPS,
S_DEFN_PRIVATE : PD29^(4),
SS_BUCKE :
L_DEFAUL : 0
]
S_EXP_TY : void,
]
AS_P_ASS :
PD2F(2): DS_P_ASS [
L_SRCPOS : row 1 col 1
AS_LIST : < >
]
S_NORMARGLIST : <>
C_OFFSET : 0,
A_UP : PD10^(2),
A_FLAGS : 0
]
PD21(2): D_P_CALL [
L_SRCPOS : row 1 col 1
A_NAME :
PD26(2): D_S_ED [
L_SRCPOS : row 1 col 1
A_NAME :
PD24(2): D_S_ED [
L_SRCPOS : row 1 col 1
A_NAME :
PD22(2): DI_U_NAM [
L_SRCPOS : row 1 col 1
L_SYMREP : SYS,
S_DEFN_PRIVATE : void,
SS_BUCKE :
L_DEFAUL : 0
]
A_D_CHAR :
PD23(2): DI_U_NAM [
L_SRCPOS : row 1 col 1
L_SYMREP : SYS_STUB_FOR_PURITY_ANALYSIS,
S_DEFN_PRIVATE : PD4^(4),
SS_BUCKE :
L_DEFAUL : 0
]
S_EXP_TY : void,
]
A_D_CHAR :
PD25(2): DI_U_NAM [
L_SRCPOS : row 1 col 1
L_SYMREP : PRPS,
S_DEFN_PRIVATE : PD1E^(4),
SS_BUCKE :
L_DEFAUL : 0
]
S_EXP_TY : void,
]
AS_P_ASS :
PD27(2): DS_P_ASS [
L_SRCPOS : row 1 col 1
AS_LIST : < >
]
S_NORMARGLIST : <>
C_OFFSET : 0,
A_UP : PD10^(2),
A_FLAGS : 0
]
PD19(2): D_P_CALL [
L_SRCPOS : row 1 col 1
A_NAME :
PD1E(2): D_S_ED [
L_SRCPOS : row 1 col 1
A_NAME :
PD1C(2): D_S_ED [
L_SRCPOS : row 1 col 1
A_NAME :
PD1A(2): DI_U_NAM [
L_SRCPOS : row 1 col 1
L_SYMREP : SYS,
S_DEFN_PRIVATE : void,
SS_BUCKE :
L_DEFAUL : 0
]
A_D_CHAR :
PD1B(2): DI_U_NAM [
L_SRCPOS : row 1 col 1
L_SYMREP : SYS_STUB_FOR_PURITY_ANALYSIS,
S_DEFN_PRIVATE : PD4^(4),
SS_BUCKE :
L_DEFAUL : 0
]
S_EXP_TY : void,
]
A_D_CHAR :
PD1D(2): DI_U_NAM [
L_SRCPOS : row 1 col 1
L_SYMREP : PWDS,
S_DEFN_PRIVATE : PD13^(4),
SS_BUCKE :
L_DEFAUL : 0
]
S_EXP_TY : void,
]
AS_P_ASS :
PD1F(2): DS_P_ASS [
L_SRCPOS : row 1 col 1
AS_LIST : < >
]
S_NORMARGLIST : <>
C_OFFSET : 0,
A_UP : PD10^(2),
A_FLAGS : 0
]
PD11(2): D_P_CALL [
L_SRCPOS : row 1 col 1
A_NAME :
PD16(2): D_S_ED [
L_SRCPOS : row 1 col 1
A_NAME :
PD14(2): D_S_ED [
L_SRCPOS : row 1 col 1
A_NAME :
PD12(2): DI_U_NAM [
L_SRCPOS : row 1 col 1
L_SYMREP : SYS,
S_DEFN_PRIVATE : void,
SS_BUCKE :
L_DEFAUL : 0
]
A_D_CHAR :
PD13(2): DI_U_NAM [
L_SRCPOS : row 1 col 1
L_SYMREP : SYS_STUB_FOR_PURITY_ANALYSIS,
S_DEFN_PRIVATE : PD4^(4),
SS_BUCKE :
L_DEFAUL : 0
]
S_EXP_TY : void,
]
A_D_CHAR :
PD15(2): DI_U_NAM [
L_SRCPOS : row 1 col 1
L_SYMREP : PRDS,
S_DEFN_PRIVATE : PD8^(4),
SS_BUCKE :
L_DEFAUL : 0
]
S_EXP_TY : void,
]
AS_P_ASS :
PD17(2): DS_P_ASS [
L_SRCPOS : row 1 col 1
AS_LIST : < >
]
S_NORMARGLIST : <>
C_OFFSET : 0,
A_UP : PD10^(2),
A_FLAGS : 0
]
>
A_UP : PDD^(2)
]
AS_ALTER :
PDF(2): DS_ALTER [
L_SRCPOS : row 1 col 1
AS_LIST : < >
S_BLOCK : PDD^(2),
S_SCOPE : void,
A_UP : PDD^(2)
]
C_OFFSET : 0,
SS_SQL :
C_FIXUP : NOT YET,
S_BLOCK : void,
S_SCOPE : void,
S_FRAME : PD4^(2),
A_UP : PD3^(2),
S_LAYER : 0,
S_FLAGS : 0,
A_ENDLIN : 0,
A_ENDCOL : 0,
A_BEGLIN : 0,
A_BEGCOL : 0,
A_FLAGS : 0
]
A_UP : PD1^(2),
A_ENDLIN : -1,
A_ENDCOL : -1,
A_BEGLIN : -1,
A_BEGCOL : -1
]
AS_PRAGM :
PDC(2): DS_PRAGM [
L_SRCPOS : row 1 col 1
AS_LIST : < >
A_UP : void,
]
SS_SQL :
SS_EXLST :
SS_BINDS : A_UP : void,
A_AUTHID : 0,
A_SCHEMA : 0,
S_FG_REFS : ]
[oracle@oel18cxecrm trace]$

The caveats!. The DIANA AST shows the main structure for this function BUT it only shows the main items, the D_COMP_U - the compilation unit - this node has attributes A_CONTEX, A_UNIT_B and AS_PRAGM amongst others. The A_UNIT_B points to a DS_BODY DIANA node and this points to the DI_FUNCT DIANA node which contains the name of the function CUST as well as its type from the DIANA node name - this is pointed at via the A_D_ attribute. The DS_BODY has also the A_HEADER and A_BLOCK attributes. The A_HEADER points at a D_F_ DIANA node which is the starting point for all of the DIANA nodes that make up the header - parameters section and return type of the function. The A_BLOCK attribute of the DS_BODY DIANA node points at the D_BLOCK DIANA node that contains the AS_ITEM, AS_STM and AS_ALTER attributes; these are the main elements of the function; the AS_ITEM is where the function variables are added to the AST and the AS_STM is where all of the body statements if the function are added; finally the AS_ALTER is where the nodes for the exception clause are added to the AST.

OK, that's complex to talk about so I have drawn a picture of the core DIANA nodes of this simple function. This is here:
The DIANA AST for a simple PL/SQL function


The diagram shows the main AST for the D_COMP_U and how the function DIANA node is added and all of the header DIANA nodes (parameters). What is strange and missing is the main body of the function. The actual PL/SQL is here:

SQL> sho user
USER is "SYS"
SQL> select text from dba_source where name='CUST' and owner='SYS';

TEXT
--------------------------------------------------------------------------------
function cust(pv_name in varchar2) return varchar2 is
lv_stmt varchar2(2000);
type c_ref is ref cursor;
c c_ref;
name credit_card.name_on_card%type;
Begin
lv_stmt:='select name_on_card from credit_card '||
'where last_name = '''||pv_name||'''';
open c for lv_stmt;
loop
fetch c into name;

TEXT
--------------------------------------------------------------------------------
if(c%notfound) then
exit;
end if;
dbms_output.put_line('name:=['||name||']');

end loop;
close c;
return(name);
end;

20 rows selected.

SQL>

The DIANA nodes for the body of the function in the dumpdiana.dump above are incorrect (not really incorrect as clearly Oracle intended this). The real DIANA for the body is not shown but instead we get SYS_STUB_FOR_PURITY_ANALYSIS calls.

This is a shame as it means that not only did Oracle tell us the DIANA is not stored for packages but also it seems that the DIANA for the real procedure and function bodies is also not accessible. This means that use of the Oracle DIANA except to generate headers for procedure and function calls is not possible BUT we can still learn a lot from the above DIANA dump. What's interesting if you get hold of the Goos, Wulf et al book is that the DIANA nodes for Oracle PL/SQL are basically the same as for ADA as described in this book. So we can see the main structure of a code block; the name, the parameters, return, variables declared, body statements and exception clauses. This means that we can learn what the rest of the DIANA might look like for other code elements such as assign, function calls and more.

Interestingly the code files for the diana functions in DIANA, DIUTIL, DUMPDIANA and PIDL hints that the root DIANA node can be found with a debugger. Which debugger? gdb, or oradebug?

As i said earlier the DIANA is written down as IDL. Lets have a quick look at that storage. There are four tables that contain the IDL for DIANA and M-CODE/P-CODE. These are:

SQL> select table_name from dba_tables where owner='SYS' and table_name like 'IDL%';

TABLE_NAME
--------------------------------------------------------------------------------
IDL_CHAR$
IDL_SB4$
IDL_UB1$
IDL_UB2$

SQL>

We can try and read the date from these tables; well, we can read all of the data except the piece columns in three of them. Here is a simple try:

SQL> @print 'select * from idl_sb4$ where obj#=78457'
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from idl_sb4$ where obj#=78457','''','''''');
Executing Query [select * from idl_sb4$ where obj#=78457]
declare
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [12338], [], [], [], [], [], [], [],
[], [], [], []
ORA-06512: at "SYS.DBMS_SQL", line 1726
ORA-06512: at line 19
ORA-06512: at line 34


SQL>

This one was interesting as the select hung for a few seconds and then gave an ORA-0600, unhandled exception. This perhaps is a bug. Try the others:

SQL> @print 'select * from idl_ub1$ where obj#=78457'
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from idl_ub1$ where obj#=78457','''','''''');
Executing Query [select * from idl_ub1$ where obj#=78457]
declare
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got LONG BINARY
ORA-06512: at line 38
ORA-06512: at line 29
ORA-06512: at "SYS.DBMS_SQL", line 1721
ORA-06512: at line 18
ORA-06512: at line 34


SQL>

And try:

SQL> @print 'select * from idl_ub2$ where obj#=78457'
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from idl_ub2$ where obj#=78457','''','''''');
Executing Query [select * from idl_ub2$ where obj#=78457]
declare
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
ORA-06512: at line 38
ORA-06512: at line 29
ORA-06512: at "SYS.DBMS_SQL", line 1721
ORA-06512: at line 18
ORA-06512: at line 34


SQL>

The simplest way to read this data; the piece column at least is to find the block address for each table rows that you are interested in and then do a block dump and read the data that way. This is what I did in the BlackHat paper back in 2006. Lets look at the table descriptions:

SQL> desc idl_sb4$
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJ# NOT NULL NUMBER
PART NOT NULL NUMBER
VERSION NUMBER
PIECE# NOT NULL NUMBER
LENGTH NOT NULL NUMBER
PIECE NOT NULL UNDEFINED

SQL> desc idl_ub1$
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJ# NOT NULL NUMBER
PART NOT NULL NUMBER
VERSION NUMBER
PIECE# NOT NULL NUMBER
LENGTH NOT NULL NUMBER
PIECE NOT NULL LONG RAW

SQL> desc idl_ub2$
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJ# NOT NULL NUMBER
PART NOT NULL NUMBER
VERSION NUMBER
PIECE# NOT NULL NUMBER
LENGTH NOT NULL NUMBER
PIECE NOT NULL UNDEFINED

There are two tables that have a PIECE column data type of "LONG RAW" and one that has a data type of "UNDEFINED". This is interesting but how interesting? i.e. how many others are there of "UNDEFINED" or "LONG RAW":

SQL> select count(*) from dba_tab_columns where data_type='UNDEFINED';

COUNT(*)
----------
4

SQL> select count(*) from dba_tab_columns where data_type='LONG RAW';

COUNT(*)
----------
12

Not many at all. Lets see the "UNDEFINED" columns and what tables they are in:

SQL> col owner for a30
SQL> col table_name for a30
SQL> col column_name for a30
SQL> set lines 220
SQL> l
1* select owner,table_name,column_name from dba_tab_columns where data_type='UNDEFINED'
SQL> /

OWNER TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
SYS IDL_UB2$ PIECE
SYS IDL_SB4$ PIECE
SYS ORA_KGLR7_IDL_UB2 PIECE
SYS ORA_KGLR7_IDL_SB4 PIECE

SQL>

All of these are related to the IDL and DIANA and PL/SQL. What about the "LONG RAW" columns:

SQL> select owner,table_name,column_name from dba_tab_columns where data_type='LONG RAW';

OWNER TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
SYS TRIGGERJAVAF$ FLAGS
SYS IDL_UB1$ PIECE
SYS JAVASNM$ LONGNAME
SYSTEM MVIEW$_ADV_AJG AJGDES
SYSTEM MVIEW$_ADV_FJG FJGDES
SYSTEM MVIEW$_ADV_GC GCDES
SYSTEM MVIEW$_ADV_CLIQUE CLIQUEDES
SYSTEM MVIEW$_ADV_INFO INFO
SYS ORA_KGLR7_IDL_UB1 PIECE
SYS JAVASNM_TMP$ LONGNAME
SYS JAVA$MC$ PIECE

OWNER TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
SYS JAVA$METHOD$METADATA PIECE

12 rows selected.

SQL>

All seem to relate to code of some type, PL/SQL, VIEW, TRIGGER and JAVA. What about the CHAR IDL table? lets see the data for my CUST function:

SQL> connect sys/oracle1@//192.168.56.78:1523/xepdb1 as sysdba
Connected.
SQL> desc idl_char$
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJ# NOT NULL NUMBER
PART NOT NULL NUMBER
VERSION NUMBER
PIECE# NOT NULL NUMBER
LENGTH NOT NULL NUMBER
PIECE NOT NULL LONG

SQL> select object_id from dba_objects where object_name='CUST' and owner='SYS';

OBJECT_ID
----------
78457

SQL> @print 'select * from idl_char$ where obj#=78457'
SP2-0310: unable to open file "print.sql"
SQL> @print 'select * from idl_char$ where obj#=78457'
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from idl_char$ where obj#=78457','''','''''');
Executing Query [select * from idl_char$ where obj#=78457]
OBJ# : 78457
PART : 0
VERSION : 201326592
PIECE# : 3
LENGTH : 110
PIECE :
"CUST"E1678B6AA399D28B9391B3C5D1B351C2"PV_NAME"VARCHAR2"SYS"SYS_STUB_FOR_PURITY_
ANALYSIS"PRDS"PWDS"PRPS"PWPS""
-------------------------------------------

PL/SQL procedure successfully completed.


This is interesting and backs up what we saw for the function in the DUMPDIANA above. This shows the symbols for the function name and the parameter and its data type and then for the added in by Oracle SYS_STUB_FOR_PURITY_ANALYSIS as discussed earlier. If you look at a wrapped file from 9ir2 and earlier then this is very similar to the symbol table section in those wrapped files, except that in those files the whole of the symbol table was visible. Here is a desc of my function again for reference to show that all symbols necessary to re-generate the function signature from DIANA are there:

SQL> desc cust
FUNCTION cust RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PV_NAME VARCHAR2 IN

SQL>

Can we learn more about the nodes (DIANA) and attributes - remember from the DIUTIL package earlier where there are calls to the PIDL and DIANA packages and where these are clearly used in the walking of the available DIANA AST. Well we can query the database and find out more about the DIANA and its attributes with these packages.

Here is part of the DIANA package via a DESCRIBE command:

SQL> desc diana
FUNCTION AS_ALTER RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NODE BINARY_INTEGER IN
FUNCTION AS_ALTERS RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NODE BINARY_INTEGER IN
FUNCTION AS_ALTS RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NODE BINARY_INTEGER IN
FUNCTION AS_ALTTYPS RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NODE BINARY_INTEGER IN
FUNCTION AS_APPLY RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
...

We cannot see the variables that are defined in the package via the describe command but we can see them via a select from the source code or by looking at the source code file in the /rdbms/admin directory. Here is the start and end of the DIANA nodes definitions from the DIANA package:

SQL> select text from dba_source where name='DIANA';

TEXT
--------------------------------------------------------------------------------
package diana is

D_ABORT constant pidl.ptnty := 1;
D_ACCEPT constant pidl.ptnty := 2;
D_ACCESS constant pidl.ptnty := 3;
D_ADDRES constant pidl.ptnty := 4;
D_AGGREG constant pidl.ptnty := 5;
D_ALIGNM constant pidl.ptnty := 6;
D_ALL constant pidl.ptnty := 7;
D_ALLOCA constant pidl.ptnty := 8;
D_ALTERN constant pidl.ptnty := 9;
...
DI_ASSEMBLY constant pidl.ptnty := 313;
D_WHTLST constant pidl.ptnty := 314;
DS_WHTLST constant pidl.ptnty := 315;

The DS_WHTLST is clearly the node for the new white listing in packages added in 12c. This is supported in the PL/SQL syntax with the "ACCESSIBLE BY" clause.

We can also look at the start of the PIDL package:

SQL> desc pidl
FUNCTION PTATTANM RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ATTR_ENUM BINARY_INTEGER IN
FUNCTION PTATTBTY RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NODE_ENUM BINARY_INTEGER IN
ATTR_ENUM BINARY_INTEGER IN
FUNCTION PTATTCNT RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NODE_ENUM BINARY_INTEGER IN
FUNCTION PTATTNNM RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NODE_ENUM BINARY_INTEGER IN

...

This package can be used to list out all of the attributes for each DIANA node. This can be worked out by studying this package and also by studying the source of scripts in the /rdbms/admin directory that use these functions and procedures.

We can use my attrib.sql script that has been on my website since around 2005. We can use this script to create my attrib procedure and then use that to list attributes for a DIANA node. Here is the first example of the D_COMP_U root DIANA node seen in the DUMPDIANA output above for my CUST procedure:

SQL> get attrib
1 create or replace procedure attrib(nod sys.pidl.ptnty) is
2 len pidl.ub2;
3 i binary_integer;
4 begin
5 dbms_output.put_line('Node Type '||pidl.ptattnnm(nod));
6 dbms_output.put_line('Num Attributes '||pidl.ptattcnt(nod));
7 len := pidl.ptattcnt(nod) - 1;
8 for i in 0..len loop
9 dbms_output.put_line(i||': '||pidl.ptatttyp(nod,i)
10 ||':'||pidl.ptattanm(pidl.ptatttyp(nod,i))
11 ||':'||pidl.ptattbty(nod,pidl.ptatttyp(nod,i))
12 ||': REF '||pidl.ptattrty(nod,pidl.ptatttyp(nod,i)));
13 end loop;
14* end;
SQL>
SQL> @attrib

Procedure created.

SQL> begin
2 attrib(23);
3 end;
4 /
Node Type D_COMP_U
Num Attributes 10
0: 9:A_CONTEX:1: REF 1
1: 40:A_UNIT_B:1: REF 1
2: 62:AS_PRAGM:1: REF 1
3: 114:SS_SQL:30: REF 0
4: 113:SS_EXLST:30: REF 0
5: 111:SS_BINDS:30: REF 0
6: 41:A_UP:1: REF 0
7: 138:A_AUTHID:2: REF 0
8: 142:A_SCHEMA:2: REF 0
9: 197:S_FG_REFS:30: REF 1

PL/SQL procedure successfully completed.

Look at the attributes above for D_COMP_U DIANA node and compare with the tree dump above. As an example we can also look at the D_BLOCK DIANA node as it also appears in then DIANA AST tree above. First find out the D_BLOCK value and then use attrib to get its attributes:

SQL> select text from dba_source where name='DIANA' and upper(text) like '%D_BLOCK%';

TEXT
--------------------------------------------------------------------------------
D_BLOCK constant pidl.ptnty := 17;

SQL>
SQL> begin
2 attrib(17);
3 end;
4 /
Node Type D_BLOCK
Num Attributes 17
0: 56:AS_ITEM:1: REF 1
1: 64:AS_STM:1: REF 1
2: 43:AS_ALTER:1: REF 1
3: 69:C_OFFSET:3: REF 0
4: 114:SS_SQL:30: REF 0
5: 66:C_FIXUP:11: REF 0
6: 79:S_BLOCK:1: REF 0
7: 103:S_SCOPE:1: REF 0
8: 88:S_FRAME:1: REF 0
9: 41:A_UP:1: REF 0
10: 92:S_LAYER:6: REF 0
11: 135:S_FLAGS:4: REF 0
12: 188:A_ENDLIN:3: REF 0
13: 187:A_ENDCOL:3: REF 0
14: 186:A_BEGLIN:3: REF 0
15: 185:A_BEGCOL:3: REF 0
16: 120:A_FLAGS:4: REF 0

PL/SQL procedure successfully completed.

SQL>

Compare again with above. Remember that AS_ITEM is where variables are declared - i.e. the declare block. The AS_STM is the statements for the main body and AS_ALTER is the exception clause. Goos, Wulf et al confirm this in their DIANA book.

I mentioned earlier that some nodes can have zero attributes and that some nodes are from ADA. The D_ABORT is a good example. It has zero attributes:

SQL> select text from dba_source where name='DIANA' and upper(text) like '%ABORT%';

TEXT
--------------------------------------------------------------------------------
D_ABORT constant pidl.ptnty := 1;

SQL>

SQL> begin
2 attrib(1);
3 end;
4 /
Node Type D_ABORT
Num Attributes 0

PL/SQL procedure successfully completed.

SQL>

This is a node from ADA and is most likely not used in PL/SQL as there does not seem to be an ABORT PL/SQL keyword but ABORT does appear in the reserved words list and also in the Oracle documentation as a PL/SQL reserved word - ABORT reserved word.

SQL> select * from v$reserved_words where keyword like '%ABORT%';

KEYWORD
--------------------------------------------------------------------------------
LENGTH R R R R D CON_ID
---------- - - - - - ----------
ABORT
5 N N N N N 0


SQL>

Some more ADA keywords and DIANA nodes can be found. For instance ADA supports tasks and the TASK keyword as well as entries.

SQL> select text from dba_source where name='DIANA' and upper(text) like '%TASK%';

TEXT
--------------------------------------------------------------------------------
DI_TASK_ constant pidl.ptnty := 156;

SQL>
SQL> select text from dba_source where name='DIANA' and upper(text) like '%ENTR%';

TEXT
--------------------------------------------------------------------------------
D_ENTRY constant pidl.ptnty := 39;
D_ENTRY_ constant pidl.ptnty := 40;
DI_ENTRY constant pidl.ptnty := 137;
VTABLE_ENTRY constant pidl.ptnty := 300;
function c_ENTRY_(node pidl.ptnod) return pidl.ub4;
function c_ENTRY_PT(node pidl.ptnod) return pidl.ub4;
function c_ENTRY_(node pidl.ptnod) return pidl.ub4 is
function c_ENTRY_PT(node pidl.ptnod) return pidl.ub4 is

8 rows selected.

SQL> select * from v$reserved_words where keyword like '%ENTR%';

KEYWORD
--------------------------------------------------------------------------------
LENGTH R R R R D CON_ID
---------- - - - - - ----------
ENTRY
5 N N N N N 0


SQL>

we can see the DIANA, we can see part of the AST structure for a procedure or function and we can analyse all nodes, their attribute and so on; wee can see some of a diana tree for the headers of functions and procedures but we cannot get the body DIANA (except via an unwapper).

Unwrapping of pre 10g PL/SQL wrapped code is in effect talking to the DIANA and AST. I wrote unwrap_c.sql 16 years ago and created an unwrapper for PL/SQL in PL/SQL. I also wrote unwrappers for PL/SQL 9i and earlier in C - of course, it is my favourite language. These tools are not available so please don't ask me for them. unwrap_c.sql can create the DIANA AST BUT its old and as we need to use a 9i wrap.exe to get the wrap file so that it can then be processed. This is not practical method or the right thing to do to write tools to analyse PL/SQL. This is a 9i PL/SQL compiler and old and doesn't do 18c, 19c, 20c PL/SQL new features of course.

To do something modern with PL/SQL we need to write our own parsers for PL/SQL as the database software does not expose the whole AST for all PL/SQL to use in our own tools. I suspect that might be a way to get the PL/SQL compiler to spit out the whole AST for debugging and analysis - if I was the writer of the PL/SQL compiler and knowing Oracles penchant for adding trace, instrumentation and tools to spit out internals I would add it, so I would be surprised if there is not a way to do that. If there is an event, parameter, command to tell the PL/SQL compiler to spit out the AST I have not found it and if I did find it it probably is not the right way to parse PL/SQL ourselves. BUT, clearly having access to the PL/SQL AST would be a massive time saver to create PL/SQL based tools if we could access it.

We have delved into some PL/SQL internals and whilst you don't need to know this to write PL/SQL its useful if you want to analyse that PL/SQL for any reason. Its always good to get a better understanding of how something works; Oracle really helps us do this with all of the clues and pieces that they leave exposed for us to look at in the database, code files, tools etc.

I said in my blackhat paper that 10g and above still uses DIANA (clearly we can see this is the case on my examples above written and tested in 18c XE). Some people mis-quoted me after that paper in 2006 that I said the wrap mechanism in 10g and above is the same as 9i and earlier. This is NOT what I said or meant and those that misquoted me and also in some cases cut and pasted my slides into their own papers didn't get it. I said that clearly 10g and above still uses DIANA and the AST in some way. I hope that this post was interesting for you to read.

I enjoyed writing it and its nice to spend some time to share knowledge.

OK, bye from WFH (Working From Home)

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

Be Careful of What You Include In SQL*Net Security Banners

A short post today to add a little to the post I made the other day. In that post Add A SQL*Net Security Banner And Audit Notice I talked about using the sqlnet.ora parameters SEC_USER_AUDIT_ACTION_BANNER and SEC_USER_UNAUTHORIZED_ACCESS_BANNER to add security banners to your SQL*Net logins through tools such as SQL*Plus.

I got an email from someone today who advised me of a gotcha around this. He said he had implemented something similar using a logon trigger many many years ago where this logon trigger was able to output a banner of the form:

/*************************************************************************
THIS IS MY SECURITY BANNER
**************************************************************************/

But some shell scripts (bash, sh, ksh etc) that then called sqlplus to connect to the database read in the output which included the banner and the stars and these were, because of how the shell script was constructed, converted into shell input and the stars were converted in his case to file listings from the CWD (Current Working Directory). This caused problems for him.

So, be careful in what you add to your banners; keep it straight text and no special characters as that is unlikely to cause problems for any automated processes that connect to the database BUT test and make sure!

Bye for now from WFH!

Oracles Free TNS Firewall - VALIDNODE_CHECKING

I said in a post a couple of days ago that my overall plan to secure an Oracle database; actually my plan is to secure the data in an Oracle database not blindly just secure Oracle. We must focus on securing data first and last and everywhere in-between.

As I said a few days ago the first step is to stop people connecting to the database who should not be allowed to connect. We must limit the actual people who are allowed to directly connect to the database to just and only just those users who need to. no more. Once these users / people are identified then we can further limit how they can connect (i.e. what tools are allowed) and then further strengthen them with strong passwords and least rights; i.e. only have exactly the right privileges to do their job and no more. This is easy to say but in practice hard to do for many many reasons.

Finally after we control the users and their rights we can then think about data security controls including permissions on tables/views etc and even context based security such as VPD, OLS, DV Realms or hand coded solutions with views and triggers all based on factors such as user id, time, where, when, what etc.

Valid node checking is Oracles free simple TNS firewall that exists in the listener. I have been advising clients and others at talks and presentations and training to use this technology for years. Its free and simple but a little brute force - I.e. it works at the IP level and port (because it works on TNS its tied to the current listener port). It would be better if there was a little more flexibility maybe down to the tool level/ user/ ?? . We can do that level with a login trigger though so all is not lost.

OK, lets test valid node checking. First go to the Linux box and go to the $ORACLE_HOME/network/admin and open the sqlnet.ora file and turn on valid node checking by setting TCP.VALIDNODE_CHECKING=yes and then create an invited nodes list - a white list of IP addresses or Hostnames. This can be done with the TCP.INVITED_NODES parameter. See my box as follows to see that I have added the IP Address of the database server only at this point:

[oracle@oel1124 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /u01/app/oracle

TCP.VALIDNODE_CHECKING=yes
TCP.INVITED_NODES=(192.168.56.85)
[oracle@oel1124 admin]$

Now try and connect remotely from a client PC using SQL*Plus:

C:\_aa\PB\bin>sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 31 09:38:01 2020

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

SQL> connect system/oracle1@//192.168.56.85:1521/bfora.localdomain
Connected.
SQL>

This clearly doesn't work as I should not have been able to connect from any IP Address except the database server. Let us find out my IP Address first:

C:\_aa\PB\bin>ipconfig

Windows IP Configuration


Ethernet adapter Ethernet 3:

Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::a11c:3e6e:4d67:b94a%8
IPv4 Address. . . . . . . . . . . : 192.168.56.1
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :

Ethernet adapter Ethernet 4:

Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::45f6:ee3f:46b4:bd8f%14
Autoconfiguration IPv4 Address. . : 169.254.189.143
Subnet Mask . . . . . . . . . . . : 255.255.0.0
Default Gateway . . . . . . . . . :

Wireless LAN adapter Local Area Connection* 1:

Media State . . . . . . . . . . . : Media disconnected
Connection-specific DNS Suffix . :

Wireless LAN adapter Local Area Connection* 2:

Media State . . . . . . . . . . . : Media disconnected
Connection-specific DNS Suffix . :

Wireless LAN adapter Wi-Fi:

Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::8554:bf8f:3b91:e321%13
IPv4 Address. . . . . . . . . . . : 192.168.1.96
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.1.1

C:\_aa\PB\bin>

We have 192.168.56.1 - this is the gateway for Virtual box. so we should not be able to connect as that IP Address is not in the valid node checking invited nodes list. Restart the listener and re-register it:

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
LSNRCTL> start
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oel1124/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel1124.localdomain)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 31-MAR-2020 09:37:13
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oel1124/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel1124.localdomain)(PORT=1521)))
The listener supports no services
The command completed successfully
LSNRCTL> exit
[oracle@oel1124 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 31 09:37:18 2020

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


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> alter system register;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
[oracle@oel1124 admin]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 31-MAR-2020 09:37:28

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 31-MAR-2020 09:37:13
Uptime 0 days 0 hr. 0 min. 16 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oel1124/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel1124.localdomain)(PORT=1521)))
Services Summary...
Service "bfora.localdomain" has 1 instance(s).
Instance "bfora", status READY, has 1 handler(s) for this service...
Service "bforaXDB.localdomain" has 1 instance(s).
Instance "bfora", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

Now try and connect remotely again from 192.168.56.1 and see what happens:

C:\_aa\PB\bin>sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 31 09:41:13 2020

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

SQL> connect system/oracle1@//192.168.56.85:1521/bfora.localdomain
ERROR:
ORA-12547: TNS:lost contact


SQL>

Now the connection is prevented so we have proved that it works. Go in now and change the sqlnet.ora again to include my IP Address so that I can connect to the database from my SQL*Plus client but no one else can:

[oracle@oel1124 admin]$ vi sqlnet.ora
[oracle@oel1124 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /u01/app/oracle

TCP.VALIDNODE_CHECKING=yes
TCP.INVITED_NODES=(192.168.56.85,192.168.56.1)
[oracle@oel1124 admin]$

Now restart the listener again:

[oracle@oel1124 admin]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 31-MAR-2020 09:39:39

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
LSNRCTL> start
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oel1124/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel1124.localdomain)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 31-MAR-2020 09:39:46
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oel1124/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel1124.localdomain)(PORT=1521)))
The listener supports no services
The command completed successfully
LSNRCTL> exit
[oracle@oel1124 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 31 09:39:54 2020

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


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> alter system register;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
[oracle@oel1124 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 31-MAR-2020 09:40:04

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 31-MAR-2020 09:39:46
Uptime 0 days 0 hr. 0 min. 18 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oel1124/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel1124.localdomain)(PORT=1521)))
Services Summary...
Service "bfora.localdomain" has 1 instance(s).
Instance "bfora", status READY, has 1 handler(s) for this service...
Service "bforaXDB.localdomain" has 1 instance(s).
Instance "bfora", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oel1124 admin]$

Now try the remote connection using SQL*plus from my client PC:

SQL> connect system/oracle1@//192.168.56.85:1521/bfora.localdomain
Connected.
SQL>

Of course it works now. There are two points here. Yesterday in the post "Add A SQL*Net Security Banner And Audit Notice" I showed that for the banner parameters SEC_USER_AUDIT_ACTION_BANNER and SEC_USER_UNAUTHORIZED_ACCESS_BANNER in the sqlnet.ora we had to re-start the database for them to take effect. But, for valid node checking we only need to restart the listener. Inconsistency is not good. The second point is that this is virtual box and my true IP Address is in the 192.168.1.* range but I access the database on virtualbox networking via the gateway 192.168.56.1. This is not ideal if clients are going to access a database on a box in a virtual box network as the gateway needed to be added. Ensure that when you use valid node checking that you do not need to allow all access via a gateway as this will defeat the object of it.

OK, hope this helps, bye from WFH