Xlight FTP Server Help Document

ODBC Database Tables Structure


 

Xlight FTP Server uses three tables to store the settings of a user in the database. The primary table is the "acct_table", which has the basic settings of a user, such as host_id or host_port, username, password and his group, home path and home path permission etc. The second table is the "acct_param_table", which is referred to by the "param_index" column in the "acct_table". It has additional settings for a user. The "param_index" column in the "acct_table" links to the same "param_index" column in the "acct_param_table". If two users have the setting in the "acct_param_table", they can share it by using the same param_index in the "acct_table". The third table is "virtual_path_table". "virtual_path_table" can be used to set virtual paths for a user. A user can have multiple virtual paths in the database. These virtual paths can have different or the same "virtual_path_index" in the "virtual_path_table". The "virtual_path_indexes" column in the "acct_table" can link to one or multiple rows in the "virtual_path_table" by using the "virtual_path_index" column value from "virtual_path_table". If it contains multiple "virtual_path_index", they must be separated by ","(comma). The database column can be empty, the default value will be used if the related column is empty.

In the Xlight FTP Server, database tables can also be shared between different machines or virtual servers. By giving each machine a unique ID, the value in the "host_id" column can be used to identify different machines. Different virtual servers will have different ports, so the value in the "host_port" column can be used to identify virtual servers in the same machines.

There are two options "Skip host_id column" or "Skip host_port column". When the option "Skip host_id column" is selected, during the database query, the "host_id" column in the "acct_table" will be skipped. Because the "host_id" column is used to identify different machines, skipping the "host_id" column will make it possible to share the same user's settings in the database between different machines. When the option "Skip host_port column" is selected, during the database query, the "host_port" column in the "acct_table" will be skipped. Because the "host_port" column is used to identify different virtual servers, skipping the "host_port" column will make it possible to share the same user setting between different virtual servers. If both options are selected, it will be possible for all machines and all virtual servers to share the same user's settings in the database. These two options provide the flexibility to share or not to share user settings between machines or virtual servers.

When you select the "Only check username and password" option, the virtual server will only use the database for authenticating the user, which means it will only check columns of "host_id", host_port", "username", "password" and "no_password" in the "acct_table". It will get other settings from local user settings in the FTP server with the same username, but not from the database.

In the directory that the Xlight FTP Server has been installed, there is an "odbc_tables.sql" file, which you can use to create the required two database tables manually. Or you can use the "create tables" button in the Data Source configuration dialog box to create these two database tables.

When Xlight FTP Server uses an external database, it will not need "WRITE" or "MODIFY" right to use the database. When you configure ODBC Data Source in the local machine, you should better set up a database account with "SELECT" right only(read-only). This would provide better security.

The password column in the "acct_table" could use 32 bytes MD5 hash value or clear text password string. If you want to use MD5 hash, you can find MD5 hash calculation tools easily by searching "MD5 calculator" in Google. You can paste the generated MD5 hash value to this column.

If a column is not used, it can be empty. If a column is empty, the FTP server will use the default value for it. But the "host_id", "host_port", "username", "password" and "no_password" columns are required columns. They can not be empty.

acct_table structure

Database Column nameData TypeDescription
host_idintegerhost_id value is used to identify different machines
host_portintegerhost_port value is used to identify different virtual servers within the same machine
usernamevarchar(64)username, length: 1-64 bytes
passwordvarchar(64)password of user, length: 64 bytes; It can be MD5/SHA hash value of password or the clear text password string
no_passwordinteger0(default)=require password; 1=doesn't require password
home_pathvarchar(255)User's home path. The "home_path" column here refers to the real path. The home path has the implicit virtual path "/".
home_permvarchar(10)User's home path permission. Supported flags are following: L - List; R - Read; S -including subdir; C- create W-Write; A - Append; D - Delete; N-Rename; For example, if you want "write", "list", "read" and "including sub-directories" permission for this user, you can use "WLRS-----" in this column. The order of flag values has no impact on the permission.
groupnamevarchar(64)groupname, length: 1-64 bytes
expire_timevarchar(24)Account expire time. format="yyyy-mm-dd hh:mm:ss" ,where yyyy-mm-dd is year-month-day and hh:mm:ss is hour-minute-second. The range of hours is between 0-23. If this column is empty, it means this account will not expire(default).
hide_pub_pathinteger0(default)= show public virtual path; 1= hide public virtual path to this user
hide_grp_pathinteger0(default)= show his group virtual path; 1= hide his group virtual path
enable_quotasinteger0(default)= disable user quotas; 1=enable user quotas;
enable_ratiosinteger0(default)=disable upload/download ratios; 1=enable upload/download ratios
freeze_userinteger0(default)= normal user; 1= disable this user
bypass_svr_maxinteger0(default)= follow server maximum users; 1= bypass server maximum users
virtual_path_indexesvarchar(255)It links to the "virtual_path_index" columns of "virtual_path_table". If it contains multiple "virtual_path_index", they must be separated by ","(comma).
param_indexintegerparam index links to the same column in the "acct_param_table", which is the index to additional settings of this user. Different users can share the same param_index value if they have the same settings in the "acct_param_table".


acct_param_table structure

