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.

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

Add A SQL*Net Security Banner And Audit Notice

I would have to say whilst I see security banners on customers Unix boxes when I am allowed to log in as part of a security audit I canot ever remember seeing a security banner when I log into a customer database using SQL*Plus or our database security scanner PFCLScan or indeed any other tool. It is possible to add one and to therefore make sure that anyone who accesses your systems using SQL*Net is told that they can only access if they have permission and also to be told that they are being audited.

Oracle supports adding an Unauthorized access banner and an audit action banner. Lets see how this works. First connect to my 11.2.0.4 database as normal and check if we have a banner and show the version:

C:\_aa\PB\bin>sqlplus system/oracle1@//192.168.56.85:1521/bfora.localdomain

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 30 12:36:21 2020

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


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

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL>

OK, no banners, I didn't set them up yet. First lets connect to the Unix/Linux box and create the banner text files:

[root@oel1124 ~]# su - oracle
[oracle@oel1124 ~]$ cd $ORACLE_HOME/network/admin
[oracle@oel1124 admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@oel1124 admin]$ vi unauth.txt
[oracle@oel1124 admin]$ vi audit.txt
[oracle@oel1124 admin]$ cat unauth.txt
No unauthorised access is allowed to this system. You must have permission and a valid account and password provided by facilities and authorised by the Managing Director.

This system is Copyright PeteFinnigan.com Limited (c) 2020. All rights reserved.
[oracle@oel1124 admin]$ cat audit.txt
This system is protected by PeteFinnigan.com Limited.

All actions in this system by YOU are audited and those audit trails may be used to enforce security restrictions.
[oracle@oel1124 admin]$

Now lets update the sqlnet.ora file to reference the text files for the banners. I created my two text files in the $ORACLE_HOME/network/admin directory in the same location as my sqlnet.ora file BUT you can put the files anywhere that the Oracle software owner has access to read the files. So next let's update the 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

SEC_USER_AUDIT_ACTION_BANNER=/u01/app/oracle/product/11.2.0/db_1/network/admin/audit.txt
SEC_USER_UNAUTHORIZED_ACCESS_BANNER=/u01/app/oracle/product/11.2.0/db_1/network/admin/unauth.txt
[oracle@oel1124 admin]$

The banner text for each parameter can be at most 512 bytes. So the banner message needs to be fairly succinct but as there are two of them we can have 1024 bytes of message and that should be enough for most people.

So, how to make it work? We can restart the listener:

