Revoke SYSDBA from the Users

Revoke SYSDBA priv

select * from v$pwfile_users where sysdba='TRUE';

Fact sheet about Oracle database passwords

Fact sheet about Oracle database passwords


Oracle Password Algorithm (7-10g Rel.2) (Designed by Bob Baldwin)

Up to 30 characters long. All characters will be converted to uppercase before the hashing starts
8-byte hash, encrypted with a DES encryption algorithm without real salt (just the username).
The algorithm can be found in the book "Special Ops Host and Network Security for Microsoft, Unix, And Oracle" on page 727.

Oracle database 11g offers the (optional) possibility to use passwords up to 50 characters (uppercase/lowercase). In Oracle 11g the passwords are now hashed with DES (column: password) AND using SHA-1 (column: spare4). The SHA-1 passwords are now supporting mixed-case passwords. In 11g the password hashes are no longer available in dba_users.

• Oracle (7-10g R2) encrypts the concatenation of (username||password)
  and sys/temp1 and system/p1 have the identical hashkey (2E1168309B5B9B7A)
• Oracle (11g R1) uses SHA-1 to hash the concatenation of (password||salt)

Oracle Password Cracker

A comparision of different password cracker can be found here. Benchmarks for different password crackers are available here (10g) and here (11g) .


Location of Oracle password hashes

• Database - SYS.USER$ - Password
• Oracle Password File
• Data File of the system tablespace
• (full) Export-Files
• archive logs

Show Oracle password hashkey (old DES hash)

You should always select database users from the table not from the views (ALL_USERS, DBA_USERS). An explanation (modification of database views via rootkits) can be found here.

• DBA_USERS : SELECT username, password FROM DBA_USERS;
• SYS.USER$ : SELECT name,password FROM SYS.USER$ WHERE password is not null;

Show Oracle password hashkey (11g, new SHA-1 hash)

In 11g the password hash is no longer accessible via dba_users

• SYS.USER$ : SELECT name,spare4 FROM SYS.USER$ WHERE password is not null;

How to change an Oracle password?

You should always use the password command because the password is sent unencrypted over the net (without Advanced Security Option) if you use the alter user syntax.

• alter user myuser identified by my!supersecretpassword;
• grant connect to myuser identified by my!supersecretpassword
• update sys.user$ set password='F894844C34402B67' where name='SCOTT'; (restart of the database necessary)
• SQL*Plus command: password or password username

How to change an Oracle password temporarily?

In Oracle it is possible to change a password temporarily. This can be useful for DBA which act as a different user.

SQL> select username,password from dba_users where username='SCOTT';

USERNAME PASSWORD
-------- ----------------
SCOTT F894844C34402B67

SQL> alter user scott identified by mypassword;


Now login with the following credentials: scott/tiger
After doing your work you can change the password back by using an undocumented feature called "by values"

SQL> alter user scott identified by values 'F894844C34402B67';


Oracle default password list

600+ default Oracle passwords


Oracle Password Policy

It is possible to setup a password policy (for strong Oracle passwords). A sample file how to do this can be found at $ORACLE_HOME/rdbms/admin/utlpwdmg.sql. If you use this functionality please be aware that the password policy function has access to the cleartext password (for the comparisions reasons). With Oracle 11gR1 Oracle greatly enhanced the password verification function.

A hacker could modify your function and log all cleartext passwords entered by the users to a table or send it to a foreign webserver with utl_http. That's why you should checksum this function, e.g. with Repscan.

Oracle brute force attacks / Oracle Password Decryption (7-10gR2)

