Pete Finnigan's Oracle Security Weblog

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.

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!

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.

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 : S5FE25BF206BEFF6BB589C830E111C363A906C191704E35978F32A1F6164 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..

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!

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!

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!

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.

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.

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.

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.

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

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: