Call: +44 (0)1904 557620 Call

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.

Belated Christmas wishes and a happy new year to all readers

Well it has been a while since my last blog entry - almost two weeks in fact, xmas added onto heavy work loads all gets in the way of blogging...:-)

I still have a backlog of things to blog about so watch out for these in the new year, i cannot promise to blog much between now and next week as I have a lot of work to fit in as well as the new years celebrations!!

I just saw Marcel-Jan's post on my Oracle Security Forum titled "Twitter banned passwords" and it piqued my interest. It is not Oracle security related but it is relevant and not because I also use twitter but because its a good idea. When setting a new password the system should stop you from setting stupid passwords no matter the system. The Oracle database can also do this for you BUT you need to do the work to set it up. The check can be added as part of the password management in profiles; specifically by defining a password verification or complexity function that includes rules to stop weak passwords being set. The standard function supplied by Oracle includes a simple if(password==bad val1 or password==bad val2.... type code.

I usually recommend my clients to create a more proper complexity function that matches the password policies defined in their own access and use policies. This would normally include length, character set, change requirements, lack of simple passwords etc. One of the things we do is to build a table in the database containing all the weak passwords and then the password complexity function checks that any new password does not exist in this table. This allows a much much bigger list of passwords to check against that should not be used.

This is one of the problems with the Oracle database; this stuff is not turned on by default. Simply, you need to do the work. This is a recurring factor of Oracle security; Oracle security is not simply about following checklists or applying CPU's (Critical Patch Updates), it's a lot more. Securing a database is much more about the detail and the configuration. The other problem is that in databases a solution is often not simple. A weak password is a good example. It sounds simple; set it to a strong value but its more than that, you also need to define strong and then enforce strong, you should also limit the chance of abuse and also record abuse attempts. This means setting password management, complexity, fixing the password, audit and more. Complex but complete solutions are needed. Back to the post by Marcel-Jan; this post shows good advice from Twitter, you should use the same ideas in your Oracle databases but remember you need to do the work. That doesn't mean that you cannot steal the material from twitter's article wink, why not take the list of common passwords for twitter. Just because they are common for twitter doesnt mean that they are not common for all other platforms. People are people, are people; and they choose passwords not the software they are used on; this is where we come in and make the software help choose or rather stop some choices from being made. The article Marcel-Jan references is titled "370 Passwords You Shouldn’t (And Can’t) Use On Twitter" and the password list is here.

The Oak Table book should be off to print

I got my two chapters of the new Oak Table book "Expert Oracle Practices: Oracle Database Administration from the Oak Table" as final proofs from Apress today for a final check and send back; the book should be off to the printers tomorrow so should be out very soon. I have seen pdf's of my final chapters of course but not anyone elses. I am looking forward to seeing the complete book and reading it. The first Oak Table book was excellent and I expect the same and more from this one. Great, should be a slightly late xmas present; Amazon says its due out on 28th December.

Announcement: Oracle Security Training in Washington DC, March 25-26 2010

In conjunction with our recent partnership with - (broken link) 1 Security Solutions Applied we are happy to announce a new public training event which is run by both companies. We are offering an oppertunity for students to experience the Limited two day seminar - (broken link) How to security audit an Oracle database. The class will be held in the Washington DC area on March 25th and March 26th 2010.

There is currently an early bird price of $1,199 and interest in the class is strong. Please - (broken link) register your intentions to attend here and if you would like any further information please feel free to contact me here or 1 Security Solutions Applied. My contact email is on my contact page and ! Security Solutions Applied is on theirs. This is a great oppertunity to learn the techniques used by experts in auditing and securing Oracle databases.

Buying books, writing books and uploading slides

Wow, its been around a week since i last wrote here. I was on client work all last week, indeed i have been snowed under for quite a while so finding time for blogging is hard. I had promised myself to find half an hour most days and did this for most of November but this has fallen by the wayside due to work and company business. I am going to try and keep to this promise though over coming weeks; I do have a growing list of things I would like to cover here.

The week before last I was in Birmingham at the UKOUG conference speaking on the Monday to quite a well attended room - which was good as i was on at the same time as Steven Feuerstein and Jonathan Lewis (two seperate talks by them of course). The talk went well, at least I thought sosmile. I also then chaired the Oracle security round table session which had some good discussions but was down on attendance over pervious years; not sure if thats a clash with other sessions or simply that there were less people at the conference this year.

I was in London last week teaching for a client and I had planned to meet Paul Wright for a chat but he had man flu and was at home in bed so was unable to meet up. So i ended up catching a tube to Leicester Square and wandering around down Charing Cross Road and Tottenham Court Road so obviously I ended up in Foyles book shop. I picked up a copy of Norbert Debes book published by Apress "Secrets of the Oracle Database (Expert's Voice in Oracle) " and also Tim Halls book "Oracle Job Scheduling: Creating Robust Task Management with dbms_job and Oracle 10g dbms_scheduler " which was very cheap, eleven quid. I have niot read either yet but i am aware of Norberts book and had seen some of the content before it became an Apress book as Norbert asked me to review and write the forward but at the time i simply just could not fit it in but it was nice to see that Norbert thanked me in the acknowledgements. I always like Tim's writings, he is very thourough and his writing style is excellent always with good clear examples. I will need to find time to go through each book now.

I also spent an hour yesterday evening after work sorting the code downloads for the new book - Expert Oracle Practices: Oracle Database Administration from the Oak Table which is due for release soon. I am looking forward to seeing it in print.

I have uploaded the slides for my talk at the UKOUG in Birmingham recently. As usual the slides are available from my Oracle Security white papers page.

Dennis has released a paper describing his FPGA cracker

I got an email from Dennis Yurichev at the beginning of last week with an article he has written about his FPGA based password cracker explaining how he created it, what tools are hardware are used and how at a high level the algorithms have been implemented. Dennis's paper titled "How to create FPGA-based Oracle RDBMS cracker that works in average 30-40 times faster [1] than password crackers on Intel Core Duo 2." is available to read and is very interesting.

Unwrapping PL/SQL

There was an interesting questuion on my forum a couple of days ago titled " its legal to host unwrap?"; bear in mind the title means the opposite, i.e. is it illegal not its illegal. These semantics do not alter the question though! The poster wants to host a free unwrapper, its an interesting question that is already answered at one level. i.e. someone else has already done it.

There is already a Swiss site that hosts a 10g unwrapper for free - - I am myself unsure what the legal position would be in hosting an unwrapper as you would have no control over what people could unwrap. If you unwrap privately for clients as a paid service then as Gary suggests in his reply in the forum you can put in place contracts where the client has to show that he has legal ownership of the code he wants unwrapping but as the poster suggests he wants to host a free service. A contracted service which is of course more controlled is then a source recovery service and companies do have a genuine need for this where they have lost the original source code. Just because someone else has done it already doesnt make it legal!

I was at the UKOUG conference Monday and Tuesday and with clients yesterday and today so had littlke time to blog but one of the things I was going to talk about as it happens was unwrapping as I was cornered twice at the UKOUG conference by people asking me about unwrapping and the paper I wrote a few years ago for the How to unwrap PL/SQL that i presented at Black Hat in Las Vegas. Of course more recently Anton made available some details on how to unwrap 10g PL/SQL on his blog. David also talked about unwrapping in his Oracle Hackers Handbook - book, interestingly he had a view on the legality in that he refrained from publishing the lookup table used in the wrap process but this was actually about trade secrets and reverse engineering and not about using an unwrapper. I published a simple demo unwrapper that used the DIANA and PIDL packages to show how Oracle unwraps as part of the pstub code used for remote PL/SQL calls. This needs to work with wrapped and unwrapped code hence the need for it to work with DIANA. This is how 9i wrap works, 10g is different but both still use DIANA under the hood of course. The code is called unwrap_r.sql but it wont unwrap anything real as its simply demonstrating the use of DIANA and PIDL and those mechanisms only expose the signatures of packages and nothing else.

I also have unwrappers for 10g and 9i and lower completely written in PL/SQL of course. Here is a little demo of it running on some 9i PL/SQL code. First create a simple procedure to use for this test case. The code is just made up for this experiment and is very simple:

