by: Paul Fleming
As an Oracle DBA, you probably have heard a lot about the new security features in Oracle 11G such as database vault, audit vault, strong verifier, enhanced ASO, etc. But with a busy schedule, how do you find the time to study all the topics in depth? Are all of them going to affect your daily DBA operations? What should you know first and foremost? Of course, depending on the particular database you are administering, you will have your unique need. But as a rule of thumb, what tends to affect you the most is almost never those cool new features. The reason is simple: if you don't use them, they won't affect you. What tends to affect you is the change in the existing features that might lead to compatibility or interoperability issues. When that script you have used for years suddenly stops working, you will be disappointed. When your boss or above calls to ask what happened, you could feel terrible.
In this article, we discuss the new password case sensitivity feature in 11G. Every Oracle session starts with authentication. If the authentication fails at the beginning of your script, everything that follows will almost certainly be broken. So what was changed, why, and what should you know?
A Primer on Verifier
I'll start with a primer on password verifier. As you probably know, for security reasons, Oracle user's passwords are never stored as clear text. Instead, they are stored as a "verifier", a one-way hash value calculated from the clear text password. Hash verifier has a few unique features. One, it is "one-way": It is easy to calculate the hash value from a clear text password, but it is nearly impossible to figure out the clear text password from a hash value. Two, different clear texts almost never yield the same hash value (called collision). These features allow Oracle database to store the hash value of a user's password as a surrogate for the clear text password. When a user provides a password to logon, Oracle calculates the hash value from that password, and allows the user to logon if it matches the hash value that is stored in the database. To get a feel what an Oracle verifier looks like, take a look at the PASSWORD column in the user$ table in SYS schema (of course, you will need SYSDBA privilege to do that).
SELECT NAME,PASSWORD FROM SYS.USER$ ORDER BY NAME;
What Was Changed
Prior to 11G, Oracle passwords were case insensitive. "foobar", "FOOBAR", and "FoOBaR" are identical when used as an Oracle password. They yield the same hash value. If a user could log in with password "foobar", he could log in with "FOOBAR" as well. In 11G, however, when you create or modify user accounts, by default passwords are case sensitive. 11G also introduces a "salt" in the hash value generation so that even two users have identical passwords, their verifiers would still be different.
Oracle allows to you to control password case sensitivity for backward compatibility by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter. Only users who have the ALTER SYSTEM privilege can set the SEC_CASE_SENSITIVE_LOGON parameter. Set it to TRUE to enable case sensitivity or FALSE to disable case sensitivity.
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE | FALSE;
How Case Sensitivity Affects Password Files
As you may remember, Oracle stores the passwords (again in the form of verifiers) of users with SYSDBA and SYSOPER privileges in a "password file" on the server, rather than in the database. You can enable or disable case sensitivity for password files by using the ignorecase argument in the ORAPWD command line utility. The default value for ignorecase is n (no), which enforces case sensitivity.
Here is an example of how to enable password case sensitivity for SYSDBA and SYSOPER users
orapwd file=orapw entries=100 ignorecase=n
Enter password for SYS: password
This creates a password file called orapwd. Since ignorecase is set to n (no), the password entered for the password parameter will be case sensitive. Afterwards, if you connect using this password, it succeeds(as long as you enter the password using the exact same characters with the exact same case as created). If you enter the same password with different case, it will fail
If you, the DBA, sets the system to ignorecase to y, then the passwords in the password file are case insensitive, and this means that you can enter the password using any capitalization that you want.
It is important to note that given the way it is controlled, within one Oracle system, the password's case sensitivity of a regular user and a SYSDBA/SYSOPER user can be independent.
How Password Case Sensitivity Affects Import and Upgrade
What happens when you export some users from a previous database where the password is case insensitive into 11G? The case-insensitive passwords in these accounts remain case insensitive, even if the 11G system has turned on password case sensitivity. This makes sense because otherwise old users may not be able to log on from a previously working script or client program. This, however, will change when the user changes his or her password.
You can find users who have case sensitive or case insensitive passwords by querying the DBA_USERS view. The PASSWORD_VERSIONS column in this view indicates the release in which the password was created. For example:
SELECT USERNAME, PASSWORD_VERSIONS FROM DBA_USERS;
USERNAME PASSWORD_VERSIONS
------------------------------ -----------------
JONES 10G 11G
ADAMS 10G 11G
CLARK 10G 11G
PRESTON 11G
BLAKE 10G
The passwords for accounts jones, adams, and clark were originally created in Release 10g and then reset in Release 11g. Their passwords, assuming case sensitivity has been enabled, are now case sensitive, as is the password for preston. However, the account for blake is still using the Release 10g standard, so it is case insensitive. Once he changes his password, it will become case sensitive and more secure.
Conclusion
Users' passwords in Oracle 11G database become case sensitive by default. Regular user and SYSDBA/SYSOPER user's password case sensitivity can be controlled independently. If you have a script with inconsistent password cases, the script may stop working with 11G even though it works fine with previous releases. To learn more about this topic, read "Security Guide" (Oracle Part Number B28531-03).
Peter Dwyer is an internationally known System and Database Architect for Oracle environments. He is founding principal of The Certainty Group (
http://www.certaintygroup.com), a Boston-based Oracle database consultation company specializing in quickly assessing and correcting complex problems in multi-vendor, mission-critical systems that demand high performance and reliability.
