Saturday, November 26, 2011

DBMS_METADATA and hashed passwords

In the wonderfully named "ORAganism" blog (don't try oragasm - it is used for something QUITE different) Neil Johnson remarks on the privilege requirements for DBMS_METADATA.

I like DBMS_METADATA. In the old days, you had scripts that tried to re-engineer CREATE scripts from DBA_TABLES and so on. They were complicated and worked in most situations. DBMS_METADATA is much more powerful.

One feature worth noting about DBMS_METADATA is that it can also extract details about users which can be handy in the post 11g world of Oracle.

In 11g the PASSWORD column in the DBA_USERS returns null, rather than hashed version of the user's password as it did in 10g and earlier. That made it a lot harder to extract those password hashes from the database. There is some underlying SYS object that shows the hashes, but SYS objects are really tricky to access.

DBMS_METADATA gives a backdoor to that information. Not to everyone, but it should be usable by 'regular' DBAs without jumping through hoops.

select dbms_metadata.get_ddl('USER','GARY') a from dual;

   DEFAULT TABLESPACE "APEX_4875120311438442"

Before you try to crack my password, don't bother. In my little home environment, it is the same as the username.


Anonymous said...

So on my 32 bit XE 11g I created user gary identified by gary, then ran the dbms.metadata, and it gave a different values, starting with S:4EFCD32...

I'm curious if that S:4 is a bit size identifier, or just something random, seeing as yours is S:8? Don't have a 64 bit 11 to check.

sydoracle said...

How does the bit after the semi-colon compare. The bit before is the case-sensitive version, and I don't know whether the locale of the install would have an effect on that.

I do recall that in the older case-insensitive password checks, there is an implicit UPPER which had some side-effects if you used passwords with characters beyond ASCII 127. [IE the upper done by your client might not match the upper done by the server.]