2.4.1 Creating a New Profile

Table of Contents Previous Next


2 Database Administration : 2.4 Profile Management : 2.4.1 Creating a New Profile

Use the 수정 PROFILE command to create a new profile. The syntax is:
CREATE PROFILE profile_name
[LIMIT {
parameter value} ... ];
Include the LIMIT clause and one or more space-delimited parameter/value pairs to specify the rules enforced by Advanced Server.
profile_name specifies the name of the profile.
parameter specifies the attribute limited by the profile.
value specifies the parameter limit.
Advanced Server supports the value shown below for each parameter:
FAILED_LOGIN_ATTEMPTS specifies the number of failed login attempts that a user may make before the server locks the user out of their account for the length of time specified by PASSWORD_LOCK_TIME. Supported values are:
An INTEGER value greater than 0.
DEFAULT - the value of FAILED_LOGIN_ATTEMPTS specified in the DEFAULT profile.
UNLIMITED – the connecting user may make an unlimited number of failed login attempts.
PASSWORD_LOCK_TIME specifies the length of time that must pass before the server unlocks an account that has been locked because of FAILED_LOGIN_ATTEMPTS. Supported values are:
A NUMERIC value greater than or equal to 0. To specify a fractional portion of a day, specify a decimal value. For example, use the value 4.5 to specify 4 days, 12 hours.
DEFAULT - the value of PASSWORD_LOCK_TIME specified in the DEFAULT profile.
UNLIMITED – the account is locked until it is manually unlocked by a database superuser.
PASSWORD_LIFE_TIME specifies the number of days that the current password may be used before the user is prompted to provide a new password. Include the PASSWORD_GRACE_TIME clause when using the PASSWORD_LIFE_TIME clause to specify the number of days that will pass after the password expires before connections by the role are rejected. If PASSWORD_GRACE_TIME is not specified, the password will expire on the day specified by the default value of PASSWORD_GRACE_TIME, and the user will not be allowed to execute any command until a new password is provided. Supported values are:
A NUMERIC value greater than or equal to 0. To specify a fractional portion of a day, specify a decimal value. For example, use the value 4.5 to specify 4 days, 12 hours.
DEFAULT - the value of PASSWORD_LIFE_TIME specified in the DEFAULT profile.
UNLIMITED – The password does not have an expiration date.
PASSWORD_GRACE_TIME specifies the length of the grace period after a password expires until the user is forced to change their password. When the grace period expires, a user will be allowed to connect, but will not be allowed to execute any command until they update their expired password. Supported values are:
A NUMERIC value greater than or equal to 0. To specify a fractional portion of a day, specify a decimal value. For example, use the value 4.5 to specify 4 days, 12 hours.
DEFAULT - the value of PASSWORD_GRACE_TIME specified in the DEFAULT profile.
UNLIMITED – The grace period is infinite.
PASSWORD_REUSE_TIME specifies the number of days a user must wait before re-using a password. The PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX parameters are intended to be used together. If you specify a finite value for one of these parameters while the other is UNLIMITED, old passwords can never be reused. If both parameters are set to UNLIMITED there are no restrictions on password reuse. Supported values are:
A NUMERIC value greater than or equal to 0. To specify a fractional portion of a day, specify a decimal value. For example, use the value 4.5 to specify 4 days, 12 hours.
DEFAULT - the value of PASSWORD_REUSE_TIME specified in the DEFAULT profile.
UNLIMITED – The password can be re-used without restrictions.
PASSWORD_REUSE_MAX specifies the number of password changes that must occur before a password can be reused. The PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX parameters are intended to be used together. If you specify a finite value for one of these parameters while the other is UNLIMITED, old passwords can never be reused. If both parameters are set to UNLIMITED there are no restrictions on password reuse. Supported values are:
An INTEGER value greater than or equal to 0.
DEFAULT - the value of PASSWORD_REUSE_MAX specified in the DEFAULT profile.
UNLIMITED – The password can be re-used without restrictions.
PASSWORD_VERIFY_FUNCTION specifies password complexity. Supported values are:
DEFAULT - the value of PASSWORD_VERIFY_FUNCTION specified in the DEFAULT profile.
Use DROP PROFILE command to remove the profile.
The following command creates a profile named acctg. The profile specifies that if a user has not authenticated with the correct password in five attempts, the account will be locked for one day:
The following command creates a profile named sales. The profile specifies that a user must change their password every 90 days:
The following command creates a profile named accts. The profile specifies that a user cannot re-use a password within 180 days of the last use of the password, and must change their password at least 5 times before re-using the password:
The following command creates a profile named resources; the profile calls a user-defined function named password_rules that will verify that the password provided meets their standards for complexity:
When specifying PASSWORD_VERIFY_FUNCTION, you can provide a customized function that specifies the security rules that will be applied when your users change their password. For example, you can specify rules that stipulate that the new password must be at least n characters long, and may not contain a specific value.
function_name (user_name VARCHAR2,
new_password VARCHAR2,
old_password VARCHAR2) RETURN boolean
user_name is the name of the user.
new_password is the new password.
old_password is the user's previous password. If you reference this parameter within your function:
When a user with the CREATEROLE attribute changes their password, the parameter will pass the previous password if the statement includes the REPLACE clause. Note that the REPLACE clause is optional syntax for a user with the CREATEROLE privilege.
When a user that is not a database superuser and does not have the CREATEROLE attribute changes their password, the third parameter will contain the previous password for the role.
The following statement sets the ownership of the verify_password function to the enterprisedb database superuser:
Then, the verify_password function is associated with the profile:
The following statements confirm that the function is working by first creating a test user (alice), and then attempting to associate invalid and valid passwords with her role:
Then, when alice connects to the database and attempts to change her password, she must adhere to the rules established by the profile function. A non-superuser without CREATEROLE must include the REPLACE clause when changing a password:
If alice decides to change her password, the new password must not contain the old password:
To remove the verify function, set password_verify_function to NULL:

2 Database Administration : 2.4 Profile Management : 2.4.1 Creating a New Profile

Table of Contents Previous Next