amar on web

Case sensitive passwords in 11g

Oracle has made commendable changes in database release 11g for security, password authentication mechanism is one of the areas attacked. Following are the key changes for Password authentication.

Passwords are case sensitive starting from release 11g.
The password can now contain a mixture of special and multi-byte characters.
This feature is controlled by an initialization parameter (you have the option to revert to non-case sensitive password).
A strong password hashing algorithm, 160-bit SHA-1, is included (along with the old DES based algorithm for backward compatibility).
A View DBA_USERS_WITH_DEFPWD is provided to identify users left with default passwords (yes lazy DBAs need it!).
Database link passwords are also encrypted (introduced in release 10g).

Migration impact

When migrating from earlier release, the password will not be case sensitive. However, the first time the password is changed for an account after upgrade, it will become case sensitive. It is recommended that users be requested (forced!) to change the password after migrating to 11g. The view DBA_USERS has been added with a new column PASSWORD_VERSIONS that identifies if a password is still in the old encryption format or is changed to the new hashing algorithm.

AM02:SYS> select username, password, password_versions from dba_users;

USERNAME        PASSWORD             PASSWORD
--------------- -------------------- --------
SYS                                  10G 11G 
SYSTEM                               10G 11G
AMAR                                 10G 11G

3 rows selected.

In the above output the value "10G 11G" for PASSWORD_VERSIONS means that both old and new algorithm is available for the password and the 11g format is being used. A value of only "10G" in this column would mean that the user password has not been changed post migration. One drawback is that release 11g now stores the password in both the old and new encryption algorithm. I believe this may be attributed to backward compatibility. This could also result in some one hacking in using the earlier available options, read on.

Attributes

Checkout how the encrypted password looks in 10g:-

AM01:SYS> select name, password, spare4 from user$ where name = 'AMAR';

NAME       PASSWORD             SPARE4
---------- -------------------- ----------------------------------------
AMAR                            C5D5B636B43DB0C5

The 11g password hash value is stored in SPARE4 column of SYS.USER$ table. See how it looks now:-

AM02:SYS> select name, password, spare4 from user$ where name = 'AMAR';
NAME       PASSWORD             SPARE4
---------- -------------------- ---------------------------------------------------------------
AMAR       C5D5B636B43DB0C5     S:B342332C111B24DE10322F25BD47229585B837F76DF51C8F857826CA4A89

Here is an example of case-sensitivity; the length supported is still limited to 30 characters.

AM02:SYS> alter user amar identified by AmAr;
User altered.

AM02:SYS> conn amar/amar
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

AM02:SYS> conn amar/AmAr
Connected.

Here is an example of using real special characters in password.

AM02:AMAR> alter user amar identified by "amÿn½¾é³";
User altered.

AM02:AMAR> conn amar/amÿn½¾é³
Connected.

New Initialization Parameter

Initialization parameter SEC_CASE_SENSITIVE_LOGON is introduced to control case sensitivity. When set to FALSE, existing passwords are no more case-sensitive. For backward compatibility, Oracle has retained both approach. Setting SEC_CASE_SENSITIVE_LOGON to false turns off the 11g security.

AM02:SYS> alter system set sec_case_sensitive_logon=false scope=both;
System altered.

AM02:SYS> alter user amar identified by "AmAr*ReVerT";
User altered.

AM02:SYS> conn amar/amar*revert
Connected.

Does the old Password Hack work?

Well it is not exactly a hack but an option that has been misused in the past. Recollect IDENTIFIED BY VALUES clause that allows you to connect as another user, ok even to SYS (on network if REMOTE_LOGIN_PASSWORDFILE parameter is set to EXCLUSIVE and a password file is in place). If not, you know it now..

Till 10g, read access on DBA_USERS and ALTER USER privilege can allow you to login as another user without knowing the password. The encrypted password visible in PASSWORD column of DBA_USERS can be used. Access to DBA_USERS is available through SELECT ANY DICTIONARY privilege or SELECT_CATALOG_ROLE.

Howit works in 10g
User TEST wants to connect as AMAR to do some updates. TEST has the above privileges.

AM01:TEST> select password from dba_users where username='AMAR';

PASSWORD
------------------------------
C5D5B636B43DB0C5

1 row selected.

AM01:TEST> alter user amar identified by abc;

User altered.

TEST can now open another session and login as AMAR, after which the user password can be reset to the original one.

AM01:TEST> alter user amar identified by values 'C5D5B636B43DB0C5';
User altered.

Will it work in 11g?
Well for one, the view DBA_USERS no longer shows the password.

AM02:TEST> select password from dba_users where username = 'AMAR';

PASSWORD
------------------------------


1 row selected.

So that takes care of the above approach. Going further, what if the user has access to SYS.USER$ table (controlling access on this definitely will not be a problem for DBA, but just in case the grant slips through).

AM02:TEST> select password, spare4 from sys.user$ where name = 'AMAR';

PASSWORD         SPARE4
---------------- ----------------------------------------------------
77B95C2B162C2898 S:F86B0A39D5D6FF3D1ED81E1D389D558EC174A48D6500DE29D2A90068921D

