Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
There are 68 visitors online    
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.

Pete Finnigan's Oracle security weblog


Home » Archives » April 2009 » Undocumented Oracle - Using ENUM's in PL/SQL

[Previous entry: "New Oracle Security book out"] [Next entry: "April 2009 CPU is out"]

Undocumented Oracle - Using ENUM's in PL/SQL

April 14th, 2009 by Pete


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!

April 2009
SMTWTFS
   1234
567891011
12131415161718
19202122232425
2627282930  

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.

Weblog Home
Weblog Archives


Home
Oracle Security Tools page
Oracle security papers
Oracle Security alerts

Web Development
SQL Server Security

RSS 1.0 FEED
RSS 2.0 FEED
Atom 0.3 FEED
Powered by gm-rss 2.0.0


Valid XHTML 1.0!