"What would be most easy way to determine what users have access to
dba_users? I assumed only dba's had this and of course I was wrong as eg.
the dbsnmp account can list it."
This is a good question that i come across quite a few times. The reason it is a good question is because of misconceptions. people can think that only a "DBA" can access views and the data viewable from them if they are a DBA. Another good example is the misconception that users need the role CONNECT to, well connect!. This is obviously not true as they actually need the system privilege "CREATE SESSION" to be granted as the role CONNECT has far too many system privileges.
So back to the point, how do you find out which users can actually see the view DBA_USERS? - This is what I answered to my friend:-
The easiest way is to do this is to download my script who_can_access.sql and run it.
Here is an example for my system:-
SQL> @c:\petefinnigan.com\extracts\master_site\development\who_can_access.sql
who_can_access: Release 1.0.2.0.0 - Production on Sat Apr 30 18:07:02 2005
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
NAME OF OBJECT TO CHECK [USER_OBJECTS]: DBA_USERS
OWNER OF THE OBJECT TO CHECK [USER]: SYS
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Checking object => SYS.DBA_USERS
====================================================================
Object type is => VIEW (TAB)
Privilege => SELECT is granted to =>
User => CTXSYS (ADM = NO)
User => ORAPROBE (ADM = NO)
Role => PWD_ROLE (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => ROLE_TEST (ADM = NO)
Role => NON_PWD_ROLE (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => ROLE_TEST (ADM = NO)
Role => SELECT_CATALOG_ROLE (ADM = NO) which is granted to =>
User => SH (ADM = NO)
Role => DBA (ADM = YES) which is granted to =>
User => SYS (ADM = YES)
User => HACK (ADM = NO)
User => TEMP (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => TEST01 (ADM = NO)
User => APP_EXAMPLE (ADM = NO)
User => TEST01_PRIV (ADM = NO)
User => SCHEMA_OWNER (ADM = NO)
User => REMOTE_OS_USER (ADM = NO)
User => ODM (ADM = NO)
User => SYS (ADM = YES)
User => ODM_MTR (ADM = NO)
Role => OLAP_DBA (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => HACK (ADM = NO)
User => TEMP (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => TEST01 (ADM = NO)
User => APP_EXAMPLE (ADM = NO)
User => TEST01_PRIV (ADM = NO)
User => SCHEMA_OWNER (ADM = NO)
User => REMOTE_OS_USER (ADM = NO)
User => SYS (ADM = YES)
User => OLAPSYS (ADM = NO)
User => ORAPROBE (ADM = NO)
Role => ADMIN_ROLE01 (ADM = NO) which is granted to =>
Role => EXP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => HACK (ADM = NO)
User => TEMP (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => TEST01 (ADM = NO)
User => APP_EXAMPLE (ADM = NO)
User => TEST01_PRIV (ADM = NO)
User => SCHEMA_OWNER (ADM = NO)
User => REMOTE_OS_USER (ADM = NO)
User => SYS (ADM = YES)
Role => IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => HACK (ADM = NO)
User => TEMP (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => TEST01 (ADM = NO)
User => APP_EXAMPLE (ADM = NO)
User => TEST01_PRIV (ADM = NO)
User => SCHEMA_OWNER (ADM = NO)
User => REMOTE_OS_USER (ADM = NO)
User => SYS (ADM = YES)
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
SQL>
As you can see there is a hierarchical output. You can also specify that the output be sent to a file instead.
There are two other possible ways that a user could get access to the view. The first is if they have been granted the system privilege SELECT ANY DICTIONARY. This can be checked for with who_has_priv.sql - running it for my database gives:
SQL> @c:\petefinnigan.com\extracts\master_site\development\who_has_priv.sql
who_has_priv: Release 1.0.2.0.0 - Production on Sat Apr 30 18:10:48 2005
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
PRIVILEGE TO CHECK [SELECT ANY TABLE]: SELECT ANY DICTIONARY
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Privilege => SELECT ANY DICTIONARY has been granted to =>
====================================================================
Role => DBA (ADM = YES) which is granted to =>
User => SYS (ADM = YES)
User => HACK (ADM = NO)
User => TEMP (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => TEST01 (ADM = NO)
User => APP_EXAMPLE (ADM = NO)
User => TEST01_PRIV (ADM = NO)
User => SCHEMA_OWNER (ADM = NO)
User => REMOTE_OS_USER (ADM = NO)
User => OSCAN (ADM = NO)
User => PETE2 (ADM = NO)
User => DBSNMP (ADM = NO)
Role => OLAP_DBA (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => HACK (ADM = NO)
User => TEMP (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => TEST01 (ADM = NO)
User => APP_EXAMPLE (ADM = NO)
User => TEST01_PRIV (ADM = NO)
User => SCHEMA_OWNER (ADM = NO)
User => REMOTE_OS_USER (ADM = NO)
User => SYS (ADM = YES)
User => OLAPSYS (ADM = NO)
User => SQLINJECT (ADM = NO)
Role => OEM_MONITOR (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
SQL>
Finally if the parameter O7_dictionary_accessibility=true (default before 9i) then if a user has the
system privilege SELECT ANY TABLE then they would also be able to see the view. To check if the parameter is set use my script check_parameter.sql:-
SQL> @c:\petefinnigan.com\check_parameter.sql
check_parameter: Release 1.0.2.0.0 - Production on Sat Apr 30 18:14:46 2005
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
PARAMETER TO CHECK [utl_file_dir]: O7_DICTIONARY_ACCESSIBILITY
CORRECT VALUE [null]: FALSE
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Investigating parameter => O7_DICTIONARY_ACCESSIBILITY
====================================================================
Name : O7_DICTIONARY_ACCESSIBILITY
Value : FALSE
Type : BOOLEAN
Is Default : ***SPECIFIED IN INIT.ORA
Is Session modifiable : FALSE
Is System modifiable : FALSE
Is Modified : FALSE
Is Adjusted : FALSE
Description : Version 7 Dictionary Accessibility Support
Update Comment :
-------------------------------------------------------------------------
value is correct
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
SQL>
It is false on my system which it should be. If it were TRUE then i could use who_has_priv.sql to find
any users with the SELECT ANY TABLE privilege:
SQL> @c:\petefinnigan.com\who_has_priv.sql
who_has_priv: Release 1.0.2.0.0 - Production on Sat Apr 30 18:16:13 2005
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
PRIVILEGE TO CHECK [SELECT ANY TABLE]: SELECT ANY TABLE
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Privilege => SELECT ANY TABLE has been granted to =>
====================================================================
Role => DBA (ADM = YES) which is granted to =>
User => SYS (ADM = YES)
User => HACK (ADM = NO)
User => TEMP (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => TEST01 (ADM = NO)
User => APP_EXAMPLE (ADM = NO)
User => TEST01_PRIV (ADM = NO)
User => SCHEMA_OWNER (ADM = NO)
User => REMOTE_OS_USER (ADM = NO)
User => ODM (ADM = NO)
User => SYS (ADM = YES)
User => XDB (ADM = NO)
User => MDSYS (ADM = NO)
User => OSCAN (ADM = NO)
User => WKSYS (ADM = NO)
User => ZULIA (ADM = NO)
User => CTXSYS (ADM = NO)
Role => APPTEST (ADM = NO) which is granted to =>
User => SYSTEM (ADM = YES)
Role => APP_ROLE (ADM = NO) which is granted to =>
User => SCOTT (ADM = NO)
User => SYSTEM (ADM = YES)
User => APP_ROLE_TEST (ADM = NO)
User => OLAPSYS (ADM = NO)
Role => OLAP_DBA (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => HACK (ADM = NO)
User => TEMP (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => TEST01 (ADM = NO)
User => APP_EXAMPLE (ADM = NO)
User => TEST01_PRIV (ADM = NO)
User => SCHEMA_OWNER (ADM = NO)
User => REMOTE_OS_USER (ADM = NO)
User => SYS (ADM = YES)
User => OLAPSYS (ADM = NO)
Role => EXP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => HACK (ADM = NO)
User => TEMP (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => TEST01 (ADM = NO)
User => APP_EXAMPLE (ADM = NO)
User => TEST01_PRIV (ADM = NO)
User => SCHEMA_OWNER (ADM = NO)
User => REMOTE_OS_USER (ADM = NO)
User => SYS (ADM = YES)
Role => IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => HACK (ADM = NO)
User => TEMP (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => TEST01 (ADM = NO)
User => APP_EXAMPLE (ADM = NO)
User => TEST01_PRIV (ADM = NO)
User => SCHEMA_OWNER (ADM = NO)
User => REMOTE_OS_USER (ADM = NO)
User => SYS (ADM = YES)
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
SQL>
This is a good example of how to audit a database for insecure access to objects that could reveal information that would be useful to a hacker. In this case if this view can be read then a hacker can take the database users password hashes and crack them with a password cracker off-line.
This discussion could be taken much further of course. There could be a few reasons to ensure that users do not have access to this view, the first is as I have just indicated would be to ensure no one can get the password hashes. Another reason may be to ensure user lists cannot be created also for the purpose of illegitimate access, this time to brute force accounts, the view can also be used to glean other information about users that may be useful to hackers. The problem does not stop here though. The view DBA_USERS reads its information from base tables in the data dictionary, in this case including SYS.USER$ for the password hashes. This table should also therefore be checked for access in the same manner as described above. For other data in available in the view DBA_USERS such as the usernames then checks need to be made to see where else this can be got from. The same base table of course SYS.USER$ and other views such as ALL_USERS can be used to create lists of user accounts.
Clearly a simple question like "how can I find which users can access the DBA_USERS view" needs to be thought about carefully in the context of auditing a database for security.