Database Column nameData Type  Description
param_indexintegerparam_index value is the index to additional settings of a user.
max_down_speedintegerMaximum download speed for this user in Kbps, 0(default)= unlimited download speed.
max_up_speedintegerMaximum upload speed for this user in Kbps, 0(default)= unlimited upload speed.
max_user_connintegerMaximum allowed connections for this user, 0(default)= unlimited user connections.
max_conn_per_ipintegerMaximum allowed per IP connections for this user, 0(default)= unlimited per IP connections.
max_session_timeintegerMaximum session time for this user, 0(default)= unlimited session time.
max_down_file_numintegerMaximum number of files this user can download for each session, 0(default)= an unlimited number of files.
max_up_file_numintegerMaximum number of files this user can upload for each session, 0(default)= an unlimited number of files.
max_session_downintegerMaximum amount in MB this user can download for each session, 0(default)= unlimited download amount.
max_session_upintegerMaximum amount in MB this user can upload for each session, 0(default)= unlimited upload amount.
max_down_file_sizeintegerMaximum file size in MB this user can download for each session, 0(default)= unlimited download file size.
max_up_file_sizeintegerMaximum file size in MB this user can upload for each session, 0(default)= unlimited upload file size.
disk_quotasintegerMaximum disk usage allowed for this user
disk_quota_unitintegerDisk quota unit. Available choice are: KB(default), MB
ratios_typeinteger0= by Kbytes; 1= by files;
download_ratiosintegerThe first half of download/upload ratios. It should be used together with the upload_ratios value shown below.
upload_ratiosintegerThe second half of download/upload ratios. It should be used together with the download_ratios value shown above.
starting_creditintegerthe starting credit of this user
ftp_cmd_speedintegervalue range, 0-1000. FTP command speed per second after user login. 0(default)= 20 commands per second.
allowed_login_ipvarchar(255)Format:ip address/subnet mask; Maximum 16 items are allowed. Items are separated by "|". For example: "192.168.252.1/255.255.255.0|202.96.3.1/255.255.255.0". Leaving this column empty, if you don't use it.
denied_login_ipvarchar(255)Format:ip address/subnet mask; Maximum 16 items are allowed. Items are separated by "|". For example: "192.168.252.1/255.255.255.0|202.96.3.1/255.255.255.0". Leaving this column empty, if you don't want to deny the user's login IPs (default).
allowed_login_timevarchar(255)Format:"start - end", where the start and end is in the format "mon/day/year hour:minute" or "weekday hour:minute". Note: The minimum unit is minute not second. A maximum of 10 items are allowed for this column. Items are separated by "|". For example, "01/11/2004 11:00 - 02/11/2004 12:00 | 03/21/2004 - 04/21/2005 | Mon - Tus | Wed 20:04 - Fri 20:04". Leave this column empty, if you don't want to restrict the user's login time. The start and end times must match. For example, if the start time uses weekday, the end time must also use weekday, If the start time uses time, the end must also use time.
deny_ftp_commandsvarchar(128)Supported FTP commands: "LIST, PASV, PORT, RETR, CWD, CDUP, STOR, DELE, REST, ABOR, SIZE, APPE, MKD, RMD, RNFR, RNTO, SYST, HELP, NLIST, NOOP". Each command item is separated by "|". Leave this column empty, if you don't have any denied FTP commands.
files_allow_accessvarchar(255)You can put the name of the files you want the user to access in this column. If names are put in this column, this user can only access and see these files in the FTP server. But he can not access and see other files other than those in this column. The name can be unix shell-style wildcards. Different file names are separated by "|". For example, if "*.exe|*.txt" is put here, then this user can only access and see files with ".exe" and ".txt" extensions. Leave this column empty, if you don't want this kind of control.
files_deny_accessvarchar(255)You can put the name of the files you want to deny the user from accessing in this column. If names are put in this column, this user can not access and see these files in the FTP server. But he can still access and see other files. The name can be unix shell-style wildcards. Different file names are separated by "|". For example, if "*.exe|*.txt" is put here, then this user can not access and see files with ".exe" and ".txt" extensions. Leave this column empty, if you don't want this kind of control.
send_socket_bufferintegerSocket buffer for sending data to the client. The Unit is in KB. 0(default) will use 32KB as the socket buffer size. FTP clients will normally have better throughput with a large socket buffer, especially when users are in the Gigabyte Ethernet.
recv_socket_bufferintegerSocket buffer for receiving data from the client. The Unit is in KB. 0(default) will use 32KB as the socket buffer size. FTP clients will normally have better throughput with a large socket buffer, especially when users are in the Gigabyte Ethernet.

virtual_path_table structure

Database Column nameData TypeDescription
virtual_path_indexintegervirtual_path_index value is the index to a virtual path.
virtual_pathvarchar(255)The name of the virtual path.
real_pathvarchar(255)Real path associated with this virtual path. The variable %username% can be used for the real path. %username% will be replaced with the actual user name after user logins. If the real path doesn't exist when the user logs in, Xlight FTP Server will create it automatically.
permissionvarchar(10)Virtual path permission. Supported flags are following: L - List; R - Read; S -including subdir; C- create W-Write; A - Append; D - Delete; N-Rename; For example, if you want to "write", "list", "read" and "including sub-directories" permission, you can use "WLRS-----" in this column. The order of flag values has no impact on the permission.