SAND CDBMS supports the use of LDAP (Lightweight Directory Access Protocol) servers for authenticating clients connecting to a SAND database. Instead of creating many users for the SAND database, each with the same access privileges, a smaller number of "roles" can be defined and shared among multiple LDAP-authenticated users, thereby simplifying the creation and management of SAND database users.
LDAP authentication can be used with SAND CDBMS for the following operating systems:
- Windows 2000/XP/2003
In order to use SSL with LDAP, a pair of library files, SSL/TLS and Crypto, must be manually installed on the target system, if they are not already present. These library files can be downloaded via the OpenSSL Project:
Before LDAP user authentication can be used for connecting to a SAND database, the nucleus.ini configuration file must be set up with the proper parameters, and one or more new user authorizations might have to be created for the LDAP users. On the LDAP server side, if the LDAPROLE parameter is set, a "role" must be added to the profile of each user that will have access to the SAND database.
These configuration requirements are described below.
LDAP user authentication is enabled for a SAND database by setting the following parameters in the appropriate DATABASE section of the nucleus.ini file:
- LDAPROLE or LDAPBINDINGS
If any one of these parameters is missing, empty, or invalid, LDAP authentication will be disabled, and standard SAND database login procedures will be used instead.
The following LDAP-related parameters are set in the [DATABASE instance-name] section(s) of the nucleus.ini file:
(Required) The host name or IP address, and optionally the port number, for the LDAP server(s). If the port number is omitted, the default port is assumed (389, or 636 for SSL).
The format of the LDAPHOST string (or "URL") is as follows:
LDAP://<host name or IP>[:<port number>]
If Secure Sockets Layer (SSL) is enabled, specify "LDAPS" instead of "LDAP":
LDAPS://<host name or IP>[:<port number>]
Multiple or redundant LDAP servers can be specified in the LDAPHOST parameter simply by separating the URLs with commas. For example:
The LDAP servers are read from left to right in the LDAPHOST list. If the leftmost server is not available, the next one in the list will be tried, and so on until an available LDAP server is found.
(Required) The format of the base "distinguished name" message string that will be sent to the LDAP server. It has the following form:
[ou=<organizational unit>,]dc=<domain component>[,dc=<domain component>]
The "ou" parameter specifies an organizational unit, such as the country code. The "dc" parameter(s) specify each component of the domain, delimited by commas. For example, "uk.myhost.com" would be represented as "ou=uk,dc=myhost,dc=com".
(Required if LDAPBINDINGS not set) The name of the LDAP attribute containing the SAND database role.
(Required if LDAPROLE not set) The list of LDAP group and SAND database role bindings in the following format:
<LDAP group>:<SAND database role>
Multiple binding pairs must be separated by semicolons, as in this example:
Beyond simple one-to-one bindings, some logical construction is permitted in the string, allowing logical OR/AND group memberships to determine bindings. For instance, membership in both group 1 and group 2 can be defined as a requirement for binding to authorization X. Similarly, membership in either group 1, group 2, or group 3 can be defined as a requirement for binding to authorization Y. The logical OR is represented by a comma ( , ), while the logical AND is represented by a plus sign (+). Logical evaluation is performed left-to-right, although this order can be altered with parentheses, since logical expressions within parentheses are evaluated first.
In addition, a binding can be limited to a particular SAND database by including @database immediately after the binding pair.
There is also a way to specify a default binding if no other binding applies to the LDAP user. This is done by specifying *:user last in the LDAPBINDINGS list (where * represents "all Active Directory groups").
The following table summarizes the special characters permitted in the LDAPBINDINGS string:
Symbol Description ,
Logical OR. The LDAP user can be a member of one or another group in the binding pair. For example, groupA,groupB:user1 specifies that users belonging to either "groupA" or "groupB" will be binded to the SAND authorization "user1".
+ Logical AND. The LDAP user must be a member of both groups in the binding pair. For example, groupA+groupB:user2 specifies that only users belonging to both "groupA" or "groupB" will be binded to the SAND authorization "user2". ( ) Items enclosed by parentheses in the LDAPBINDINGS string will be logically evaluated first. This is useful for removing ambiguity in the order of evaluation. For example, (groupA+groupB),(groupA+groupC):user3 indicates that users belonging to both "groupA" and "groupB", or to both "groupA" and "groupC", will be binded to the SAND authorization "user3". @ Database specifier, used to limit a binding to a particular database. For example, groupD:user4@db1 indicates that the LDAP group "groupD" is binded to the SAND authorization "user4" only for the "db1" database. * A shorthand way to represent"all LDAP groups" in a binding pair (for example, *:user4), allowing a default binding to be assigned if no other binding applies to the user. For example, *:public specifies that all Active Directory groups be binded to the SAND authorization "public". Note that this default binding must be specified last in the LDAPBINDINGS list for it to work properly. Since this binding is always valid for every user, any bindings that come after it in the list will never be applied.
If an LDAP user belongs to more than one group, the first binding in the list that applies to the user (reading from left to right) will be used.
Sets the user authentication mode. There settings are:
The user name and password entered through the client are used to connect to the LDAP server and perform the authorization lookup.
SAND uses a third party "service user" (defined with the LDAPSERVICEUSER and LDAPSERVICEPASSWORD parameters) to perform the authorization lookup on the LDAP server on behalf of the client. When the special constant "_me_" is entered as the user name on the client, the service user will retrieve the client user's system login name and perform the lookup on the LDAP server using that information. In effect, Open Mode is a form of shared authentication.
Note that both LDAP modes can be enabled by including both "LOGIN" and "OPEN" for the LDAPMODE parameter, separated by a comma (for example, "LOGIN,OPEN"). In this case, the user can enter either their user name and password, or the "_me_" constant.
If "OPEN" is specified and "LOGIN" is not, only users that connect with "_me_" will be accepted.
(Required if LDAPMODE=Open) Specifies a third party "service user" that will perform the LDAP authorization lookup on behalf of the client user. Typically, this is a specialized user defined on the LDAP server with limited privileges beyond authorization lookup.
The LDAP server password for the user specified with the LDAPSERVICEUSER parameter.
A comma-separated list of one or more Active Directory domains (for example, "dev,qa,mkt,sales"). The domain associated with the user's system login name is checked against this list. If it is not found, the LDAP authentication will fail.
If this parameter is omitted, user domain verification is not performed.
Specifies the LDAP attribute that stores the user name. This parameter should only be set if the standard CN (Common Name) attribute is not used to store the user name.
Sets the full path, including file name, of the system's LDAP library, in case the default library will not be used. This parameter applies only to operating systems other than Windows and AIX.
Default: libldap.so in the LDLIBRARYPATH location
There are two optional client parameters that can be set in the odbc.ini file or as part of an ODBC connection string:
Specifies the SAND database role that will be used for the connecting user. This parameter is intended mainly for users that have more than one role assigned (that is, are involved in multiple group/role bindings defined by the LDAPBINDINGS parameter). The role must exist in the LDAPBINDINGS string, and the user must be a member of a group assigned to the role, otherwise an error will be returned.
When enabled (PROMPTROLE=1), the User name field of the ODBC connection dialog box captures the SAND database role that will be used for the connecting user. The user's system login name is automatically used for the LDAP authentication. If LDAPMODE=Open, the user's login password is optional. If LDAPMODE=Login, the password is required.
When the PROMPTROLE parameter is disabled (set to 0, which is the default), the ODBC connection dialog box behaves in the standard way.
Note that if both parameters are present, the ODBC connection dialog box will appear with the value specified by the Role parameter in the User name field, which can be accepted or changed.
New User Authorization(s)
Every LDAP user that will have access to the SAND database must be associated with an existing user authorization in the database. Multiple LDAP users can use the same authorization, which allows the authorization to be treated as a class of users with specific privileges on the database (a "role"), rather than a single user. For instance, the DBA can create a user authorization called "Sales", which would have access only to tables in the Sales schema. Each LDAP user that works in sales could be linked to the Sales user authorization, so by logging in to the database with their Active Directory user name and password, they would actually connect to the database as the user Sales.
Because a single user authorization will likely be used by more than one LDAP user, the default schema and privileges to assign the authorization should be carefully considered by the system administrator. The authorization name should be chosen to reflect the role of this category of user. A password for the authorization is not required, as it will not be used during LDAP authentication, but it is good security practice to include one for when the LDAP server is disabled.
Use the SQL CREATE AUTHORIZATION command to create a new user authorization, optionally with password and/or default schema.
Use the GRANT command to give specific database privileges to the user authorization.
LDAP User Role
There are two ways to associate a role (SAND database authorization) with an LDAP user:
- On the LDAP server side, explicitly name the role in the LDAP user's profile or in the profile of an LADP group to which the user belongs.
- In the nucleus.ini file, bind an LDAP group to a selected role, so that all members of the group use that role when connecting to the SAND database.
Note that if both methods are employed, roles specified in LDAP profiles take precedence over group/role bindings.
LDAP Profile Attribute (LDAPROLE)
A role can be named explicitly in an LDAP profile using the attribute specified by the LDAPROLE parameter in the nucleus.ini file. For example, if "LDAPROLE=SANDRole" appears in the nucleus.ini file, an attribute called "SANDRole" must exist in the LDAP profile, and the role specified by this attribute must correspond to an existing authorization with the same name in the SAND database.
The LDAPROLE attribute may appear in LDAP group profiles, in addition to LDAP user profiles. A role that is specified in an LDAP group profile applies to every user that is a member of the group. Since groups can belong to other groups, it is therefore possible for several different roles to be potentially associated with a single LDAP user. In this situation, the role that will actually be associated with the user is the one specified in the user's profile, or the one specified in the profile of the most immediate group.
The order of precedence for determining which LDAP role will be associated with the user is as follows:
- the role in the user's profile
- the role in the profile of the group to which the user belongs
- the role in the profile of the group that contains the group to which the user belongs
For instance, say that user1 is an LDAP user belonging to the NAmericanSales group, which in turn belongs to the Sales group. If all of the user and group profiles specify a role, the user1 profile role will be the one used. If the user1 profile does not specify a role, the role from the NAmericanSales profile will be used. Finally, if neither the user1 and NAmericanSales profiles specify a role, the role from the Sales profile will be used.
If a user belongs to multiple groups, each with a profile that specifies a role, and that user's profile does not specify a role, it is hard to predict which role will actually be used. For example, if LDAP user user2 belongs to the NAmericanSales group, the NAmericanMkt group, and the CustomerCare group, and each of those group profiles specifies a role while the user's profile does not, the role ultimately associated with the user can come from the profile of any one of those three groups, since they are all at the same level of precedence.
Group/Role Binding (LDAPBINDINGS)
A role can be binded to a specific LDAP group, so that all LDAP users belonging to that group will use the role when connecting to the SAND database. The binding is defined through the LDAPBINDINGS parameter in the relevant [DATABASE instance-name] section of the nucleus.ini file. The binding has the following form:
<LDAP group>:<SAND database role>
More than one binding can be specified by separating different bindings with a semicolon:
<LDAP group 1>:<SAND database role 1>;<LDAP group 2>:<SAND database role 2>
The order in which multiple bindings appear can be relevant if a user belongs to more than one group, since only the first binding that applies to the user (reading the bindings from left to right) will be used.
A default binding can be included, using the asterisk character (*) to represent "all LDAP groups". For example, the following binds all LDAP groups to the user authorization "public":
As the default binding, one that will always apply to a given LDAP user, it should always be placed last in the list of bindings. That way, it will only be used if no other binding in the list applies to the LDAP user. It should be noted that any binding placed after the default binding in the LDAPBINDINGS list will never be used.
LDAP users belonging to either "groupA" or "groupB" will be binded to the SAND authorization "auth1". Otherwise, the LDAP user will be binded to the authorization "public" (default binding).
LDAP users belonging to both "groupA" or "groupB" will be binded to the SAND authorization "auth2". Otherwise, the LDAP user will be binded to the authorization "public" (default binding).
LDAP users belonging either to both "groupA" and "groupB", or to both "groupA" and "groupC", will be binded to the SAND authorization "auth3". Otherwise, the LDAP user will be binded to the authorization "public" (default binding).
LDAP users belonging to Active Directory group "groupA" are binded to the SAND authorization "auth5" only for the "db1" database. If an LDAP user does not belong to "groupA" or the active database is not "db1", the next binding is evaluated: If the user belongs to "groupB" and the active database is "db2", the user will log in as "auth6". Otherwise, the LDAP user will be binded to the authorization "public" (default binding).
After LDAP user authentication has been properly configured and enabled, any client attempting to connect to a SAND database enters their LDAP user name and password, or just the user name keyword "_me_" (depending on the mode), which the SAND server passes on to the LDAP server for authentication. Depending on how authentication is configured, once this login information is authenticated, the LDAP server looks up the role associated with the user name from the LDAP user/group profile and returns it to the SAND server, or else the SAND server reads the group role in the appropriate LDAPBINDINGS parameter in the nucleus.ini file. The role is really just a user name previously defined in the SAND database. Ultimately, the SAND server uses this role to connect the user to the database.
Note that the nserv console will display that it is running in LDAP user authentication mode when it is enabled. On the client side, there is no indication that the SAND server is running LDAP authentication, except when certain error conditions occur (see the Error Messages section below).
The sequence of events in LDAP user authentication is summarized as follows (assuming no login errors):
- A user attempts to connect to a SAND database via any compatible client program. The information supplied by the user depends on the mode:
- LOGIN mode (default): The user provides a user name and password that are LDAP login credentials. If the client ODBC parameter PROMPTROLE is enabled (set to 1), the user enters the assigned role in the User name field, rather than a user name (the user's Active Directory login name will be used automatically).
- OPEN mode: The user enters the keyword _me_ as the user name instead of LDAP login credentials, indicating that the user's Active Directory login information will be used for LDAP authentication. If the client ODBC parameter PROMPTROLE is enabled (set to 1), the user enters the assigned role in the User name field, rather than "_me_" (the user's Active Directory login information will be used automatically).
- If the LDAPDOMAIN parameter is set, the user's login domain is verified against the LDAPDOMAIN "white list".
- The SAND server (nserv) sends the user name and password (if applicable) to the LDAP server.
- The LDAP server authenticates the supplied login information, sends the LDAP group(s) to which the user belongs, and requests the attribute name specified by the LDAPROLE parameter from the SAND server.
- If the LDAPROLE parameter is set:
- The SAND server sends the attribute name to the LDAP server.
- The LDAP server retrieves the profile for the user and reads the attribute containing the "role" (a user authentication for the SAND database). If the attribute is not found in the user's profile, or if it is empty, the user's group profile(s) are checked for the role.
- The LDAP server sends the role to the SAND server.
If the LDAPBINDINGS parameter is set and LDAPROLE is not set, the user's LDAP group(s) are checked against the LDAPBINDINGS list:
- If neither of the client ODBC parameters ROLE and PROMPTROLE are set, the role in the first binding that contains a group to which the user belongs is retrieved.
- If the client ODBC ROLE and/or PROMPTROLE parameters are set, the SAND server verifies that a group to which the user belongs is binded to the specified role.
- The SAND server connects the user to the database, logging in with the role (user name). No password is required for this internal login.
If the user name "DBA" is supplied with LDAP authentication enabled, the system will bypass the LDAP server and attempt to log the user in to the SAND database directly. In this way, the DBA can always access the SAND server in the event the LDAP server is unreachable.
Consequently, an LDAP user named "DBA" can never be authenticated through the LDAP server.
ERROR: Failed to establish connection with username <'username'>: Invalid Credentials.
Either the LDAP user name and/or password is invalid, or the LDAPDNFORMAT string is badly formed.
ERROR: Failed to establish connection with username <'username'>: Unwilling To Perform.
The LDAP user's specified role has no corresponding authorization name in the SAND database.
This error can also appear if a user name with no password was received by the LDAP server. LDAP does not allow an empty password.
ERROR: Failed to load LDAP library: '<library>'.
The LDAP library file could not be found, or the file could not be opened. Verify that the LDAP library file exists, and that it is in the path specified by the LDAPPATH parameter.
ERROR: Failed to set connection information.
There was an error while attempting to connect to the LDAP server. Make sure the LDAP host name and port number (LDAPHOST) are correct, and that the LDAP server is actually running.
ERROR: Invalid LDAPMode value: <value>
A value other than LOGIN and OPEN is specified for the LDAPMODE parameter. Note that those two values represent the only LDAP modes available.
ERROR: LDAP library does not contain the necessary functionality: '<library>'.
The LDAP library was loaded, but one or more functions required for the operation of the LDAP module were not found in the library. The specified LDAP library might be outdated or nonstandard, in which case it should be replaced.
ERROR: LDAPServiceUser value is required
The LDAPSERVICEUSER parameter is not set. Since the LDAPMODE parameter is set to OPEN, the LDAPSERVICEUSER parameter must also be defined.
ERROR: Role '<role>' does not exist in database for user '<username>'.
The role specified in the LDAP user's profile does not have a corresponding authorization name in the SAND database.
Note that this error is returned on the client side as "Role does not exist in database".
INFO: Role not found for user <'username'>.
The user was authenticated by LDAP, but the attribute specified by the LDAPROLE parameter was not found in the LDAP user's profile.
Authentication method is not accepted
This message is returned in either of the following situations:
- the LDAPMODE setting does not include OPEN, and "_me_" is specified (the special "_me_" keyword can only be used in OPEN mode)
- the LDAPMODE setting does not include LOGIN, and full Active Directory login credentials are specified (Active Directory user name and password are only supplied in LOGIN mode).
No role assigned to user
The user is not a member of any of the groups in the LDAP bindings list, and no default group/role binding is defined.
Role does not exist
The user specified a role (via the ROLE or PROMPTROLE ODBC parameter) that is not in the LDAP bindings list.
Role is not specified for user
The user specified a role (via the ROLE or PROMPTROLE ODBC parameter) that is in the LDAP bindings list, but is not binded to a group with which the user is associated.
User account is disabled
The specified Active Directory user account is currently deactivated and cannot be used to log in.
User account is expired
The specified Active Directory user account is has expired and cannot be used to log in.
User domain is required
The LDAPDOMAIN parameter has been set, but either the user does not have a domain or the client is running in UNIX, which does not support domains.
User domain was refused
The user's domain was not found in the LDAPDOMAIN list.
User password is expired
The password for the specified Active Directory user account has expired and must be changed before the account can be used to log in.
LDAP user information:
user name: bobsmith
SAND database DBLdap:
In this example, the LDAPHOST, LDAPDNFORMAT, and LDAPROLE parameters are all configured in the nucleus.ini file, so LDAP authentication is enabled for database DBLdap. LDAPBINDINGS is also set, but will not be used to determine the user's role, since LDAPROLE takes precedence.
To connect to the database, the user "bobsmith" enters his user name and password ("ch128m") from a client program. After this login information is received by the SAND server, it is sent to the specified LDAPHOST ("sandldap") on port 2162. The LDAP server authenticates the user, then requests the LDAPROLE value ("SANDRole") from the SAND server. Once the LDAPROLE value is received, the LDAP server looks up the profile for the user and reads the attribute named "SANDRole", which stores the role (user name) that will give the user access to the SAND database. The role ("MARKETING") is sent back to the SAND server, and since there is a corresponding user authorization in the Authorizations system table, the user is successfully connected to the database.
Note that the user authorization MARKETING has an associated password ("sesaMe867"). In this example, the password is ignored, as the user is authenticated by the LDAP server.
Also note that an LDAP library other than the system default is being used in this example. As defined by the LDAPPATH parameter, the LDAP library file is ldaplib.so, which is located in /tmp/lib/.
LDAPBINDINGS Example 1
In an alternative example where the LDAPROLE parameter is not set, the relevant section of the nucleus.ini file looks like this:
The Active Directory user information is as follows:
user name: janedoe
The LDAP user information is as follows:
user name: janedoe
Active Directory groups: SLS, DEV
And the Authorizations table in SAND database DBLdap contains the following information:
In this example, the user ("janedoe") logs in as "_me_", as allowed by the OPEN mode option (LDAPMODE). This means that the user's Active Directory login name will be used to look up the user's profile on the LDAP server, an action that is performed on the user's behalf by the "service user" ("ldapauth"). Since the service user is the entity that actually connects to the LDAP server, only the user's Active Directory login name needs to be provided for the profile lookup. In this kind of system, it is assumed that the Active Directory and LDAP login credentials are correlated, and that logging into the Active Directory domain successfully means that access to the LDAP server should also be assured, hence the trusted authentication scheme.
Alternatively, the user could have supplied a user name and password on the client, instead of "_me_", since the LDAPMODE parameter specifies both OPEN and LOGIN modes in this example.
Once the user is authenticated on the LDAP server, and the SAND server receives the names of the Active Directory group to which the user belongs ("SLS" and "DEV"), the SAND server will analyze the LDAPBINDINGS string from the nucleus.ini file. Reading the LDAPBINDINGS string from left to right, the SAND server will retrieve the role from the first binding pair that contains a group to which the user belongs. In this case, the user belongs to both the SLS and DEV groups, but the binding that applies to the user is "SLS:SALES", since this comes before "DEV:DEVELOPMENT" in the bindings list. The role in the selected binding is "SALES", which corresponds to a user name in the Authorizations table of the database: it will be used to connect the user to the database. The password associated with this role ("Qu3tzalc0atl") is not used when connecting the LDAP user via the role.
Note that the LDAPBINDINGS string contains a default binding ("*:PUBLIC") to be used in case none of the other bindings are applicable to the user. If the user "janedoe" was a member of the group "QA" only, instead of "SLS" and "DEV", or if the current database was not db01, none of the LDAPBINDINGS would apply, except for the default. So in that case, the LDAP user would log in as database user "PUBLIC".
LDAPBINDINGS Example 2
A variation of the first LDAPBINDINGS example adds the following to the nucleus.ini file (the rest of the parameters are the same):
And the client's ODBC connection string now includes the following parameters:
All other information (Active Directory, LDAP, database) is the same as in the previous example.
In this example, the user "janedoe" does not enter "_me_" as the user name in the ODBC connection dialog box. Instead, since the ODBC connection string specifies the ROLE and PROMPTROLE parameters, the dialog box appears with "development" already in the user name field, which (in this context) specifies the role for the user. The user name is not entered on this dialog box; the user's system login name will be retrieved automatically. The LDAP mode is both LOGIN and OPEN, so a password is optional.
The presence of the LDAPDOMAIN parameter means that the user's login domain will be checked against the LDAPDOMAIN "white list" as an extra level of security. Since the user's domain ("CCM") matches one of the domains in the list, the LDAP authentication will proceed. If the domain was not found in the list, the LDAP authentication would be halted with a client-side error ("User domain was refused").
As in the previous example, successful LDAP authentication returns the names of the user's LDAP groups to the SAND server. Unlike the previous example, the SAND server looks in the LDAPBINDINGS string for the role specified by the ODBC Role parameter, rather than searching for the first group to which the user belongs. The specified "development" role is included in the LDAPBINDINGS string (as part of the ""DEV:DEVELOPMENT" binding) , so the server then verifies that the group binded to "development" is one to which the user belongs. Since the user is a member of the "DEV" group, the role is confirmed as valid for the user and will be used to connect the user to the database.