SQL> get sample1.sql
1 create or replace procedure test_proc (pv_num in number,
2 pv_var in varchar2, pv_var3 in out integer) is
3 l_num number:=3;
4 l_var number;
5 j number:=1;
6 procedure nested (pv_len in out number)
7 is
8 x number;
9 begin
10 x:=pv_len*5;
11 end;
12 begin
13 case l_num
14 when 1 then
15 -- IF 1
16 l_var:=3;
17 dbms_output.put_line('This is a header');
18 dbms_output.put_line('The number is '||l_var);
19 dbms_output.put_line('The case var is '||l_num);
20 when 2 then
21 -- IF 2
22 l_var:=4;
23 dbms_output.put_line('This is a header');
24 dbms_output.put_line('The number is '||l_var);
25 dbms_output.put_line('The case var is '||l_num);
26 when 3 then
27 -- IF 3
28 l_var:=6;
29 dbms_output.put_line('This is a header');
30 dbms_output.put_line('The number is '||l_var);
31 dbms_output.put_line('The case var is '||l_num);
32 else
33 dbms_output.put_line('wrong choice');
34 end case;
35 if ((j=1) and (j=3)) then
36 dbms_output.put_line('here is IF');
37 elsif ((j=2) or (j!=3)) then
38 dbms_output.put_line('The elsif clause');
39 else
40 dbms_output.put_line('else clause');
41 end if;
42 j:=4;
43 nested(j);
44 dbms_output.put_line('nested=:'||j);
45 for j in reverse 1..pv_num
46 loop
47 if mod(j,2) = 0 then
48 dbms_output.put_line('for loop with reverse');
49 end if;
50 end loop;
51* end;

I can then wrap this with the 9i wrap utility:

C:\unwrapper>wrap iname=sample1.sql oname=sample1.plb

PL/SQL Wrapper: Release Production on Mon Jun 01 14:02:34 2009

Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.

Processing sample1.sql to sample1.plb

C:\unwrapper>head sample1.plb

Then I can show it is indeed wrapped by viewing the contents (Note the above commands are in a DOS box, the head command is on the same machine but from cygwin as the head command is available:

$ head -20 sample1.plb
create or replace procedure test_proc wrapped

Now load the wrapped file into an 11gR1 database and check its stored wrapped:

SQL> @sample1.plb

Procedure created.

SQL> select substr(text,1,60)
2 from dba_source
3 where name='TEST_PROC'
4 and rownum=1;

procedure test_proc wrapped


Now we can simply unwrap it using my PL/SQL based unwrapper:

SQL> @unwrap_c

unwrap_c: Release - Production on Mon Jun 01 14:07:13 2009
Copyright (c) 2008, 2009 Limited. All rights reserved.

OUTPUT METHOD Screen/File [S]: S

create or replace procedure TEST_PROC( PV_NUM in NUMBER,
procedure NESTED( PV_LEN in out NUMBER) is
X:= PV_LEN * 5;
case L_NUM
when 1 then
DBMS_OUTPUT. PUT_LINE('This is a header');
DBMS_OUTPUT. PUT_LINE('The number is ' || L_VAR);
DBMS_OUTPUT. PUT_LINE('The case var is ' || L_NUM);
when 2 then
DBMS_OUTPUT. PUT_LINE('This is a header');
DBMS_OUTPUT. PUT_LINE('The number is ' || L_VAR);
DBMS_OUTPUT. PUT_LINE('The case var is ' || L_NUM);
when 3 then
DBMS_OUTPUT. PUT_LINE('This is a header');
DBMS_OUTPUT. PUT_LINE('The number is ' || L_VAR);
DBMS_OUTPUT. PUT_LINE('The case var is ' || L_NUM);
DBMS_OUTPUT. PUT_LINE('wrong choice');
end case;
if ( ( J = 1) and ( J = 3)) then
elsif ( ( J = 2) or ( J != 3)) then
DBMS_OUTPUT. PUT_LINE('The elsif clause');
DBMS_OUTPUT. PUT_LINE('else clause');
end if;
DBMS_OUTPUT. PUT_LINE('nested=:' || J);
for J in reverse 1.. PV_NUM loop
if MOD( J,2) = 0 then
DBMS_OUTPUT. PUT_LINE('for loop with reverse');
end if;
end loop;

INFO: Elapsed time = [.1 Seconds]

PL/SQL procedure successfully completed.

For more information please visit


This gives a 100% source code recovery for 9i and lower PL/SQL code and I have mechanisms built in that can prove this even if the original source code was lost. For 10g and 11g the algorithm is simpler and if its implemented right it will always give a complete source recovery. For 9i this (100% source recovery) is much harder to achieve as the method of wrapping is much more complex - see my Black Hat paper above for details of why its harder.