It is not possible to decrypt a hashstring but the simple Oracle salt (=Username) it is possible to do a brute force or dictionary attack. There are several Oracle brute force or dictionary attack tools available. These tools encrypt the username/password and compare the hashkeys. If the hashkey are identical the password is known. From simple SQL based tools (<500 href="http://www.red-database-security.com/software/checkpwd.html">checkpwd. The fastest tool for brute force attacks orabf calculates 1.100.000 passwords/second. The fastest tool for dictionary attacks are checkpwd and repscan with 600.000 pw per second. On a Pentium 4 with 3 GHz it takes (26 ascii characters only, e.g. 26^5)

• 10 seconds to calculate all 5-ascii-character-combinations
• 5 minutes to calculate all 6-ascii-character-combinations
• 2 hours to calculate all 7-ascii-character-combinations
• 2,1 days to calculate all 8-ascii-character-combinations
• 57 days to calculate all 9-ascii-character-combinations
• 4 years to calculate all 10-ascii-character-combinations

You should always use strong and long passwords to avoid brute force or dictionary attacks.


Typical Error messages related to Oracle database passwords

The following error messages are related to Oracle passwords:

• ORA_28000: The account is locked
• Wait for PASSWORD_LOCK_TIME or contact your DBA
• ORA-28001: The password has expired
• Change the password or contact your DBA
• ORA-00988: Missing or invalid password(s)
• Use double quotes for the password (e.g. alter user scott identified by "!alex";)
• ORA-01017: Invalid username/password; logon denied
• Contact your DBA if the problem still persists

Oracle database passwords in cleartext

Cleartext passwords can be typically but not necessarily found at the following places

• Server
• Shell History files
• Unix Scripts
• Log Files
• Dump Files
• Trace Files
• Application Server
• JDBC-Config-Files
• Trace Files
• DBA Client PC
• Desktop-Shortcut
• Batch-Files
• Configuration files of Oracle Tools (like connections.ini)
• Trace Files

References

• Oracle Password Cracker Checkpwd 2.00 by Red-Database-Security GmbH (fast, 600.000 pw/sec, C-based, 7-11g)
• Oracle Password Cracker Benchmark Benchmark for various Oracle password crackers (old password algorithm)
• Oracle 11g Password Cracker Benchmark Benchmark for various Oracle11g password crackers
• Oracle Default Password Scanner (DPS) SQL Script that checks 689 default passwords
• Oracle Password Algorithmus 7-10g Rel.2
• Analysis Oracle Password Algorithmus 11g
• Oracle Password Cracker 11g (P.o.C.)
• Comparision Chart Oracle Password Cracker
• Hardening DBA Client PC
• An Assessment of the Oracle Password Hashing Algorithm

Solution of ORA-28002: the password will expire within 5 days

Error Description:
-----------------------------------
Whenever a user try to connect to database it raise ORA-28002: error.

-bash-3.00$ sqlplus Hemesh/a

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 7 23:58:46 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-28002: the password will expire within 10 days


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Cause of The Problem:
-----------------------------------
Lets know the user profile.
SQL> select profile from dba_users where username='HEMESH';

PROFILE
------------------------------
DEFAULT

Now lets know the resource settings 'PASSWORD_GRACE_TIME','PASSWORD_LIFE_TIME' of default profile.

SQL> select LIMIT, RESOURCE_NAME from dba_profiles where RESOURCE_NAME in ('PASSWORD_GRACE_TIME','PASSWORD_LIFE_TIME','PASSWORD_REUSE_MAX','PASSWORD_REUSE_TIME') and PROFILE=(select profile from dba_users where username='HEMESH');

LIMIT RESOURCE_NAME
---------------------------------------- --------------------------------
60 PASSWORD_LIFE_TIME
1800 PASSWORD_REUSE_TIME
UNLIMITED PASSWORD_REUSE_MAX
10 PASSWORD_GRACE_TIME

The resource PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX must be set in conjunction with each other. PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused.

In this case our interested resource is PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME.

The resource of Default profile PASSWORD_LIFE_TIME specify the number of days the same password can be used for authentication.

The resource PASSWORD_GRACE_TIME specify the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires.

Here in the profile of HEMESH user the value of PASSWORD_GRACE_TIME is set to 10. So it just arises a warning ORA-28002 but still allow users to logon to database. The password will expire if it is not changed within the grace period, and further connections are rejected. If you do not set a value for PASSWORD_GRACE_TIME, its default of UNLIMITED will cause the database to issue a warning but let the user continue to connect indefinitely.

One may interpret wrongly of parameter PASSWORD_LIFE_TIME with account creation time. Actually the PASSWORD_LIFE_TIME limit of a profile is measured from the last time an account's password was changed or the account creation time if the password has never been changed.

The account creation time and password change time can be seen from USER$.CTIME and USER$.PTIME respectively. Like,
SQL> select ctime, ptime from sys.user$ where name='HEMESH';

CTIME PTIME
--------- ---------
08-MAY-08 08-MAY-08

You can also get the account creation time from dba_users view.
SQL> SELECT CREATED FROM DBA_USERS WHERE USERNAME = 'HEMESH';

CREATED
---------
08-MAY-08

Now let's look current time which is 08-JUL-08
SQL> select sysdate from dual;

SYSDATE
---------
08-JUL-08

So between the password change time and current time there it is passed 60 which is equal to PASSWORD_LIFE_TIME. Now the setting of PASSWORD_GRACE_TIME to 10 allow the user HEMESH to connect to database 10 days more but will issue a warning.

Solution of The Problem:
--------------------------------------
A)Change the user password.
------------------------------------
If you just want to avoid the error temporary then change the user password.
SQL> conn Hemesh/a
ERROR:
ORA-28002: the password will expire within 10 days
SQL> password
Changing password for HEMESH
Old password:
New password:
Retype new password:
Password changed
SQL> conn Hemesh/a!12
Connected.

Now you can see the change time by .
SQL> select ctime, ptime from sys.user$ where name='HEMESH';

CTIME PTIME
--------- ---------
08-MAY-08 08-JUL-08

This is a temporary solution. After 60 days the user will again see the warning message.

B)Change PASSWORD_LIFE_TIME resource of profile assigned to user.
----------------------------------------------------------------------------------------

The permanent solution is to change PASSWORD_LIFE_TIME resource of profile DEFAULT which is assigned to user HEMESH.

SQL> select profile from dba_users where username='HEMESH';

PROFILE
------------------------------
DEFAULT
If you make it unlimited then user never will see above error. Like,
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

SQL > select * from dba_users where username='HEMESH';

ACCOUNT_STATUS = Expired(GRACED)

SQL> ALTER USER HEMESHidentified by newpassworld;

user altered.

SQL > select * from dba_users where username='HEMESH';

ACCOUNT_STATUS = OPEN

******************* Open an Expired Account***********************************

SQL> select account_status from dba_users where username='THOMAS';

ACCOUNT_STATUS

--------------------------------

EXPIRED

Up to 10g ) SQL> select password from dba_users where username='THOMAS';

from 11g) SQL>select password from SYS.user$ where name ='THOMAS';

PASSWORD

-----------------------------

063D8DE086C2860D

SQL> alter user thomas identified by values '063D8DE086C2860D';

User altered.

SQL> select account_status from dba_users where username='THOMAS';

ACCOUNT_STATUS

--------------------------------

OPEN

SQL> conn thomas/thomas

Connected.