Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

Oracle Security on Windows presentation uploaded

Yesterday I spoke at the UKOUG Windows SIG in Blythe Valley Park near Birmingham. My presentation was around securing Oracle on a Windows platform. I have uploaded my presentation to my Oracle security white papers page. There are two versions, a 1.8 Meg pdf that has one slide per page and a smaller 600k pdf that has six slides per page.

September 2007 - 3 years of Oracle security blogging

Just a short note tonight. It was three years ago last Thursday that i started to write this Oracle security blog. It seems to be a sort of tradition to make an anniversary post; so hence this post tonight.

The blog is still going strong, despite having been very busy last year I still managed to post around 220 entries which is not bad, I am still year-on-year slowing down with the number of posts but as per last year I still think the subject is not dead, in fact the opposite, there is still plently varied subjects to post about around the world of Oracle security.

When i started this blog I was getting around 360 visits per day and approximately 120,000 visits per year and around 250,000 page views per year. That has grown to an average of around 4800 visits per day, peaking at over 6500 on some weekdays. I got around 1.5 Million visitors last year and around 4 million page views, so its still growing and is slowing slightly based on the start up curve but thats to be expected.

The highlights this year? - probably just recently for me was re-starting my own company - PeteFinnigan.com Limited to specialise in Oracle security audits, consulting and training.

Thanks to everyone who has visited my blog over the last three years, I hope it was worth a visit or two!

Oracle 11g Password algorithm revealed

I have posted a few entries in this blog about the new Oracle 11g features and I have concentrated on the new SHA1 11g password algorithm. I made a post titled "Oracle 11g Security - part 5 {Playing for time}" on wednesday - a comment was added on Thursday the 20th by Niels to reveal the algorithm which i already knew. Niels example is in PHP.

The algorithm is very simple and easy to guess. Once you realise that the SHA1 hash stored in SYS.USER$.SPARE4 is too long. I mentioned this before here in this blog. The SHA1 verifier should be 160 bits but the spare4 column holds S: and a further 240 bits of hash. It was clear from some investigations that the password function used a salt and also it was obvious that the salt is stored also in SPARE4. the data is even prepended by S:. It then becomes a simple guess as to whether the algorithm is SHA1(userpwdsalt), SHA1(saltuserpwd), SHA1(pwdsalt) or SHA1(saltpwd) a few experiements with PL/SQL and DBMS_CRYPTO reveals the result.

The algorithm is simple. This can be described as:

sys.user$spare4 = SHA1(pwd concat with salt) contat with salt

or to test

