CREATE LOGIN POLICY statement

Use this statement to create a login policy.

Syntax
CREATE LOGIN POLICY policy-name policy-options
policy options :
policy-option [ policy-option ... ]
policy-option :
policy-option-name = policy-option-value
policy-option-value : 
{ UNLIMITED | legal-option-value }
Parameters
  • policy_name   The name of the login policy.

  • policy_option_name   The name of the login policy option. If you do not specify an option, the value from the root login policy is applied.

  • policy_option_value   The value assigned to the login policy option. If you specify UNLIMITED, no limits are imposed. To view a list of default login policy values, see Remarks.

Remarks

If you do not specify a policy option, values for the login policy are taken from the root login policy.

All new databases include a root login policy. You can modify the root login policy values, but you cannot delete the policy. An overview of the default options for the root login policy is provided in the table below.

Policy_option_name Description Default value Applies to
password_life_time The maximum number of days before a password must be changed. Unlimited All users including those with DBA authority
password_grace_time The number of days before the password expires during which login is allowed, but the default post_login procedure issues warnings. 0 All users including those with DBA authority
password_expiry_on_next_login If the value for this option is ON, the user's password will expire after the next login. OFF All users including those with DBA authority
locked If the value for this option is ON, users are prohibited from establishing new connections. OFF Only to users without DBA authority
max_connections The maximum number of concurrent connections allowed for a user. Unlimited Only to users without DBA authority
max_failed_login_attempts The maximum number of failed attempts, since the last successful attempt, to login to the user account before the account is locked. Unlimited Only to users without DBA authority
max_days_since_login The maximum number of days that can elapse between two successive logins by the same user. Unlimited Only to users without DBA authority
max_non_dba_connections The maximum number of concurrent connections that a user without DBA authority can make. This option is only supported in the DEFAULT login policy. Unlimited Only to users without DBA authority. Only to the default login policy.
Permissions

Must have DBA authority.

Side effects

None.

See also
Standards and compatibility
  • SQL/2003   Vendor extension.

Examples

The following example creates the Test1 login policy. This example has an unlimited password life and allows the user a maximum of 5 attempts to enter a correct password before the account is locked.

CREATE LOGIN POLICY Test1 
password_life_time=UNLIMITED
max_failed_login_attempts=5;