http://www.castaglia.org/proftpd/doc/contrib/ProFTPD-mini-HOWTO-SQL.html

http://blog.wu-boy.com/2006/10/21/22

http://www.khoosys.net/single.htm?ipg=848

http://www.lslnet.com/linux/f/docs1/i34/big5258812.htm

確定 proftpd 有支援 sql module

# proftpd -l | grep sql
mod_sql.c
mod_sql_mysql.c
mod_quotatab_sql.c

CREATE DATABASE `ftp` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

此資料表紀錄使用者的基本資訊，uid 是使用者系統 uid，gid 是使用者 group 的 id，passwd 密碼，homedir 為使用者登入的家目錄，shell 可以為該使用者指定相應的 shell。

CREATE TABLE `ftp` (
`username` varchar( 60 ) default NULL ,
`uid` int( 11 ) NOT NULL ,
`gid` int( 11 ) default NULL ,
`password` varchar( 30 ) default NULL ,
`homedir` varchar( 60 ) default NULL ,
`shell` varchar( 11 ) default '/bin/false',
PRIMARY KEY ( `uid` ) ,
UNIQUE KEY ( `username` )
) TYPE = MYISAM 
;

grpname 是群組名稱，gid 是系統組的 ID，members 是組的成員。注意：多成員，他們之間要用逗號隔開，不能使用空格 例如 3 個使用者 test1 test2 test3 ，members就要寫 (test1,test2,test3)。

CREATE TABLE `groups` (
`groupname` varchar( 30 ) NOT NULL default '',
`gid` int( 11 ) NOT NULL default '0',
`members` text default NULL
) TYPE = MYISAM;

建立使用者 proftpd，並指定 ftp 資料庫權限。

# used to connect to the database
# databasename@host database_user user_password
SQLConnectInfo  ftp@localhost proftpd [password]

# Here we tell ProFTPd the names of the database columns in the "usertable"
# we want it to interact with. Match the names with those in the db
SQLUserInfo     ftp username password uid gid homedir shell

# Here we tell ProFTPd the names of the database columns in the "grouptable"
# we want it to interact with. Again the names match with those in the db
SQLGroupInfo    groups groupname gid members

# The passwords in MySQL are encrypted using CRYPT
SQLAuthTypes            Plaintext
SQLAuthenticate         users* groups*

# set min UID and GID - otherwise these are 999 each
#SQLMinID        500

# create a user's home directory on demand if it doesn't exist
SQLHomedirOnDemand on

#設定mysql log檔
SQLLogFile /var/log/proftpd/sql.log
PersistentPasswd off

# Update count every time user logs in
SQLLog PASS updatecount
SQLNamedQuery updatecount UPDATE "count=count+1, accessed=now() WHERE userid='%u'" ftpuser

# Update modified everytime user uploads or deletes a file
SQLLog  STOR,DELE modified
SQLNamedQuery modified UPDATE "modified=now() WHERE userid='%u'" ftpuser

# groupadd ftpgroup

# /etc/init.d/proftpd restart