sp_modifylogin

Description

Modifies the default database, default language, default role activation, login script, full name, the password expiration interval, the minimum password length, and the maximum number of failed logins allowed for a specified Adaptive Server login account.

Syntax

sp_modifylogin {loginame | "all overrides"}, option, value

Parameters

loginame

is the login account to be modified.

"all overrides"

removes the system overrides that were set using the "passwd expiration", "min passwd length", or "max failed_logins" parameters. To remove all the login-specific values, specify:

sp_modifylogin "all overrides", "option", "-1"
option

specifies the name of the option to be changed. Table 1-16 lists valid options and their descriptions.

Table 1-16: sp_modifylogin option variable and definitions

Option

Definition

authenticate with

Authenticates the login. Specify these values:

  • ASE – use Adaptive Server internal authentication using syslogin passwords.

  • LDAP – use external authentication with an LDAP Server.

  • PAM – use external authentication with PAM.

  • KERBEROS – use external authentication with KERBEROS.

  • ANY – by default, users are authenticated using this authentication method. A user with ANY authentication means that Adaptive Server checks if there is any external authentication mechanism defined, and if there is, it is used. Otherwise, it uses ASE authentication.

defdb

The “home” database to which the user is connected when he or she logs in.

deflanguage

The official name of the user’s default language.

fullname

The user’s full name.

"add default role"

The role or roles to be activated by default at login.

"drop default role"

The role or roles to be dropped from the list of roles activated by default at login. This option affects only user-defined roles, not system roles.

"passwd expiration"

specifies the password expiration interval in days. It can be any value between 0 and 32767, inclusive. The password expires when the number of specified days passes. For example, if you create a new login on August 1, 2007 at 10:30 AM, with a password expiration interval of 30 days, the password expires on August 31, 2007 at 10:30 AM.

"min passwd length"

The minimum password length required for the specified login. It can be any value between 0 and 30, inclusive. 0 specifies that no password is required. The default is 6.

"max failed_logins"

The number of failed login attempts that causes the specified login to be locked. When you set “max failed_login” to:

  • 1 – login gets locked on one login failure.

  • 0 – logins never get locked regardless of the number of failed login attempts.

  • -1 – syslogins.logincount gets incremented on every failed login attempt, but login is not locked.

login script

Name of the stored prodecure to run automatically when user logs in.

value

is the value of the option you specified for the option parameter. The value parameter is a character datatype; therefore, quotes are required for positive and negative numeric values.

Examples

Example 1

Changes the default database for “sarah” to pubs2:

sp_modifylogin sarah, defdb, "pubs2"

Example 2

Sets the default language for “claire” to French:

sp_modifylogin claire, deflanguage, "french"

Example 3

Changes the full name of user “clemens” to “Samuel Clemens”:

sp_modifylogin clemens, fullname, "Samuel Clemens"

Example 4

Adds the specialist role to the list of roles activated by default when user csmith logs in:

sp_modifylogin csmith, "add default role", specialist_role

Example 5

Drops the intern role from the list of roles activated by default when user “hpillai” logs in:

sp_modifylogin hpillai, "drop default role", intern_role

Example 6

Changes the maximum number of failed login attempts for the login “joe” to 40:

sp_modifylogin "joe", "max failed_logins", "40"

Example 7

Changes the overrides for maximum failed login attempts of all logins with overrides to 3:

sp_modifylogin "all overrides", "max failed_logins", "3"

Example 8

Removes the overrides for maximum failed logins option for all logins with overrides:

sp_modifylogin "all overrides", "max failed_logins", "-1"

Example 9

The login account named “nightlyjob” can only authenticate using local ASE authentication, and not an external mechanism such as LDAP or PAM:

sp_modifylogin "nightlyjob", "authenticate with", "ASE" 

Example 10

Runs the proc_p1 script when Bob1 logs in to Adaptive Server:

sp_modifylogin Bob1, 'login script', proc_p1

Usage


Using the "clear" value with sp_modifylogin

Passing in the value of "clear" for the options "passwd expiration", "min passwd length" and "max failed_logins", with a specific loginame, causes a previous setting of that option for the specific user to be cleared.For example, setting the "passwd expiration" value specific to user "John" causes John’s password to expire in 30 days, even though the system default for passwod expiration is 90 days:

1> sp_modifylogin 'John', 'passwd expiration', 30
2> go

Use the following to clear the "passwd expiration" value specific to user "John":

1> sp_modifylogin 'John', 'passwd expiration', 'clear'
2> go

If you succeed, John’s password expiration becomes the same as the system default from now on, and you see message 19813, which says:

"The login-specific 'passwd expiration' attribute has
been removed."

If you use 'clear' to clear a setting for a user that did not have a previous value set, you see message 19812, and the user continues to use the system default for that attribute as before:

"There is no login-specific 'passwd expiration'
attribute set for this user."

Changing a user’s default database


Changing a user’s role activation


sp_modifylogin account

Adaptive Server version 15.0.2 includes the following option for sp_modifylogin account:

sp_modifylogin account,  "max failed_logins", -1

