The need to ensure that hashed password values are safe
Posted by John Hallas on January 4, 2010
Dennis Yurichev has produced a FGPA password checker which is available on the internet. I took one of our standard style passwords and pasted it into the link above and it took only 45 minutes to crack the password, which is rather a concern if anyone can get see the hashed passwords that are held in the data dictionary
|DBSNMP||1FF13052A07F0164||Standard format randomly generated pwd||SOLVED||BDLTL5PD||time spent: 45m59s; average speed: 56M|
That password, on a test system, has now been changed, however it was 8 characters long and to see it cracked on a publicly available site does make one more focused on the need for security, not that I was not already. The only good news is that password is shown in uppercase when it reality it was a mixture of case.
Lesson 1 – If using 11G ensure that the initialisation paremeter sec_case_sensitive_logon is set to true
So which privileges can be used to view hashed passwords.
The hashed value of the password was stored in sys.user$ AND in dba_users in V10, however security concerns caused V11 to be changed so that the hashed password is only stored in the sys.user$ table, despite there still being a column for it in dba_users.
In 10G we know that the following privileges give access to that password value ( I am ignoring the DBA role for now)
|Select any dictionary||Yes||Yes|
|Select any table||No||No|
Things are better in 11G
|Select any dictionary||Not stored||Yes|
|Select any table||Not stored||No|
Lesson 2 – Do not give out SELECT ANY DICTIONARYprivilege without understanding the risk
Lesson 3 - If using 10G then beware of SELECT_CATALOG_ROLE
Interestingly if you want to give Grid access out and you want a user to be able to see performance pages then one way is to grant the OEM_MONITOR role, which contains SELECT_CATALOG_ROLE. testing here shows that if you copy that role then you cannot login to Grid, even without changing privileges. Our thought was to create a OEM_MONITORING role that was based on OEM_MONITOR but did not contain all the 2000+ grants that OEM_MONITOR contains and in particular to remove SELECT_CATALOG_ROLE. the OEM_MONITOR role has the following privileges :-
SQL>userprivs1 Enter value for user: OEM_MONITOR Adm Granted Opt Dfl ----------------------------------------------------------------- --- --- SELECT_CATALOG_ROLE NO YES ADVISOR NO ANALYZE ANY NO ANALYZE ANY DICTIONARY NO CREATE JOB NO CREATE SESSION NO MANAGE ANY QUEUE NO SELECT ANY DICTIONARY NO DEQUEUE on SYS.ALERT_QUE (by SYS) NO EXECUTE on DBSNMP.BSLN (by DBSNMP) NO EXECUTE on DBSNMP.BSLN_INTERNAL (by DBSNMP) NO EXECUTE on DBSNMP.BSLN_METRIC_SET (by DBSNMP) NO EXECUTE on DBSNMP.BSLN_METRIC_T (by DBSNMP) NO EXECUTE on DBSNMP.BSLN_OBSERVATION_SET (by DBSNMP) NO EXECUTE on DBSNMP.BSLN_OBSERVATION_T (by DBSNMP) NO EXECUTE on DBSNMP.BSLN_STATISTICS_SET (by DBSNMP) NO EXECUTE on DBSNMP.BSLN_STATISTICS_T (by DBSNMP) NO EXECUTE on DBSNMP.BSLN_VARIANCE_SET (by DBSNMP) NO EXECUTE on DBSNMP.BSLN_VARIANCE_T (by DBSNMP) NO EXECUTE on DBSNMP.MGMT_RESPONSE (by DBSNMP) NO EXECUTE on DBSNMP.MGMT_UPDATE_DB_FEATURE_LOG (by DBSNMP) NO EXECUTE on SYS.DBMS_AQ (by SYS) NO EXECUTE on SYS.DBMS_AQADM (by SYS) NO EXECUTE on SYS.DBMS_DRS (by SYS) NO EXECUTE on SYS.DBMS_MONITOR (by SYS) NO EXECUTE on SYS.DBMS_SERVER_ALERT (by SYS) NO EXECUTE on SYS.DBMS_SYSTEM (by SYS) NO EXECUTE on SYS.DBMS_WORKLOAD_REPOSITORY (by SYS) NO SELECT on DBSNMP.BSLN_BASELINES (by DBSNMP) NO SELECT on DBSNMP.BSLN_METRIC_DEFAULTS (by DBSNMP) NO SELECT on DBSNMP.BSLN_STATISTICS (by DBSNMP) NO SELECT on DBSNMP.BSLN_THRESHOLD_PARAMS (by DBSNMP) NO SELECT on DBSNMP.BSLN_TIMEGROUPS (by DBSNMP) NO SELECT on DBSNMP.MGMT_BASELINE (by DBSNMP) NO SELECT on DBSNMP.MGMT_BASELINE_SQL (by DBSNMP) NO SELECT on DBSNMP.MGMT_BSLN_BASELINES (by DBSNMP) NO SELECT on DBSNMP.MGMT_BSLN_DATASOURCES (by DBSNMP) NO SELECT on DBSNMP.MGMT_BSLN_INTERVALS (by DBSNMP) NO SELECT on DBSNMP.MGMT_BSLN_METRICS (by DBSNMP) NO SELECT on DBSNMP.MGMT_BSLN_STATISTICS (by DBSNMP) NO SELECT on DBSNMP.MGMT_BSLN_THRESHOLD_PARMS (by DBSNMP) NO SELECT on DBSNMP.MGMT_HISTORY (by DBSNMP) NO SELECT on DBSNMP.MGMT_HISTORY_SQL (by DBSNMP) NO SELECT on DBSNMP.MGMT_LATEST (by DBSNMP) NO SELECT on DBSNMP.MGMT_LATEST_SQL (by DBSNMP) NO
However MOS note 455191.1 does state that these priviliges might be too much for some sites and the following privileges might be sufficient
Instead of granting OEM_MONITOR role, the following roles/privileges can be assigned directly to the user:
</code>System Privileges <code>
</code> ->ANALYZE ANY <code>
</code> ->CREATE TABLE
The select_catalog_role still poses a problem to those using 10G (or earlier) but does assist those managing 11G security. I do not think that the majority of our users would need ANALYZE ANY or CREATE TABLE as part of a grid usage role so we will not be granting those and we will ensuring that the password column of dba_users and user$ is audited.
Lesson 4 - Consider a reduction in the use of OEM_MONITOR and replace with specific privileges
Finally Pete Finnegan (and others I am sure) has written about how dangerous it is to allow users to read any trace files that they generate and he gives an example of taking a buffer dump to capture a hashed password
Lesson 5 - be careful who has access to read trace files