Call: +44 (0)7759 277220 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.

April 2009 CPU is out

Oracle Corp. issued 43 fixes Tuesday as part of its quarterly Critical Patch Update, repairing flaws in its database management system, application server and application product lines.


"Oracle issues 43 updates, fixes serious database flaws"

Oracle's advisory is here, there are 16 new fixes for the database and two of them can be remotely exploited without authentication (no username or password required) over the network.

Undocumented Oracle - Using ENUM's in PL/SQL

I was asked by a colleague a couple of weeks ago if it was possible to create ENUM's in PL/SQL like its possible to create in languages such as C. The actual example the person emailed me is too business/market specific for his company and I don't want to repeat it here as I don't want to give away who he works for without his permission so I will use a simpler examples.

Because I have some knowledge of PL/SQL my thoughts turned to TYPEs as it should be possible to create a TYPE that is in effect a constrained TYPE or an enumerated type. A simple example would be the BOOLEAN type in PL/SQL that is limited to values of TRUE and FALSE:




SQL> declare
2 pv_var boolean;
3 begin
4 pv_var:=TRUE;
5 pv_var:=FALSE;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL>




That works as designed but what if we tried to assign a different value?




SQL> declare
2 pv_var boolean;
3 begin
4 pv_var:=7;
5 end;
6 /
pv_var:=7;
*
ERROR at line 4:
ORA-06550: line 4, column 9:
PLS-00382: expression is of wrong type
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored


SQL> declare
2 pv_var boolean;
3 begin
4 pv_var:='NOT';
5 end;
6 /
pv_var:='NOT';
*
ERROR at line 4:
ORA-06550: line 4, column 9:
PLS-00382: expression is of wrong type
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored


SQL>




That doesn't work as the values are constrained - great. What is interesting is that we are always allowed to set the variable to NULL so in essence the TWO value ENUM has a three value set of possible values, TRUE, FALSE and NULL:



SQL> declare
2 pv_var boolean;
3 begin
4 pv_var:=NULL;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL>



The BOOLEAN data value is defined in the standard.sql (stdspec.sql and stdbody.sql) files as:



type BOOLEAN is (FALSE, TRUE);



Now that looks exactly like whats needed for my colleague. Indeed the ADA language that PL/SQL is based on supports enumerations in the same format as the BOOLEAN type is supported in PL/SQL in the STANDARD PACKAGE. Can we then use this same syntax in PL/SQL for instance:



SQL> declare
2 type colors is (RED, GREEN, BLUE, YELLOW);
3 begin
4 null;
5 end;
6 /
type colors is (RED, GREEN, BLUE, YELLOW);
*
ERROR at line 2:
ORA-06550: line 2, column 16:
PLS-00505: User Defined Types may only be defined as PLSQL Tables or Records
ORA-06550: line 2, column 1:
PL/SQL: Item ignored


SQL>



The above simple test shows that its not possible for us to create ENUMs in PL/SQL like we can do in ADA. i.e. we can only create TYPEs that are tables or records. This is annoying but also an illustration of undocumented Oracle as Oracle themselves use the TYPE syntax to create an enumeration type in the STANDARD package but dont allow us to do the same. Clearly this syntax does compile if the code is within the standard package but not anywhere else so it is possible (but clearly not recommended or advised as it would affect support/warranty) to add new enumerations to the standard package. Oracle must have a check in the compiler that forces error PLS-00505 if the TYPE is an enumerator and not in the STANDARD package. I am also logged in AS SYSDBA above so its not the user that allows this but the location (STANDARD PACKAGE).

Why do Oracle use syntax available to them only in the STANDARD package and not available to us? - well, my educated guess would be that they have only implemented this syntax in a very narrow way, i.e. to fulfill a particular case and not much more. They must have made sure it compiles the BOOLEAN correctly but not tested or implemented much else hence we cannot use it. This is also a reason not to simply add your own enums to the standard package as they may compile but the results are likely to be undefined in some cases where the testing didn't iron out the rules properly.

It is quite interesting that Oracle constrain (or create an enumeration) a TYPE using the TYPE syntax as ADA does when Oracle also supports the SUBTYPE syntax to provide constrained and un-constrained types. An unconstrained type is really just an ALIAS for another type. See the STANDARD package for some examples. A constrained type is a type that limits the numeric values (which are allowed in user PL/SQL space; what is not allowed it seems is the connection between ENUM identifiers which in the C language would be numercially associated and indeed C implements the enum values as integers) and numeric constrains. This leads me to the idea that an ENUM can be emulated via CONSTANTS and SUBTYPES and this idea is what I have passed on to my colleague:



SQL> declare
2 RED constant number(1):=1;
3 GREEN constant number(1):=2;
4 BLUE constant number(1):=3;
5 YELLOW constant number(1):=4;
6 --
7 VIOLET constant number(1):=7;
8 --
9 subtype colors is binary_integer range 1..4;
10 --
11 pv_var colors;
12 begin
13 pv_var:=YELLOW;
14 end;
SQL> /

