Modifies a configuration file.

Syntax

ALTER PROFILE profile_name RENAME TO new_name;

ALTER PROFILE profile_name
      LIMIT {parameter value}[...] ;

Description

You can use the ALTER PROFILE command to modify a user-specified configuration file. PolarDB for PostgreSQL(Compatible with Oracle) support the following two types of syntax:

  • ALTER PROFILE…RENAME TO: changes the name of a configuration file.
  • ALTER PROFILE…LIMIT: modifies the limits that are associated with a configuration file.

You can include the LIMIT clause and one or more space-delimited parameter/value pairs to specify the rules that are enforced by PolarDB for PostgreSQL(Compatible with Oracle). You can also use the ALTER PROFILE…RENAME TO command to change the name of a configuration file.

Parameters

ParameterDescription
profile_nameThe name of the configuration file.
new_nameThe new name of the configuration file.
parameterThe parameters in the configuration file.
valueThe values of the parameters.

PolarDB for PostgreSQL(Compatible with Oracle) support the following parameter values:

  • FAILED_LOGIN_ATTEMPTS specifies the maximum number of failed logon attempts before the server locks the account for the period that is specified by the PASSWORD_LOCK_TIME parameter. Valid values:
    • An INTEGER value greater than 0.
    • DEFAULT: the value of the FAILED_LOGIN_ATTEMPTS parameter that is specified in the DEFAULT configuration file.
    • UNLIMITED: The number of failed logon attempts is unlimited.
  • PASSWORD_LOCK_TIME: specifies the required period before the server unlocks an account that has been locked due to excessive logon attempts. Valid values:
    • A NUMERIC value greater than or equal to 0. To specify a fractional portion of a day, you must specify a decimal value. For example, you can use the value 4.5 to specify 4 days and 12 hours.
    • DEFAULT: the value of the PASSWORD_LOCK_TIME parameter that is specified in the DEFAULT configuration file.
    • UNLIMITED: The account is locked until it is unlocked by a database superuser.
  • PASSWORD_LIFE_TIME: specifies the number of days that the current password can be used before the user is prompted to provide a new password. When using the PASSWORD_LIFE_TIME clause, you can include the PASSWORD_GRACE_TIME clause to specify the number of days after the password expires until connections from the role are rejected. If you do not specify the PASSWORD_GRACE_TIME parameter, the password expires on the day that is specified by the default value of the PASSWORD_GRACE_TIME parameter. The user is not allowed to run any command until a new password is provided. Valid values:
    • A NUMERIC value greater than or equal to 0. To specify a fractional portion of a day, you must specify a decimal value. For example, you can use the value 4.5 to specify 4 days and 12 hours.
    • DEFAULT: the value of the PASSWORD_LIFE_TIME parameter that is specified in the DEFAULT configuration file.
    • UNLIMITED: The password never expires.
  • PASSWORD_GRACE_TIME: specifies the grace period after the password expires until the user is required to change the password. After a specified period ends, the user is allowed to connect the server and cannot run any command until the expired password is updated. Valid values:
    • A NUMERIC value greater than or equal to 0. To specify a fractional portion of a day, you must specify a decimal value. For example, you can use the value 4.5 to specify 4 days and 12 hours.
    • DEFAULT: the value of the PASSWORD_GRACE_TIME parameter that is specified in the DEFAULT configuration file.
    • UNLIMITED: The grace period is unlimited.
  • PASSWORD_REUSE_TIME: specifies the number of days a user must wait before reusing a password. You must use the PASSWORD_REUSE_TIME parameter with the PASSWORD_REUSE_MAX parameter. If you specify a finite value for one parameter and specify UNLIMITED for the other parameter, previous passwords cannot be reused. If you specify UNLIMITED for both parameters, no limit is imposed on password reuse. Valid values:
    • A NUMERIC value greater than or equal to 0. To specify a fractional portion of a day, you must specify a decimal value. For example, you can use the value 4.5 to specify 4 days and 12 hours.
    • DEFAULT: the value of the PASSWORD_REUSE_TIME parameter that is specified in the DEFAULT configuration file.
    • UNLIMITED: No limit is imposed on password reuse.
  • PASSWORD_REUSE_MAX: specifies the number of password changes that must occur before a password can be reused. You need to use the PASSWORD_REUSE_TIME parameter with the PASSWORD_REUSE_MAX parameter. If you specify a finite value for one parameter and specify UNLIMITED for the other parameter, previous passwords cannot be reused. If you specify UNLIMITED for both parameters, no limit is imposed on password reuse. Valid values:
    • An INTEGER value greater than 0.
    • DEFAULT: the value of the PASSWORD_REUSE_MAX parameter that is specified in the DEFAULT configuration file.
    • UNLIMITED: No limit is imposed on password reuse.
  • PASSWORD_VERIFY_FUNCTION: specifies password complexity. Valid values:
    • The name of a PL/SQL function.
    • DEFAULT: the value of the PASSWORD_VERIFY_FUNCTION parameter that is specified in the DEFAULT configuration file.
    • NULL
  • PASSWORD_ALLOW_HASHED: specifies whether to allow using an encrypted password. If you set the value to TRUE, the system allows you to change the password by specifying the hash-calculated encrypted password on the client. However, if you set the value to FALSE, you must specify a password in plain-text for verification. Otherwise, an error occurs when the server receives the encrypted password. Valid values:
    • A BOOLEAN value TRUE/ON/YES/1 or FALSE/OFF/NO/0.
    • DEFAULT: the value of the PASSWORD_ALLOW_HASHED parameter that is specified in the DEFAULT configuration file.
Note The PASSWORD_ALLOW_HASHED parameter is not supported by Oracle.

Examples

The following example shows how to modify a configuration file named acctg_profile:

ALTER PROFILE acctg_profile
       LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1;

acctg_profile calculates the number of failed connection attempts when a logon role attempts to connect to the server. The configuration file specifies that if a user does not use the correct password for verification in three attempts, the account is locked for one day.

In the following example, the name of the configuration file is changed from acctg_profile to payables_profile:

ALTER PROFILE acctg_profile RENAME TO payables_profile;