RelationalDBDesign RelationalDBDesign


Creating Users   «Prev 

Forcing 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.

Username and Password:

Oracle uses the username and password to connect to the remote database. The username and password for a database link 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:
  1. The complete database link name as stored in the data dictionary, including the database, domain, and optional connect_descriptor components.
  2. 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.
  1. 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.
  2. 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 name must 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.