PL/SQL procedure successfully completed.

SQL> 13
13* pv_var:=YELLOW;
SQL> c/YELLOW/VIOLET/
13* pv_var:=VIOLET;
SQL> /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 13


SQL>



As you can see we create a set of CONSTANT values that represent the values of the ENUM and then create a SUBTYPE that constrains the values of any variable of this SUBTYPE to these values.

This is not a perfect solution as we cannot do TYPE BOOLEAN IS (TRUE,FALSE) as Oracle does in the standard package or indeed TYPE COLORS IS (RED,GREEN,BLUE,YELLOW) as we can in ADA but its close; there are also issues around using this across multiple peices of code as ideally we would write this once which would inevitably cause us to need to do PACKAGE.CONSTANT. What is missing is the tie between the words (enums) and the type but what we have here is almost what C does, except C does the mapping between say YELLOW and 4 in the bacground for us and allows us to write YELLOW without seperately defining it. To illustrate the use of this idea to my colleague I created a simple example program that allows the use of the "enum" as a parameter, return type of a function etc, basically in a similar context as I would use an enum in my C programs.



SQL> declare
2 RED constant number(1):=1;
3 GREEN constant number(1):=2;
4 BLUE constant number(1):=3;
5 YELLOW constant number(1):=4;
6 --
7 VIOLET constant number(1):=7;
8 --
9 subtype colors is binary_integer range 1..4;
10 --
11 pv_var colors;
12 --
13 function test_a (pv_var1 in colors) return colors
14 is
15 begin
16 if(pv_var1 = YELLOW) then
17 return(BLUE);
18 else
19 return(RED);
20 end if;
21 end;
22 --
23 begin
24 pv_var:=test_a(YELLOW);
25 if (pv_var=YELLOW) then
26 dbms_output.put_line('YELLOW');
27 elsif(pv_var=RED) then
28 dbms_output.put_line('RED');
29 elsif(pv_var=BLUE) then
30 dbms_output.put_line('BLUE');
31 elsif(pv_var=GREEN) then
32 dbms_output.put_line('GREEN');
33 else
34 dbms_output.put_line('UN-KNOWN');
35 end if;
36 end;
37 /
BLUE

PL/SQL procedure successfully completed.

SQL>



OK, enough of ENUM's in PL/SQL for now. I like to look into the internals of Oracle and in particular PL/SQL and in the standard package there are lots of other gems such as the definition of VARCHAR2 which follows the similar syntax used in ADA to define a new type. In ADA we can do:



TYPE BOOL IS NEW BOOLEAN;
mybool: BOOL




Which is similar syntax to PL/SQL but again we only find this syntax used in the standard package. There are 10 occurances of this syntax in the shipped SQL code of the 11g database all of which use new occurances of CHAR_BASE or DATE_BASE. If we try and use the same syntax in user space code as follows we get:



SQL> declare
2 type mychar is new char_base;
3 begin
4 null;
5 end;
SQL> /
type mychar is new char_base;
*
ERROR at line 2:
ORA-06550: line 2, column 16:
PLS-00504: type CHAR_BASE may not be used outside of package STANDARD
ORA-06550: line 2, column 1:
PL/SQL: Item ignored


SQL>



Interesting, these are undocumented language features of PL/SQL that come as standard from ADA but we are not allowed to use them ourselves in our PL/SQL code. It is interesting as I said to delve into the code shipped by Oracle to see what they use and do that we are not allowed to, because in some cases we may actually extend our knowledge and maybe we will find a feature that does work and is useful in some cases. For me it's definetely about the learning more about how Oracle works. Have fun!

New Oracle Security book out

I received a copy of Ron Ben Natan's new book "How To Secure and audit Oracle 10g and 11g" (The link is Amazon.co.uk because I am in the UK, you can find the book on Amazon.com from the author name or ISBN) last week. I wanted to find time to mention Ron's book last week but travelling for work meant I had little spare time at all.

This is a good book, I like the format of "howtos", i like the way it concentrates particularly on the audit trails sections (note the audit in the title refers to audit trail rather than security auditing) on the functionallity available from Oracle. This is important as still most sites I visit do not employ audit trails in the database itself. Often sites use auditing in the database but for application level activities. It is important that sites audit database activity with the same vigour as application level auditing and understanding whats available with the database is a good start to set up and run and use something.

The book is focused at a hardening level and covers various privilege and access issues. It also covers encryption and authentication and of course auditing solutions. It also covers the new technologies/products from Oracle; Audit vault and Database vault.

This is a good book.

Oracle Security training in Edinburgh with Pete Finnigan

