Database Reference
In-Depth Information
SQL> CONNECT / AS SYSDBA
Connected.
SQL> ALTER SESSION SET EVENTS '10079 trace name context forever, level 2';
Session altered.
SQL> PASSWORD ndebes
Changing password for ndebes
New password:
Retype new password:
Password changed
SQL> ORADEBUG SETMYPID
Statement processed.
SQL> ORADEBUG TRACEFILE_NAME
/opt/oracle/obase/admin/TEN/udump/ten1_ora_20364.trc
The resulting trace file contains the following packet dump:
C850BD0 FFD668BF 646E06BF 73656265 00000030 [.h....ndebes0...]
C850BE0 54554110 454E5F48 53415057 524F5753 [.AUTH_NEWPASSWOR]
C850BF0 0000C044 31384000 39314642 38373930 [
D....@81BF190978]
C850C00 41323232 39363642 45453539 42303242 [222AB66995EEB20B]
C850C10 46323546 30324343 30313239 39453434 [F52FCC20921044E9]
C850C20 34423130 32353232 45423332 44393431 [01B4225223BE149D]
C850C30 30304542 00003245 00270000 410D0000 [BE00E2....'....A]
C850C40 5F485455 53534150 44524F57 00000000 [UTH_PASSWORD....]
Obviously, the password was sent encrypted. Thus, the SQL*Plus
PASSWORD
command is a
safe way to change passwords, whereas
ALTER
USER
user_name
IDENTIFIED
BY
new_password
is
not, since it sends the password unencrypted along with the SQL statement text. By the way,
the preceding encryption is different from the password hash in
DBA_USERS.PASSWORD
, such that
eavesdropping a communications link cannot be used to glean password hashes stored in the
dictionary base table
USER$
. Oracle Call Interface provides the function
OCIPasswordChange()
and it is safe to assume that SQL*Plus uses this function to implement the
PASSWORD
command.
Unfortunately the manuals do not state whether or not
OCIPasswordChange()
encrypts passwords.
Some applications use roles, which are protected by a password, to enable certain privi-
leges only when a user connects with the application. This is intended to restrict the privileges
of users who connect with SQL*Plus or other applications. Event 10079 may be used to prove
that both the SQL statement
SET
ROLE
role_name
IDENTIFIED
BY
password
as well as
DBMS_
SESSION.SET_ROLE
send the role's password unencrypted to the DBMS server. This means that
any user who knows enough about Oracle Net, can get the unencrypted role password from a
packet dump. Since an end user cannot add an
ALTER
SESSION
statement to an application, an alter-
native way to dump Oracle Net packets is needed. All that is necessary is to copy
tnsnames.ora
and
sqlnet.ora
to the user's home directory and to set
TNS_ADMIN
to the same directory. Then, after
adding the following two lines to
sqlnet.ora
:
