Pete Finnigan's Oracle Security Weblog

I wrote an Oracle password cracker completely in PL/SQL some time ago and have been using it on Oracle Database Security audits as a good starting point to test the strength of Oracle database passwords. Of course I will not abandon tools such as Laszlo Toth's excellent woraauthbf as the sheer speed from it is necessary to check true password strength.

So why write a tool completely in PL/SQL?

A number of reasons really. I wanted to have a tool that just runs and checks the basics without extracting password hashes, usernames etc from the database. I know checkpwd connects to the database and also that woraauthbf will do so soon as well (I am writing some OCI code for Laszlo to allow this, just not much time recently to complete it..:-( )

Any binary based tool, is well a binary.... it needs an Oracle client that works out of the box, the instant client often requires some tweaking to make it work. If the tool doesnt connect to the database you need to gather the usernames/hashes first. Just running with no dependancies is attractive.

In the olden days before C based crackers were freely available the only free Oracle passwords crackers were PL/SQL based (the most popular was the bear dang one that did brute force checks and the Adam Martin one that did a dictionary attack) but they all used the ALTER USER command to alter the users password in the database for each password guess and then compared the hash generated. This has a number of issues, the first is that the database is altered (any security tool should not alter the software or data being checked), any password management would become locked or would need to be removed, any triggers, audit etc would be affected.....more.....

I wanted to see if its possible to write a PL/SQL based password cracked in PL/SQL that doesnt use ALTER USER commands but actually implements the encryption routines so that any password can be checked. Also I wanted the tool to be an anonymous block in PL/SQL so that it can be run from any client with no external or internal dependancies in the database.

Let's take a look at it running and then discuss it:

SQL> @cracker-v2.0.sql cracker: Release 2.0.1.0.0 - Alpha on Fri Aug 29 16:28:24 2008 Copyright (c) 2008 PeteFinnigan.com Limited. All rights reserved. T Username Password CR FL STA ======================================================= U SYS [ORACLE1 ] DI CR OP U SYSTEM [ORACLE1 ] DI CR OP U OUTLN [OUTLN ] DE CR EL U DIP [DIP ] DE CR EL U TSMSYS [TSMSYS ] PU CR EL U ORACLE_OCM [ORACLE_OCM ] PU CR EL U XDB [CHANGE_ON_INSTALL ] DE CR EL R GLOBAL_AQ_USER_ROLE [GL-EX {GLOBAL} ] GE CR OP U DBSNMP [ORACLE1 ] DI CR OP U WMSYS [WMSYS ] DE CR EL U EXFSYS [EXFSYS ] DE CR EL U CTXSYS [CHANGE_ON_INSTALL ] DE CR EL U XS$NULL [ ] -- -- EL U ANONYMOUS [IMP {anonymous} ] IM CR EL R SPATIAL_WFS_ADMIN [SPATIAL_WFS_ADMIN ] PU CR OP U ORDSYS [ORDSYS ] DE CR EL U ORDPLUGINS [ORDPLUGINS ] DE CR EL U SI_INFORMTN_SCHEMA [SI_INFORMTN_SCHEMA ] DE CR EL U MDSYS [MDSYS ] DE CR EL U OLAPSYS [ ] -- -- EL U MDDATA [MDDATA ] DE CR EL U HR [CHANGE_ON_INSTALL ] DE CR EL U SPATIAL_WFS_ADMIN_US [SPATIAL_WFS_ADMIN_US] PU CR EL R WFS_USR_ROLE [WFS_USR_ROLE ] PU CR OP R SPATIAL_CSW_ADMIN [SPATIAL_CSW_ADMIN ] PU CR OP U SPATIAL_CSW_ADMIN_US [SPATIAL_CSW_ADMIN_US] PU CR EL R CSW_USR_ROLE [CSW_USR_ROLE ] PU CR OP U WKSYS [CHANGE_ON_INSTALL ] DE CR EL U WKPROXY [CHANGE_ON_INSTALL ] DE CR EL U WK_TEST [WK_TEST ] DE CR EL U SYSMAN [ORACLE1 ] DI CR OP U MGMT_VIEW [ ] -- -- OP U FLOWS_FILES [ ] -- -- EL U APEX_PUBLIC_USER [ ] -- -- EL U FLOWS_030000 [ ] -- -- EL U OWBSYS [OWBSYS ] PU CR EL R OWB$CLIENT [S ] BF CR OP R OWB_DESIGNCENTER_VIE [S ] BF CR OP U SCOTT [TIGER ] DE CR OP U OE [CHANGE_ON_INSTALL ] DE CR EL U IX [CHANGE_ON_INSTALL ] DE CR EL U SH [CHANGE_ON_INSTALL ] DE CR EL U PM [CHANGE_ON_INSTALL ] DE CR EL U BI [CHANGE_ON_INSTALL ] DE CR EL U PETE [PETE ] DE CR OP U BILL [BILL ] PU CR OP U A [A ] PU CR OP U B [B ] PU CR OP U C [C ] PU CR OP U RES_TEST [RES_TEST ] PU CR OP U P1 [P1 ] PU CR OP U P2 [P2 ] PU CR OP U XX [123456 ] DI CR OP U ORASCAN [ORASCAN ] PU CR OP U IMPOSS [IMP {imposs123456789] IM CR OP U D [ ] -- -- OP INFO: Number of crack attempts = [4344517] INFO: Elapsed time = [328.94 Seconds] INFO: Cracks per second = [13200] PL/SQL procedure successfully completed. SQL>

The script runs fairly fast for a PL/SQL script, its no where near as fast as a C based approach but thats not its intention. My intention was to have a script that runs fast enough to check:

• username=password


• password=known default


• password=simple word


• known hash but not cracked


• simple brute force

What do the columns mean? - the first means 'U'ser or 'R'ole, the second is the name, the third the cracked password, if its an impossible password it says so, if its EXTERNAL or GLOBAL it says so and also if we know its a default hash but don't know the password it says. The CR column defines the mode of being cracked, PU => pass=username, DE => default password, DI => dictionary word, BF => brute force, IM => impossible password. The FL column is CRacked or not, the final colum is the account status.

The release code will include more account analysis.

Then the idea is to output the relevant input for worauthbf. The key aim of this script is to allow people to easily check their databases for the worst sins that can be aimed at passwords, i.e. very simple passwords. I am starting to see a number of sites cracking passwords for their databases in an attempt to strengthen passwords. This sqlplus script will make that process much easier.

The speed is not bad, between 13,000 and 16,000 hashes per second, its not the 1.2Million hashes per second with woraauthbf BUT remember that i am testing on a laptop, this code will actually run on your production database server, thats not as easy to do with a binary based approach and also its not the intention to replace woraauthbf, its an attempt to get people to test the basics more easily.

If anyone would like this script then I will release the code next week after I have tidied it up and added some extra bits I want to add. So please watch out for a post next week with the code in it.

In todays day and age we should all design code and applications to use the minimum privileges necessary, and only those necessary, and even ideally not even those privileges This is what we call in security circles reducing the attack surface and this is more important today than ever due to the focus on security at the data, database and application level.

yesteday I saw a post by Jeffrey McDonald on his blog titled "OCSG+XE" and noted that he suggested a tip; to create the user OCSG40 and to then grant ALL PRIVILEGES to it. I responded with a comment as follows:

"Hi Jeffrey,

Thanks for your post. I would like to make a comment about the example code that grants all privileges to the user OCSG40. This is not a good tip nowadays. Granting ALL PRIVILEGES is actually slightly worse than granting DBA as these privileges are not turned off in definer rights code, they are always on. Also even SYS does not have all privileges in 11g anymore, as it has lost EXEMPT ACCESS POLICY and EXEMPT IDENTITY POLICY.

I always recommend that the minimum privileges necessary are granted for each role and user and these must be designed for purpose.

cheers

Pete"

Then Jeffrey updated his post to first suggest that another password instead of OCSG40 could be used and also to grant the privileges CREATE SESSION and CREATE TABLE. This is quite a difference to ALL PRIVILEGES.

I wanted to make the point about this here, its not a dig at Jeffrey but quite the opposite, he responded well and changed what is afterall a blog post not documentation, anyone installing should read the docs not rely on a blog post (actually its worrying how many people do rely on Google and not official documentation nowadays). My experience is that the world is getting better, there are still apps out there where the schema owner is a DBA or has ALL PRIVILEGES granted or even SYSDBA, there are even new apps that I see written and deployed now that still do this. The key change for me is that customers of mine who have written applications are becoming more savy and are willing to listen and make changes. I have dealt with some pretty big vendors over the last few years and its becoming much easier to get them to reduce privileges. This is good and should be applauded BUT we are no where near yet. Most sirtes still have far far too excessive privileges but the tide is turning.

One thing i mentioned above and should elucidate on is that its often possible to reduce the privileges to almost nothing. In our example of OCSG40 (I don't know the app in details so this could be wrong buit the general sentiment is not) has CREATE TABLE. If this application does not generate tables dynamically then it doesnt need this privilege during run time use. It needs it for install and perhaps upgrades but not for run-time. we can remove it and add it back when needed. A lot of my clients are now doing this. Even if it does need it at run-time sometimes its better to grant that privilege to another schema and expose that privilege through a PL/SQL API, that way use of the privilege is better controlled. Another way may be to retsrict use via DDL triggers. We need to get clever with the granting of privileges.

One final point that must be made is that Oracle itself is not improving fast enough, the products are still open by default and the number of privileges in use after install and before customers create anything is extremely excessive and also worringly we see a lot of duplication.

A few days ago another major data leak occured in the UK. This time involving a UK consultancy called PA Consulting and also the British Home Office. An article Worker suspended over loss of prisoner data

"A staff member at PA Consulting Group has been suspended after the contractor lost details on all prisoners in England and Wales, along with those of tens of thousands of offenders.

The data was being held, unencrypted, on a memory stick for processing purposes, the Home Office said in a Friday statement, saying that precisely how that stick was lost is now the subject of an internal investigation. A Home Office spokesperson told ZDNet.co.uk that PA Consulting had been "appointed by the Home Office in June 2007 to provide application support for tracking prolific and other priority offenders through the criminal justice system"."

Whilst this is the latest in a line of data losses in the UK, it seems to be part of a world wide trend in data loss. Is data loss a new issue? or is it simply that data loss reporting is a new trend? or is it even worse than this and in fact data loss recognition is in fact the new trend? - I mean that in years gone by (even recent years) did people even know or care that data loss had occured?

It is a current certainty that data losses are occuring and that now people and governments are standing up and paying attention, unfortunately we are in the "we know its happening" phase and not the "we have stopped it happening" phase. But it is (perversely) a step in the right direction that the public do know that this is going on.

In each of these major UK government involved (in the sense that its the data they hold) cases there is an enquiry into what went wrong and supposedly fixes to stop it happening but it seems to carry on happening. In each case the details are different in terms of how it happended but the end result is that data gets lost. Why is data being taken out of the systems designed to protect that data? - why is it ending up on CD's and memory sticks or on laptops left on trains?

To me this is an indication of evidence I see day to day in work for customers to protect data held in Oracle databases. I teach classes on how to perform an Oracle Database Security Audit and I also conduct Oracle Database Security Audits and whilst these government data losses are not indicated to be from Oracle databases, the lessons I teach and evidence I find is the same endemic issue.

One of the key things I want to understand is who accesses data and at what level and from where and how. That is I want to understand how data "flows" into and out of the database. Leading from this I also want to understand "where" the data actually is. In all companies that I audit there are always more routes to the data than the customer thinks and also more people accessing it in ways that the customers management think. Coupled with this is the problem that customers niavely think that data is in one place and held in one table. This is not the case, in my experience data is held in many places and used for many purposes. The idea that the employee data is on SCOTT.EMP is very niave. The data is often in other tables, such as interface tables, summary tables, reports layers..... Worse the data is often outside of the database in report files, csv files on desktops, export files, backup files.....

This is one of the key issues for me, most companies do not appeciate or understand exactly where their data is or how its accessed and by whom. They often think that they know....

I had a chat with a friend of mine on the phone last night and he asked me a question. I won't reveal his name in case he doesn't want me to but he knows who he is.

He asked me if its possible to stop a user from changing his own password. This is an issue as the system privilege ALTER USER allows password changes of any user BUT Oracle silently allows the use of the ALTER USER command on a users own account without the system privilege being granted. If it was as simple as a user can only change a password including his own IF he has the ALTER USER system privilege then that would make it simpler to restrict and block, i.e. dont grant the system privilege.

Interestingly the same issue applies to the ALTER SESSION command. You don't need ALTER SESSION to change NLS_LANG or date format, in fact its only needed to set events / trace. I always find this an inconsistency, that Oracle perhaps should not have allowed. i.e. the syntax actually has two meanings, one as a system privilege and one as a command to do the action in a restricted sense without having the system privilege, what I would have called a "hack" (not in the hacker / cracker sense) but in the traditional software development sense.

So how can we block a command that cannot be granted or revoked? - If we could assume that the users can only access the database directly with SQL*Plus then maybe its possible.... then maybe not. I talked about trying to block SQL*Plus five years ago in a newsletter but concluded its impossible. It still is impossible but this is a slight variation on the same issue. The problem is that users cannot be restricted to one tool.

So OK, what other options are there? - clearly a tool such as Sentrigo Hedgehog could be an option as we can detect the use of the syntax and actively block the command.

I wanted to see if there is a native way also. I came up with the idea of a DDL trigger that fires on the use of an ALTER USER command and then blocks its use. Clearly we can extend the same ideas to ALTER SESSION problems that are similar but there is then a less convenient syntax (i.e. we cannot detect that its on the USER dictionary object for an ALTER SESSION) by using the sql_text function to retrieve the SQL of the triggering SQL and then parse out the relevant ALTER SESSION command that you want to block. OK, here goes for a simple solution to my friends problem:

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 13 20:01:08 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Enter user-name: system/xxxxxx Connected to: Personal Oracle Database 11g Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> drop user alter_test cascade; User dropped. SQL> create user alter_test identified by alter_test; User created. SQL> grant create session to alter_test; Grant succeeded. SQL> connect alter_test/alter_test Connected. SQL> alter user alter_test identified by alter_test; User altered. SQL> connect system/xxxxxx Connected. SQL> create or replace trigger alter_trigger 2 before alter 3 on database 4 declare 5 begin 6 if (ora_dict_obj_type = 'USER') then 7 raise_application_error(-20010,'you cannot change your own password'); 8 end if; 9 end; 10 / Trigger created. SQL> connect alter_test/alter_test Connected. SQL> alter user alter_test identified by x; alter user alter_test identified by x * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-20010: you cannot change your own password ORA-06512: at line 4 SQL> connect system/oracle1 Connected. SQL> drop user alter_test cascade; User dropped. SQL> drop trigger alter_trigger; Trigger dropped. SQL>

Let's just walking through this code; first we connect as SYSTEM and remove the test user. Then we create a user ALTER_TEST and grant only CREATE SESSION to it (he doesnt have ALTER USER system privilege). Then we test that he can change his own password back to the same value (we could have changed it to any other value).

Next we re-connect as SYSTEM and create a very simple trigger (in a real system you would want to make this more robust of course with error checking and recovery; you would also most likely not want it to detect the use of the system privilege for legitimate users who are allowed to change passwords and most likely not stop some key staff from changing their own passwords; You would also want it to function correctly with password management).

The trigger is simple. It fires on ALTER at the database level; that is its a system trigger at the database level and not the schema level. It simply checks that the dictionary object being altered, in this case a user, so we detect that the USER dictionary object is being altered. This means a logical object not the SYS.USER$ table, so don't put USER$ in there.

Finally we can connect as the test user again, ALTER_TEST and now try and change our own password. This time the RAISE_APPLICATION_ERROR fires and reports that the trigger fired and stopped us changing our own password. It worked!

There are probably other solutions if Ithink harder about it but this one will work.

We have been away for the past 8 or 9 days on a familly holiday so no blog posts over this last period. This holiday was strange, [Keep reading there is some Oracle security content coming!] OK not strange but interesting with some co-incidences.....We went to Northumberland and stayed near a town called Alnwich which I have not been to since I was a child; its around 130 miles from home so quite a distance.

One day we had been out and done stuff for our children and then ended up in Alnwich to go to buy some provisions from the supermarket. We had a leaflet for a shop called Barter-Books that is one of the biggest second hand book shops in the UK and as it was raining and also because my son likes trains we went to have a look as the shop is located in the old (closed) Victorian train station for Alnwich. I had a wander round with the baby and ran into an old friend from just over 30 years ago from my home town area, we had a good chat and all noted how it was strange co-incidence to meet all that distance from home. It was good as he also has contact with a school friend I have not seen for years and was trying to contact a year ago with no luck.

Then we left the book shop and walked back to the car park where we had left our car and then bumped into an ex-colleague from when I worked at Siemens Insight, he also didn't live in the area. Then on the next day we went to the beach at Bamburgh and then intended to get fish and chips for lunch at the town of Seahouses but we couldn't find anywhere to park so instead drove down the coast to Beadnall where I had seen a take-away fish and chip shop with parking and a grass area to sit down and eat. We went in and the person in front of us in the queue was Niel Chandler who was organising one of the UKOUG Sig's I talked at recently in London. All these co-incidences reminded me of when I was a child and my parents took us to France camping and we found out that the people in the tent next to us actually lived across the road from us back in England.

Whilst I was away Oracle have released an update notice for the July 2008 CPU (Critical Patch Update) for customers who have downloaded the patch for Windows Oracle databases on 10.1.0.5 then the patch failed to install the sdotopo.jar file. The issue can be manually worked around by following these steps:

>cd %ORACLE_HOME%\md\lib
>copy sdotopo.jar sdotopo.jar_save
>cd
>cd files\md\lib
>copy sdotopo.jar %ORACLE_HOME%\md\lib\sdotopo.jar

The details are in Metalink note 579285.1 - Critical Patch Update July 2008 Database known Issues.

Finally I have been working on my companies / website newsletter. I have written a new newsletter for the first time in quite some time. There are a large number of subscribers and I have to be honest neglected them due to other work commitments and initially because I discovered blogging. I tried to send out the latest issue of the newsletter before we went away on holiday but it failed and I ran out of time. I got straight back onto this yesterday afternoon and tried to send it again. Apologies to anyone who has subscribed who may have actually received it more than once (I don't think that this is the case from talking with the ISP but I am unconvinced). I have had issues as more and more ISP's are now becoming spam concious and are stopping anything that looks like spam. This is actually annoying for legitimate businesses and the current trend seems to be driving people towards paying an ASP to send the emails for them. I want to avoid this if possible, not because I am a skinflint but because I simply don't want to host my subscriber list on any other site/ASP/IPS no matter how much they say they are genuine. I have tried and given up trying to send from my webserver as I have tried a number of packages and all are not reliable or need too much work to make them work properly. I then resorted to locally managed software on my PC. This also has failed (the attempt to send before my holidays and yesterday) - I now know why this is so. It is nothing to do with the software but again the ISP trying to prevent spam. After 45 minutes on the line to the help desk today I think I have a way forward. So another attempt to send out the PeteFinnigan.com newsletter will be made in a minute.

If anyone has any good comments on managing and dealing with ISP/ASP/Software etc around newsletters I would be interested to know.

I will say more about the newsletter soon; in terms of content, old issues, subscribing etc.