[oracle@oel1124 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-MAR-2020 13:04:23

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[oracle@oel1124 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-MAR-2020 13:04:29

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

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 30-MAR-2020 13:04:29
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
[oracle@oel1124 admin]$
[oracle@oel1124 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-MAR-2020 13:05: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 30-MAR-2020 13:04:29
Uptime 0 days 0 hr. 0 min. 35 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]$

So, when we connect to the database with SQL*Plus do we get the banners:

C:\_aa\PB\bin>sqlplus system/oracle1@//192.168.56.85:1521/bfora.localdomain

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 30 13:09:19 2020

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


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

SQL>

The short answer is no. We must restart the database for these parameters to take affect. This is odd in my opinion as these are network settings so it would have made more sense for these to take effect when the listener is restarted but never mind. Lets restart the database:

[oracle@oel1124 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 30 13:12:47 2020

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


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

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
No unauthorised access is allowed to this system. You must have permission and a valid account and password provided by facilities and authorised by the Managing Director.

This system is Copyright PeteFinnigan.com Limited (c) 2020. All rights reserved.

This system is protected by PeteFinnigan.com Limited.

All actions in this system by YOU are audited and those audit trails may be used to enforce security restrictions.

ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1006636072 bytes
Database Buffers 637534208 bytes
Redo Buffers 7094272 bytes
Database mounted.
No unauthorised access is allowed to this system. You must have permission and a valid account and password provided by facilities and authorised by the Managing Director.

This system is Copyright PeteFinnigan.com Limited (c) 2020. All rights reserved.

This system is protected by PeteFinnigan.com Limited.

All actions in this system by YOU are audited and those audit trails may be used to enforce security restrictions.

Database opened.
SQL>

This is interesting. Both banner texts are displayed in the order, Unauthorised banner and then audit banner text. The texts are displayed twice on startup. I was connected already AS SYSDBA and shutdown the database. I didn't reconnect but the banners displayed before the instance was started and then again after the instance started. Not sure why twice and also why display when no connection was made. Now lets connect with SQL*Plus from a client and see what happens:

C:\_aa\PB\bin>sqlplus system/oracle1@//192.168.56.85:1521/bfora.localdomain

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 30 13:19:38 2020

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

No unauthorised access is allowed to this system. You must have permission and a valid account and password provided by facilities and authorised by the Managing Director.

This system is Copyright PeteFinnigan.com Limited (c) 2020. All rights reserved.

This system is protected by PeteFinnigan.com Limited.

All actions in this system by YOU are audited and those audit trails may be used to enforce security restrictions.


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

SQL>

Now we get both banners. What happens if I use SQL*Plus in silent mode:

C:\_aa\PB\bin>sqlplus -S system/oracle1@//192.168.56.85:1521/bfora.localdomain


^C
C:\_aa\PB\bin>

Nothing; it hangs and never times out. the -S works when there is no banner files but not when there is? is this is a bug? If we use SQL*Plus with -V then it does work and only shows the version:

C:\_aa\PB\bin>sqlplus -V system/oracle1@//192.168.56.85:1521/bfora.localdomain

SQL*Plus: Release 11.2.0.4.0 Production


C:\_aa\PB\bin>

If we do a connect from within SQL*Plus rather than a connect on the command line:

SQL> connect system/oracle1@//192.168.56.85:1521/bfora.localdomain
No unauthorised access is allowed to this system. You must have permission and a valid account and password provided by facilities and authorised by the Managing Director.

This system is Copyright PeteFinnigan.com Limited (c) 2020. All rights reserved.

This system is protected by PeteFinnigan.com Limited.

All actions in this system by YOU are audited and those audit trails may be used to enforce security restrictions.

Connected.
SQL>

This works the same as a connection on the command line.

What about other tools. We can create a project in PFCLScan our database vulnerability scanner that uses OCI to connect to the database and we can check the engine logs to see if the banner works:

...
[2020 Mar 30 12:28:42] Oscan: [-] Test database connection
[2020 Mar 30 12:28:42] Oscan: Attached to [//192.168.56.85:1521/bfora.localdomain]
[2020 Mar 30 12:28:42] Oscan: Connected to [//192.168.56.85:1521/bfora.localdomain] as [system ]
...

The above is a section from the plugin log where the database connection is tested. What about actually connecting during the scan:


[2020 Mar 30 12:30:49] Oscan: Connect to the database....
[2020 Mar 30 12:30:49] Oscan: Server Attached to [//192.168.56.85:1521/bfora.localdomain]
[2020 Mar 30 12:30:49] Oscan: Connected to [//192.168.56.85:1521/bfora.localdomain] as [system]
...

No, again it doesn't show any banner in the OCI connection. So we would need to make a modification to our OCI code to display the banners. We would need to use OCI_AttrGet(…) and use the OCI_ATTR_ACCESS_BANNER and OCI_ATTR_AUDIT_BANNER attributes to get the banners if they exist and display them in our OCI code.

So in summary we can set banners for unauthorised access and also audit trail notification but there is no mechanism to answer a question to accept the banner and then log in and it is too easy to ignore then banner in SQL*Plus with a -V or cause a hang with -S. Also unless your write code into your tools then OCI does not display these banners by default.

OK, by for now from WHF!!


ORA-28050 - Can I drop the SYSTEM User?

Two things most annoy me with the Oracle database in terms of securing it and this is the abundance of default users in most Oracle databases that I perform security audits on and also the massive amount of PUBLIC grants that are there by default for all users.

The primary goal in securing Oracle is to stop people connecting to the database. If you can do that and only allow in the exact number of users necessary to do their job to connect. This can be achieved by network controls, Oracle network controls such as validnode checking and even database controls such as login triggers or Database Vault CONNECT command rules... If you then ensure that each account has only the exact rights necessary to do their job and no more then you are on your way to good data security and lock down. You can then lock down the access controls to the data itself and even use context based security such as VPD or OLS, DV, redaction, masking, TSDP and more. This is least privileges.

Then you have some level of initial control. An attack then must take place on one of a small number of general ways:

  1. The user accounts that are allowed to connect can exploit some other feature of the database or data controls. This means that if you have a valid account and can connect to the database you then need to find a gap in the database settings, data access controls or exploit the fact you didn't really have least rights

  2. The attacker could exploit a remote vulnerability in the database access protocols (TNS, OLOGON etc) and find a way to access the database without a username or with a username and no password. This could be as simple as guessing a default users password or as extreme as a network stack buffer overflow, or...

  3. The attacker could find an exploit in the application stack and tunnel to the database. For instance SQL Injection in application code.

  4. Others, possibly....


So, back to my first two points. We want to stop people connecting, for those that we do allow to connect we limit what they can use (programs), we have strong password controls and we then aim for least rights and finally proper data domains, data access controls and even context based security BUT we are put in a difficult position by many default accounts existing that we maybe don't need and those tens of thousands of PUBLIC grants.

If we could have a database with no default accounts and no PUBLIC grants then we would have a much stronger starting point. Unfortunately we get 44K, 45K in a stock 12.2c, 18c, 19c database.

So as part of this discussion and quest I wanted to look at the SYSTEM user. Most sites I go to the DBAs use SYSDBA to do their daily work or if not they use SYSTEM as the default DBA account. But what does SYSTEM have. First lets connect to my 11.2.0.4 database:

SQL> sho user
USER is "SYS"
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL>

Now what objects does SYSTEM have:

SQL> select count(*),object_type from dba_objects where owner='SYSTEM' group by object_type;

COUNT(*) OBJECT_TYPE
---------- -------------------
64 INDEX PARTITION
20 SEQUENCE
45 TABLE PARTITION
4 QUEUE
1 PROCEDURE
25 LOB
1 PACKAGE
1 PACKAGE BODY
2 TRIGGER
167 TABLE
253 INDEX

COUNT(*) OBJECT_TYPE
---------- -------------------
8 SYNONYM
14 VIEW
4 FUNCTION
9 TYPE

15 rows selected.

SQL>

What are the main PL/SQL objects owned by SYSTEM:

SQL> col object_name for a30
SQL> col object_type for a30
SQL> l
1* select object_name,object_type from dba_objects where owner='SYSTEM' and object_type in('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY')
SQL> /

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
ORA$_SYS_REP_AUTH PROCEDURE
DBMS_REPCAT_AUTH PACKAGE
LOGMNR$TAB_GG_TABF_PUBLIC FUNCTION
LOGMNR$COL_GG_TABF_PUBLIC FUNCTION
LOGMNR$SEQ_GG_TABF_PUBLIC FUNCTION
LOGMNR$KEY_GG_TABF_PUBLIC FUNCTION
DBMS_REPCAT_AUTH PACKAGE BODY

7 rows selected.

SQL>

These are REPCAT and LogMiner objects. Interesting. What about tables:

SQL> select object_name,object_type from dba_objects where owner='SYSTEM' and object_type ='TABLE';

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNR_SESSION_EVOLVE$ TABLE
LOGMNR_GLOBAL$ TABLE
LOGMNR_GT_TAB_INCLUDE$ TABLE
LOGMNR_GT_USER_INCLUDE$ TABLE
LOGMNR_GT_XID_INCLUDE$ TABLE
LOGMNR_UID$ TABLE
LOGMNRGGC_GTLO TABLE
LOGMNRGGC_GTCS TABLE
LOGMNRC_DBNAME_UID_MAP TABLE
LOGMNR_LOG$ TABLE
LOGMNR_PROCESSED_LOG$ TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNR_SPILL$ TABLE
LOGMNR_AGE_SPILL$ TABLE
LOGMNR_RESTART_CKPT_TXINFO$ TABLE
LOGMNR_ERROR$ TABLE
LOGMNR_RESTART_CKPT$ TABLE
LOGMNR_INTEGRATED_SPILL$ TABLE
LOGMNR_FILTER$ TABLE
LOGMNR_SESSION_ACTIONS$ TABLE
LOGMNR_PARAMETER$ TABLE
LOGMNR_SESSION$ TABLE
LOGMNRT_MDDL$ TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
MVIEW$_ADV_WORKLOAD TABLE
MVIEW$_ADV_BASETABLE TABLE
MVIEW$_ADV_SQLDEPEND TABLE
MVIEW$_ADV_PRETTY TABLE
MVIEW$_ADV_TEMP TABLE
MVIEW$_ADV_FILTER TABLE
MVIEW$_ADV_LOG TABLE
MVIEW$_ADV_FILTERINSTANCE TABLE
MVIEW$_ADV_LEVEL TABLE
MVIEW$_ADV_ROLLUP TABLE
MVIEW$_ADV_AJG TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
MVIEW$_ADV_FJG TABLE
MVIEW$_ADV_GC TABLE
MVIEW$_ADV_CLIQUE TABLE
MVIEW$_ADV_ELIGIBLE TABLE
MVIEW$_ADV_OUTPUT TABLE
MVIEW$_ADV_EXCEPTIONS TABLE
MVIEW$_ADV_PARAMETERS TABLE
MVIEW$_ADV_INFO TABLE
MVIEW$_ADV_JOURNAL TABLE
MVIEW$_ADV_PLAN TABLE
AQ$_QUEUE_TABLES TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
AQ$_QUEUES TABLE
AQ$_SCHEDULES TABLE
AQ$_INTERNET_AGENTS TABLE
AQ$_INTERNET_AGENT_PRIVS TABLE
OL$ TABLE
OL$HINTS TABLE
OL$NODES TABLE
DEF$_ERROR TABLE
DEF$_DESTINATION TABLE
DEF$_CALLDEST TABLE
DEF$_DEFAULTDEST TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
DEF$_LOB TABLE
DEF$_PROPAGATOR TABLE
DEF$_ORIGIN TABLE
DEF$_PUSHED_TRANSACTIONS TABLE
REPCAT$_REPCAT TABLE
REPCAT$_FLAVORS TABLE
REPCAT$_REPSCHEMA TABLE
REPCAT$_SNAPGROUP TABLE
REPCAT$_REPOBJECT TABLE
REPCAT$_REPCOLUMN TABLE
REPCAT$_KEY_COLUMNS TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
REPCAT$_GENERATED TABLE
REPCAT$_REPPROP TABLE
REPCAT$_REPCATLOG TABLE
REPCAT$_DDL TABLE
REPCAT$_REPGROUP_PRIVS TABLE
REPCAT$_PRIORITY_GROUP TABLE
REPCAT$_PRIORITY TABLE
REPCAT$_COLUMN_GROUP TABLE
REPCAT$_GROUPED_COLUMN TABLE
REPCAT$_CONFLICT TABLE
REPCAT$_RESOLUTION_METHOD TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
REPCAT$_RESOLUTION TABLE
REPCAT$_RESOLUTION_STATISTICS TABLE
REPCAT$_RESOL_STATS_CONTROL TABLE
REPCAT$_PARAMETER_COLUMN TABLE
REPCAT$_AUDIT_ATTRIBUTE TABLE
REPCAT$_AUDIT_COLUMN TABLE
REPCAT$_FLAVOR_OBJECTS TABLE
REPCAT$_TEMPLATE_STATUS TABLE
REPCAT$_TEMPLATE_TYPES TABLE
REPCAT$_REFRESH_TEMPLATES TABLE
REPCAT$_USER_AUTHORIZATIONS TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
REPCAT$_OBJECT_TYPES TABLE
REPCAT$_TEMPLATE_REFGROUPS TABLE
REPCAT$_TEMPLATE_OBJECTS TABLE
REPCAT$_TEMPLATE_PARMS TABLE
REPCAT$_OBJECT_PARMS TABLE
REPCAT$_USER_PARM_VALUES TABLE
REPCAT$_TEMPLATE_SITES TABLE
REPCAT$_SITE_OBJECTS TABLE
REPCAT$_RUNTIME_PARMS TABLE
REPCAT$_TEMPLATE_TARGETS TABLE
REPCAT$_EXCEPTIONS TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
REPCAT$_INSTANTIATION_DDL TABLE
REPCAT$_EXTENSION TABLE
REPCAT$_SITES_NEW TABLE
LOGMNR_DICTSTATE$ TABLE
LOGMNRC_GTLO TABLE
LOGMNRC_GTCS TABLE
LOGMNRC_SEQ_GG TABLE
LOGMNRC_CON_GG TABLE
LOGMNRC_CONCOL_GG TABLE
LOGMNRC_IND_GG TABLE
LOGMNRC_INDCOL_GG TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNRC_GSII TABLE
LOGMNRC_GSBA TABLE
LOGMNR_SEED$ TABLE
LOGMNR_DICTIONARY$ TABLE
LOGMNR_OBJ$ TABLE
LOGMNR_TAB$ TABLE
LOGMNR_COL$ TABLE
LOGMNR_ATTRCOL$ TABLE
LOGMNR_TS$ TABLE
LOGMNR_IND$ TABLE
LOGMNR_USER$ TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNR_TABPART$ TABLE
LOGMNR_TABSUBPART$ TABLE
LOGMNR_TABCOMPART$ TABLE
LOGMNR_TYPE$ TABLE
LOGMNR_COLTYPE$ TABLE
LOGMNR_ATTRIBUTE$ TABLE
LOGMNR_LOB$ TABLE
LOGMNR_CON$ TABLE
LOGMNR_CDEF$ TABLE
LOGMNR_CCOL$ TABLE
LOGMNR_ICOL$ TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNR_LOBFRAG$ TABLE
LOGMNR_INDPART$ TABLE
LOGMNR_INDSUBPART$ TABLE
LOGMNR_INDCOMPART$ TABLE
LOGMNR_LOGMNR_BUILDLOG TABLE
LOGMNR_NTAB$ TABLE
LOGMNR_OPQTYPE$ TABLE
LOGMNR_SUBCOLTYPE$ TABLE
LOGMNR_KOPM$ TABLE
LOGMNR_PROPS$ TABLE
LOGMNR_ENC$ TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNR_REFCON$ TABLE
LOGMNR_PARTOBJ$ TABLE
LOGMNRP_CTAS_PART_MAP TABLE
SCHEDULER_PROGRAM_ARGS_TBL TABLE
SCHEDULER_JOB_ARGS_TBL TABLE
LOGSTDBY$PARAMETERS TABLE
LOGSTDBY$EVENTS TABLE
LOGSTDBY$APPLY_PROGRESS TABLE
LOGSTDBY$APPLY_MILESTONE TABLE
LOGSTDBY$SCN TABLE
LOGSTDBY$FLASHBACK_SCN TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGSTDBY$PLSQL TABLE
LOGSTDBY$SKIP_TRANSACTION TABLE
LOGSTDBY$SKIP TABLE
LOGSTDBY$SKIP_SUPPORT TABLE
LOGSTDBY$HISTORY TABLE
LOGSTDBY$EDS_TABLES TABLE
DEF$_AQCALL TABLE
DEF$_AQERROR TABLE
SQLPLUS_PRODUCT_PROFILE TABLE
HELP TABLE
MVIEW$_ADV_INDEX TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
MVIEW$_ADV_PARTITION TABLE
MVIEW$_ADV_OWB TABLE

167 rows selected.

SQL>

Again a lot of LogMiner, repcat and even outlines in OL$. What roles does SYSTEM have granted directly; not roles granted to roles etc but just the direct grants:

SQL> select granted_role from dba_role_privs where grantee='SYSTEM';

GRANTED_ROLE
------------------------------
AQ_ADMINISTRATOR_ROLE
DBA
MGMT_USER

SQL>

So, finally what grants are made on the SYSTEM objects:

SQL> col privilege for a30
SQL> col grantee for a30
SQL> col table_name for a30
SQL> set lines 220
SQL> select p.privilege,p.grantee,p.table_name,o.object_type from dba_tab_privs p, dba_objects o where p.owner='SYSTEM' and o.owner=p.owner and o.object_name=p.table_name;

PRIVILEGE GRANTEE TABLE_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
DELETE PUBLIC OL$ TABLE
INSERT PUBLIC OL$ TABLE
SELECT PUBLIC OL$ TABLE
UPDATE PUBLIC OL$ TABLE
DELETE PUBLIC OL$HINTS TABLE
INSERT PUBLIC OL$HINTS TABLE
SELECT PUBLIC OL$HINTS TABLE
UPDATE PUBLIC OL$HINTS TABLE
DELETE PUBLIC OL$NODES TABLE
INSERT PUBLIC OL$NODES TABLE
SELECT PUBLIC OL$NODES TABLE

PRIVILEGE GRANTEE TABLE_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
UPDATE PUBLIC OL$NODES TABLE
SELECT SYS REPCAT$_REPSCHEMA TABLE
SELECT SYS REPCAT$_REPPROP TABLE
SELECT SYS DEF$_CALLDEST TABLE
SELECT SYS DEF$_ERROR TABLE
SELECT SYS DEF$_DESTINATION TABLE
SELECT SELECT_CATALOG_ROLE SCHEDULER_PROGRAM_ARGS VIEW
SELECT SELECT_CATALOG_ROLE SCHEDULER_JOB_ARGS VIEW
SELECT SELECT_CATALOG_ROLE SCHEDULER_PROGRAM_ARGS_TBL TABLE
SELECT SELECT_CATALOG_ROLE SCHEDULER_JOB_ARGS_TBL TABLE
SELECT SYS DEF$_AQCALL TABLE

PRIVILEGE GRANTEE TABLE_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
SELECT SYS DEF$_AQCALL QUEUE
SELECT PUBLIC PRODUCT_PRIVS VIEW
SELECT PUBLIC HELP TABLE
DELETE PUBLIC MVIEW$_ADV_OWB TABLE
INSERT PUBLIC MVIEW$_ADV_OWB TABLE
SELECT PUBLIC MVIEW$_ADV_OWB TABLE
UPDATE PUBLIC MVIEW$_ADV_OWB TABLE
DELETE PUBLIC MVIEW$_ADV_PARTITION TABLE
INSERT PUBLIC MVIEW$_ADV_PARTITION TABLE
SELECT PUBLIC MVIEW$_ADV_PARTITION TABLE
UPDATE PUBLIC MVIEW$_ADV_PARTITION TABLE

PRIVILEGE GRANTEE TABLE_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
DELETE PUBLIC MVIEW$_ADV_INDEX TABLE
INSERT PUBLIC MVIEW$_ADV_INDEX TABLE
SELECT PUBLIC MVIEW$_ADV_INDEX TABLE
UPDATE PUBLIC MVIEW$_ADV_INDEX TABLE
DELETE OLAPSYS MVIEW$_ADV_OUTPUT TABLE
INSERT OLAPSYS MVIEW$_ADV_OUTPUT TABLE
SELECT OLAPSYS MVIEW$_ADV_OUTPUT TABLE
UPDATE OLAPSYS MVIEW$_ADV_OUTPUT TABLE
DELETE OLAPSYS MVIEW$_ADV_PARTITION TABLE
INSERT OLAPSYS MVIEW$_ADV_PARTITION TABLE
SELECT OLAPSYS MVIEW$_ADV_PARTITION TABLE

PRIVILEGE GRANTEE TABLE_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
UPDATE OLAPSYS MVIEW$_ADV_PARTITION TABLE
DELETE OLAPSYS MVIEW$_ADV_INDEX TABLE
INSERT OLAPSYS MVIEW$_ADV_INDEX TABLE
SELECT OLAPSYS MVIEW$_ADV_INDEX TABLE
UPDATE OLAPSYS MVIEW$_ADV_INDEX TABLE

49 rows selected.

SQL>

So, SYSTEM looks like a DBA account as it has the DBA role and its well known for many years as the default DBA account in an Oracle but it also has objects such as OL$ and also LogMiner objects. This is a classic case of a conflict. Is it a DBA or is it a schema?. Well like the rest of us who create Oracle databases and create users and schemas Oracle seems to have muddied the waters a bit with SYSTEM.

Ideally no one should use SYSTEM; its a default and it is the opposite of Least Rights in the database so clearly does not serve a purpose in a well designed and secure database. So can we drop SYSTEM:

SQL> drop user system cascade;
drop user system cascade
*
ERROR at line 1:
ORA-28050: specified user or role cannot be dropped


SQL>

No, we are stuck with SYSTEM; this is bad news. Ideally we should be able to have an Oracle database with just SYS and no other users and no PUBLIC grants; but that is never going to happen. It should and then we have a chance of Least Rights in our databases.

OK, more soon!

Bye from WFH

Setting Users Impossible Passwords BY VALUES and Schema Only Accounts

I plan to try and write some Oracle security based blog posts whilst working from home. These promises when I have made them in the past usually end up not coming true due to other work and things getting more priority. But; I will try as it's good to spend a little time in research and writing and sharing in this important time. I am busy with work, so that's also good but I would like to try and find some time and share some Oracle Security knowledge to somehow help people (Oracle and Security people) stuck at home to at least learn something or at least be entertained a bit.

I do have a massive list of potential blog post subjects and topics that I always collect and write down. Some I have already started some research and tests and examples and some are just titles for now. As you will have seen I have blogged more in the last couple of years than I did for some time; I want to try and do more and more regularly and get the knowledge out there.

So here goes for todays subject. A guy from the USA sent me a question. He said in Oracle 11g he used syntax such as:

SQL> connect system/oracle1@//192.168.56.85:1521/bfora.localdomain
Connected.
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> alter user pete identified by values 'LOCKED';

User altered.

SQL>

He said when he does this it creates a value in SYS.USER$.PASSWORD that can never be hashed to a real hex value so the account cannot be logged into if it is open. He asked if his thinking is correct or flawed.

Yes, this is the most famous undocumented feature of Oracle and I have been telling people to do this with accounts for 20 years or more to stop them from being logged into. In more recent times (since proxy was added to Oracle) this is a great tool to use make sure that a user does not log into a schema directly. So we do not give the schema passwords to developers / DBAs or release teams. Instead the schema remains inaccessible directly and instead the support or release is done via a proxy account. In this way no one logs onto schemas but code can still be run and installed to create objects such as tables, views, PL/SQL code. We can even enable audit targeted at the support person or release person using their proxy and audit every action whilst not auditing every action as the schema when not proxied.

But the syntax he showed and used in 11g is actually the syntax for 10g and earlier as the syntax in the BY VALUES clause didn't include the SHA1 password hash. We can see that it did work:

SQL> connect sys/oracle1@//192.168.56.85:1521/bfora.localdomain as sysdba
Connected.
SQL> select name,password,spare4 from sys.user$ where name='PETE';

NAME PASSWORD
------------------------------ ------------------------------
SPARE4
--------------------------------------------------------------------------------
PETE LOCKED


SQL>

But it is not strictly correct as we should also have the SHA1 hash in SYS.USER$.SPARE4 but it works still in 11g. The questioner said if the method is correct then he gets an error in 12c when he uses the same syntax. Lets try in 18c:

SQL> connect system/oracle1@//192.168.56.78:1523/xepdb1
Connected.
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

SQL>

Lets create a sample user to test with:

SQL> create user pete identified by pete;

User created.

SQL>

Now try and use the old syntax as the questioner said he used it in 12c and got an error:

SQL> alter user pete identified by values 'LOCKED';
alter user pete identified by values 'LOCKED'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string


SQL>

Yes, we get the same issue that the questioner said he got in 12c. He asked is this still possible from 12c as he felt its more secure to stop connections as schemas. I agree, its better to never connect as the schema so this is a method BUT 18c and 19c have a new way. Lets look at the old way first. The BY VALUES clause failed because it doesn't include the later password hashes. So instead we can use DBMS_METADATA.GET_DDL to get the right syntax:

SQL> set serveroutput on
SQL> begin
2 dbms_output.put_line(dbms_metadata.get_ddl('USER','PETE'));
3 end;
4 /

CREATE USER "PETE" IDENTIFIED BY VALUES
'S:03E63AA057AE0C4C55D184E9BF44719C43ABBC5E8B8A5C8FE06ECAFE9EDA;T:BC005789B76E3B
7E1BCD1DFDEF98B04C8DA8B1DC02170444001A59092242301AA18D93BD99D9E60187BD38DDE9A274
21C0FB77C72166B53A27199F17203E7D8B69FCD1E1F8EE26A1CA57D8BD9ED78D97'

DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"

PL/SQL procedure successfully completed.

SQL>

This gives me the right syntax. I can modify the statement above and change CREATE USER to ALTER USER and also change the hashes to 0000... instead. I can run this as follows:

SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:000000000000000000000000000000000000000000000000000000000000;T:0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000';

User altered.

SQL>

What is stored in sys.user$:

SQL> connect sys/oracle1@//192.168.56.78:1523/xepdb1 as sysdba
Connected.
SQL> select name,password,spare4 from sys.user$ where name='PETE';

NAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
SPARE4
--------------------------------------------------------------------------------
PETE

S:000000000000000000000000000000000000000000000000000000000000;T:000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000


SQL>

Can we instead change the string to LOCKED or similar as with the older syntax:

SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:LOCKED;T:LOCKED';
ALTER USER "PETE" IDENTIFIED BY VALUES 'S:LOCKED;T:LOCKED'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string


SQL>

No, it seems not, maybe we can use LOCKED as a string but match the length of the original strings, 60 characters for the SHA1 S: string and 160 characters for the SHA2 T: string:

SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:LOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKED;T:LOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCK';
ALTER USER "PETE" IDENTIFIED BY VALUES 'S:LOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKED;T:LOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCK'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string


SQL>

No, that doesn't work, what if we just change the zeros to a HEX letter such as A:

SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;T:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';

User altered.

SQL>

So the number uses is most likely tested to see if it is a valid HEX string. Lets change one letter to an invalid hex string and see what happens:

SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:GAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;T:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
ALTER USER "PETE" IDENTIFIED BY VALUES 'S:GAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;T:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string


SQL>

I changed the first letter to G of the SHA1 hash and the error comes back. So lets try one more test. If we change the SHA1 hash to 59 characters BUT valid HEX what happens:

SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;T:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
ALTER USER "PETE" IDENTIFIED BY VALUES 'S:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;T:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string


SQL>

So, in summary, the old BY VALUES still works in 12c, 18c, 19c BUT its much stricter than the methods in 11g where you could type in any old text as an invalid password. Now you must supply a string for the SHA1 and SHA2 hashes that are the correct length, 60 characters and 160 characters and also the hash value must be valid HEX. So if you use this method set the strings to valid HEX, BUT that presumably means that because its valid hex then its maybe possible to enter some password that would hash to that HEX value. The chances of someone guessing a password combination that would hash to the HEX value you chose is pretty pretty slim.

In 18c and 19c there is now a better method. The BY VALUES is in effect now built into the DDL syntax with the NO AUTHENTICATION syntax. This is called "SCHEMA ONLY ACCOUNTS" in Oracle and is a new welcome shortcut. We can test this for my sample user PETE in 18cXE:

SQL> alter user pete no authentication;

User altered.

SQL>

The values in SYS.USER$ are:

SQL> select name,password,spare4 from sys.user$ where name='PETE';

NAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
SPARE4
--------------------------------------------------------------------------------
PETE

S:000000000000000000000000000000000000000057C9FE8ED313BD2F8D26;T:000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000104D5ED049FF791913E7242803485640


SQL>

The values are not all zeros. For instance the last 20 characters of the SHA1 hash are the SALT. A simple SQL script called sha1.sql on my site from 2007 shows how this password algorithm works and shows that the SALT is the last 20 characters of this hash. This is interesting and if you chose to use the BY VALUES instead of this feature in 18c or 19c or indeed 20c then it would make sense to generate a random SALT also and not just use zeros. If you are on 12.1 or 12.2 then the NO AUTHENTICATION syntax is not there anyway. Why not just use all ZEROs or all 99999s or AAAAs or whatever. Well if everyone just used all 000s then it may be possible that someone at some point could brute force inputs (possible passwords) and crack or find a password that could hash to 60 zeros or 60 AAAs or whatever. A random SALT would prevent that pre-defined hash/password problem. Also don't use DBMS_METADATA.GET_DDL and use the same SYS.USER$.SPARE4 values from one database for all passwords in another.

Any schema in your database should have one of these impossible passwords or from 18c a SCHEMA ONLY ACCOUNT - the same thing really.

More on this soon!! Bye from WFH (Working From Home)