Adaptive Server Enterprise users who invoke CIS, knowingly or unknowingly, require login names/passwords to remote servers. By default, the username/password pair used by CIS to connect to a remote server is the same username/password used by the client to connect to Adaptive Server Enterprise.
This default mapping is frequently insufficient, and since its first release CIS has supported a one-to-one mapping of Adaptive Server Enterprise login names and passwords to remote server login names and passwords. For example, using the stored procedure sp_addexternlogin, it is possible to map Adaptive Server Enterprise user steve, password sybase to DB2 login name login1, password password1:
sp_addexternlogin DB2, steve, login1, password1
In version 12.5, it is possible to provide a many-to-one mapping so that all Adaptive Server Enterprise users who need a connection to DB2 can be assigned the same name and password:
sp_addexternlogin DB2, NULL, login2, password2
One-to-one mapping has precedence, so that if user steve has an external login for DB2, that would be used rather than the many-to-one mapping.
In addition to this, it is possible to assign external logins to Adaptive Server Enterprise roles. With this capability, anyone with a particular role can be assigned a corresponding login name/password for any given remote server:
sp_addexternlogin DB2, null, login3, password3, rolename
The use of the fifth argument to this procedure, containing the role name, identifies the name of a role, rather than the name of a user. Whenever a user with this role active requires a connection to DB2, the appropriate login name/password for the role is used to establish the connection. When establishing a connection to a remote server for a user that has more than one role active, each role is searched for an external login mapping, and the first mapping found is used to establish the login. This is the same order as displayed by the stored procedure sp_activeroles.
The general syntax for sp_addexternlogin is:
sp_addexternlogin <servername>, <loginname>, <external_loginname>, <external_password> [, <rolename>]
<rolename> is optional; if specified then the loginname parameter is ignored.
Precedence for these capabilities are as follows:
If one-to-one mapping is defined, it is used - this has the highest precedence.
If no one-to-one mapping is defined, then if a role is active and a mapping for it can be found, the role mapping is used to establish a remote connection;
If neither of the above are true, then many-to-one mapping is used if defined.
If none of the above is true, then the Adaptive Server Enterprise login name and password are used to make the connection.
If role mapping is done, and a user’s role is changed (via set role), then any connections made to remote servers that used role mapping is disconnected.
The stored procedure sp_helpexternlogin has been updated to allow viewing the various types of extern logins that have been added using sp_addexternlogin. The syntax for sp_helpexternlogin is:
sp_helpexternlogin [<servername> [,<loginname> [,<rolename>]]]
All three parameters are optional, and any of the parameters can be NULL.
The stored procedure sp_dropexternlogin has also been modified to accept a third argument, <rolename>. If <role name> is specified then the second argument, <login name>, is ignored.