I am going to be in Edinburgh on April 21st and April 22nd to teach my popular two day class "How to perform a security audit of an Oracle database" with PiSec Limited. This class is great for all types of skills from Security auditors to DBA's, to CISO's to developers. I have taught all of these types of people in many countries around the world and all have enthused about the skills learned and developed. The class takes you through the process of performing a security audit of an Oracle database from threats, exploits, planning, building toolkits, through a complete althrough of a sample audit on a real Oracle database, finally through to analysis, write up and next steps. The course is filled with real world experience from someone with real world experience in this field. The key focus is on understanding the data and flow of the data so that its possible to develop a policy and implement it to secure all of your databases.

You can register for this course with PiSec Limited Registration page. Places are limited so please hurry to show your interest. I look forward to seeing you all in Edinburgh.

A database installed version of who has privilege script

I have released a number of scripts to my site for creating heirarchical reports of privileges. These include find_all_privs.sql, who_has_priv.sql, who_can_access.sql, who_has_role.sql and check_parameter.sql. These are standalone sqlplus scripts. I was asked a week or so ago about whether these scripts can be installed in the database as packages or functions of procedures. So i spent ten minutes or so and made the who_has_priv script work in the database as a procedure. The converted script is available as who_has_priv_procedure.sql and its also available on my Oracle Security Tools page.

A simple session showing how the script is installed is shown here:




SQL> connect system/manager
Connected.
SQL> create user priv identified by priv;

User created.

SQL> grant create session, create procedure, select any dictionary to priv;

Grant succeeded.

SQL> connect priv/priv
Connected.
SQL> @who_has_priv_procedure

Procedure created.

SQL>



Next here are two simple executions of the procedure in the database to see how it works:



SQL> set serveroutput on size 1000000
SQL> exec who_has_priv('ALTER SESSION');
who_has_priv: Release 1.0.1.0.0 - Production on Thu Mar 19 12:15:53 2009
Copyright (c) 2009 PeteFinnigan.com Limited. All rights reserved.
Privilege => ALTER SESSION has been granted to =>
====================================================================
Role => DBA (ADM = YES) which is granted to =>
User => SYS (ADM = YES)
User => SYSMAN (ADM = NO)
User => C (ADM = NO)
User => B1 (ADM = NO)
User => SYSTEM (ADM = YES)
User => SYS (ADM = NO)
User => IX (ADM = NO)
User => SH (ADM = NO)
User => PP (ADM = NO)
Role => RECOVERY_CATALOG_OWNER (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => OWBSYS (ADM = YES)
User => BI (ADM = NO)
User => MONITOR (ADM = NO)
User => CTXSYS (ADM = NO)
Role => OWB$CLIENT (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => OWBSYS (ADM = YES)
User => EVILUSER (ADM = NO)
User => SCOTT (ADM = NO)
User => SYSMAN (ADM = NO)
User => FLOWS_030000 (ADM = NO)
User => HR (ADM = NO)
User => XDB (ADM = NO)
For updates please visit http://www.petefinnigan.com/tools.htm



PL/SQL procedure successfully completed.

SQL> exec who_has_priv('GRANT ANY PRIVILEGE');
who_has_priv: Release 1.0.1.0.0 - Production on Thu Mar 19 12:22:36 2009
Copyright (c) 2009 PeteFinnigan.com Limited. All rights reserved.
Privilege => GRANT ANY PRIVILEGE has been granted to =>
====================================================================
Role => IMP_FULL_DATABASE (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => WKSYS (ADM = NO)
Role => DBA (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => SYSMAN (ADM = NO)
User => C (ADM = NO)
User => B1 (ADM = NO)
User => SYSTEM (ADM = YES)
Role => DATAPUMP_IMP_FULL_DATABASE (ADM = NO) which is
granted t
o =>
Role => DBA (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => SYSMAN (ADM = NO)
User => C (ADM = NO)
User => B1 (ADM = NO)
User => SYSTEM (ADM = YES)
User => SYS (ADM = YES)
Role => DATAPUMP_IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => SYSMAN (ADM = NO)
User => C (ADM = NO)
User => B1 (ADM = NO)
User => SYSTEM (ADM = YES)
User => SYS (ADM = YES)
User => SYS (ADM = NO)
Role => DBA (ADM = YES) which is granted to =>
User => SYS (ADM = YES)
User => SYSMAN (ADM = NO)
User => C (ADM = NO)
User => B1 (ADM = NO)
User => SYSTEM (ADM = YES)
User => WKSYS (ADM = NO)
User => PP (ADM = NO)
For updates please visit http://www.petefinnigan.com/tools.htm



PL/SQL procedure successfully completed.

SQL>



Download the script and install if its useful. Beware that the original thinking around why these scripts are sqlplus scripts and not packages is that its better to not install security tools within the database unless they are well controlled. This is because a security tool is useful not only to the DBA who wants to secure his database but also to the person who wants to find out the weaknesses.

If anyone would like the other scripts mentioned above to install into the database as procedures, let me know via a comment and I will modify them and make them available