Script for Creating Necessary ODBC Tables

The script/schema below can be used to create the SQL or Oracle tables needed for ODBC authentication in EFT Server.

The scripts can be found in the SQL Server or Oracle subdirectory of the EFT Server Data directory: C:\Documents and Settings\All Users\Application Data\GlobalSCAPE\EFT Server\SQL Server. On Windows 2008, Application Data files for all users are in a hidden folder named %systemroot%\ProgramData (e.g., C:\ProgramData\GlobalSCAPE\EFT Server Enterprise\Oracle and C:\ProgramData\GlobalSCAPE\EFT Server Enterprise\SQL Server).

SQL

The following SQL script creates the tables necessary to run on the Server.

if exists (select * from sysobjects where id = object_id(N'[dbo].[ftpserver_ids]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[ftpserver_ids]

GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[ftpserver_users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[ftpserver_users]

GO

CREATE TABLE [dbo].[ftpserver_ids] (

[User_ID] [int] NULL ,

[Group_ID] [int] NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[ftpserver_users] (

[ID] [int] IDENTITY (1, 1) NOT NULL ,

[name] [varchar] (50) NULL ,

[password] [varchar] (200) NULL ,

[description] [varchar] (200) NULL ,

[type] [int] NULL ,

[password_type] [int] NULL ,

[md_iter] [int] NULL ,

[otp_seed] [varchar] (16) NULL ,

[anonymous] [int] NULL ,

[anonymous_email] [int] NULL ,

[FullName] [varchar] (200) NULL ,

[Email] [varchar] (200) NULL ,

[Phone] [varchar] (200) NULL ,

[Pager] [varchar] (200) NULL ,

[Fax] [varchar] (200) NULL ,

[Comments] [varchar] (200) NULL ,

[Enabled] [int] NULL ,

[HomeDirectory] [varchar] (512) NULL ,

[SettingsLevel] [varchar] (200) NULL

) ON [PRIMARY]

GO

Oracle

The following schema is required for ODBC authentication in Oracle.

CREATE TABLE  "FTPSERVER_IDS"

   ("USER_ID" NUMBER(18,0),

                "GROUP_ID" NUMBER(18,0)

   )

/

CREATE TABLE  "FTPSERVER_USERS"

   ("ID" NUMBER(18,0) NOT NULL ENABLE,

                "NAME" VARCHAR2(4000),

                "PASSWORD" VARCHAR2(4000),

                "DESCRIPTION" VARCHAR2(4000),

                "TYPE" NUMBER(18,0),

                "PASSWORD_TYPE" NUMBER(18,0),

                "MD_ITER" NUMBER(18,0),

                "OTP_SEED" VARCHAR2(4000),

                "ANONYMOUS" NUMBER(18,0),

                "ANONYMOUS_EMAIL" NUMBER(18,0),

                "FULLNAME" VARCHAR2(4000),

                "EMAIL" VARCHAR2(4000),

                "PHONE" VARCHAR2(4000),

                "PAGER" VARCHAR2(4000),

                "FAX" VARCHAR2(4000),

                "COMMENTS" VARCHAR2(4000),

                "ENABLED" NUMBER(18,0),

                "HOMEDIRECTORY" VARCHAR2(4000),

                "SETTINGSLEVEL" VARCHAR2(4000),

                 CONSTRAINT "FTPSERVER_USERS_PK" PRIMARY KEY ("ID") ENABLE

   )

/

CREATE UNIQUE INDEX  "FTPSERVER_USERS_PK" ON  "FTPSERVER_USERS" ("ID")

/

CREATE SEQUENCE   "FTPSERVER_USERS_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 2 CACHE 20 NOORDER  NOCYCLE

/

CREATE OR REPLACE TRIGGER  "BI_FTPSERVER_USERS"

  BEFORE INSERT ON "FTPSERVER_USERS"               

  FOR EACH ROW

BEGIN

    SELECT "FTPSERVER_USERS_SEQ".NEXTVAL INTO :NEW.ID FROM DUAL;

END;

/

ALTER TRIGGER  "BI_FTPSERVER_USERS" ENABLE

/