6.2 Passwords For years , many operating systems have supported the ability to define password composition, complexity, aging, expiration, history, and account locking. With past releases, Oracle has lagged behind. But with the advent of Oracle8, these features have now become available in an Oracle database. All the password functions mentioned here are defined using the CREATE PROFILE statement, as shown in the previous section. To enable password management in your Oracle system, the UTLPWDMG.SQL script must be run as sys from the sysmgr account. This script can be found in: -
The $ORACLE_HOME/rdbms/admin directory on a UNIX system -
The $ORACLE_HOME/rdbms/admin directory on a Windows NT system -
The $ORACLE_HOME:[rdbms] directory on an OpenVMS system First, let's examine what each of these features lets the DBA accomplish. 6.2.1 Password Composition and Complexity Oracle's password composition and complexity features enable the DBA to describe how a password must look (its physical composition). Oracle's new complexity verification mechanism checks each password to ensure that it is complex enough to provide reasonable protection from someone who might be trying to guess a password to break into the database. Since the complexity verification is provided by Oracle through a PL/SQL function, you can add even more complexity to the default mechanism by writing your own function. The function must be owned by sys in order to perform properly. 6.2.1.1 Basic rules The basic rules enforced by the default PL/SQL function include a minimum length for the password and the requirement that one or more alphabetic, numeric, and punctuation marks must appear within the password. The default rules are that the password must: -
Contain a minimum of four characters -
Not be the same length as the userid -
Contain at least one alphabetic character, one numeric character, and one punctuation mark -
Differ from the previous password defined for this user by at least three characters The first three rules are pretty easy to understand. The intent of the fourth rule is to try to keep you from changing your password by just one character at a time. For example, if you have used the password my_1pass previously, this rule would prevent you from changing your password to my_2pass . These two passwords do not differ by at least three characters. A few passwords that would fulfill the complexity rules are: - du4_ck
- qxrb21.htm#
- ruok4me?
As with system passwords, you should not use words or names that have a known meaning to you, like your birthday or your child's or pet's name . [1] [1] Chapter 3 of Practical UNIX & Internet Security , by Simson Garfinkel and Gene Spafford, lists some excellent rules for choosing passwords. 6.2.1.2 Writing your own function If you decide to write your own function because you want to impose additional password complexity, you must use the format: routine_name( userid_parameter IN VARCHAR(30), password_parameter IN VARCHAR(30), old_password_parameter IN VARCHAR(30)) RETURN BOOLEAN Once you've created the function, you must assign the routine using either the user's profile or the system default profile by specifying the following CREATE PROFILE or ALTER PROFILE statement: CREATE/ALTER PROFILE profile_name LIMIT (first line of statement) PASSWORD_VERIFY_FUNCTION routine_name (parameter that names the routine) The complexity function can be defined for a specific user or group through the CREATE PROFILE statement. Here are some suggestions you may want to keep in mind when creating your own complexity function: -
If the password verification routine raises an exception, an appropriate error message must be returned -
The routine is owned by sys and functions in system context -
If the routine becomes invalid, an appropriate error message must be returned 6.2.2 Password Aging and Expiration Most operating systems have a mechanism to define how long a password can be used on the system before it expires . Oracle has not had this feature until Oracle8. The password lifetime can be set on either a group or an individual basis. By default, when password aging is enabled and the end of the life cycle for the password is reached, Oracle provides a grace period in which the user will be notified that the password must be changed. If the password is not changed during the grace period, the account will be locked and no logins will be permitted to the account until a DBA intercedes. The DBA can explicitly set when passwords will be expired via the PASSWORD_LIFE_TIME parameter, and the grace period with the PASSWORD_GRACE_TIME parameter. This feature is useful both when the DBA wants every account to change passwords (for example, if a system break-in has occurred) and when a new account is created (so the user will be forced to change the password to something only that user knows ). With the advent of password expiration comes the necessity to provide users with a mechanism through which they can easily change their passwords. You may need to write a small, simple, possibly web-based application to perform this action. The utility could be as simple as an SQL script that: -
Prompts the user for the current password -
Prompts for a new password -
Prompts for a verification of the new password -
Performs a comparison of the new password and the verification presented -
Connects to the database -
Issues the command: ALTER USER <username> IDENTIFIED BY <new_password>; Note the use of ALTER USER instead of the older style: GRANT <a_privilege> TO <username> IDENTIFIED BY <new_password>; The DBA should make an effort to ensure that the ALTER USER command syntax is used since Oracle is moving away from supporting the "GRANT ... IDENTIFIED BY..." format. If the users have access to the database via SQL*Plus, they can use the built-in password utility to change their passwords. That utility prompts for the old password, then the new one, and the new one again to verify it. We will discuss this utility later in this chapter. Because there are so many approaches to enabling a user to change passwords, we won't provide a specific example here. 6.2.3 The Password Life Cycle Let's look at the password life cycle (i.e., the length of time that a particular password remains valid) in a little more detail. Let us assume that the password life cycle has been set to 90 days (PASSWORD_LIFE_TIME 90) and the grace period has been set to 15 days (PASSWORD_GRACE_TIME 15). When the user logs in any time after the password reaches day 89, the expiration warning message will appear and the user will be notified of the 15-day grace period. If the user does not change passwords within the 15-day grace period, the account will be locked. Oracle provides for a password history through the use of the parameters PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX. These parameters work together and define how long a period of time must elapse before a password can be reused (if ever). Some users like their passwords so much that they keep returning to them this represents a security risk. PASSWORD_REUSE_TIME defines the number of days before a password can be reused, while PASSWORD_REUSE_MAX defines the maximum number of password changes before the password is allowed to be used again.  | When one of the parameters (either PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX) is set to a value, the other parameter must be set to UNLIMITED. You can specify an integer value for either PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX, but not both. Both parameters can be set to UNLIMITED. | | 6.2.4 Account Locking Until Oracle8, the only way to "lock" an account on an Oracle system was to revoke the role that had the CREATE SESSION privilege from the user or just revoke the CREATE SESSION privilege individually. In most cases, the role that was revoked was the CONNECT role. With Oracle8, the account can now be locked through several different approaches. The server will automatically lock an account: -
After a specific number of failed attempts have been made to access that account (set by the FAILED_LOGIN_ATTEMPTS parameter). -
When a password has expired and has not been reset during the allowed grace period or explicitly reset by the DBA (or, if your company has created the position, by the security manager). The account can be set to unlock after a specified period of time or by the DBA or security manager (PASSWORD_LOCK_TIME). The number of attempts allowed before an account is locked can be set on a group or individual. When a user successfully logs in, the number of failed attempts is automatically reset to zero. Likewise, if an account is re-enabled after a period of time, the failed attempts will be reset to zero. | | If a DBA or security manager locks an account, the account will not unlock automatically after a specified period of time. If a user fails to log in successfully and is locked out, however, the account will be unlocked after the PASSWORD_LOCK_TIME has been reached. If PASSWORD_LOCK_TIME is set to UNLIMITED, the account will never automatically unlock. | | Even if resource limitations are disabled through RESOURCE_LIMIT or through the ALTER SYSTEM command, the behavior of the password manager features is still enforced. In other words, if you have activated the password facility by running the UTLPWDMG.SQL script, there is no "undo" script you can run, and when you set the profile variables back to DEFAULT, the default password values will become available again.  | If you decide that you no longer want password features to be enabled, there is no easy way to disable these features once they have been enabled. | | 6.2.5 Password Enhancements in the Data Dictionary Views The following columns have been added to the USER_USERS and DBA_USERS views in Oracle8 to support password features (see the description of these views in Chapter 4): - ACCOUNT_STATUS
-
Tells whether the account is locked, expired, or open - GRACE_DATE
-
Tells when the password will expire (the sum of the date when the password was changed and the password resource parameter, PASSWORD_LIFE_TIME) - LOCK_DATE
-
Tells when the account was locked - EXPIRE_DATE
-
Tells when the account will expire The following new columns have been added to the DBA_PROFILES view in Oracle8: - RESOURCE_TYPE
-
Is set to either KERNEL or PASSWORD - USER_PASSWORD_LIMITS
-
Contains the column RESOURCE_NAME (the name of the password resource) and LIMIT (either a limit set by the DBA or the DEFAULT value) 6.2.6 Passwords and Data Encryption When a user is created in the database, a password is assigned by the person creating the account usually within the CREATE USER script. In this way, Oracle can verify that the user should be allowed access to the database. Oracle automatically encrypts the password and stores it in the data dictionary in a 16-character encrypted format so it's not easily readable by anyone examining the data dictionary views. In the same way, the user proves, by using the correct password, that access should be allowed. When client/server networking is being used, Oracle also enables the encryption of passwords when connecting to a database over the network. If this feature is enabled through the use of the Advanced Networking Option (ANO), Oracle uses a modified Data Encryption Standard (DES) algorithm to encrypt the password before it is sent over the network. Oracle8 also provides the ability to encrypt actual data and to produce checksums. Use of network data encryption and checksums ensures that the data cannot be read by intruders while it is in transit to the database. As with password encryption, DES is used to encrypt the data, but the RSA Data Security RC4 standard is used as well. Using the Advanced Networking Option available in Oracle8, a cryptographically secure message digest can be included in the header of each packet to ensure that data has not been intercepted, deleted, read, or in any way tampered with. We'll describe DES, the RSA algorithms, cryptography, and data encryption more extensively in Chapter 15. In Chapter 17, we'll provide an overview of the Advanced Networking Option as well as Trusted Oracle and the Oracle Application Server. 6.2.7 Password Scripts and Commands Have you ever wondered how the Oracle import utility accesses accounts to build the account's objects without ever knowing what the user's password really is? That utility uses an undocumented keyword, VALUES, to enable the use of the encrypted form of a password without ever knowing the unencrypted password. This section describes this facility, as well as the PASSWORD command available from SQL*Plus in Oracle8. 6.2.7.1 Swapping passwords There are several situations in which you might want to be able to use an Oracle database account without knowing the user's actual password. In these cases, you want to be able to change a user's password temporarily and then change it back to its original value. For example, you might want to: -
Use a specific account to test something -
Run an application as a very privileged user without having the user's true password displayed in the operating system command (as a UNIX system would) -
Avoid the need to hard-code a real password in an operating system command file How can you "swap" out the user's real password without knowing it and then, after you are finished using the account, replace the password you set with the user's original password? From a DBA account, you can take the following steps to use an account whose password you do not know (Oracle7 or higher). -
Generate a SQL-generating SQL script by querying the DBA_USERS table to obtain the user's current encrypted password. -
Alter the user's password to a value you know. -
Perform the work you need to do. -
Run the SQL script you created in Step 1 to replace the user's original password. The section of script you can use to capture the encrypted password would look like the following: SET TERMOUT OFF PAGESIZE 0 FEEDBACK OFF VERIFY OFF ECHO OFF SPOOL redo.sql SELECT 'ALTER USER &&1 IDENTIFIED BY VALUES '''''password''''';' FROM dba_users WHERE username = UPPER('&&1') PROMPT '$<place your operating system command to delete the file here>' PROMPT '$ EXIT' SPOOL OFF In the first PROMPT line, place your specific operating system command to delete the REDO.SQL file. For example, on an OpenVMS system, the PROMPT line would look like the following: PROMPT '$ DELETE/NOLOG/NOCONFIRM redo.sql;' On a UNIX system, the command might look like the following: PROMPT 'host rm -f redo.sql' The output from the script, using the OpenVMS DELETE command, would look like the following: ALTER USER mary IDENTIFIED BY VALUES '91B4B1F913D22B19'; $ DELETE/NOLOG/NOCONFIRM redo.sql; $ EXIT You then issue the following commands to modify the user account and connect to the modified account: ALTER USER mary IDENTIFIED by abc75!d; CONNECT mary /abc75!d After performing the necessary work, simply reconnect to the system account and run the REDO.SQL script to reset the mary account to its original password. This approach is very valuable to any DBA who has to perform work from a cron job as the sys user. The UNIX operating system has a command that will display the entire command line. If the username and password are used on the command line, they can be seen by other users. Using this approach will let you access the sys account with a benign password that will not compromise security if it is seen. | | If you have enabled the password history feature on your Oracle8 database, this approach can be used only if you modify the user's profile values before you begin your work. If you do not modify the parameter PASSWORD_REUSE_TIME, you violate the restriction on the amount of time that must pass or the number of passwords that must be used before the current password can be reused. | | 6.2.7.2 The Oracle8 PASSWORD command Beginning in Oracle8, there is a new SQL*Plus command, PASSWORD, which you can use to change your password. If you have DBA privileges on a database, you can also use this command from SQL*Plus to change another user's password. For security reasons, this utility echoes all password characters you type in as asterisks (*). In the example presented here, the PASSWORD command is used by mary to change her own password from one value to another. The transaction looks like the following: SQL> PASSWORD /* Changing password for mary */ Old password: ******* New password: ******* Retype new password: ******* Password changed Before Oracle8, if you, working as the DBA, wanted to change a user's password, the ALTER command had to be used, as shown here: ALTER USER <username> IDENTIFIED BY <new password>; In Oracle8, you can now use the PASSWORD command to change a user's password by typing PASSWORD followed by the username as follows : SQL> PASSWORD mary Changing password for mary New password: ******* Retype new password: ******* Password changed When changing another user's password, you are not prompted for the user's old password. Users can take advantage of the PASSWORD command from SQL*Plus without having to know the syntax of the ALTER USER command. | 
