who_can_access: Release 1.0.1.0.0 - Production on Sun Jan 09 21:58:03 2005
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
NAME OF OBJECT TO CHECK [USER_OBJECTS]: USER$
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.USER$
====================================================================
Object type is => TABLE (TAB)
Privilege => SELECT is granted to =>
User => CTXSYS (ADM = NO)
User => MDSYS (ADM = NO)
User => OLAPSYS (ADM = NO)
User => WKSYS (ADM = NO)
User => XDB (ADM = NO)
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
SQL>
Quite a few default users have access to this table. Let's check DBA_USERS:
who_can_access: Release 1.0.1.0.0 - Production on Sun Jan 09 21:59:43 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 => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
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 => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => REMOTE_OS_USER (ADM = NO)
User => SYS (ADM = YES)
User => OLAPSYS (ADM = NO)
User => ORAPROBE (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 => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
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 => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
User => HACKER (ADM = NO)
User => SYSTEM (ADM = YES)
User => REMOTE_OS_USER (ADM = NO)
User => SYS (ADM = YES)
User => TEST01 (ADM = NO)
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
SQL>
On my 9.2.0.1 database quite a lot of users and roles have access to this view.
Access to both of these and indeed any method of viewing password hashes should be blocked as far as possible by revoking the access privileges granted.
There are two other possibilities to access these tables or views. The first is if the initialisation parameter O7_dictionary_accessibility is set to TRUE then any user with SELECT ANY TABLE system privileges will also be able to access this data. The second possibility from 9i is the SELECT ANY DICTIONARY system privilege. Users and roles that have been granted these privileges can be checked for with my script who_has_priv.sql
OK, back to the subject of how to change users without a password. The trick is achieved by selecting the current hashed password from SYS.USER$ and then storing it locally. The password of the user to be connected as is changed to something known with an ALTER USER command and then the person logs in, then can then re-set the original password with the ALTER USER {blah} IDENTIFIED BY VALUES '{original password hash}'.
This can be wrapped up into a script so that the final command is spooled to a file that can be run later to restore the password. Finally Tom has a good example script and article on his site.
