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.
| Database Column name | Data Type | Description |
|---|---|---|
| host_id | integer | host_id value is used to identify different machines |
| host_port | integer | host_port value is used to identify different virtual servers within the same machine |
| username | varchar(64) | username, length: 1-64 bytes |
| password | varchar(64) | password of user, length: 64 bytes; It can be MD5/SHA hash value of password or the clear text password string |
| no_password | integer | 0(default)=require password; 1=doesn't require password |
| home_path | varchar(255) | User's home path. The "home_path" column here refers to the real path. The home path has the implicit virtual path "/". |
| home_perm | varchar(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. |
| groupname | varchar(64) | groupname, length: 1-64 bytes |
| expire_time | varchar(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_path | integer | 0(default)= show public virtual path; 1= hide public virtual path to this user |
| hide_grp_path | integer | 0(default)= show his group virtual path; 1= hide his group virtual path |
| enable_quotas | integer | 0(default)= disable user quotas; 1=enable user quotas; |
| enable_ratios | integer | 0(default)=disable upload/download ratios; 1=enable upload/download ratios |
| freeze_user | integer | 0(default)= normal user; 1= disable this user |
| bypass_svr_max | integer | 0(default)= follow server maximum users; 1= bypass server maximum users |
| virtual_path_indexes | varchar(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_index | integer | param 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". |
| Database Column name | Data Type | Description |
|---|---|---|
| param_index | integer | param_index value is the index to additional settings of a user. |
| max_down_speed | integer | Maximum download speed for this user in Kbps, 0(default)= unlimited download speed. |
| max_up_speed | integer | Maximum upload speed for this user in Kbps, 0(default)= unlimited upload speed. |
| max_user_conn | integer | Maximum allowed connections for this user, 0(default)= unlimited user connections. |
| max_conn_per_ip | integer | Maximum allowed per IP connections for this user, 0(default)= unlimited per IP connections. |
| max_session_time | integer | Maximum session time for this user, 0(default)= unlimited session time. |
| max_down_file_num | integer | Maximum number of files this user can download for each session, 0(default)= an unlimited number of files. |
| max_up_file_num | integer | Maximum number of files this user can upload for each session, 0(default)= an unlimited number of files. |
| max_session_down | integer | Maximum amount in MB this user can download for each session, 0(default)= unlimited download amount. |
| max_session_up | integer | Maximum amount in MB this user can upload for each session, 0(default)= unlimited upload amount. |
| max_down_file_size | integer | Maximum file size in MB this user can download for each session, 0(default)= unlimited download file size. |
| max_up_file_size | integer | Maximum file size in MB this user can upload for each session, 0(default)= unlimited upload file size. |
| disk_quotas | integer | Maximum disk usage allowed for this user |
| disk_quota_unit | integer | Disk quota unit. Available choice are: KB(default), MB |
| ratios_type | integer | 0= by Kbytes; 1= by files; |
| download_ratios | integer | The first half of download/upload ratios. It should be used together with the upload_ratios value shown below. |
| upload_ratios | integer | The second half of download/upload ratios. It should be used together with the download_ratios value shown above. |
| starting_credit | integer | the starting credit of this user |
| ftp_cmd_speed | integer | value range, 0-1000. FTP command speed per second after user login. 0(default)= 20 commands per second. |
| allowed_login_ip | varchar(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_ip | varchar(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_time | varchar(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_commands | varchar(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_access | varchar(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_access | varchar(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_buffer | integer | Socket 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_buffer | integer | Socket 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. |
| Database Column name | Data Type | Description |
|---|---|---|
| virtual_path_index | integer | virtual_path_index value is the index to a virtual path. |
| virtual_path | varchar(255) | The name of the virtual path. |
| real_path | varchar(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. |
| permission | varchar(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. |