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;
CREATE USER "GARY" IDENTIFIED BY VALUES 'S:8A9EA7B75F8899D7163336AD9D29F9019C0361518594E6984E1EF1C4EDB8;34BCEDBA9E0AB83F'
DEFAULT TABLESPACE "APEX_4875120311438442"
TEMPORARY TABLESPACE "TEMP"
Before you try to crack my password, don't bother. In my little home environment, it is the same as the username.
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...
ReplyDeleteI'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.
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.
ReplyDeleteI 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.]