Call: +44 (0)7759 277220 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "CREATE SCHEMA - does it do what it says on the tin?"] [Next entry: "A nice simple DBMS_OBFUSCATION_TOOLKIT example by Nimzo Benoni"]

Becoming another user



I saw a post somewhere last week, maybe Oracle-l, dba-village, lazydba or c.d.o.* - I don't remember which where someone posted a version of the Oracle su script where a database user can connect as another user temporarily without knowing their password. This is old hat to most people but I thought it worth mentioning here anyway. The trick is accomplished by using the IDENTIFIED BY VALUES version (undocumented) of the ALTER USER command. This allows a DBA - usually - actually a user that has the ALTER USER privilege can do this. The user selects the existing password from the SYS.USER$ table (This can be via the DBA_USERS view). So the user using this technique also needs access to the existing users password hash via one of these. Usually this is not forthcoming to most users. Using my who_can_access.sql script you can check who has access to these, first SYS.USER$:


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 http://www.petefinnigan.com/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 http://www.petefinnigan.com/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.