Changing a User's Password

Sooner or later, one of your users is going to forget his or her password. You can reset a user's password by using the ALTER USER command. The following example shows the password for the user named COIN_ADMIN being changed to FORGETFUL.

SQL> ALTER USER coin_admin IDENTIFIED BY forgetful;
User altered.

Users can change their own passwords using this command, but only the DBA can change a password for someone else. You also can use Security Manager, which has an easy-to-use GUI interface, to change a user's password. The following section talks you through this process.

1. From Security Manager's main window, expand the Users folder so that you can see a list of users. Do this by double-clicking the folder, or by single-clicking the "+" right next to it.
2. Click once on the COIN_ADMIN user in the left-hand pane of the window. The user's information will be displayed in the right-hand pane.
3. Now, click the Password field, and type FORGETFUL to set the new password. Then, click the Confirm Password field, and type FORGETFUL again. When you are finished, click the Show SQL button.
4. As you can see, the SHOW SQL button may be clicked any time you are interested in seeing the underlying commands that Security Manager is executing for you. Now, click the Apply button to execute the ALTER USER statement, changing the user's password.
5. The statement is executed and the SQL Text box is cleared. You can make further changes to the user or you can go on to do something else. This is the end of the Simulation.

Changing the SYS User Password
If you must change the SYS user password, then you should use the ORAPWD command line utility to create a new password file that contains the password that you want to use. Do not use the ALTER USER statement or the PASSWORD command to change the SYS user password. Note the following:

1. The SYS user account is used by most of the internal recursive SQL. Therefore, if you try to use the ALTER USER statement to change this password while the database is open, then there is a chance that deadlocks will result.
2. If you try to use ALTER USER to change the SYS user password, and if the instance initialization parameter REMOTE_LOGIN_PASSWORDFILE has been set to SHARED, then you cannot change the SYS password.

The ALTER USER statement fails with an ORA-28046: Password change for SYS disallowed error. Example 6.11 shows how to use ORAPWD to create a password file that has a new SYS password. In this example, the new password will be stored in a password file that will be called orapworcl. (If the password file already exists, then an OPW-00005: File with same name exists - please delete or rename error warns you so that you can choose another name. If you want to overwrite the existing password file, then append the force=y argument to the ORAPWD command.)

orapwd file='orapworcl'
Enter password for SYS: new_password

As a DBA, you can exert some control over how users manage their passwords. You can force periodic password changes .

Oracle has a feature that lets you force users to change their passwords periodically. You use profiles to implement password changes and can control two factors:

1. The number of days a password can be used before it must be changed
2. The length of the grace period during which time a user is warned to change the password each time he or she connects to the database

You can implement a password lifetime limit for the default profile by using a command like this:

ALTER PROFILE default LIMIT
PASSWORD_LIFE_TIME 30
PASSWORD_GRACE_TIME 3;

In this example, users assigned the default profile are allowed to use their passwords for 30 days. When the 30-day limit is reached, each time users connect they receive a message, reminding them to change their password. They have 3 days to make the change. If, after 3 days, a user has not changed his or her password the user's account is locked.
You will learn more about profiles later in this course. For now though, realize that every database has, or should have, a default profile, and that new users are assigned to this profile unless you specify otherwise. If you have been following along with the examples, the users in the COIN database should all fall under the default profile.

Oracle uses the username and password to connect to the remote database. The username and password for a database link ^[1] are optional.
Referring to Database Links: Database links are available only if you are using Oracle distributed functionality. When you issue a SQL statement that contains a database link, you can specify the database link name in one of these forms:

• The complete database link name as stored in the data dictionary, including the database, domain, and optional connect_descriptor components.
• The partial database link name is the database and optional connect_ descriptor components, but not the domain component.

Oracle performs these tasks before connecting to the remote database:

1. If the database link name specified in the statement is partial, then Oracle expands the name to contain the domain of the local database as found in the global database name stored in the data dictionary. (You can see the current global database name in the GLOBAL_NAME data dictionary view.)
2. Oracle first searches for a private database link in your own schema with the same name as the database link in the statement. Then, if necessary, it searches for a public database link with the same name.
   
   • Oracle always determines the username and password from the first matching database link (either private or public). If the first matching database link has an associated username and password, then Oracle uses it. If it does not have an associated username and password, then Oracle uses your current username and password.
   • If the first matching database link has an associated database string, then Oracle uses it. Otherwise Oracle searches for the next matching (public) database link. If no matching database link is found, or if no matching link has an associated database string, then Oracle returns an error.
