Adds a new user account to Adaptive Server; specifies the password expiration interval, the minimum password length, and the maximum number of failed logins allowed for a specified login at creation.
sp_addlogin loginame, passwd [, defdb] [, deflanguage] [, fullname] [, passwdexp] [, minpwdlen] [, maxfailedlogins] [, auth_mech]
is the user’s login name. Login names must conform to the rules for identifiers.
is the user’s password. Passwords must be at least 6 characters long. If you specify a shorter password, sp_addlogin returns an error message and exits. Enclose passwords that include characters besides A – Z, a – z, or 0 – 9 in quotation marks. Also enclose passwords that begin with 0-9 in quotation marks.
sp_addlogin invokes the password complexity checks introduced in Adaptive Server 15.0.2.
is the name of the default database assigned when a user logs into Adaptive Server. If you do not specify defdb, the default, master, is used.
is the official name of the default language assigned when a user logs into Adaptive Server. The Adaptive Server default language, defined by the default language id configuration parameter, is used if you do not specify deflanguage.
is the full name of the user who owns the login account. This can be used for documentation and identification purposes.
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.
specifies the minimum password length required for that login. The values range between 0 and 30 characters.
is the number of allowable failed login attempts. It can be any whole number between 0 and 32767.
defines the authentication mechanism.
Creates an Adaptive Server login for “albert” with the password “longer1” and the default database corporate:
sp_addlogin albert, longer1, corporate
Creates an Adaptive Server login for “claire.” Her password is “bleurouge,” her default database is public_db, and her default language is French:
sp_addlogin claire, bleurouge, public_db, french
Creates an Adaptive Server login for “robertw.” His password is “terrible2.” his default database is public_db, and his full name is “Robert Willis.” Do not enclose null in quotes:
sp_addlogin robertw, terrible2, public_db, null, "Robert Willis"
Creates a login for “susan” with a password of “wonderful,” a full name of “Susan B. Anthony,” and the server’s default database and language. Do not enclose null in quotes:
sp_addlogin susan, wonderful, null, null, "Susan B. Anthony"
Alternately, you can also use the following:
sp_addlogin susan, wonderful, @fullname="Susan B. Anthony"
Configures the login “mylogin” to override global authentication mechanisms:
sp_addlogin mylogin, mypassword, @auth_mech = ASE
For ease of management, Sybase strongly recommends that all users’ Adaptive Server login names be the same as their operating system login names. This makes it easier to correlate audit data between the operating system and Adaptive Server. Otherwise, keep a record of the correspondence between operating system and server login names.
After assigning a default database to a user with sp_addlogin, the Database Owner or System Administrator must provide access to the database by executing sp_adduser or sp_addalias.
auth_mech can take the same values as sp_modify login "authenticate with" option.
At login creation, the crdate column in syslogins is set to the current time.
Although a user can use sp_modifylogin to change his or her own default database at any time, a database cannot be used without permission from the Database Owner.
Users can use sp_password at any time to change their own password. A System Security Officer can use sp_password to change any user’s password.
Users can use sp_modifylogin to change their own default language. A System Administrator can use sp_modifylogin to change any user’s default language.
Users can use sp_modifylogin to change their own fullname. A System Administrator can use sp_modifylogin to change any user’s fullname.
In cluster environments – sp_addlogin fails if you attempt to include a local temporary database as a default database for any login.
At login creation, the crdate column in syslogins is set to the current time.
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:
ASE
LDAP
PAM
Kerberos
ANY
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:
Kerberos
LDAP
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.
You 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.
When 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.
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 2 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
If 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.
Only a System Security Officer can execute sp_addlogin.
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 |
|
System procedures sp_addalias, sp_adduser, sp_droplogin, sp_locklogin, sp_modifylogin, sp_password, sp_role
