alter login

Description

Changes the attributes of a login account.

Syntax

alter login login_name
	{ [modify attribute_value_pair_list ]
	| [add auto activated roles role_name [, role_name_list ]]
	| [drop auto activated roles { ALL | role_name [, role_name_list ]}]
	| [drop attribute_name_list ]
	| [ with password caller_password 
	modify password [immediately] new_loginName_password ] }

Parameters

login_name

specifies the name of the login account to be changed.

modify

changes attribute values to the new values specified if the attributes exist. If the attributes do not exist, the specified list of attributes and corresponding values are added to the login account. The attribute_value_ pair_list is an attribute name and a specified value. Specify one or more from the following:

Parameter

Parameter Value

Description

login profile

Valid values:

  • login_profile_name

  • ignore

  • default

  • login_profile_name binds the specified login profile to the specified login account. If a login profile binding already exist, it will be replaced with the specified login profile.

  • ignore eliminates any login profile binding. If a login profile binding exist, it will be removed. A default login profile will not be applicable and attributes will be applied as they were prior to release 15.7.

  • default default removes an existing login profile binding and associates the default login profile with the login account.

    If login profile login_profile_name is not specified and a default login profile exists, then the default login profile will be associated with the login account.

fullname

name_value

Full name of user who owns the login account. Adds a full name or modifies an existing name.

Default is NULL.

password expiration

Valid range: 0 to 32767 days

Password expiration interval.

Default is 0, meaning the password never expires.

min password length

Valid range: 0 to 30.

Minimum password length required.

Default is 6.

max failed attempts

Valid range: -1 to 32767.

Number of login attempts allowed, after which the login account is locked.

-1 indicates the failed count is tracked but not locked.

Default is 0, meaning the failed count is not tracked and the account is not locked due to failed login attempts.

authenticate with

Valid values: ASE, LDAP, PAM, KERBEROS, ANY

Specifies the mechanism used for authenticating the login account.

When ANY is used, Adaptive Server checks for a defined external authentication mechanism. If one is defined, Adaptive Server uses the defined mechanism., otherwise the ASE mechanism is used.

If authenticate with authentication mechanism is not specified, ANY will be used for the login account.

default database

default_database_name

Specifies a database to be the default.

Default is Master.

default language

default_language

Specifies a language to be the default.

Default is us_english

login script

login_script_name

Specifies a valid stored procedure. Limited to 120 characters for a login script.

exempt inactive lock

Valid values: TRUE or FALSE.

Specifies whether or not to exempt login accounts from being locked due to inactivity.

Default is FALSE which indicates account are not exempt.

add auto activated roles

specifies the previously granted, non-password protected user defined roles that must be automatically activated on login.

drop auto activated roles

specifies the previously granted user defined roles must not be automatically activated on login. ALL specifies all granted user defined roles.

drop

drops specified attributes from the login account. Specify one or more of the following attributes to be dropped:

Parameter

login profile

Removes the login profile binding from the specified login account. If the login profile ignore parameter has been specified, the parameter is removed and existing default login profile is no longer ignored.

fullname

Removes the name associated with the login account.

password expiration

Removes any password expiration values.

min password length

Removes any restrictions for a minimum password length.

max failed attempts

Removes restrictions for the number of failed attempts allowed.

authenticate with

Removes specifications for authentication mechanisms.

default database

Removes specifications for a default database.

default language

Removes specifications for a default languages.

login script

Removes specifications to apply a login script.

exempt inactive lock

Removes specifications indicating whether or not to lock login accounts due to inactivity. Sets the default value of FALSE where login accounts are not exempt.

with password caller_password modify new_loginName_password

changes the login password to the new specified password.

immediately

specifies whether a password immediately takes effect on users who are logged in. If you:

  • Specify immediately – the password changes immediately in the syslogins table, and users who are logged in get their passwords updated while they are still logged in.

  • Do not specify immediately – all users—with an exception to the caller—who are logged, in keep their old passwords until they reconnect.

Examples

Example 1

Binds the login profile emp_lp to the login account ravi.

alter login ravi modify login profile emp_lp

Example 2

When ignore is specified, all login profiles are ignored, whether it is a login profile that has been bound to the users_1 login account or a defined default login profile.

alter login users_1 modify login profile ignore

Example 3

Creates two login profiles; the first is general_lp which is a default login profile and the second is a login profile name emp_lp which is defined for a specific group of employees. After the login profiles are created, attributes from both login profiles are applied to a login account. See “Applying login profile and password policy attributes” in the Security Administration Guide for information about the order in which attributes are applied.

create login profile general_lp as default with default database master default language us_english
track lastlogin true authenticate with ASE

create login profile emp_lp with default database empdb autheticate with
LDAP

The following binds the login profile emp_lp to the login account users_2. The default language and track lastlogin are not defined in login profile emp_lp but are defined in the default login profile. Therefore, the default language and track lastlogin values are applied from general_lp.

alter login users_22 modify login profile emp_lp

Example 4

Creates two login profiles; the first is newEmployee_lp for new employees and the second is default_lp for existing employees.

create login profile newEmployee_lp with login script "newEmp_script"

create login profile default_lp as default with login script "def_script"

The following applies the login script newEmp_script to employee_new upon login.

create login employee_new with password myPasswd33 login profile
"newEmployee_lp"

The login profile default_lp is applied upon login to existing accounts that do not have a login script specified through a login profile.

Example 5

Shows how to enforce different roles that are granted and automatically activated for contract employees and full time employees:

create login profile contractEmp_lp
grant role access_role to contractEmp_lp
alter login profile contractEmp_lp add auto activated roles access_role
create login contractEmp_emp1 with password c_Emp43 login profile 
"contract_lp"
create login contractEmp_emp2 with password c_Emp44 login profile
"contract_lp"
create login contractEmp_emp3 with password c_Emp44 login profile
"contract_lp"

Usage

Precedence rules determine how login account attributes will be applied when attributes are taken from different login profiles or when values have been specified using sp_passwordpolicy.

For precedence rules, see “Applying login profile and password policy attributes” in the Security Administration Guide.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

You must have sso_role privileges to alter login accounts. The exception to sso_role is only when you change your own password and full name.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

138

login_admin

alter login

Keywords contain:

  • MODIFY attribute_value_pair_list

  • DROP attribute_name_list

  • MODIFY PASSWORD

  • ADD AUTO ACTIVATED ROLES role1 [role2][ ... [ roleN]... ]]

  • DROP AUTO ACTIVATED ROLES {ALL | role1 [, role2 [... [, roleN] ... ] ] }

See also

Commands create login, create login profile, alter login profile, drop login, drop login profile

Documents For information about altering login accounts, see the Security Administration Guide.

Functions lprofile_id, lprofile_name

System procedures sp_passwordpolicy, sp_displaylogin, sp_displayroles, sp_locklogin