SQLAuthenticate {on | off}
or
SQLAuthenticate [ users [*] ] [ group [*] ] [ userset [fast] ] [ groupset [fast] ]
Default
SQLAuthenticate on
Context
server config, <Global>, <VirtualHost>, <Anonymous>, <Limit>, .ftpaccess
Module
mod_sql
Compatibility
1.2.5rc1 and later
The SQLAuthenticate directive controls the behavior of mod_sql regarding the authentication process. SQLAuthenticate can provide fine grained control over authentication of logins and file access for both users and groups. Using this directive, mod_sql can be configured to be the authoritative authentication mechanism - in that case, mod_sql provides authentication and all other authentication mechanisms will be bypassed.
The syntax for SQLAuthenticate can take one of two possible formats. The simplest syntax is a simple on | off format:
mod_sql will perform login authentication and will also control file access using both user ID and group ID. This is equivalent to the following alternative syntax:
SQLAuthenticate users groups userset groupset
mod_sql will not perform user or group lookups nor will it control file access or functionality.
A more complex syntax is provided to provide finer control of the behavior of mod_sql. Two features in particular may be controlled via this syntax:
Authorititative lookups and authentication
File access or functionality control based on UID or GID
If this option is present, user lookups will take place. Appending an asterisk to users will cause mod_sql to become authoritiative for user lookups. All other user authentication methods will be ignored. If this option is not included, mod_sql will not perform any user lookups.
If this option is present, group lookups will take place. Appending an asterisk to groups will cause mod_sql to become authoritiative for group lookups. All other authentication methods will be ignored. If this option is not included, mod_sql will not perform any group lookups.
If this option is present, mod_sql will control file access or functionality by processing the (get|set|end)pwent calls. These calls are used to determine file access rights based on username. This option has no effect if the user[*] option is not present.
If mod_sql is used to authenticate a significant number of users, the (set|get|end)pwent calls can become expensive. The number of queries will be n+1, where n is the number of users to be looked up. On a large system, this can significantly slow logins. Using the usersetfast option will cause a single query to be performed to lookup all users, speeding up the login process. The drawback to this option is that memory utilization will be increased.
If this option is present, mod_sql will control file access or functionality by processing the (get|set|end)grent calls. These calls are used to determine file access rights based on groupname. This option has no effect if the group[*] option is not present.
If mod_sql is used to authenticate a significant number of groups, the (set|get|end)grent calls can become expensive. The number of queries will be n+1, where n is the number of groups to be looked up. On a large system, this can significantly slow logins. Using the groupsetfast option will cause a single query to be performed to lookup all groups, speeding up the login process. The drawback to this option is that memory utilization will be increased.
Turning off (not including) userset or groupset affects the functionality of mod_sql. Not allowing these lookups may remove the ability to control access or control functionality by group membership, depending on your other auth handlers and the data available to them. At the same time, choosing not to do these lookups may dramatically speed login for many large sites.
The 'fast' suffix is not appropriate for every site. Normally, mod_sql will retrieve a list of users and groups, and get information from the database on a per-user or per-group basis. This is query intensive -- it requires (n+1) queries, where n is the number of users or groups to lookup. By choosing 'fast' lookups, mod_sql will make a single SELECT query to get information from the database.
In exchange for the radical reduction in the number of queries, the single query will increase the memory consumption of the process -- all group or user information will be read at once rather than in discrete chunks.
Note:If the groupset option is specified, mod_sql requires that the SQL group table contain only a single record for each group. All members of a group must be specified in the single record. Make sure that the group table is created with a sufficent column size for group members - for example, a MySQL group table should use type TEXT for the group members column, providing 65535 characters for listing all of the group members in a comma-separated list.
If user and group lookups are desired, but other means will be used to perform file access control, and the user/group lookups are not to be authoritatuve, the following directive syntax is appropriate. This is not a particuarly interesting configuration.
SQLAuthenticate users groups
A more interesting configuration for mod_sql is shown below. In this configuration, mod_sql is authoritative for both users and groups, and also performs access control based on both user name and group membership. Utilizing a configuration such as this removes the need to provide a shell account for users on the server, while still providing "non-anonymous" ftp access with access control. The "fast" option is also used to speed up logins, at the expense of increased memory utilization.
SQLAuthenticate users* groups* usersetfast groupsetfast