Free: Integrate logins with CrushFTP Server

[spinkb]

A customer of mine wanted to integrate Kayako with CrushFTP. This way they could have a full robust FTP, FTPS, SFTP, WebDAV, and power web file management server...but with one single signon managed with Kayako.

So, with a couple code changes to CrushFTP, and utilizing the existing CrushSQL plugin to CrushFTP, here is the resulting SQL to integrate Kayako with CrushFTP.

This script is free. But you will need CrushFTP for it to be of any value. CrushFTP starts at $30. All you need to do is point the CrushSQL plugin at your MySQL server, and the crushftp database this script makes.

-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version 5.0.45-community-nt


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema crushftp
--

CREATE DATABASE IF NOT EXISTS crushftp;
USE crushftp;

--
-- Temporary table structure for view `directories`
--
DROP TABLE IF EXISTS `directories`;
DROP VIEW IF EXISTS `directories`;
CREATE TABLE `directories` (
`id` varbinary(11),
`dirName` varchar(255),
`dirFTPPath` varchar(255),
`dirLocalPath` varchar(255),
`privDownload` varchar(1),
`privUpload` varchar(1),
`privView` varchar(1),
`privDelete` varchar(1),
`privDeleteDir` varchar(1),
`privMakeDir` varchar(1),
`privRename` varchar(1),
`privResume` varchar(1),
`quotaBytes` varchar(255)
);

--
-- Temporary table structure for view `users`
--
DROP TABLE IF EXISTS `users`;
DROP VIEW IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(10) unsigned,
`userName` varchar(30),
`userPass` varchar(32),
`expireDate` char(0),
`maxIdleTime` int(2),
`maxSimultaneousLogins` int(1),
`maxLoginsPerIP` int(2),
`canRequestFilesAsZip` varchar(1),
`site` char(0),
`email` varchar(150),
`ssh_public_keys` char(0)
);

--
-- Definition of table `directory`
--

DROP TABLE IF EXISTS `directory`;
CREATE TABLE `directory` (
`id` int(11) NOT NULL default '0',
`dirName` varchar(255) NOT NULL default 'dirName',
`dirFTPPath` varchar(255) NOT NULL default '/',
`dirLocalPath` varchar(255) NOT NULL default '/',
`privDownload` char(1) NOT NULL default 'Y',
`privUpload` char(1) NOT NULL default 'N',
`privView` char(1) NOT NULL default 'Y',
`privDelete` char(1) NOT NULL default 'N',
`privDeleteDir` char(1) NOT NULL default 'N',
`privMakeDir` char(1) NOT NULL default 'N',
`privRename` char(1) NOT NULL default 'N',
`privResume` char(1) NOT NULL default 'Y',
`quotaBytes` varchar(255) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `directory`
--

/*!40000 ALTER TABLE `directory` DISABLE KEYS */;
INSERT INTO `directory` (`id`,`dirName`,`dirFTPPath`,`dirLocalPath`,`privD ownload`,`privUpload`,`privView`,`privDelete`,`pri vDeleteDir`,`privMakeDir`,`privRename`,`privResume `,`quotaBytes`) VALUES
(-1,'customer_uploads','/','D:/customer_uploads/','Y','N','Y','Y','Y','N','N','Y','');
/*!40000 ALTER TABLE `directory` ENABLE KEYS */;


--
-- Definition of view `directories`
--

DROP TABLE IF EXISTS `directories`;
DROP VIEW IF EXISTS `directories`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `directories` AS select `directory`.`id` AS `id`,`directory`.`dirName` AS `dirName`,`directory`.`dirFTPPath` AS `dirFTPPath`,`directory`.`dirLocalPath` AS `dirLocalPath`,`directory`.`privDownload` AS `privDownload`,`directory`.`privUpload` AS `privUpload`,`directory`.`privView` AS `privView`,`directory`.`privDelete` AS `privDelete`,`directory`.`privDeleteDir` AS `privDeleteDir`,`directory`.`privMakeDir` AS `privMakeDir`,`directory`.`privRename` AS `privRename`,`directory`.`privResume` AS `privResume`,`directory`.`quotaBytes` AS `quotaBytes` from `directory` where (`directory`.`id` = -(1)) union all select _utf8'-1' AS `id`,_utf8'staff' AS `dirName`,_utf8'/' AS `dirFTPPath`,_utf8'D:/FTP/staff/%user_name%' AS `dirLocalPath`,_utf8'Y' AS `privDownload`,_utf8'Y' AS `privUpload`,_utf8'Y' AS `privView`,_utf8'Y' AS `privDelete`,_utf8'Y' AS `privDeleteDir`,_utf8'Y' AS `privMakeDir`,_utf8'Y' AS `privRename`,_utf8'Y' AS `privResume`,_utf8'500000000' AS `quotaBytes`;

--
-- Definition of view `users`
--

DROP TABLE IF EXISTS `users`;
DROP VIEW IF EXISTS `users`;
CREATE ALGORITHM=UNDEFINED DEFINER=`crushftp`@`%` SQL SECURITY DEFINER VIEW `crushftp`.`users` AS select `company`.`swstaff`.`staffid` AS `id`,`company`.`swstaff`.`username` AS `userName`,`company`.`swstaff`.`password` AS `userPass`,_utf8'' AS `expireDate`,10 AS `maxIdleTime`,5 AS `maxSimultaneousLogins`,99 AS `maxLoginsPerIP`,_utf8'Y' AS `canRequestFilesAsZip`,_utf8'' AS `site`,`company`.`swstaff`.`email` AS `email`,_utf8'' AS `ssh_public_keys` from `company`.`swstaff`;



/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

[Jamie Edwards]

Thank you very much for sharing this