3. Oracle uses the database string to access the remote database. After accessing the remote database, if the value of the GLOBAL_NAMES parameter is true, then Oracle verifies that the database.domain portion of the database link name matches the complete global name of the remote database. If this condition is true, then Oracle proceeds with the connection, using the username and password chosen in Step 2. If not, Oracle returns an error.
4. If the connection using the database string, username, and password is successful, then Oracle attempts to access the specified object on the remote database using the rules for resolving object references and referring to objects in other schemas discussed earlier in this section.

You can disable the requirement that the database.domain portion of the database link namemust match the complete global name of the remote database by setting to false the initialization parameter GLOBAL_NAMES or the GLOBAL_NAMES parameter of the ALTER SYSTEM or ALTER SESSION statement.
As an Oracle DBA you can temporarily change a user's password.

An interesting but undocumented Oracle feature lets you temporarily log in as another user. Logging in as another user is helpful when troubleshooting privilege-related problems. To use this technique you do not even need to know the other user's password. For example, say that you are the SYSTEM user, and that you need to log in as the COIN_ADMIN user. The first thing to do is to retrieve the COIN_ADMIN user's password. This example shows how to do that:

SQL> SELECT password 
  2  FROM dba_users
  3  WHERE username='COIN_ADMIN';
PASSWORD
------------------------------
B1DB09EA3F74189C

Hexadecimal Representation of the encrypted Password: The value that you get for the password when you query DBA_USER is the hexadecimal representation of the encrypted password. You cannot use it to log in. Just save this value so that later you can restore it. The next task is to temporarily change the COIN_ADMIN user's password:

SQL> ALTER USER coin_admin IDENTIFIED BY temp;
User altered.

Now you can log in as

COIN_ADMIN/TEMP
SQL> CONNECT coin_admin/temp@coin
Connected

Once you are logged in as the COIN_ADMIN user, you can change the password back to its original value by issuing this command:
hexadecimal: A base-16 numbering system often used to display an exact representation of data in memory. Hexadecimal numbering goes from 1-9, and then from A-F. The hexadecimal A is equivilant to the decimal value 10. After F, the next hexadecimal value is 10, which is equivilant to the decimal value 16. Hexadecimal digits each represent exactly four binary bits, making hexadecimal a convenient alternative to writing long strings of 1s and 0s.

SQL> ALTER USER coin_admin
2  IDENTIFIED BY VALUES 'B1DB09EA3F74189C';
User altered.

Notice that the hexadecimal string that you saved earlier was used to reset the password back to its original state. The keyword VALUES is an undocumented feature that allows you to store an already encrypted password directly.

An Oracle password file is a file within the Oracle administrative or software directory structure on disk used to authenticate Oracle system administrators for tasks such as creating a database or starting up and shutting down the database. The privileges granted through this file are the 1) SYSDBA and 2) SYSOPER privileges. Authenticating any other type of user is done within the database itself. Because the database may be shut down or not mounted, another form of administrator authentication is necessary in these cases. The Oracle command-line utility orapwd creates a password file if one does not exist or is damaged. Because of the extremely high privileges granted via this file, it should be stored in a secure directory location that is not available to anyone except for DBAs and operating system administrators. Once this file is created, the initialization parameter REMOTE_LOGIN_PASSWORDFILE should be set to EXCLUSIVE to allow users other than SYS to use the password file. Also, the password file must be in the $ORACLE_HOME/dbs directory.
TIP: Create at least one user other than SYS or SYSTEM who has DBA privileges for daily administrative tasks. If there is more than one DBA administering a database, each DBA should have their own account with DBA privileges.

Click the Exercise link below to practice changing a user's password.
Changing Users Password - Exercise

[1] database link: In Oracle, a database link is a named object in one database that allows you to access and manipulate data in another database. This other database can be either another Oracle database or a database from a different vendor, as long as Oracle supports it through Heterogeneous Services.