This new value for the 'max failed_logins' option indicates that the failed login count in the syslogins column login count, is updated whenever an authentication failure occurs, but that the account is not locked. Compare with value 0, which avoids incrementing the column for every failed authentication and avoids locking the account due to authentication failures.

For more information on failed logins see sp_configure for changes to maximum failed logins and sp_passwordpolicy for changes to maximum failed login configuration options.


sp_modifylogin and sp_addlogin authenticate with option

The authenticate with option to sp_modifylogin or sp_addlogin requires that the login use only a specified authentication mechanism. The supported authentication mechanisms are:

Using authenticate with with these supported authentication mechanisms allows you to override the server-wide configuration parameters unified login required, enable ldap user auth, and enable pam user auth.

If more than one external authentication mechanism is configured and a login-specific authenticate with option has not been set, the external authentication mechanism is based on the following order:

  1. Kerberos

  2. LDAP

  3. PAM

For example, if both PAM and LDAP are configured, LDAP is chosen for external authentication, not PAM.

At the time of authentication, exactly one external authentication mechanism is attempted. Depending on the value of configuration parameters, you can try the “ASE” authentication mechanism when the external authentication fails.When none of the external authentication mechanisms are configured, Adaptive Server uses ASE authentication.


Using authenticate with to override server-wide authentication options

NoteYou must have sso_role permissions to modify the authenticate with option for a login.

To configure external authentication mechanisms such as Kerberos, LDAP, or PAM, Sybase recommends that you determine the server-wide authentication setting that matches your company’s security policy. This server-wide setting is appropriate for most client connections. Then, you can set individual logins to another authentication mechanism using the authenticate with option.

You can use authenticate with to specify an the external authentication mechanisms Kerberos, LDAP, and PAM. You can also issue sp_modifylogin or sp_addlogin authenticate with to set the authentication mechanism to “ASE” to use only the Adaptive Server internal authentication mechanism. To allow any authentication mechanism, use authenticate with ANY.

NoteWhen authentication mechanism “ANY” (the default) is set for a login, the login uses the server-wide configuration settings to control authentication.

sp_modifylogin also checks for any conflicts with any login mapping specified by a previous sp_maplogin.

Examples

Example 11

Example 1 Creates a local account to run a batch application. Consider an environment that uses Kerberos for a centralized user account repository and requires its general population of users to authenticate using Kerberos, Configure Kerberos by setting these parameters:

sp_configure  "use security services", 1
go

sp_configure "unified login required", 1
go

These configuration parameters now require all user logins, other than “sa,” to authenticate using Kerberos to gain access to the Adaptive Server.

Now consider a nightly batch operation run by the Adaptive Server database administrator or operator, which may authenticate locally without requiring the account to exist in the Kerberos repository. Use authenticate with option to sp_modifylogin or sp_addlogin:

sp_addlogin nightlybatch, localpassword, ... 
go
sp_modifylogin nightlybatch, 'authenticate with', 'ASE'
go

Example 12

Example 2 This example migrates users from Adaptive Server authentication to LDAP user authenication, using a phased approach for moving clients from local Adaptive Server authentication to LDAP user authentication.The LDAP directory server has been set up but has not yet been populated with all user accounts. A small population of users has agreed to a pilot program to test out LDAP for external authentication to Adaptive Server. To allow failover to ASE authentication when authentication with the LDAP directory server fails or the LDAP server is unavailable, enter:

sp_configure 'enable ldap user auth', 1
go

Users without LDAP accounts fail over to Adaptive Server authentication. The users in the pilot program are automatically added to the LDAP directory server and may begin authenticating using the LDAP directory server.

A user can use the @@authmech global variable to determine which authentication mechanism was used to authenticate with with:

select @@authmech

You can require users in the pilot program to use only LDAP authentication by entering:

sp_maplogin loginame, 'authenticate with', 'ldap'
go

If you decide to use LDAP user authentication for all users, change the configuration parameter to level 2. Any logins set to authenticate with LDAP during the pilot program can be reset to the default value “ANY”. Because the configuration parameter is set to 2. “ANY” logins are still required to use LDAP:

sp_configure 'enable ldap user auth', 2
go
sp_maplogin loginame, 'authenticate with', 'any'
go

NoteIf a login has authenticate with set to a specific authentication mechanism such as LDAP, Kerberos, PAM, or ASE, that login can use only that mechanism for authentication. Sybase suggests that you use authentication with to set exceptions to the server-wide settings and to force a particular authentication mechanism to be used.

Permissions

A user can use sp_modifylogin to change their default database, default language, and full name. Only a System Administrator or System Security Officer can execute sp_modifylogin to change the default database, default language, or full name of another user. Only a System Security Officer can execute sp_modifylogin to activate another user’s roles by default at login, change the password expiration interval, the minimum password length, the maximum number of failed logins allowed, and the login script associated with a specified login.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

System procedures sp_activeroles, sp_addalias, sp_addlogin, sp_adduser, sp_displaylogin, sp_displayroles, sp_helprotect,

Commands alter role, create procedure, create role, drop role, grant, revoke, set role