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.
2 comments:
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.
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.]
Post a Comment