Serv-U can store user and group accounts in a relational database. Any database will work, as long as there is an ODBC connector for it. When a domain is created with ODBC storage this tab is visible. In it you tell Serv-U how it should connect to the ODBC data source and what setup items are available from that data source.
Serv-U has been tested with a number of popular data sources, including the powerful freeware MySQL server, MS SQL Server, MS Access, and Oracle. Sample databases, corresponding Serv-U settings, and utilities are available from the Add-On pages on the Serv-U Web site:
http://www.serv-u.com/addons.asp
The way Serv-U uses an ODBC data source is very flexible. Tables and columns can be named any way you wish, as little as just username/password/home directory/access can be stored in a single table, or as much as every setup option available for accounts, and within reason database data types are automatically mapped to Serv-U’s types.
There are two options in the domain’s Advanced settings tab that can be of particular use to ODBC domains. In case passwords should be stored in clear-text rather than the default encrypted format, uncheck the ‘Store passwords in encrypted form’ option. The other option is ‘Account cache time-to-live’. This sets the number of seconds Serv-U should cache the user or group information before re-reading it from the database. The default for ODBC domains is 60 seconds. This means it can take up to 60 seconds before the server sees any changes made to the database accounts by external programs. Lowering this value means changes are seen more rapidly by the server. The downside of this is increased database access and overhead.
When this radio button is checked the tab shows the parameters used for coupling with the ODBC data source. This includes tables names, database account information, and column names of the tables.
This radio button switches the tab view to show the templates Serv-U uses internally to generate SQL statements from. There are only a few templates, and they are used to generate about two dozen SQL statements for the actual database interaction.
Pressing this button makes Serv-U disconnect from the data source, reconnect, and re-read account information. All cached account information is discarded. In case of changes to the database this is the way to make Serv-U quickly aware of them.
While the list on this tab seems long and intimidating, it is actually straightforward and easy to set up. The list consists of three sections: The first section is about ODBC data source access, the second section specifies table names of the ODBC data source, and the third section spells out the column names Serv-U should couple with to get and set account information.
In case Serv-U cannot properly connect to the data source the domain will be placed offline. This can be recognized by a red slash through the domain name in the left pane. If this happens, more information can be obtained, including the exact ODBC error, by looking at the Server session log.
The first three entries on the list deal with connecting to an ODBC data source (or DSN, to use ODBC parlance). To use any database, you first need to create a DSN through the "Data Sources (ODBC)" applet in Control Panel. The configuration for each DSN is specific to the database that is being used; please refer to the database’s documentation for this.
The following entries are part of the data source access section:
ODBC source name
Name of the ODBC data source (DSN name as you set up it in the ODBC Data Source Administrator. This field cannot be left blank.
ODBC account name
Name of an account to access the underlying database that is being used as the ODBC data source. Can be left blank if anonymous access is used to the database. Keep in mind that the account name is case sensitive on many data sources.
ODBC account password
Password for the database account. Only needed if a password is necessary to log into the database. Keep in mind that passwords are almost always case sensitive on data sources.
The table names tell Serv-U what tables should be used by Serv-U to store account information. The minimum is to have a single table, for user accounts. All the other tables are optional. Tables need to exist before they can be used in Serv-U, the server does not create any tables. The following entries are part of the table names section:
Table name of users
Name of the database table containing user accounts. This entry is always needed. In case you only need a single access rule, no IP access rules, and no groups, this is the only entry that is needed.
Table name of groups
Name of the database table containing group accounts. This entry is optional, and only needed in case groups are needed.
Table name of user dir access rights
Name of the database table containing user directory access rules. This entry is optional, and only needed in case there is no field to store a directory access rule in the user table, or more than one rule is needed per user account.
Table name of group dir access rights
Name of the database table containing group directory access rules. This entry is optional, and only needed if groups are supported, there is no field to store a directory access rule in the group account table, and/or more than a single directory access rule is needed per group account.
Table name of user IP access rights
Name of the database table containing user IP access rules. This entry is optional, and only needed in case IP access rules need to be stored for user accounts.
Table name of group IP access rights
Name of the database table containing group IP access rules. This entry is optional, and only needed in case IP access rules need to be stored for group accounts.
To couple Serv-U’s internal variables to data source fields the database column names need to be specified in the columns names section. Most of these entries are optional, only a few are required. It depends on how simple or complex you want to make your account setup. If fields are not present Serv-U will not be able to store or use that corresponding account feature. Instead the default is used in that case, defaults are always sensible and secure, i.e. the default will not allow undue access. The fields specified here need to exist in the database, Serv-U does not create them.
The minimums needed are a single table for user accounts, with fields for account name, password, home directory, and directory access. If multiple tables are present the column names are shared between columns. This means that the database tables for, say, user accounts and user directory access rules need to use the same name for the account fields in both. The same is true for several other column names. To support all Serv-U features a total of 6 tables are needed: A user accounts table, a group accounts table, a user directory access rules table, a group directory access rules table, a user IP access rules table, and a group IP access rules table.
These are the table structures for the various database tables:
User accounts table - Continued
Now on to the list of column name entries and a description of each one of them:
Column name of dir/IP access rules index no.
This field is used in directory and IP access rules tables for both users and groups. It serves to allow multiple rules per user, the first rule has index no. 1, the second rule 2 etc. This entry is only needed in case there are directory or IP access rule tables, and required if that is the case. The field should be an integer, 8 bit is sufficient. It should be indexed and part of the primary key of the access rules tables (together with the account name field).
Column name of account name
This field is used in all tables and is required. It is used for the user and group account names fields in all tables. It is a character field, length depends on what length is needed for account names, 50 characters is suggested. This field should be indexed and is the primary key for the users and groups tables, and part of the key for access rules tables (together with the index number field).
Column name of password
This field is used in the user accounts table and it is required. It is used to store either the encrypted or clear-text account passwords. It is a character field, a length of 50 characters is suggested.
Column name of password type
This field is used in the user accounts table and it is optional. It is a (small) integer field with the following values:
0 = regular password
1 = S/KEY one-time-password using MD4
2 = S/KEY one-time-password using MD5
Column name of allow change password
This field is used in the user accounts table and it is optional. It is used to tell Serv-U if the user should be allowed to change password through the FTP client program (TRUE) or not (FALSE). It is a boolean field, or if the underlying database doesn’t support true boolean (as in MySQL) a 2 bit integer will work.
Column name of S/KEY settings
This field is used in the user accounts table and it is optional. It is used to store internal bookkeeping for S/KEY passwords and is only needed in case accounts use S/KEY passwords. No value should be set directly by the database, Serv-U updates this field by itself. It is a character field, minimum length should be 50 characters.
Column name of dir/IP access rule
This column name is used for several database fields: First, the user or group directory access rule field, in both the accounts and directory access rules tables. Second, the IP access rule field in both the user and group IP access rules tables.
For directory access rules
This field can be present in either the user accounts table or user directory access rules table, similarly, if there are groups, it needs to be present in either the group accounts table or group directory access rules table. If it is present in both the accounts table and access rules table then the first directory access rule will be stored in the accounts table and all subsequent rules go into the access rules table. If there is no access rules table then only one access rule is supported for the user or group accounts. This is a character field, length depends on what is needed for paths, 200 characters is suggested. This field has the following syntax:
<full path> | <access>
with <access> :
R = read access
W = write access
A = append access
M = modify access
C = create directory access
D = delete directory access
L = list directory access
E = execute program access
P = inherit rule to sub-directories
For IP access rules
If there is a user or group IP access rules table this field is required. It contains the actual IP access rule. This is a character field, a length of 17 characters or more is required. IP addresses can be both numerical as well as symbolic (textual). The address can contain wildcards in the form of ‘*’ and ‘?’. The syntax is as follows:
<access> | <IP name or address>
with <access>:
A = allow access
D = deny access
Column name of home directory
This field is used in the user accounts table and is required. It contains the user’s home directory. This is a character field, length depends on what is needed for paths, 200 characters is suggested.
Column name of administrative privilege
This field is used in the user accounts table and is optional. It tells the server what, if any, administrative privilege the account has. This is a (small) integer field with the following values:
0 = no privilege
1 = system administrator privilege
2 = group administrator privilege
3 = domain administrator privilege
4 = read-only administrator privilege
Column name of login message file
This field is used in the user accounts table and is optional. It contains the path of the login message file that is used to generate the login message reply. This is a character field, 200 characters length is suggested.
Column name of account enable/disable
This field is used in the user accounts table and is optional. It is used to disable (TRUE) or enable (FALSE) the account. It is a boolean field.
Column name of session encryption
This field is used in the user accounts table and is optional. Its value indicates whether a client can only use secure-FTP (TRUE) or if a regular plain-text session (FALSE) is sufficient. It is a boolean field.
Column name of lock user in homedir
This field is used in the user accounts table and is optional. When a user is locked (TRUE) he sees the root ‘/’ as his home directory and all paths are relative to the root. When not locked (FALSE) the user sees the full physical path. It is a boolean field.
Column name of hide hidden files
This field is used in the user accounts table and is optional. When enabled (TRUE) the user does not see files and directories that have the ‘hidden’ file attribute set. When disabled (FALSE) the user sees all files and directories. This is a boolean field.
Column name of always allow login
This field is used in the user accounts table and is optional. When enabled (TRUE) the user is allowed to log in even when the current user limit for the account or server is reached. When disables (FALSE) the user limit rules apply. This is a boolean field.
Column name of maximum concurrent users
This field is used in the user accounts table and is optional. This field sets the maximum number of concurrent client sessions using the particular user account. This is an integer field, 0 indicates there is no limit.
Column name of maximum uplink speed
This field is used in the user accounts table and is optional. It sets the maximum speed in the uplink direction, i.e. for uploads. This is an integer field, units are bytes/second and 0 indicates there is no limit.
Column name of maximum downlink speed
This field is used in the user accounts table and is optional. It sets the maximum speed in the downlink direction, i.e. for downloads. This is an integer field, units are bytes/second and –1 indicates there is no limit.
Column name of maximum users from IP
This field is used in the user accounts table and is optional. It determines if the same client (from the same IP address) can log in multiple times or not. This is an integer field. The number in the field sets how many times a client from the same IP can log in. A value of –1 indicates there is no limit.
Column name of idle time-out
This field is used in the user accounts table and is optional. It sets the time a session can be idle before the server forcefully disconnects the session. This is an integer field, the units are seconds, 0 indicates there is no limit.
Column name of session time-out
This field is used in the user accounts table and is optional. It sets the time a session can be connected to the server, regardless of activity. When the session time-out is up the session is disconnected forcefully. This is an integer field, the units are seconds, 0 indicates there is no limit.
Column name of enable quota
This field is used in the user accounts table and is optional. If disk quota support is needed this field needs to be present. When enabled (TRUE) the account has disk quota enforced, when disabled (FALSE) there is no disk quota management for the account. This is a boolean field.
Column name of current disk quota
This field is used in the user accounts table and is optional. If disk quota support is needed this field needs to be present. This is the number of currently used bytes of disk quota for the account. It is a 64-bit integer field. If only 32-bit integers are available in the underlying database, such as in MS Access, this can be used but disk quota will be limited to a maximum of around 2GB.
Column name of maximum disk quota
This field is used in the user accounts table and is optional. If disk quota support is needed this field needs to be present. This is the maximum number of bytes the account is allowed to use. It is a 64-bit integer field. If only 32-bit integers are available in the underlying database, such as in MS Access, this can be used but disk quota will be limited to a maximum of around 2GB.
Column name of ratio type
This field is used in the user accounts table and is optional. If ratio support is needed this field needs to be present. This field tells the server how ratios should be counted. This is a (small) integer field with the following values:
0 = no ratios
1 = count files-per-session
2 = count bytes-per-session
3 = count files-over-all-sessions
4 = count bytes-over-all-sessions
Column name of upload ratio
This field is used in the user accounts table and is optional. If ratio support is needed this field needs to be present. This field tells the server how many files/bytes should be uploaded to get XX downloads. This is an integer field.
Column name of download ratio
This field is used in the user accounts table and is optional. If ratio support is needed this field needs to be present. This field tells the server how many files/bytes can be downloaded per YY uploads. This is an integer field.
Column name of ratio credit
This field is used in the user accounts table and is optional. If ratio support is needed this field needs to be present. This field keeps track of the account’s current ratio credit in files or bytes. This is a floating point number field.
Column name of account expiration date
This field is used in the user accounts table and is optional. It specifies the date that the account should automatically be deleted. Any date during or before 1980 means the account should never be deleted. This is a date/time field.
Column name of member group names
This field is used in the user accounts table and is optional. It contains the groups the user account is a member of. This is a character field, a length of 80 characters is recommended. Syntax for this is:
<group> | <group> | <group> …
Column name of account notes
This field is used in the user and groups accounts table and is optional. It is used to contain miscellaneous notes for the system administrator. This is a character field, the length depends on the note length one wants to store.
The templates used by Serv-U to generate SQL statements are shown when the ‘SQL templates’ radio button is checked. These templates should typically be left the way they are, in fact, changing them without knowing exactly what their effect will be, is a sure way to make Serv-U fail to work with the ODBC database. To undo any changes, just leave the fields blank, this will make Serv-U revert to the default templates. There are times when careful changes can make Serv-U work better in specific situations, which is why they are available, and why they can be changed. For example, in case of a database engine that is case sensitive for lookups the templates can be changed to include "UPPER()" function calls around the ‘$Name$’ values.
Most of the text in the templates is used literally to generate SQL statements. There are some special values:
$Table$ = replaced with the table name
$Name$ = replaced with the field name for lookups, usually the user or group account name
$Index$ = replaced with the sort field name
$Column$ = replaced with the column name, usually combined with ‘[‘ and ‘]’
‘ = replaced with the dB specific quote character
[…] = repeat delimiters, used for iterating over columns
, = copied, unless it is the last (dangling) one in case of ‘[‘ and ‘]’