The user can now view the password both in old and new encryption from the USER$ table. Let see if we can use the new encrypted format for resetting passwords.

AM02:TEST> alter user amar identified by abc;
User altered.

AM02:TEST> alter user amar identified by values'S:F86B0A39D5D6FF3D1ED81E1D389D558EC174A48D6500DE29D2A90068921D';
User altered.

AM02:TEST> select password, spare4 from sys.user$ where name = 'AMAR';

PASSWORD         SPARE4
---------------- ----------------------------------------------------------
                 S:F86B0A39D5D6FF3D1ED81E1D389D558EC174A48D6500DE29D2A90068921D

1 row selected.

AM02:TEST> conn amar@am02
Enter password:
Connected.

So the resetting with IDENTIFIED BY VALUES clause works. In effect, I was able to use VALUES clause to reset the original password using the old encryption format present in PASSWORD column also, though case-sensitivity is lost. One draw back of using the VALUES clause is that the used encryption format is only updated for 11g in the SYS.USER$ table. Look at the above query on SYS.USER$ after resetting the password using VALUES clause and the 11g encrypted password, the PASSWORD column is empty. Query on DBA_USERS will now show only 11g encryption for the user.

AM02:SYS> select username, password, password_versions from dba_users;

USERNAME        PASSWORD             PASSWORD
--------------- -------------------- --------
SYS                                  10G 11G
SYSTEM                               10G 11G
AMAR                                 11G
TEST                                 10G 11G

4 rows selected.

Identifying Oracle created users with default passwords

Make use of the view DBA_USERS_WITH_DEFPWD to identify all users with default passwords.

AM02:SYS> select * from dba_users_with_defpwd;

USERNAME
---------------
DIP
OUTLN
DBSNMP

3 rows selected.

Password for Database Links

The changes introduced in release 10g hold good in 11g also. Oracle used to store database link passwords as clear text in the data dictionary, i.e., without any encryption mechanism to hide them from normal viewing. Any one having access to the required database link views on one database could gain access to other databases. The onus falls on the DBA to instigate security on the databases to lock up such views. This is the case till Oracle 9i.

Oracle has taken care of this flaw from release 10g onwards, more precisely from version 10.2.0. This may however have an implication on designs that rely on connection details stored in the database link related views (yes I have come across such designs). Please check the below example to understand more.

The following are the available database views.

USER_DB_LINKS - gives information of the private database links you own, including passwords.

DBA_DB_LINKS - gives information of all the database links, no password column here.

ALL_DB_LINKS - gives information of all the database links available to you, no password column here.

SYS.LINK$ - the base table for the above views, this holds all the information for database 
            links including passwords.

So the key views here are USER_DB_LINKS and LINK$ as the password is visible (I am assuming here that the other views are not man-handled and the source is as per what Oracle provides). DBAs ensure that the LINK$ cannot be queried on by non-admin users, this prevents any and all the passwords from being exposed.

USER_DB_LINKS anyway provides the connection details for the private links for a user. I have come across designs that pick up the username and password from this view to make a front-end connection to a database (basically switching between databases). Unfortunately, this logic will no more work from release 10g onwards. Let's check out some examples.

------In Oracle 10g
SQL> create database link am10gtst connect to rowdy identified by tstpwd using 'am10';
Database link created.

SQL> select * from user_db_links where db_link = 'AM10GTST';

DB_LINK   USERNAME        PASSWORD             HOST            CREATED
--------- --------------- -------------------- --------------- ---------
AM10GTST  ROWDY                                am10            31-JUL-07

1 row selected.

------In Oracle 9i
SQL> create database link am9itst connect to rowdy identified by tstpwd using 'am10';
Database link created.

SQL> select * from user_db_links where db_link = 'AM9ITST';

DB_LINK   USERNAME         PASSWORD           HOST          CREATED
--------- ---------------- ------------------ ------------- --------
AM9ITST   ROWDY            TSTPWD             am10          02-AUG-07

1 row selected.

As you can see the password is not available for bare eyes viewing in the first query. The change done in version 10.2.0 is that the LINK$ table now contains a new column called PASSWORDX, this stores the encrypted password value. Details of the encryption scheme used is reserved with Oracle corporation. Oracle has taken care of one more drawback in data security with this feature.

SQL> select name, host, userid, password, passwordx from link$ where name = 'AM10GTST';

NAME                 HOST    USERID  PASSWORD  PASSWORDX
-------------------- ------- ------- --------- ------------------
AM10GTST.LOCALDOMAIN am10    ROWDY             057DAA14B93EDB574DF416496AD7C10E68

Summary

Oracle database release 11g is packed with new features and lot of improvements on security front. This note looked at the improvements in password encryption and prevention of illegal intrusion.

Hiding PASSWORD column in DBA_USERS has removed the threat presented in 10g and earlier releases. DBA will need to ensure that grant on SYS.USER$ is controlled in 11g. Besides, the hack works in 11g but will leave traces.

Best viewed in medium text size. Please refresh (F5) to view the latest information.
This page was created on 23-Oct-07. Last updated on 24-Jan-09.
please forward all queries to amar@amar-padhi.com