Pete Finnigan's Oracle Security Weblog

Its been a few days, I had planned to write more often now that I am my own boss again but I have been too busy working and dealing with running a business. Anyway to 11g Security; I wanted to have a first look at the new password algorithm used in 11g. The documentation says that in 11g there is a new password algorithm and that passwords are hashed with the Secure Hash Algorithm (SHA) cryptographic hash function SHA-1. It goes on to say that the Oracle database uses the SHA-1 verifier to authenticate the user password and establish a session, in addition case sensitivity is enforced and passwords are restricted to 160 bits.

Let's explore a bit.

SQL> create user x identified by x; User created. SQL>

This is the simple user we created in the last blog. Look at the new SHA-1 password shown in bold (Thanks to Tom's print_table script!):

SQL> set serveroutput on size 100000 SQL> exec print_table('select * from sys.user$ where name=''X'' '); USER# : 88 NAME : X TYPE# : 1 PASSWORD : 8E9A9A5413F0B5EE DATATS# : 4 TEMPTS# : 3 CTIME : 26-aug-2007 21:46:09 PTIME : 26-aug-2007 21:46:09 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:FF5FB0BFD44E35386C0ADDC28AD63E30DC24BABAA57E60D83185AEDE366C SPARE5 : SPARE6 : ----------------- PL/SQL procedure successfully completed. SQL>

In 10gR2 and lower if we create the same user any number of times with the same password the stored hash is always the same. lets try in 11gR1:

SQL> drop user x cascade; User dropped. SQL> grant create session to x identified by x; Grant succeeded. SQL> exec print_table('select * from sys.user$ where name=''X'' '); USER# : 90 NAME : X TYPE# : 1 PASSWORD : 8E9A9A5413F0B5EE DATATS# : 4 TEMPTS# : 3 CTIME : 28-aug-2007 21:52:52 PTIME : 28-aug-2007 21:52:52 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:1765D190198C6F55E192F49047C77D6FB851B5222C8FBD0B8359FEBA6227 SPARE5 : SPARE6 : ----------------- PL/SQL procedure successfully completed. SQL>

Very interesting indeed, the SHA-1 hash has changed, why?. Interestingly also we can see that the old password hash is still the same as expected. SHA-1 is a repeatable function, i.e. if you run it for the same input many times you should get the same output. Let's test this:

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 31 22:02:22 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> SQL> connect sys/change_on_install@ora10gr2 as sysdba Connected. SQL> set serveroutput on size 1000000 SQL> l 1 DECLARE 2 lv_pwd VARCHAR2(19) := 'XX'; 3 lv_pwd_raw RAW(128) := utl_raw.cast_to_raw(lv_pwd); 4 lv_enc_raw RAW(2048); 5 BEGIN 6 dbms_output.put_line('PWD: ' || lv_pwd_raw); 7 lv_enc_raw := sys.dbms_crypto.hash(lv_pwd_raw, 3); 8 dbms_output.put_line('S: ' || lv_enc_raw); 9* END; SQL> / PWD: 5858 S: 20026DC165C030FE3A5D9609A6E61AB26210CBC1 PL/SQL procedure successfully completed. SQL> / PWD: 5858 S: 20026DC165C030FE3A5D9609A6E61AB26210CBC1 PL/SQL procedure successfully completed. SQL>

OK, so running SHA-1 should give the same hash again and again for the same input. Let's check the old verifier that we used to do in 10gR2 and lower, create a user 'A' identified by 'AA' and then a user 'AA' identified by 'A' - in 10gR2 and lower we would get the same password hash. Let's try:

SQL> create user a identified by aa; User created. SQL> create user aa identified by a; User created. SQL> exec print_table('select name,password,spare4 from sys.user$ where name in (''A'',''AA'')'); NAME : A PASSWORD : 637CFFBB696F8AF9 SPARE4 : S:8CAE3110AE48B8AC3B10365BD7F1BBD2ECB37A0DAFD01CC11939154B7DF7 ----------------- NAME : AA PASSWORD : 637CFFBB696F8AF9 SPARE4 : S:437572D2C884BB4BCB3C635EE8BEDF92D495C93F3E58DB300553BA18FD59 ----------------- PL/SQL procedure successfully completed. SQL>

OK, what is going on here? - The old 10gR2 passwords are the same, the new 11gR1 SHA-1 passwords are different, that implies a salt is used. Also just to make sure we do the same for users B/BB and BB/B:

SQL> exec print_table('select name,password,spare4 from sys.user$ where name in (''B'',''BB'')'); NAME : B PASSWORD : 6B3E43737BA3DB1A SPARE4 : S:05B7AB47678CBB66FC866C445D1B5FBDD9D966190EE29615814A0DD996FE ----------------- NAME : BB PASSWORD : 6B3E43737BA3DB1A SPARE4 : S:89BF77CB05B09A4E07D377FDC1FC4DAF892C00FE20FA5647CD54486ADFB4 ----------------- PL/SQL procedure successfully completed. SQL>

In the case of verifying a users entered password the hash algorithm chosen should return the same hash otherwise how could you verify the password? - Clearly, if the password changes each time the same user is created in the first example and also hinted at by the second example there could be a salt used. This is better than 10gR2 and lower, hashes are not predictable in terms of the same hash for the same username/password combo. This means that a default password checker could not use a table of hashes to check against, at least not with the 11gR1 new algorithm. The new built in default password check uses the old hashes. In one sense this is worse as for sad people like me who recognise default hashes simply by inspection this will not be the case in 11gR1 from the new hashes, i.e. a default word could be used but its not checkable simply by comparing hashes, well not from the new one anyway. Looks like its not simply just SHA-1 and also looks like it could be better than the old DES based hash as the created hashes are not the same each time. Is this better or not? - I think so, its a better algorithm and its clearly been thought out by Oracle to avoid some of the issues from 10gR2 and lower. Oracle do seem to have paid attention to the security details this time in 11gR1, good!

OK, I left the last post on 11g 4 days ago with a promise for "more tomorrow.." - well as they say tomorrow never comes. Well its been busy the last few days, becoming the boss of my own company has meant I needed to spend time writing proposals, dealing with leads, emails and so on so blogging and 11g research took a bit of a back seat for a few days.

OK, Oracle 11g Security; The easiest way to hack an Oracle database is to have a password for a user account (either legally or not) - the risk if you have someone with bad intent who legally has an account and password falls out of the scope of this particular discussion as he has acess to the database and his goal maybe to increase his privileges via an exploit, either exploiting a bug or a configuration. OK, back to the story, the case we are after is a hacker (lets just call anyone not legally entering the database; suceeding or not, a hacker) who doesnt have a valid database account or password for that account. How does he get in? - well he first can guess an account name and then attempt to guess a password. The obvious accounts to guess are default accounts and Oracle is famous for including a lot of them, far more than any other major software. Again the simplest attack is to just try and log in, so fancy tools or exploit code, just try and login, you can use SQL*Plus, TOAD or many more tools or even simple choices such as Excel or Word. OK, if he cannot guess a default account password easilly then he can try and brute force the password or use a dictionary attack. You can use simple Perl scripts (There are examples on my Oracle security tools page) or even a C program using OCI API's. This is a limited attack but can be successful, reasonable rates of password tries can be made. A better attack is if you can get hold of the password hashes for all the database users, then a brute force or dictionary attack can be done using password crackers written in C. There are a few free password crackers available now.

This is the simplest way in and more worringly from doing security audits of Oracle databases it is a realistic way in as all databases I perform Oracle security audits on always have weak passwords, either a password is set to the username, a default user still has a default password or accounts have passwords that are too short, set to dictionary or easy to guess words. Often I see a worrying trend that often a lot of accounts have the same password (interestingly when you install 11g this is the basic option when creating a database - choose the same password for multiple key acounts!

What does 10gR2 have? - The password algorithm used is old and known, its recognised to be weak (this is relative to how much computing power you have), the password hashes can be read easily from the DBA_USERS view and also SYS.USER$ and also SYS.USER_HISTORY$ (old hashes), password management exists but checks for default users are external. The view DBA_USERS in 10gR2 shows:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> SQL> select username,password from dba_users; USERNAME PASSWORD ------------------------------ ------------------------------ MGMT_VIEW F25A184809D6458D SYS B024681DBF11A33E SYSTEM F4D9D0B6DF5B383C DBSNMP B4D333BC1130C687 SYSMAN 47561F76D27DEDC6 TESTUSER B222330EE300D65A OUTLN 4A3BA55E08595C81 MDSYS 72979A94BAD2AF80 ORDSYS 7EFA02EC7EA6B86F EXFSYS 66F4EF5650C20355 DMSYS BFBA5A553FD9E28A USERNAME PASSWORD ------------------------------ ------------------------------ WMSYS 7C9BA362F8314299 CTXSYS 71E687F036AD56E5 ANONYMOUS anonymous XDB 88D8364765FCE6AF ORDPLUGINS 88A2B2C183431F00 SI_INFORMTN_SCHEMA 84B8CBCA4D477FA3 OLAPSYS 3FB8EF9DB538647C SCOTT F894844C34402B67 TSMSYS 3DF26A8B17D0F29F BI FA1D2B85B70213F3 PM 72E382A52E89575A USERNAME PASSWORD ------------------------------ ------------------------------ MDDATA DF02A496267DEE66 IX 2BE6F80744E08FEB SH 9793B3777CD3BD1A DIP CE4A36B8E06CA59C OE 9C30855E7E0CB02D HR 6399F3B38EDF3288 28 rows selected. SQL>

For 11gR1 we get:

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> select username,password 2 from dba_users; USERNAME PASSWORD ------------------------------ ------------------------------ DBSNMP SYSMAN MGMT_VIEW SYS SYSTEM FLOWS_FILES MDSYS ORDSYS EXFSYS SCOTT WMSYS USERNAME PASSWORD ------------------------------ ------------------------------ ORACLE_OCM TSMSYS XS$NULL BI WKSYS PM WK_TEST MDDATA IX CTXSYS ANONYMOUS USERNAME PASSWORD ------------------------------ ------------------------------ SH OUTLN DIP OE APEX_PUBLIC_USER HR XDB SPATIAL_CSW_ADMIN_USR WKPROXY SPATIAL_WFS_ADMIN_USR ORDPLUGINS USERNAME PASSWORD ------------------------------ ------------------------------ FLOWS_030000 OWBSYS SI_INFORMTN_SCHEMA OLAPSYS 37 rows selected. SQL>

The password column is empty, the view now has been changed to prevent passwords from being displayed except if they are EXTERNAL or GLOBAL. The worrying thing though is that there are now 37 accounts installed by default, instead of 27. Also worryingly Apex is installed by default, considering its web facing purpose and also the large numbers of receny remotely exploitable bugs without authentication is also worrying.

SQL> select username from dba_users 2 where account_status='OPEN'; USERNAME ------------------------------ SYSMAN DBSNMP SYSTEM SYS MGMT_VIEW SQL>

5 accounts are open. This is still too many in my opinion.

Checking default passwords in 11g is now easier with the new view DBA_USERS_WITH_DEFPWD - see:

SQL> desc dba_users_with_defpwd Name Null? Type ----------------------------------------- -------- ---------------- USERNAME NOT NULL VARCHAR2(30) SQL> select * from dba_users_with_defpwd; USERNAME ------------------------------ DIP MDSYS WK_TEST CTXSYS OUTLN EXFSYS SCOTT MDDATA ORDPLUGINS ORDSYS XDB USERNAME ------------------------------ SI_INFORMTN_SCHEMA WMSYS 13 rows selected. SQL>

This is also a "slight" worry as a default install has 13 default passwords still set. Its slight because (OK, slight is too strong) of:

SQL> edit Wrote file afiedt.buf 1 select d.username,u.account_status 2 from dba_users_with_defpwd d, dba_users u 3* where u.username=d.username SQL> / USERNAME ACCOUNT_STATUS ------------------------------ ------------------------- EXFSYS EXPIRED & LOCKED MDDATA EXPIRED & LOCKED ORDSYS EXPIRED & LOCKED SCOTT EXPIRED & LOCKED OUTLN EXPIRED & LOCKED WMSYS EXPIRED & LOCKED MDSYS EXPIRED & LOCKED ORDPLUGINS EXPIRED & LOCKED WK_TEST EXPIRED & LOCKED XDB EXPIRED & LOCKED DIP EXPIRED & LOCKED USERNAME ACCOUNT_STATUS ------------------------------ ------------------------- SI_INFORMTN_SCHEMA EXPIRED & LOCKED CTXSYS EXPIRED & LOCKED 13 rows selected. SQL>

If we create a new user:

SQL> create user x identified by x; User created.

Then we can see in SYS.USER$ and DBA_USERS:

SQL> set head off SQL> l 1* select * from sys.user$ where name='X' SQL> / 88 X 1 8E9A9A5413F0B5EE 4 3 26-AUG-07 26-AUG-07 0 1 0 0 DEFAULT_CONSUMER_GROUP 0 S:FF5FB0BFD44E35386C0ADDC28AD63E30DC24BABAA57E60D83185AEDE366C SQL> select * from dba_users where username='X'; X 88 OPEN 22-FEB-08 USERS TEMP 26-AUG-07 DEFAULT DEFAULT_CONSUMER_GROUP 10G 11G N SQL>

The above output shows that the default case in 11g is to create a new user with an old DES based password and also a new SHA-1 based password - i.e. 10g and 11g passwords.

If we create the same user in 10gR2 then we get:

SQL> set head off SQL> / 62 X 1 8E9A9A5413F0B5EE 4 3 26-AUG-07 26-AUG-07 0 1 0 0 DEFAULT_CONSUMER_GROUP 0

This confirms that the same password is created - i.e. the old DES based one.

There are still lots of issues and even more research to be explored in my next posts, Oracle have reduced the exposure to password hashes but they can be got in other ways, I will explore this. The password throttling is a good idea but it wont protect against a known default password (if the account is open and the new view has not been checked) or a simple password that can be guessed in a small number of attempts. I support the changes but they will only work really effectively if password management is used, every account has strong passwords and all other sources of revealing hashes have been locked down.

Oracle have clearly embraced the simplest way to attack an Oracle database and taken useful actions to prevent this attack. They have removed the most obvious source or password hashes, they have added a new password algorithm, they have added a built in default password check (more on this later) and they have introduced a throttling process to prevent repeated password guessing with simple connect attempts. They have really started to take security really seriously. Hurrah for Oracle.

I have started to research the new Oracle 11gR1 specifically in the area of Oracle security. For me this doesnt just mean looking at the documentation and pulling out the new Oracle security related features. Of course I will look at those as well but i have a devious mind so I like to look at everything and see if I can spot an angle, an edge that will show me a security weakness.

Before I could start I needed to get 11gR1 loaded. The first stages of this research are two fold, look at the documentation and of course look at 11g itself. I downloaded 11g as soon as it was available for download for Linux a week or so ago but didnt get around to installing it until last night. I had to first dig out a box to install on. I was going to vmware it then i thought, no better to run natively even if the box is not top spec. I had thought about an old base unit i have but decided that it was too old and slow and instead I have reformatted the disk of my last laptop and I downloaded Oracle "unbreakable" linux at the weekend.

I then spent Monday evening trying to get it installed.... that was a failure, wouldnt you think that Oracle "unbreabable" Linux downloaded from Oracle's site would work out of the box with Oracle 11g database.... nope..... no such luck. After some digging this last night, i solved the DISPLAY issue (Thanks to Howard) and then set about fixing the packages, kernel parameters and a few other bits. I seemed to have parameter issues that are not listed in Howards install docs or others I found. The install then went reasonably smoothly, if slow. When it had finished I got another issue. I tried to log into sqlplus but got an error "sqlplus: error while loading shared libraries: /oracle/11g/libnnz11.so: cannot restore segment prot after reloc: permission denied" - This was solved after a bit of digging to find it was an SELinux policy issue. I had to log in as root and run "tail -f /var/log/audit/audit.log | tee oracle.log" then log back in as oracle and try and start sqlplus as sysdba. The log back in as root and CTRL-C the log file. Then its a simple case of feeding the log into the policy by doing "audit2allow -M oracle < oracle.log" and then run "semodule -i oracle.pp" - now sqlplus works natively on the Linux box. I can of course also log in remotely:



SQL*Plus: Release 9.2.0.1.0 - Production on Wed Aug 22 22:55:27 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


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>


Even from 9iR2. Now I have a platform to play with. I have some ideas of what to investigate and look at. More tomorrow....

David dropped me an email to let me know that part 6 of his series of papers discussing Oracle forensics is out. Part 6 is titled " http://www.databasesecurity.com/dbsec/oracle-forensics-6.pdf - (broken link) Examining Undo Segments, Flashback and the recycle bin and is worth having a look.

This is an overtly commercial post, which I don't normally do here, so please forgive the intrusion at this exciting time for me and my family. Last Friday was my last day in salaried employment and from tomorrow I will again be an independent working for my own Limited company PeteFinnigan.com Limited. I will be offering a variety of Oracle and database security related services and will of course be available for hire. I will be updating my site to include details of the services I offer but i also want to maintain the usefulness of the site to the many thousands of readers I get every day and I will continue to maintain the site and add content, hopefully more that I have been doing now that i will be in more control of my own workload. I am also keen to maintain the vendor neutral stance that I try and take in my writings. I don't see that this should change as I will be working independently and not tied to any vendor.

I am in the first stages of writing some service leaflets of my offerings and also in the first stages of updating my website to include a brief description of what I offer and also adding a menu to access the services. There has been a lot of interest in my work over the last few weeks and I have already a number of customers signed up so I am not going to get a rest after leaving my salaried job as I need to start chargeable work from Monday. This is great though.

My services are described briefly as follows:


As I say I will get more detailed descriptions of each service on my site over the coming week and these will include details of what’s involved, durations and costs. I will also most likely extend the list of services that i am able to offer at the same time. I will announce here when it’s done. I will also detail the training that will be available. All training will be available on client sites and prices will be available very soon. It is also intended that some public training may be organised and I am also interested to discuss partnering/reseller arrangements also.

The blog will continue and also watch out for some additional content on my site over the coming weeks. I have quite a back log of things I would like to add. I am also signed up to speak at a number of events over the next few months. I am speaking at the http://www.ukoug.org/calendar/show_event.jsp?id=2908UKOUG Windows SIG on September 25th about Oracle security on Windows at Blythe Valley Park, then I will speak three times at the http://conference.ukoug.org/ - (broken link) UKOUG conference in Birmingham, December 3rd to 6th on Oracle Forensics, Oracle security tools and I will also do a completely new two hours Oracle security master class. I will also host the Oracle security round table at the same event.

For any further details on the services and training that we offer please see the contact details page.

David has released part 5 of his Oracle forensics paper series. Thi part is titled " http://databasesecurity.com/dbsec/OracleForensicsPt5.pdf - (broken link) Finding evidence of data theft in the absense of auditing. The paper concentrates on finding evedence of SQL being executed by examining the CBO usage and statistics from the COL_USAGE$ table. The V$ fixed views are looked at including the object cache, SQL text and more. Finally David looks at the AWR views briefly for a snapshot of SQL executed.

The contents of this paper and the previous 4 are also summarised in a presentation given by David at Blackhat. The presentation is titled http://databasesecurity.com/dbsec/forensics.ppt - (broken link) Oracle Forensics. This is an interesting area and one that I am also interested in.

A couple of comments though. In the part 5 paper and also in the presentation there is descriptions of how to use the AWR views to examine the database for evidence of attack. The subject of this feature and views has been discussed by a number of bloggers recently. The pythian blog summed this up with an open letter to Larry Ellison to request a lifting of the licensing for AWR and ASH views. This is summed up in the post http://www.pythian.com/blogs/526/an-open-letter-to-larry-ellison-on-awr-and-ash-licensing#comment-84626 - (broken link) Open Letter to Larry Ellison on AWR and ASH Licensing The issue is that these views exist and are populated and are available but to look at the contents requires the purchase of an additional license on top of the enterprise edition license. So to suggest using these views as part of a forensics analysis and tools is not strictly correct as most sites probably do not have licensing for the use of these views.

The second comment I have is in regards to one of the early slides in the Blackhat presentation; it states "There are 0 (zero) database-specific forensic analysis and incident response tools on the market – free or commercial." - Whilst this is true in a literal sense, the tool David previews on the last slide FEDS (Forensic Examiners Database Scalpel) is a tool for reading Oracle blocks on first analsys of the slide presented. There are many tools commercial and internal to Oracle that can be used to examine blocks. I have listed a few here in the past, these include BBED, DUL, Ora*Dude and more. Reading data blocks is not new, Oracle even provide dump commands to allow you to do this either as raw binary or formatted blocks. I hope that David is planning to include much more in FEDS than just block dumps.

I just got back from holidays to the nice news that 11g is available for download now. This is only for Linux but hopefully other platforms will follow soon. I am currently downloading although I dont have a spare Linux box set up at the moment to install on; probably I will set one up..:-)

I got an email from someone a couple of weeks or so ago about the fact that he had downloaded Patrik Karlsson's excellent OAT (Oracle Auditing Tools) software and that it had been flagged as a virus by the security department. He is a DBA and wanted to use the tools to provide security auditing and protection; the main aim of the tools. He asked me to corroborate his assertions that these tools are actually safe and are not a virus.

I downloaded them again myself and Windows Defender baulked and said there was a severe danger as "it can capture passwords". I then ran Norton anti-virus scanner against the zip file and it reported three problems; 2 for PWDump and 1 for Netcat. Is the anti-virus software correct to mark OAT as a virus? - this is debatable. In their own right PWDUmp and Netcat could and should be marked as dangerous if they were downloaded to a users PC as part of an email or other surupticious way of getting them onto the PC. A user not expecting to get these tools on hos PC would want them marked. They are not however virus's or trojans in my opinion. In the context of OAT though they are not a virus either or dangerous as the context must be taken into account. OK, in this case a DBA had downloaded an Oracle security toolkit - no danger, what if OAT had been deployed as a payload to an unsuspecting persons PC would it then be classed as dangerous? - not sure, the targetting of a zip of an Oracle audit toolkit would need extra "features" to enable the attacker to do something with it and also the PC infected would need to be specifically targetted. So should anti-virus software and spam/spyware tools such as defender find OAT dangerous? - they should detect netcat and most likely PWDump but should then detect when part of a toolkit such as OAT? - probably? - does this mean the future for free security tools is changing, if they are being marked as dangerous - viri, trojans or spyware? - probably?