Script for Creating Necessary ODBC Tables

The scripts used to create the SQL or Oracle tables needed for ODBC authentication in EFT can be found in the SQL Server or Oracle subdirectory of the EFT Data directory:

  • On Windows Server 2008, data files for all users are in a hidden folder named ProgramData (e.g., C:\ProgramData\Globalscape\EFT Server Enterprise (or \EFT Server) \Oracle and \SQL Server).

  • On Windows Server 2003, the scripts are installed by default in C:\Documents and Settings\All Users\Application Data\Globalscape\EFT Enterprise (or \EFT Server) \SQL Server and \Oracle.

 There is no need to create the scripts; they are copied below for information only.

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] [NVARCHAR] (50) NULL,

  [PASSWORD] [NVARCHAR] (200) NULL,

  [DESCRIPTION] [NVARCHAR] (200) NULL,

  [TYPE] [INT] NULL,

  [PASSWORD_TYPE] [INT] NULL,

  [MD_ITER] [INT] NULL,

  [OTP_SEED] [NVARCHAR] (16) NULL,

  [ANONYMOUS] [INT] NULL,

  [ANONYMOUS_EMAIL] [INT] NULL,

  [FULLNAME] [NVARCHAR] (200) NULL,

  [EMAIL] [NVARCHAR] (200) NULL,

  [PHONE] [NVARCHAR] (200) NULL,

  [PAGER] [NVARCHAR] (200) NULL,

  [FAX] [NVARCHAR] (200) NULL,

  [COMMENTS] [NVARCHAR] (200) NULL,

  [ENABLED] [INT] NULL,

  [HOMEDIRECTORY] [NVARCHAR] (512) NULL,

  [SETTINGSLEVEL] [NVARCHAR] (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" NVARCHAR2(50),

  "PASSWORD" NVARCHAR2(200),

  "DESCRIPTION" NVARCHAR2(200),

  "TYPE" NUMBER(18,0),

  "PASSWORD_TYPE" NUMBER(18,0),

  "MD_ITER" NUMBER(18,0),

  "OTP_SEED" NVARCHAR2(16),

  "ANONYMOUS" NUMBER(18,0),

  "ANONYMOUS_EMAIL" NUMBER(18,0),

  "FULLNAME" NVARCHAR2(200),

  "EMAIL" NVARCHAR2(200),

  "PHONE" NVARCHAR2(200),

  "PAGER" NVARCHAR2(200),

  "FAX" NVARCHAR2(200),

  "COMMENTS" NVARCHAR2(200),

  "ENABLED" NUMBER(18,0),

  "HOMEDIRECTORY" NVARCHAR2(512),

  "SETTINGSLEVEL" NVARCHAR2(200),

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

)

/

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

/