substr(spare4,3,40) = SHA1(pwd concat with substr(spare4,43,10)

Here is a simple test case to show how to verify the Oracle 11gR1 password algorithm:


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> connect sys@ora11g as sysdba
Enter password: ******
Connected.
SQL> create user g identified by g;

User created.

SQL> @c:\11g_notes\sha1.sql
NAME OF USER TO CHECK [system]: g
PWD to test [manager]: g
PWD found

PL/SQL procedure successfully completed.

SQL>


Then try an invalid password:


SQL> @c:\11g_notes\sha1.sql
NAME OF USER TO CHECK [system]: g
PWD to test [manager]: G
PWD not found

PL/SQL procedure successfully completed.

SQL>


OK, the simple algorithm works. the code in sha1.sql is:


SQL> get c:\11g_notes\sha1.sql
1 set feed on
2 set head on
3 set arraysize 1
4 set space 1
5 set verify off
6 set pages 25
7 set lines 80
8 set termout on
9 set serveroutput on size 1000000
10 undefine user_to_find
11 undefine pwd_guess
12 accept user_to_find char prompt 'NAME OF USER TO CHECK [system]: ' default system
13 accept pwd_guess char prompt 'PWD to test [manager]: ' default manager
14 DECLARE
15 lv_pwd_raw RAW(128);
16 lv_enc_raw RAW(2048);
17 lv_hash_found varchar2(300);
18 cursor c_main(cp_user in varchar2) is
19 select substr(spare4,3,40) hash,
20 substr(spare4,43,20) salt,
21 spare4
22 from sys.user$
23 where name=cp_user;
24 lv_user c_main%rowtype;
25 BEGIN
26 open c_main(upper('&&user_to_find'));
27 fetch c_main into lv_user;
28 close c_main;
29 lv_pwd_raw:= utl_raw.cast_to_raw('&&pwd_guess')||hextoraw(lv_user.salt);
30 lv_enc_raw := sys.dbms_crypto.hash(lv_pwd_raw, 3);
31 lv_hash_found:=utl_raw.cast_to_varchar2(lv_enc_raw);
32 if lv_enc_raw = lv_user.hash then
33 dbms_output.put_line('PWD found');
34 else
35 dbms_output.put_line('PWD not found');
36 end if;
37* END;
38


11g brings case sensitivity but a major flaw exists in that the older 10gR2 and lower algorithm is also still used and the SYS.USER$.PASSWORD column still contains the lower version case insensitive password. As I have discussed here recently there have been improvements to protect the hashes but if the SHA1 hash is available then the older one is also and this makes cracking the passwords via a dictionary or brute force attack easier.

FX has also added a blog entry to his blog titled "Oracle 0xDEADF00D" which details Recurity Labs efforts to crack the Oracle 11g SHA1 Password algorithm using rather more technical methods than I used. its well worth reading how he used IDA Pro, GDB and FindCrypt to locate the function responsible for creating the hash in the Oracle 11g binary on Linux.

Oracle 11g Security - part 5 {Playing for time}

I did a couple more tests on the new password algorithm, its worth having a look at them for interest. They show; along with the previous blog how to start to investigate issues without using deep techniques.

Why are we looking at the password algorithm? - two reasons , we would like to be able to test for weak passwords and also we would like to see if the new algorithm is weaker than the old.

Yesterday we fixed CTIME by using the fixed_date parameter but PTIME is not fixed. One way to check if PTIME is used in the authentication (we cannot easily prove if its used in the salt) is to update SYS.USER$ and see if we can still log in:


SQL> connect system@ora11g
Enter password: ******
Connected.

SQL> set serveroutput on size 1000000
SQL> exec print_table('select * from sys.user$ where name=''Z''');
USER# : 110
NAME : Z
TYPE# : 1
PASSWORD : 319B5E833E3291F7
DATATS# : 4
TEMPTS# : 3
CTIME : 18-sep-2007 17:05:12
PTIME : 18-sep-2007 17:05:12
EXPTIME :
LTIME :
RESOURCE$ : 0
AUDIT$ :
DEFROLE : 1
DEFGRP# :
DEFGRP_SEQ# :
ASTATUS : 0
LCOUNT : 0
DEFSCHCLASS : DEFAULT_CONSUMER_GROUP
EXT_USERNAME :
SPARE1 : 0
SPARE2 :
SPARE3 :
SPARE4 :
Sbig grin5FE25BF206BEFF6BB589C830E111C363A906C191704E35978F32A1F6164
SPARE5 :
SPARE6 :
-----------------

PL/SQL procedure successfully completed.

SQL> update sys.user$ set ptime=to_date('18-sep-2007 18:00:00','DD-MON-YYYY HH24:MI:SS')
2 where name='Z';
update sys.user$ set ptime=to_date('18-sep-2007 18:00:00','DD-MON-YYYY HH24:MI:SS')
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> connect sys@ora11g as sysdba
Enter password: ******
Connected.
SQL> /

1 row updated.

SQL> commit;

Commit complete.

SQL> grant create session to z;

Grant succeeded.

SQL> connect z/z@ora11g
Connected.
SQL>


Ok, so changing the PTIME for user proves that the salt is not read from data in SYS.USER$ as part of the authentication process, it doesn't prove whether PTIME is part of the salt. A second test that used to be useful for pre 11gR1 was to show that a password for a user XX/X and a user X/XX were the same. For 11g we already know that this is not the case but if the salt were based on ptime then the hashes would be the same if user/pwd for X/XX and XX/X produced the same hash. This is a newton raphson issue as we have two or more unknowns, we dont know if PTIME affects the salt/hash and whether XX/X and X/XX should produce the same hash. lets test anyway. How can we get the same PTIME and CTIME for two users?

Run this script:


-- create a number users fast to see if we can get the same PTIME for each
drop user yyyy;
drop user yyy;
drop user yy;
drop user y;

create user yyyy identified by y;
create user yyy identified by yy;
create user yy identified by yyy;
create user y identified by yyyy;

set serveroutput on size 1000000

exec print_table('select name,password,ctime,ptime,spare4 from sys.user$ where name like ''Y%''');


Running gives:


SQL> connect system@ora11g
Enter password: ******
Connected.
SQL> @fast_cr

User dropped.


User dropped.


User dropped.


User dropped.


User created.


User created.


User created.


User created.

NAME : Y
PASSWORD : 25712A8BFC65A418
CTIME : 18-sep-2007 18:50:24
PTIME : 18-sep-2007 18:50:24
SPARE4 :
S:AC003C8C1DB8A7A097C4DE69059ACD3E0FB2F7654C893239029D5359B9BC
-----------------
NAME : YY
PASSWORD : 25712A8BFC65A418
CTIME : 18-sep-2007 18:50:24
PTIME : 18-sep-2007 18:50:24
SPARE4 :
S:FC48CEC1B9204C770E6782AE69BD80F2FC5727BC370B64FEAC5928140091
-----------------
NAME : YYY
PASSWORD : 25712A8BFC65A418
CTIME : 18-sep-2007 18:50:24
PTIME : 18-sep-2007 18:50:24
SPARE4 :
S:C92FBDBAA3B255F2C8F68AD4DCFB575E06EA2F003378AAC91B1F2303F06A
-----------------
NAME : YYYY
PASSWORD : 25712A8BFC65A418
CTIME : 18-sep-2007 18:50:24
PTIME : 18-sep-2007 18:50:24
SPARE4 :
S:3756488C7B6AE75FF4AF4A05047CAE903ABAE8A969A4F2A6A2370A4185A3
-----------------

PL/SQL procedure successfully completed.

SQL>


So all the old passwords are the same, also the CTIME and PTIME are the same for each user but the new hashes are not. Does this mean neither date are involved in the hash or that XX/X does not produce the same password as X/XX. I am sure the latter is true, that XX/X doesn't produce the same password as XX/X as this was a weakness in earlier versions, hashes could in rare circumstances be the same, I am sure Oracle fixed this in 11gR1. Is PTIME involved in the salt? - dont know.

OK, now I will start to look and blog about something different in 11g Oracle Security next.. satisfied

Oracle 11g Security - part 4 {Times and dates and lengths}

I talked last time on Oracle 11g Security about the new password hash algorithm and that there may be a salt involved in the algorithm because the password changes each time its reset to the same value or if the same account is dropped and recreated with the same password. A post on my Oracle Security forum raised an issue as to whether the salt could be the create time of the user or the password time. In the post titled "Oracle 11g Authentication" Gary suggested testing with the fixed_date parameter to change the password and see if the SHA-1 hash remained the same for the same username/password. So I thought I would give it a try. First connect to 11gR1 and set the fixed_date parameter:


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> alter session set nls_date_format='YYYY-MM-DD:hh24:mi:ss';

Session altered.

SQL> alter system set fixed_date='2007-03-04:16:00:00';

System altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2007-03-04:16:00:00


Then create a simple user/password and print out the details:


SQL> create user c identified by c;

User created.

SQL> set serveroutput on size 1000000
SQL> exec print_table('select * from sys.user$ where name=''C''');
USER# : 95
NAME : C
TYPE# : 1
PASSWORD : AEE1788DB3B2F3C3
DATATS# : 4
TEMPTS# : 3
CTIME : 04-mar-2007 16:00:00
PTIME : 17-sep-2007 19:45:57
EXPTIME :
LTIME :
RESOURCE$ : 0
AUDIT$ :
DEFROLE : 1
DEFGRP# :
DEFGRP_SEQ# :
ASTATUS : 0
LCOUNT : 0
DEFSCHCLASS : DEFAULT_CONSUMER_GROUP
EXT_USERNAME :
SPARE1 : 0
SPARE2 :
SPARE3 :
SPARE4 :
S:8C9FD807B8BED0246C2C86C4748CB3700EF68A8642E66C8C0BAC746A9EB7
SPARE5 :
SPARE6 :
-----------------

PL/SQL procedure successfully completed.

SQL>


Alter the users password to the same value and then drop and recreate the user again with the same password:


SQL> alter user c identified by c;

User altered.

SQL> exec print_table('select * from sys.user$ where name=''C''');
USER# : 95
NAME : C
TYPE# : 1
PASSWORD : AEE1788DB3B2F3C3
DATATS# : 4
TEMPTS# : 3
CTIME : 04-mar-2007 16:00:00
PTIME : 17-sep-2007 19:47:44
EXPTIME :
LTIME :
RESOURCE$ : 0
AUDIT$ :
DEFROLE : 1
DEFGRP# :
DEFGRP_SEQ# :
ASTATUS : 0
LCOUNT : 0
DEFSCHCLASS : DEFAULT_CONSUMER_GROUP
EXT_USERNAME :
SPARE1 : 0
SPARE2 :
SPARE3 :
SPARE4 :
S:C10D037D6CE8BBC546B761491DFE708407C8032E1DD2367D83C4146CE3D0
SPARE5 :
SPARE6 :
-----------------

PL/SQL procedure successfully completed.

SQL> drop user c cascade;

User dropped.

SQL> create user c identified by c;

User created.

SQL> exec print_table('select * from sys.user$ where name=''C''');
USER# : 96
NAME : C
TYPE# : 1
PASSWORD : AEE1788DB3B2F3C3
DATATS# : 4
TEMPTS# : 3
CTIME : 04-mar-2007 16:00:00
PTIME : 17-sep-2007 19:48:49
EXPTIME :
LTIME :
RESOURCE$ : 0
AUDIT$ :
DEFROLE : 1
DEFGRP# :
DEFGRP_SEQ# :
ASTATUS : 0
LCOUNT : 0
DEFSCHCLASS : DEFAULT_CONSUMER_GROUP
EXT_USERNAME :
SPARE1 : 0
SPARE2 :
SPARE3 :
SPARE4 :
S:6F62FF27DA1319AECC190F045DA02AA7A2594864AC3E8534681A5950FE4E
SPARE5 :
SPARE6 :
-----------------

PL/SQL procedure successfully completed.

SQL>


Note that in each case the ctime is set to the same date (from the fixed_date parameter) but the SHA-1 verifier is different in each case. Therefore we can show that at least one assumtpion is wrong, the salt isn't the CTIME, well not exclusively the CTIME. As Gary points out the other possibility is the PTIME as its the only other variable parameter in the table. That assumes that the salt is indeed in the table..:-)

The other obvious point to note is that the SHA-1 verifier should be 160 bits but the value stored is actually 240 bits long. The Salt - indicated by S: could be included with the SHA-1 verifier and may be simply random but that would mean that subsequent connections would need to extract the salt from the hash as stored and use it, that would be neater but would afford no better security than for instance reading the PTIME column. Anyway, its interesting to speculate!

Decompilation - reality or myth

I have always been interested by dissassembly, reverse engineering, decompilation and other related feats not because I want to break and steal software as this seems to be the perception of what these things do or can be used for by anyone who hears of these techniques. I am interested because they are hard to do, impossible even, i am interested because of teh techniques not because of the end uses some people put these techniques to. That said there is a burgening reverse engineering community out there not just in software but also in engineering where the uses are

I like technical challenges, internals details and hard to do techniques. I guess it broadens the mind. I have always said that to know how to secure an Oracle database you must think like a hacker, know hacker techniques but also know other things, not just security, such as programming, all the database features, security techniques and more, just be curious.

Back to decompilation. Decompilation generally falls into two categories, the first where source code retrieval is needed and the second where the goal is to undertand parts of the program or the whole in a high level language such as C but where complete original source code recovery is not needed. Its generally understood that for binary decompilation complete source recovery is impossible because the compiler removes information as it does its work, two examples are the loss of variable names and the optimizer can alter the original function of the code.

For some languages complete decompilation and source code recovery is possible, the PL/SQL language for instance at least to 9i via the wrapped file it is possible to completely recover the source code as this was one of the intended design features of DIANA. Java is the same, even the Sun compilr includes a decompiler in its suite and free tools such as Jad are available.

For binary decompilation its much harder, for a start the original high level language may not be know, it could be C, C++, Delphi, compiled VB, even Assembler or combinations of these languages and many more. There have been a few reasonably successful tools written over the years, including DCC, REC and more recently the open source Boomerang.

The most interesting aspect is the idea to decompile to understandable C or C++ irrespective of the original source language and also ignoring the fact that variable names have been lost. Combining these ideals with a tool like IDA Pro makes for a very powerful reverse engineering tool. I was very interested to see that Ilfak Guilfanov, famous for writing IDA Pro has been working on a decompiler plugin for IDA that is in closed beta at the moment but will be released as a seperate product in the future. An entry in his blog titled "Decompilation gets real" gives a good taster as does an example on the documentation (limited) - in a page titled http://www.hexblog.com/hexrays/manual/primer.html - (broken link) Hex-Rays Decompiler - Quick primer shows a much longer better example of how it will work. Lets hope Datarescue make it available soon!

Using Log Miner for database forensics

I posted the other day links to my upcoming presentations, one of which at the main UKOUG is about Oracle Forensics. Alex Gorbachev was good enough to email me a link to an article on his blog titled http://www.pythian.com/blogs/509/forensic-dba-oracle-logminer-helps-detect-sabotage - (broken link) Forensic DBA: Oracle LogMiner Helps Detect Sabotage. This is a great article that shows how Alex investigate using Log Miner how a departing DBA made some changes to various procedures in the database. Alex also points to a second article that he wrote last year titled http://www.pythian.com/blogs/269/oracle-logminer-helps-investigate-security-issues - (broken link) Security Issues. This is a great story that shows where a database had a 1017 error on a materialised view and the management started to worry that someone had changed the password without authorisation. This is a really good example of how to investigate an issue where audit was not enabled. Alex checks timestamps, SYS connection audit files, log Miner to find the changes to USER$ and then he dumps the archive logs to see the actual changes. Alex found that the session was local, the terminal and the process ID. He was able to correllate with with /var/log/message and also found the person who made the change and why. This is a great pair of articles and good background for my UKOUG paper!

6 Oracle security presentations added to Oracle security white papers page

I have added short descriptions and links to 6 Oracle security presentations that I wrote and presented at various conferences this year and last year whilst I was employed at Siemens and before I recently started by own company. I have mentioned the five papers previously in my blog but I have added short descriptions and links to the six in my Oracle Security white papers page. They the top six papers on the page.

Hacking hardened and patched Oracle databases

Alex has posted the slides from his Hack In The Box presentation titled "Hacking Hardened and Patched Oracle Databases" and he has also posted an extension to one of the ideas to create tables with; shall we say unusual names that can be made to be used and read by other parts of database software or by DBA scripts to exploit the database or operating system.

Security analysis of the JInitiator buffer overflows

Steve Kost has a good paper on his site titled "Security Analysis - Oracle JInitiator 1.1.8 Buffer Overflow Vulnerability Analysis" that talks about the recent JInitiator bugs found and reported by Will DorMann at US-Cert. Steve's paper gives an overview of the bug, an overview of JInitiator, the ActiveX Control overview, vulnerability analysis, a risk analysis and then goes into remediation steps that can be taken. This is a very detailed analysis and if, particularly you run E-Business Suite you should read and take note.

Make Oracle PCI compliant

http://www.dcortesi.com/whoami/ - (broken link) Damon sent me a good link to an 11g Oracle security paper on Oracle's web site that is worth mentioning here. The paper is titled "Oracle Database Security and the Payment Card Industry Data Security Standard" and is not a bad paper in terms of listing the PCI requirements and trying to relate them to the Oracle features that could be used to satisfy the requirement. This section is very "add-on" centric, such as recommending Oracle Database Vault and Oracle Audit Vault and Oracle Advanced Security Option and more. A lot of Oracle customers dealing with PCI probably would not have these addtions enabled. The paper would have been better if it offered "free" -in terms of the solution being available in your normal license - solutions instead or at least as alternatives. That said the paper is not bad as it gives anyone dealing with credit cards and PCI compliance with an Oracle database a head start.

Oracle security presentations

I am speaking a number of times in the coming months on the subjects of Oracle Security. These are:

http://www.ukoug.org/calendar/show_event.jsp?id=2908 - (broken link) UKOUG Windows SIG - Blythe Valley Park - September 25th

My presentation is called "Securing Oracle on Windows" - The description is:

"Most larger databases are in general hosted on Unix systems and so most of the material around securing Oracle tends to focus on Unix systems. This presentation hopes to rectify this imbalance somewhat. Pete has real world experience in performing security audits on key Oracle systems that run on Windows and in this paper will look at some of the major differences and also highlight some of his own experience. Securing Oracle is often very time consuming and complex and doing so on Windows is often harder often due to the industry preference for Unix documentation"

http://www.ukoug.org/calendar/show_event.jsp?id=2912 - (broken link) UKOUG DBMS SIG - Chesford Grange in the midlands - November 7th

My presentation is called "Oracle Security on 11g" - This talk doesnt have a description yet but will be about the new Oracle security features in 11gR1 and whats changed. This talk will look at what the security issues are in Oracle, how the issues have changed for the betterin 11gR1 and will aso look at some of the new features and functions available.

I also have 4 slots at the UKOUG main conference in Birmingham this year, these are summarised as follows:

< http://conference.ukoug.org/default.asp?p=685&dlgact=shwprs&prs_prsid=1406&day_dayid=12 - (broken link) UKOUG Conference Birmingham - Oracle Security Masterclass - 6th December 2007 - 13:10

http://conference.ukoug.org/default.asp?p=685&dlgact=searchshwprs&prs_prsid=1407&day_dayid=9&src_dayid=9%2C+10%2C+11%2C+12&prs_keywords=Pete+Finnigan - (broken link) UKOUG Conference Birmingham - Oracle Security Tools - 4th December 2007 - 13:30

http://conference.ukoug.org/default.asp?p=685&dlgact=searchshwprs&prs_prsid=1409&day_dayid=9&src_dayid=9%2C+10%2C+11%2C+12&prs_keywords=Pete+Finnigan - (broken link) UKOUG Conference Birmingham - Oracle Security Round Table - 4th December 2007 16:50

http://conference.ukoug.org/default.asp?p=685&dlgact=searchshwprs&prs_prsid=1408&day_dayid=9&src_dayid=9%2C+10%2C+11%2C+12&prs_keywords=Pete+Finnigan - (broken link) UKOUG Conference Birmingham - Oracle Forensics - 5th December 2007 16:50

Code Breaking

I saw Mary Ann's post to her blog today titled "Summer reading" and I was in tune with Mary Ann's comments on books and reading. I am also someone who likes books, i have over 1000 in my office alone, and I like to read and dip into books. Mary Ann mentions Simon Singh's book the Code Breakers, I have the same book, its not a bad read, i have to say that I also likd very much hos other book "Fermats Last Theorum" which was excellent, so good I read it twice. Here is a bad photo of me in my office and just some of my books, i was having a play with my webcam:

Pete Finnigan