Oracle DBA – A lifelong learning experience

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)

Privilege Dba_users Sys.user$
Select_catalog_role Yes No
Select any dictionary Yes Yes
Select any table No No

Things are better in 11G

Privilege Dba_users Sys.user$
Select_catalog_role Not stored No
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:

Roles:
->CONNECT
->SELECT_CATALOG_ROLE <code>
</code>System Privileges <code>
</code>    ->ANALYZE ANY <code>
</code>    ->CREATE TABLE
Profile <code>
->DEFAULT[/code]

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

Advertisements

5 Responses to “The need to ensure that hashed password values are safe”

  1. Thanks for blogging it.
    Few notes:
    1) Pre-11g passwords are all case-insensitive: try to set any password using different case: you’ll got the same hash value.
    2) Ironically, new 11g SHA-1 passwords can be cracked much more faster than old:
    http://blog.red-database-security.com/2009/11/29/ighashgpu-cracking-oracle-passwords-with-790-million-passwordssecond/

  2. […] 2-How to secure hashed passwords John Hallas-The need to ensure that hashed password values are safe […]

  3. […] users procedures and package bodies – but is best to be avoided in 11g and above because of this security issue. Arup Nanda explains the difference between select_catalog_role and ‘select any […]

  4. Jf said

    can you shared you script userprivs1?thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: