Notification is a novel evolution of our enhanced Server-Side Queue (system queue) support. Using the facilities of c-tree system queues, client applications can direct the FairCom Server to monitor a data file and place notification messages on a queue when changes are made to the file.

This chapter discusses both synchronous and asynchronous processing:

• Synchronous File Notification - The file notification feature is a memory-based queue that is synchronously executed.
• Asynchronous Record Update Notifications - Record Update Notification callback functions allow applications to externally process records as they are modified (added, updated, and deleted) and are asynchronously processed and persisted.

Synchronous File Notification

File notification gives your c-tree application the ability to respond to a variety of events. With the proper handling of these events, you can enable such robust features as syncing data between servers in a replicated fashion, or triggering security and auditing actions based upon a specified file event.

Each notification message includes the following details:

• The type of operation (add, delete, or update)
• For a transaction-controlled file, the transaction number in which the change occurred
• The record offset of the modified record.

The following optional information can also be included in notification messages:

• The unique key value involved in the operation. (An update may return both old and new key values.)
• The record image involved in the operation. (An update may return both old and new record images.)
• The node name of the client that performed the operation.

Asynchronous Record Update Notifications

Many c-tree applications store documents and other non-trivial data types. c-tree allows nearly any type of data to be stored, including text, XML, JSON, PDFs, word processing documents, and email. Frequently, these types of data require alternate indexing algorithms compared to how standard data types are indexed with basic b-tree algorithms. What is needed is a method to hand off these types of record updates to alternative handling.

In V11 and later, a set of Record Update Notification callback functions allows applications to externally process records as they are added or updated within a c-tree database.

These callback functions are called when records in the file are modified (added, updated, and deleted) and are asynchronously processed and persisted. Contrast this with the FairCom DB notification feature which is a memory-based queue. While standard notification can trigger callback handling, this is done in a synchronous manner which can impact up-front application performance. The record update notification callback also handles change events, however, its intent is to allow user-defined actions to occur when a record in a particular table is modified, in a deferred asynchronous manner. With this deferred handling, update events are deferred for background processing while maintaining application responsiveness.

Implementing Record Update Notifications

Function prototypes to handle record updates are located in the following c-tree source module:

ctree\source\ctrucbdll.c

To create a functional shared library, build the module using the FairCom DB build utility mtmake and choose the FairCom DB CTUSER model option. ctuser.dll (or libctuser.so) is generated and this module is then copied into your server's binary folder location.

The callback feature defines three functions:

1. File open callback function called when a connection opens a file,
2. File close callback called when a connection closes or deletes a file, and
3. Record update callback called when an ISAM-level record add, update, or delete operation is performed on a file.

The ctRecordUpdateCallbackControl() function, described below, is used to add and delete callback function definitions. A file can have more than one callback function definition. Each callback function definition is identified by its name, which is a case-sensitive ASCII string.

Each callback definition consists of the following attributes:

• Callback physical .DLL name
• Callback logical identifier name
• Callback function names for file open, file close, and record update
• Parameter string (optional)
• Callback time: this is the time when the record update callback is called. Supported options are:
  1. called during a record update
  2. called when a transaction commits (available only for a transaction-controlled file; for a non-transaction-controlled file, this option causes the callback to be called during a record update)
  3. written to transaction log or a memory queue and processed by a background thread after a transaction commits or the operation completes
  4. written to transaction log or a memory queue and no further action taken: the application is responsible for processing any queued entries

Restrictions

1. FairCom DB allows a callback function definition to be added when the data file is open in shared mode. In standalone mode, adding a callback function requires the data file to be open in exclusive mode.
2. A callback function definition can only be deleted when the data file is open in exclusive mode.

Management API Function

• ctRecordUpdateCallbackControl() - Callback definition management API

See also:

• Update Callback Specifications
• Option to specify external library name in platform-independent format

Update Callback Specifications

File Open and File Close Callbacks

The file open and close callback functions are intended to allow an application to manage resources that will be used by the record update callback function. Typically an application will allocate resources in the file open function and will free the resources in the file close function. For example, initializing an alternative indexing environment on open, and freeing that environment context at close.

The file open callback function is called when the following events occur:

1. When a connection opens a file that has a callback definition, the callback function is called at the end of the file open operation. If it is an ISAM level file open call such as OPNIFIL() or OPNRFIL(), the callback is called after the data file and its associated indexes have been successfully opened.
2. When a connection adds a callback definition to the file, the callback function is called after the callback definition resource in the data file has been successfully updated.

A callback that is added to a data file that is open in shared mode becomes visible to other connections that already have the file open on their next record add, update, or delete operation. In that situation, the other connection finds that the new callback exists and it calls the file open callback function for the newly-added callback before calling the record update callback function.

The file close callback function is called when the following events occur:

1. When a connection closes or deletes the file, the callback function is called right before the data file is closed, so the connection still has the data file and its associated index files open.
2. When a connection deletes a callback definition from the file, the callback function is called after the callback definition resource in the data file has been successfully updated.

The file open and close functions have the following prototypes:

NINT rucbOpenFileCallback(pRUCBF prucbf);

NINT rucbCloseFileCallback(pRUCBF prucbf);

The record update callback function parameter structure, RUCBF, has the following definition:

/* record update callback parameters */

typedef struct rucbf {

    pTEXT   datnam;    /* Data file name.                          */

    pTEXT   params;    /* Optional callback parameter string.      */

    NINT    calltm;    /* Current context for this call.           */

    FILNO   datno;     /* User file number of data file.           */

    pVOID   psession;  /* User-defined connection-level pointer.   */

    pVOID   ptable;    /* User-defined table-level pointer.        */

} RUCBF, *pRUCBF;
 

The two state pointers, psession and ptable, are available for use by the user-defined callback function code.

The psession state pointer is shared by all callback functions for all files in a given connection. It is appropriate for storing connection-wide state information. One way to use this pointer is to allocate memory and set psession to point to that memory on the first call to the file open callback function in a connection. The application can also maintain a connection-wide reference count, and when the file close function finds that the reference count is zero, it can free the memory.

The ptable state pointer is specific to a particular callback function for a particular file. It is appropriate for storing table-level state information.

Record Update Callback

When a user adds, updates, or deletes a record at the ISAM level, the callback is called at one of the following events:

1. If the callback event is set to RUCBonrecupd, or if the callback time is set to RUCBontrancmt and the file is not under transaction control, the callback is called right after the record and its keys have been added, updated, or deleted.
2. If the callback event is set to RUCBontrancmt and the file is under transaction control, the callback is called when the transaction that modified the record is committing.
3. if the callback event is set to RUCBonqueuethrd, the callback is called after the transaction commits, when the deferred index thread has read the entry for that record modification operation from the transaction logs.
4. if the callback event is set to RUCBonqueueapp, the callback is never called. It is up to the application to read the entry for that record modification operation from the transaction logs and take the appropriate action.

The record update callback function has the following prototype:

NINT rucbRecordUpdateCallback(pRUCBF prucbf,pDFRKY pdfrky);

The RUCBF structure is the same as mentioned above and the additional DFRKY structure contains the information about the record add, update, or delete operation and has the following definition:

typedef struct dfrky {

    COUNT   opcode;     /* deferred index operation code            */

    TEXT    status1;    /* status bit field #1                      */

    TEXT    status2;    /* status bit field #2                      */

    ULONG   dfrkctr;    /* deferred index create counter            */

    LONG    fid[3];     /* unique file ID of data file              */

    LONG    oreclen;    /* size of old record image (rewrite)       */

    LONG    reclen;     /* size of record image                     */

    LONG    datnamlen;  /* length of data file name plus null       */

    LONG8   orecbyt;    /* old record offset (rewrite)              */

    LONG8   recbyt;     /* record offset                            */

    TEXT    varinf[1];  /* variable length information:

                        ** For ctDFR_ADDKEY and ctDFR_DELKEY:

                        **   null-terminated data file name

                        **   old record image (rewrite)

                        **   record image

                        ** For ctDFR_RWTKEY and ctDFR_RWTPKEY:

                        **   null-terminated data file name

                        **   old record image

                        **   record image

                        ** For ctDFR_LOADKEY:

                        **   null-terminated data file name

                        **   null-terminated index file name

                        */

} DFRKY, ctMEM **ppDFRKY;
 

Your record update callback implementation handles one of following four update operations provided in the opcode from the DFRKY structure:

• ctDFR_ADDKEY - A record was added
• ctDFR_RWTKEY - A record was updated
• ctDFR_RWTPKEY - A record was partially updated
• ctDFR_DELKEY - A record was deleted

You'll find this framework available in the ctrucbdll.c module.

First and Last Operations

An application that uses the record update callback function feature needs to know what operations are associated with a particular transaction and what are the first and last operations for that transaction. In V11.5 and later FairCom Server provides this information to the record update callback function:

• The first operation in a transaction has the ctDFR_TRANFRS bit set in the status1 field of the pdfrky parameter that is passed to the record update callback function.

The last operation in a transaction has the ctDFR_TRANLST bit set in the status1 field of the pdfrky parameter that is passed to the record update callback function. So, if a transaction has only one DFRKEY entry, that entry will have both the ctDFR_TRANFRS bit and the ctDFR_TRANLST bit set. If a transaction has more than one DFRKEY entry, the first entry will have the ctDFR_TRANFRS bit set and the last entry will have the ctDFR_TRANLST bit set.

• An optional parameter of type pRUCBSTT is passed to the record update callback function. This structure has the following definition:

typedef struct rucbstt_t {

    LONG    verson; /* structure version   */

    LONG    avail;  /* padding- available for use  */

    LONG8   tranno; /* transaction number for the operation */

} RUCBSTT, *pRUCBSTT;

Note: For FairCom Server to pass this third parameter to your record update callback function, your record update callback DLL or shared library must export the function rucbCheckVersionCallback(), which has the following function prototype:

NINT rucbCheckVersionCallback(pRUCBACB prucbacb,pNINT pversion);

prucbacb is the record update callback definition in the format of the record update callback add operation structure, RUCBACB. The function can choose to examine the record update callback definition to decide which version of the RUCB API it supports. For example, it can choose based on the callback name (prucbacb->cbname) or the name of the record update callback function (prucbacb->fncnames[2]).

The function should set pversion to the version of the record update callback API your DLL uses and return zero to indicate success. Supported versions are:

• a) Version 1 of record update callback API. Your record update callback function must conform to the following prototype:
  NINT rucbRecordUpdateCallback (pRUCBF prucbf,pRUCBO prucbo);
• b) Version 2 of record update callback API. Your record update callback function must conform to the following prototype:
  NINT rucbRecordUpdateCallback (pRUCBF prucbf,pRUCBO prucbo,pRUCBSTT prucbstt);

If your record update callback DLL does not export a function named rucbCheckVersionCallback(), FairCom Server uses version 1 of the record callback API.

Option to specify external library name in platform-independent format

Features such as the record update callback function use an external shared library or DLL that c-tree loads to support user-defined callback functions. The library name is stored in a resource in the file. Because the name was platform-dependent (starting with lib and ending in .so on Unix and ending with .dll on Windows for example), a data file containing a record callback library reference could not be easily used on both Unix and Windows systems.

In V11.5 and later, c-tree supports specifying the DLL or shared library name in a way that causes c-tree to automatically convert the library name to the standard format for the platform on which it is running.

To use this feature, specify ^ as the first character of the library name. For example, specifying the name ^mycallback causes c-tree to convert the name to libmycallback.so on Unix systems (libmycallback.sl on HP/UX and libmycallback.dylib on MacOSX), and to mycallback.dll on Windows systems.

When the library name starts with ^, the conversion is ALWAYS applied. For example on Linux, the library name ^liberty is converted to libliberty.so.

This new feature can also be used in the following situations:

1. when using a conditional index external library
2. when using a data record filter external library
3. when using a deferred index external library

Using File Notification

To use the notification process the user must:

1. Open a system queue by calling ctSysQueueOpen(), which returns a queue handle.
2. Call ctNotify() to establish the notification process.
3. Call ctSysQueueRead() to read notification messages from the queue.
4. When notifications are no longer needed, call ctNotify() to stop notifications or ctSysQueueClose() to close the notification queue.

Receiving Notifications for Actions on a File

After establishing notification on a c-tree data file, use the ctSysQueueRead() function to read notification messages from a system queue. Non-transaction ISAM updates are immediately processed and placed into the system queue by the notify system. Transaction (including pre-image) updates are not processed until they are committed. The following sections discuss the format of a notification message and how to read notification messages returned by ctSysQueueRead().

Notification Queue Message Format

A notification message always starts with a fixed portion followed by an optional variable-length region.

The fixed portion is made by the ctNOTBLK structure defined in ctport.h:

typedef struct notblk {

   ULONG action;         /* actual opcode                    */

   LONG  actor;          /* thread ID                        */

   LONG  tranhw;         /* transaction # HW                 */

   LONG  tranlw;         /* transaction # LW                 */

   LONG  opcode;         /* requested opcodes                */

   LONG  objhandle;      /* requested (user) handle          */

   LONG  idxmemno;       /* index member number              */

   ULONG contents;       /* actual contents bit map          */

   ULONG controls;       /* requested controls bit map       */

   LONG  datahw;         /* data HW (eg rec pos)             */

   LONG  datalw;         /* data LW                          */

   LONG  auxdhw;         /* auxilary info HW (eg old pos)    */

   LONG  auxdlw;         /* auxilary info LW                 */

   ULONG varlen;         /* remaining length (if any)        */

   } ctNOTBLK, * pctNOTBLK;

The contents member determines what is in the variable-length region and should be the same used in the ctNotify() call.

The variable-length portion may contain the following items, depending on the options specified in the contents parameter to ctNotify():

1. key values (present if ctNT_CON_UNQKEY OR-ed in contents),
2. actor node name (present if ctNT_CON_NODNAM OR-ed in contents),
3. full record image (present if ctNT_CON_RECBUF OR-ed in contents)

These items always appear in this order, even if not all are specified to be returned.

Fixed Portion of Notification Queue Message

The members of the fixed portion of the notification queue message are set as follows:

The action member of the ctNOTBLK structure contains the particular opcode of the monitored event as follows:

The actor member contains the thread ID of the user/client that performed the operation.

The tranhw and tranlw members contain the transaction number in which the operation occurred: tranhw is the high-word, tranlw the low-word

The opcode member is the opcode originally requested in the ctNotify() call. This may be different from the action member as the action is actual event that occurred, while opcode is the combination of events monitored. For instance if you call ctNotify(ctNT_ISMUPD | ctNT_PARTIAL,...) the action member can be ctNT_DELREC while the opcode is ctNT_ISMUPD | ctNT_PARTIAL.

The objhandle member contains the data file number on which the event occurred or, in case the notification request is for an index, the host index file number on which the event occurred.

The idxmemno member contains the index member number on which the event occurred. The actual index file number is given by objhandle + idxmemno.

The contents member is the actual contents of the variable-length part. This may be different from the contents parameter of the ctNotify() call. For example, when the action performed is ctNT_DELREC no record is returned in the optional full record image part even if it was requested by the ctNotify() call, and the contents value reflects this situation by not having ctNT_CON_RECBUF OR-ed in.

The controls member is reserved for future use.

The datahw and datalw members are the high word and low word of the record offset in the data file after modification (add, rewrite, delete).

The auxdhw and auxdlw members, in case of a record update, contain the original record offset in the data file (high word and low word) whether or not the record has been moved.

The varlen member of the ctNOTBLK structure indicates the length of the variable-length message portion.

Optional Key Values

If the contents member of the ctNOTBLK contains ctNT_CON_UNQKEY, the notification message contains in the variable portion, just after the ctNOTBLK, information about the unique key generated. The information is stored in this way:

FILNO rkeyno;

COUNT keylen;

TEXT  key[]; /* buffer of keylen bytes */

If action is ctNT_RWTREC there is an additional field

TEXT  oldkey[]; /* buffer of keylen bytes */

• rkeyno is the index number, starting from 1 relative to the data file (1 is the first index, 2 the second index...). It is set to 0 if there is no unique index.
• keylen is the length of the key.
• key is a buffer of keylen bytes containing the key stored now in the index file.
• oldkey is a buffer of keylen bytes containing the old key stored in the index file before an update, whether or not the key changed.

Optional Actor Node Name

If the contents member of the ctNOTBLK contains ctNT_CON_NODNAM, the 32-byte node name appears next in the notification message (immediately following the ctNOTBLK structure or the key values if key values were requested).

TEXT  nodnam[32];

• nodnam is a null terminated string containing the node name of the actor that modified the file causing the notification triggering.

Optional Full Record Image

If the full record image is requested and the operation is not a delete, the record length and record contents appear next in the notification message:

LONG reclen;   

TEXT recbuf[];  /* buffer of reclen bytes */

• reclen is the record length
• recbuf is a buffer reclen bytes long containing the record that is currently contained in the data file.

Reading Notification Messages

Due to the variable length nature of the notification message, it is necessary to provide the ctSysQueueRead() function with a buffer large enough to contain the entire message; otherwise, the ctSysQueueRead() call will fail with error TQUE_ERR (638) and the message is not read.

When not requesting any optional values (contents set to 0 in ctNotify() call), the notification message length is fixed and the notification message size is sizeof(ctNOTBLK).

When requesting optional information, the message length size can be either retrieved by calling ctSysQueueMlen() or evaluated by adding to the fixed message length the lengths of each option requested as described in the following table.

For instance, suppose that contents is ctNT_CON_UNQKEY | ctNT_CON_RECBUF and the key length is not known and the record length is fixed to 40, then the message length will be:

sizeof(ctNOTBLK) + 2 * sizeof(COUNT) + 2 * MAXLEN +  sizeof(LONG) + 40

If the message length is known or can be estimated, a notification message can be read by passing the address of a buffer, whose size is greater or equal to the message size, the buffer size and the timeout value to ctSysQueueRead(),. For example:

NINT     qhandle;  /* Set by call to ctSysQueueOpen */

NINT     rc;

pTEXT    buffer;

buffer = (pTEXT) malloc (msglen);

/* Read next available queue message with 5 second timeout. */

rc = ctSysQueueRead(qhandle,buffer,msglen,5000);

If the message length is not known, it is necessary to determine the size with a call to ctSysQueueMlen(). This function takes the queue handle and an optional timeout, so it can be used to wait until the next message is available in the queue and to determine the size of the message. After determining the message size, allocate a sufficiently-sized buffer and pass its address and size to ctSysQueueRead(). For example:

NINT      qhandle;  /* Set by call to ctSysQueueOpen */

NINT      rc;

NINT      msglen;

pTEXT     pbuffer;

/* Read size of next available queue message with 5 second timeout. */

msglen = ctSysQueueMlen(qhandle,5000);

/* Allocate buffer to hold queue message. */

pbuffer = (pTEXT)malloc(msglen);

/* Read next available queue message. */

rc = ctSysQueueRead(qhandle,pbuffer,msglen,0);

The following code demonstrates how to extract information from the message read from a notification queue.

Notification Queue Example

COUNT ProcessQueueMessage (pTEXT buff) /* buff is a pointer to the message */

{

   /* cast the buffer to a ctNOTBLK structure */

   /* in order to make easier to extract the  */

   /* information in the fixed portion        */

   pctNOTBLK pnotblk = (pctNOTBLK)buff;

   /* set info to point at the beginning of the */

   /* variable-length part                      */

   pTEXT info = buff + sizeof(ctNOTBLK);

   ctrt_printf("action actor tranlw opcode objhandle contents

                controls datalw varlen\n");

   ctrt_printf("%8d %5d %6d %6d %9d %8d %8d %6d %6d\n",

      pnotblk->action, pnotblk->actor, pnotblk->tranlw, pnotblk->opcode,

      pnotblk->objhandle, pnotblk->contents, pnotblk->controls,

      pnotblk->datalw, pnotblk->varlen);

   /* if the action is ctNT_CLSOBJ return */

   if (pnotblk->action == ctNT_CLSOBJ)

      return (1);

   /* if contents contains ctNT_CON_UNQKEY there */

   /* is unique key information to extract     */

   if (pnotblk->contents & ctNT_CON_UNQKEY)

   {

      FILNO rkeyno;

      COUNT keylen;

      /* extract relative index number */

      cpybuf(&rkeyno, info, 2);

      /* step over the index number */

      info += 2;

      /* extract the key length */

      cpybuf(&keylen, info, 2);

      /* step over the key length */

      info += 2;

      ctrt_printf("Unique Key:\n");

      ctrt_printf("\tIndex number (relative) %d\n",rkeyno);

      ctrt_printf("\tkeylen                  %d\n",keylen);

      /* your key value handling here                 */

      /* the key starts at the memory pointed by info */

      /* and its length is keylen                     */

      /* skip over the key */

      info += keylen;

      /* if the action is ctNT_RWTREC there is also   */

      /* the old key value to retrieve                */

      if (pnotblk->action == ctNT_RWTREC)

      {

         /* your key handling code here */

         /* skip over the key */

         info += keylen;

      }

   }

   else

   {

      ctrt_printf("Unique Key [NOT REQUESTED]\n");

   }

   /* if contents contains ctNT_CON_NODNAM there */

   /* is a node name information to extract      */

   if (pnotblk->contents & ctNT_CON_NODNAM)

   {

      /* print the node name that is pointed */

      /* by info                             */

      ctrt_printf("Node Name: %s\n",info);

      /* skip over the nodename */

      info += 32;

   }

   else

   {

      ctrt_printf("Node Name [NOT REQUESTED]\n");

   }

   /* if contents contains ctNT_CON_RECBUF  */

   /* there is a record image to extract    */

   if (pnotblk->contents & ctNT_CON_RECBUF)

   {

      LONG notrln;

      /* extract the record length */

      cpybuf(&notrln, info, 4);

      /* skip over */

      info += 4;

      ctrt_printf("Record Buffer Length: %d\n",notrln);

      /* your record buffer handling here */

      /* the record buffer starts at info */

      /* and is notrln bytes long         */

      info += notrln;

   }

   else

   {

      ctrt_printf("Record Buffer [NOT REQUESTED]\n");

   }

   /* sanity check to verify that we reached */

   /* the end of the message                 */

   if ((buff + sizeof(ctNOTBLK) + pnotblk->varlen) != info)

   {

      ctrt_printf("Message parsing problem\n");

   }

   return (0);

}

Disabling Notification for Actions on a File

Notification for actions on a file is terminated by a physical file close or an explicit call of the form:

ctNotify(opcode,objhandle,qhandle,contents,controls|ctNT_CTL_STOP);

where all the arguments must agree with a previous call to notify except that the controls parameter must include the ctNT_CTL_STOP flag.

Notification Callbacks

Instead of passing the notification information to a queue, use the FairCom Server SDK to make calls to ctCallback() to associate a callback function to a notification.

ctCallback() is used in the same manner as ctNotify() except that the third parameter is a pointer to a callback function instead of a queue handle:

NINT ctCallback(NINT opcode, NINT objhandle, ctCallbackPtr cbptr,

                NINT contents, NINT controls);

A notification setup with ctCallback() causes the function pointed to by cbptr to be called (instead of a message written to a queue). This capability is only available with the FairCom Server SDK, and ctCallback() can only be called from code compiled into the server itself (using the FairCom Server SDK).

The prototype for the callback function pointer is:

typedef NINT (*ctCallbackPtr)(pVOID msg, NINT msglen, pVOID aux,

                              NINT auxlen);

The callback function returns NO_ERROR (0) on success and a non-zero value on failure. Parameter msg and the optional parameter aux are input parameters. If both msg and aux are passed in, then they should be conceptually pasted together to form one long message.

It is important to note that as currently coded, the target file’s header semaphore is held while the callback function is executed. Therefore the callback function cannot introduce pauses or delays or attempt to lock the header of the target file.

Partitioned Files

The FairCom Database Engine supports a unique feature known as Partitioned Files. A partitioned file logically appears to be one file (or more accurately one data file and its associated index files), but is actually a set of files whose contents are partitioned by the value of the partition key. Both the data files and index files are partitioned. This permits data with a defined range of values for the partition key to be rapidly purged or archived (instead of having to delete record-by-record each record within this range).

Overview

Highlights

• Maintain data in separate c-tree data/index files, while enjoying access from a single host file.
• Rapidly purge or archive individual member files.
• Partitioned files are easily implemented at the ISAM and FairCom DB API development levels with a single additional API call.
• Easily partition an existing linked file with SQL.
• Use all standard FairCom DB data searches on the entire file or directly on a member file.

Implementation

Implementing partitioned files requires a FairCom DB library with partition capabilities and Extended files with the ctPARTAUTO extended file mode. To implement partitioned files:

1. Activate the partitioned files capabilities at compile time with the ctPARTITION define. This define is on by default, provided the ctHUGEFILE, RESOURCE, CTS_ISAM, and ctCONDIDX defines are in place. Check ctree.mak/ctoptn.h and ctopt2.h for the current settings.
2. To create a partitioned file (using the default partition naming, partition rule, and maximum number of partitions), simply create an Extended file with ctPARTAUTO in the x8mode parameter of the Xtd8 extended file creation block. Partitioned files do not have to be HUGE unless the logical file size will exceed the 2GB/4GB limit, but they do require the extended header, i.e., they are Extended files.

Partition Naming

The partition file name is the base file name with the 3-digit raw partition number as the file extension. Only automatic naming is available at this time.

Maximum Partition Number vs File Size

By default, 16 bits of the 64-bit record offset are used to reference the raw partition number, allowing each partitioned file to support up to 65535 member files over its lifetime. This can be adjusted at create time using the callparm parameter of the extended file creation block, where a value of 0 defaults to 16 bits, values less than 4 bits default to 4 bits (maximum 15 member files), and 32 bits is the maximum value (4,294,967,295 member files). The number of bits determines the total number of raw partitions for the entire life of the host file. This is not the number of partitions active at one time. Raw partitions are not reassigned.

Rules

In V11 and later, user-defined conditional expressions can be used for partition rules, as described in the next section.

Legacy Versions

The partition key can be set when the file is created using the prtkey parameter of the extended file creation block. This value defaults to 0, indicating the first key associated with the data file. Set this value to the relative key number for the desired index if the default is not appropriate for your application.

The default rule, developed for testing purposes only, uses a simple algorithm to divide added records into partitions based on the first byte of the selected key as a test of the partition capability. See the function kprawno() in ctpart.c for the sample algorithm.

Cautions and Restrictions

See "Partition Ordering and Range Query" in Raw Partition Numbers for information on number generating rules.

User-Defined Partitioned File Conditional Expressions

In FairCom DB V11 and later, user-defined conditional expressions can be used for partition rules.

User-Defined Conditional Expressions for Easy Partitioned File Creation

Partition files are easily created with user-defined conditional expressions. Partitioned tables are fully supported via both SQL and FairCom DB ISAM applications.

A partitioned file logically appears to be one data file (and its associated index files). It is actually a set of files whose contents are physically partitioned by the value of a partition key.

Partitioned files are intended for applications requiring fast purging and archiving of large amounts of data at once. As both data and index files are partitioned, this permits data within a defined range of partition key values to be rapidly purged or archived (rather than deleting each record within this range).

Consider rapidly acquired log data. Frequently, this data has a lifespan of days to months. After this time it is usually purged or archived for permanent storage. This can take time with large amounts of new data that have since been acquired. By avoiding lengthy key searches for expired data over the entire data set, it is much better performing to have all related data in a single silo and operate on it with a single operation. Partitioned files give you this powerful ability.

For background information regarding partitioned files, refer to Partitioned Files in the FairCom DB Programmer's Reference and Function Reference Guide.

Conditional Expressions and Partition Rules

Conditional expressions make it easy to build a partition rule. A partition rule is a conditional expression ultimately evaluating to a number. That number associates data with a specific partition within the file.

FairCom DB expression parsing can now evaluate an expression into a numeric representation to be used for the partition rule. Many built-in conditional expression functions exist for flexible rule generation. Time and date based functions, numeric functions, and string manipulation functions are all available. FairCom DB expression syntax can even reference complex functions via an external shared library (DLL) in calculating partition numbers.

If the table has an embedded DODA resource and a conditional expression references schema segments, an expression can refer to index key fields explicitly by DODA name. Together with field names, complex expression rules can be crafted.

Prior to FairCom DB V11, partitioned files required these rules to be hard-coded at compile time (specified in ctpart.c) and did not allow run-time flexibility in adapting rules for unique deployed environments.

By combining existing FairCom DB advanced support for conditional expressions with our high performing partitioned files, applications can now create exact expression rules directly when creating partitioned files. In addition, existing partitioned files can have their partition rule changed and subsequently rebuilt based on a new rule logic allowing applications to grow and adapt with their customer needs.

Partitioned files are supported directly from the FairCom DB ISAM API, FairCom DB API and FairCom DB SQL.

Performance

Additional processing of advanced expression parsing can potentially impact performance to a small degree. Should performance become an issue, you might consider an external DLL to efficiently evaluate partition rules directly.

Partitioned Files in FairCom DB SQL

In FairCom DB SQL, a table is partitioned when a partition index is created. To create a partition index with conditional expressions for partitioning, use the following syntax:

CTREATE INDEX .... STORAGE_ATTRIBUTES 'partition=<expression>'

To create a partition index forcing hard-coded rules (pre-V11), the following is supported:

CTREATE INDEX .... STORAGE_ATTRIBUTES 'partition'

To change a partition rule on an existing partitioned file, call ALTER INDEX with your new rule in the STORAGE_ATTRIBUTES clause.

Example

This script demonstrates partition file rules in SQL. It creates a table, prtest, with an index on the integer field f7. The storage_attributes clause define a partition rule in which each record is stored in a partition number equal to the value of field f7 plus 1:

create table prtest (f1 integer, f2 char(50), f3 char (50), f4 char(50), f5 timestamp, f6 varchar(50), f7 integer, f8 time);

create index pridx on prtest (f7) storage_attributes 'partition=f7+1';

With a rich assortment of conditional expression functions available, much more complex rules can be created.

For example, a table containing a field "invoice_date" and requiring monthly partitions can be created with this simple expression:

month(invoice_date)

For syntax details, refer to Conditional Expression Parser topics in the FairCom DB Programmer's Reference and Function Reference Guide.

Example

Using functions to convert Unix time_t fields to c-tree Date and Time types (TIMET2CTDATE) in a partitioned file expression to partition into months since Jan, 2010:

CREATE TABLE unixtest (name CHAR(10), u_date INTEGER)

    or

CREATE TABLE unixtest (name CHAR(10), u_date BIGINT)

CREATE INDEX unixtest_date_idx ON unixtest (u_date) STORAGE_ATTRIBUTES 'partition=( ( YEAR( TIMET2CTDATE( u_date) ) -2010) * 12) + MONTH ( TIMET2CTDATE(u_date))'

    Date String   Unix Date Partition created

    Mon, 23 Feb 2015 11:01:32 GMT 1424689292 62

    Sat, 23 Jan 2016 11:01:32 GMT 1453546892 73

    Tue, 23 Feb 2016 11:01:32 GMT 1456225292 74

    Wed, 23 Mar 2016 11:01:32 GMT 1458730892 75

For syntax details, for the TIMET2* functions, see C Language Equivalents.

FairCom DB API Partition File API Support

Partitioned file support is extended to the FairCom DB API API. While creating a file it is possible to call ctdbSetTablePartitionIndexNbr to set the partition index, ctdbSetTablePartitionNumberBits to set the number of bits reserved for partition numbers, and ctdbSetTablePartitionRule to set the partition rule.

On existing tables, after calling the above function you then call ctdbAlterTable forcing an CTDB_ALTER_FULL action.

ctdbSetTablePartitionRule

This FairCom DB API function sets partition rules:

ctdbEXPORT CTDBRET ctdbDECL ctdbSetTablePartitionRule(CTHANDLE Handle, pTEXT expr);

• expr - The expression, expr, will be evaluated against the key for the partition index. It must evaluate to an integer.

The partition rule uses standard c-tree expression syntax.

FairCom DB ISAM Usage

Partitioned files are created with standard c-tree APIs. A partitioning conditional expression is defined with the PTADMIN Partition Administration API call and the ptADMINrule parameter. Specify an extended (Xtd8) ctPARTAUTO mode in the x8mode parameter of an extended file creation block. While partitioned files require an extended (Xtd8) header they do not have to be HUGE files unless the logical file size will exceed the 2GB/4GB limit.

A partition key is set when the file is created using the prtkey parameter of the extended file creation block. The default is 0 (the first key associated with the data file). Set this value to the relative key number for the desired index if the default is not appropriate for your application.

A partition file name is the base data file name with a 3-digit raw partition number as the file extension. Only automatic naming is available in this mode.

Note: Alternative file naming is possible with custom modifications to the ctpart.c module and recompiling your FairCom Database Engine.

By default, 16 bits of the 64-bit record offset reference the raw partition number, allowing each partitioned file to support up to 65,535 member files over its lifetime, thus also somewhat limiting overall file size (i.e., the more bits used for partition numbering, the smaller the overall size of the file can be). The maximum value is 32-bits (4,294,967,295 member files). This numbering vs. size tradeoff is set at file create time using the callparm parameter of the extended file creation block (Xtd8), where a value of 0 defaults to 16-bits. Values less than 4-bits default to 4-bits (maximum 15 member files).

Note: Default partition naming, partition rules, and maximum number of partitions are used by default when not defined by the Xtd8 extended parameter block and the PTADMIN API call.

Operation

For now, we make two major assumptions:

1. The FairCom Database Engine assigns data records to a partition by applying the data file’s partition rule to the partition key; and
2. Partitions are assigned in increasing order of the partition key values. That is, if KeyValue2 > KeyValue1, then the partition assigned to KeyValue2 will be the same as or after the partition assigned to KeyValue1.

Neither of these two assumptions is absolutely critical, but the second assumption does permit much more efficient key searches when the relationship between key values and partitions is well ordered.

Once the host file is created, the operation of partitioned files should be invisible to the application. The functions that add, update, and delete records are the same for both partitioned and non-partitioned files. However, functions requiring a record offset must use the ctSETHGH() and ctGETHGH() functions, even if the partitioned files are not HUGE to ensure the high-order bytes are included, as described below.

Raw Partition Numbers

The raw partition numbers must be 1 or greater. When passing a file position that includes a partition number to a routine, the partition number is encoded in the high-order bits of the high-order word. Ordinarily, the application will only get such information from a call to CurrentFileOffset() followed by a call to ctGETHGH().

Partition numbers are stored in the higher-order bytes of the 64-bit record offset. This allows the ISAM API calls to remain unchanged. Simply change the parameters of your file creation call, and your application is ready to use partitioned files. For this reason, functions requiring a record offset must use the ctSETHGH() and ctGETHGH() functions, even if the partitioned files are not HUGE to ensure these high-order bytes are included.

Partition Ordering and Range Query

Partitions are assigned in increasing order of the partition key values. That is, if KeyValue2 > KeyValue1, then the partition assigned to KeyValue2 will be the same as or after the partition assigned to KeyValue1.

We allow any user-defined expression that evaluates to a numeric value to be used as a partition rule. However, our partition search logic requires that a partition rule assigns partitions in increasing order of the partition key values. That is, the partition function is required to be a monotonically increasing function: for any two partition key values A and B, if A > B then the partition rule must output values p(A) and p(B) such that p(A) > p(B).

We don’t currently check that a user-defined partition rule meets the monotonically increasing property. If a rule is supplied that doesn't have this property, partition queries will return incorrect results such as not finding key values that exist in the table.One example of a function that does not meet this requirement is partitionRule = (partitionKeyValue MOD 12). Note that the values of this function increase then decrease again rather than always increasing as the partition key value increases.

It is up to the developer to be aware of this requirement and to only use partition rules that meet this requirement.

Partition Number Base

Use the PartitionAdmin() function to increase or decrease the lowest permitted partition number, called the “base” partition number. The system enforces an absolute lowest value for the base of one (1), but PartitionAdmin() can be used to change the base as long as it is one or greater. However, when changing this base value, PartitionAdmin() ensures no inconsistencies will arise. For example, one cannot increase the base value if it would eliminate any active or archived partitions (however it can eliminate purged partitions).

Raw Partition Numbers

The raw partition numbers must be 1 or greater. When passing a file position that includes a partition number to a routine, the partition number is encoded in the high-order bits of the high-order word. Ordinarily, the application will only get such information from a call to CurrentFileOffset() followed by a call to ctGETHGH().

Partition numbers are stored in the higher-order bytes of the 64-bit record offset. This allows the ISAM API calls to remain unchanged. Simply change the parameters of your file creation call, and your application is ready to use partitioned files. For this reason, functions requiring a record offset must use the ctSETHGH() and ctGETHGH() functions, even if the partitioned files are not HUGE to ensure these high-order bytes are included.

Partition Ordering and Range Query

Partitions are assigned in increasing order of the partition key values. That is, if KeyValue2 > KeyValue1, then the partition assigned to KeyValue2 will be the same as or after the partition assigned to KeyValue1.

We allow any user-defined expression that evaluates to a numeric value to be used as a partition rule. However, our partition search logic requires that a partition rule assigns partitions in increasing order of the partition key values. That is, the partition function is required to be a monotonically increasing function: for any two partition key values A and B, if A > B then the partition rule must output values p(A) and p(B) such that p(A) > p(B).

We don’t currently check that a user-defined partition rule meets the monotonically increasing property. If a rule is supplied that doesn't have this property, partition queries will return incorrect results such as not finding key values that exist in the table.One example of a function that does not meet this requirement is partitionRule = (partitionKeyValue MOD 12). Note that the values of this function increase then decrease again rather than always increasing as the partition key value increases.

It is up to the developer to be aware of this requirement and to only use partition rules that meet this requirement.

Partition Number Base

Use the PartitionAdmin() function to increase or decrease the lowest permitted partition number, called the “base” partition number. The system enforces an absolute lowest value for the base of one (1), but PartitionAdmin() can be used to change the base as long as it is one or greater. However, when changing this base value, PartitionAdmin() ensures no inconsistencies will arise. For example, one cannot increase the base value if it would eliminate any active or archived partitions (however it can eliminate purged partitions).

Unique Keys

Unique indexes are managed using the host index files to maintain values across all partitions for unique non-partition keys. The values in the host index use their partition number (plus 1) as their associated values. The partition index contains the key value with the associated record position within the data partition. This permits the partitioned files to be treated as a self-contained set of ISAM files, and still enforces unique keys across all partitions. Of course, there is a performance penalty associated with indexing the non-partition unique keys twice.

A duplicate key flag value, 2, permits unique keys within a partition, but no check is made for global uniqueness across all partitions.

Serial Segments (SRLSEG)

SRLSEG is managed by using the data file host header to maintain the serial numbers used by key segments across all partitions. SRLSEG key segments can be used to make a key value unique, and this still works with partitioned files.

FairCom DB V11.0 and later supports disabling/enabling SRLSEG with a PUTIFIL() call and during compact and rebuild when the updateIFIL option is specified. A file that contains an extended header now supports turning the SRLSEG or SCHSRL segment mode on or off by:

1. A call to PUTIFIL().

or

1. A call to the rebuild and compact function with the updateIFIL option specified in the IFIL's tfilno field.

Note: A call to rebuild or compact will fail with error IAIX_ERR (608) if the key segment definitions specified by the caller change the serial number attributes (either turning serial number on or off or changing its location in the record) when the updateIFIL option is not specified in the tfilno field of the IFIL structure.

Transaction Processing

A partitioned file that supports transaction processing must be a transaction-dependent (TRANDEP) file. This is automatically enforced.

Set maximum active partitions for auto-purge feature

FairCom Database Engine supports setting a maximum number of active partitions on a partitioned file. When a new partition is created, if the new number of active partitions exceeds the limit, the oldest partitions are purged.

This feature is well-suited for a time-based or incrementing sequence number partition key. In this situation, the partition number matches the time order of the partition creation, and so the automatic purge provides a way to keep the N most recent partitions.

Support for setting the maximum number of partitions has been implemented in the FairCom Low-Level API function PUTHDR(). This feature is also available through the FairCom DB API, C++, and REST APIs.

To set this value: After creating the partition host, call PUTHDR(partition_host_data_file_number, max_partition_members, ctMAXPARTMBRhdr).

A value of zero for max_partition_members is the default, and means no maximum number of partitions.

Note that c-tree itself supports up to 65535 active partitions per partition host, so this PUTHDR() call fails with error PBAD_ERR if a max_partition_members value larger than 65535 is specified.

Typical Scenario

The following example clarifies how the partition purge behaves:

1. Create the partition host and set maximum partition members to 5 for the partition host file.
2. Add records whose partition key cause partitions 1, 2, 3, 4, and 5 to be created.
3. Add a record whose partition key causes partition 6 to be created. Partition 1 is automatically purged, leaving active partitions 2, 3, 4, 5, and 6.
4. Add a record whose partition key causes partition 7 to be created. Partition 2 is purged, leaving active partitions 3, 4, 5, 6, and 7.
5. Add a record whose partition key causes partition 9 to be created. Partitions 3 and 4 are purged, leaving active partitions 5, 6, 7, and 9.

Note: The number of active partitions is calculated based on the highest-numbered active partition. When a new highest-numbered partition N comes into existence, only that partition and max_partition_members - 1 consecutive partitions preceding that partition are kept.

Limitations:

1. If a partition that is to be purged has been updated by a transaction that is still active, the purge of that partition is skipped. Although the partition is not purged at that time, it can be purged later by the creation of a new partition after the transaction that last updated that partition has committed.
2. Because the purging is done asynchronously, by a separate thread, it is possible that the number of partitions might exceed the specified limit. This can happen if the rate of creating partitions is faster than the rate of purging partitions.

API Changes:

The PUTHDR() function now accepts a mode of ctMAXPARTMBRhdr, and the specified value sets the maximum number of partitions on a partitioned host file.

The ctFILBLK() function now accepts a new mode bit, ctFBfailIfTranUpdated. When this mode bit is used, if a file that is being blocked has been updated in a transaction that is still active, instead of aborting that transaction the file block fails with error code 1136 (FBTU_ERR): "File block failed because the file has been updated in an active transaction and the caller requested that the file block should fail in this situation instead of aborting the transaction."

Encryption

Encryption is supported with partitioned files.

Notice that there is an important complication with archived partitions. Encryption involves creating a random key for each individual physical file. That data encryption key is in turn encrypted with the master key. This is why a single master key can decrypt an entire database and each file is still protected by a unique "private" key. Partitioned files are collections of many physical files. Imagine you archive a set of those files and physically remove them, then change (rotate) your master key as you should over time. If you bring the archived partitions back online at a future time, you can no longer decrypt their contents because the original master key is no longer available.

Partitioned File Security - File password support

File passwords are supported for partitioned files. A partition created on-the-fly is assigned the same security information as the host file.

Partition Administration Function

The Partition Administration function, PartitionAdmin(), allows on-the-fly adjustment to the partitions associated with a given host file. This includes the capability to:

• Add, remove, or archive partition(s)
• Modify lower limit of the raw partition number
• Modify limit on the number of partitions
• Reuse the raw partition number of a purged member
• Activate archived member(s)
• Return a member file status

For additional information on PartitionAdmin(), see PartitionAdmin.

Managing Partitions

Each FairCom DB data partition and index pair resides as an independent data set. Their purpose is for ease and speed of data management. They can easily be purged, archived, re-activated, and even rebuilt as individual data silos with a single operation. Administration is done through the FairCom DB PTADMIN API function.

SQL Administration

FairCom DB SQL includes built-in procedures to administer partitions directly from your SQL application. The fc_ptadmin_num() procedure provides access to many basic administration functions.

call  fc_ptadmin_num('admin',  'custmast',  'archive',  123)

To identify current lowest and highest active partition numbers, the built-in SQL procedure fc_get_partbounds() returns this information.

call  fc_get_partbounds('admin',  'custmast')

File Security and Encryption

Any time multiple users access a database concerns with file security arise. This is particularly important when you have programs that allow easy access to the database. You must be able to prevent unauthorized access to restricted files, you may want to limit who can add or change records in the database, and it might be important to control who can delete files. The FairCom Server supports several complementary forms of system security: user passwords, file permissions, file passwords and data encryption. Careful use of these features provides complete control over who can access a particular file, and what that user can do with the file once it has been accessed.

Note: FairCom DB File and User Security are available only when using the client/server operational model.

It is important to note that file security is a function of access via the FairCom Server, therefore access by non FairCom DB programs may not be limited in many operating environments.

Accessing File Security

Extended Functions

In the first several chapters of this manual, we mentioned the extended functions. These functions perform the same operations as their complementary standard functions, with the additional ability to access the FairCom DB file security features. These functions have a suffix of Xtd (for instance, the extended version of InitCTree() is InitCTreeXtd()). The extended functions are:

• CreateDataFileXtd()
• CreateIndexFileXtd()
• CreateIFileXtd()
• CreateISAMXtd()
• InitISAMXtd()
• InitCTreeXtd()
• OpenCtFileXtd()
• OpenFileWithResourceXtd()
• OpenIFileXtd()
• OpenISAMXtd()
• RebuildIFileXtd()
• TempIIndexXtd()

Security function

In addition to the extended functions, FairCom DB provides the Security() function to manipulate file passwords and permissions.

FIPS Support

NOTE: This does not apply to FairCom JDBC and ADO.NET drivers, and json.db,

FIPS SuFIPS 140.2 is an encryption standard established by the US federal government. Faircom provides support for FIPS 140.2 through OpenSSL 3.0. To enable this support, use server keyword

FIPS_ENCRYPTION YES 

When successfully enabled, the following message is logged to CTSTATUS.FCS at startup:

"OpenSSL FIPS Cryptographic provider in use"

To enable this support for C based clients, set environment variable FAIRCOM_FIPS_CLIENT_MODE=Y before starting the process. To validate the provider is correctly configured or diagnose problems set environment variable FAIRCOM_LOG=<filename>

When successfully enabled, the following message is logged to <filename> at startup:

"OpenSSL FIPS Cryptographic provider in use"

This affects all FairCom command line utilities, C based API's and other API's that rely on these such as:

C.isam

C.lowlevel

C.nav

C.sql.direct

Cpp.nav

Csharp.nav

java.jpa.nav

Java.nav

Php.sql.pdo

Python.nav

Python.sql

Sql.odbc

Vb.nav

Unaffected client API's are:

Sql.jdbc

csharp.sql.ado.net

json.db

NOTE: For unaffected client API's, driver side FIPS support may be possible purely through the host language (java, .NET, etc)

Forms of System Security

FairCom DB provides security in the form of users, user groups, files, and passwords.

Users, Groups, and Passwords

System Administrator

The FairCom Server comes with a System Administrator utility program, as described in the FairCom Server documentation. This utility performs a number of security related tasks.

User ID and Password

For username and password specifications, see System specifications.

For controlling user password requirements, see Setting password requirements.

Groups

The System Administrator will create one or more Groups. Each group is assigned a unique Group ID that is a 32-byte ASCIIZ string.

To provide a convenient way to permit users with related needs to share information, each user is assigned membership in from one to sixteen groups. The Administrator adds the groups to the system and assigns users to the groups. Each user has a default group, which counts as one of the user’s sixteen groups. If a user is not assigned to a group, the user’s default group is the GUEST group.

When an application program logs on to the FairCom Server with one of the extended functions, it sends a User ID and user password to the FairCom Server. Logging on with one of the equivalent standard functions, or sending a NULL User ID with one of the extended functions, automatically assigns the User ID of GUEST and membership in the GUEST group. If a User ID and/or password are sent which do not exist, the initiating call to the FairCom Server returns an error code and the program is not connected to the FairCom Server.

File Permissions

When FairCom DB creates a file, the User ID of the creating user is assigned to the file as the file’s owner. The owner’s default group is also assigned to the file unless the creating function specifies one of the owner’s other Group ID’s.

Further, the user may specify a permission mask that determines the kind of access that users may acquire on subsequent opens. The mask is comprised of three components: owner permissions, group permissions and world permissions. With this structure, you are able to allow different users different levels of access to the file.

When the owner of a file opens that file, that user is given owner’s permissions. Typically this level allows the user the most flexibility in managing the file. If a user who is not an owner of the file opens that file, FairCom DB looks at the groups the user belongs to. If that user is a member of the group assigned to the file then the user is granted group permissions. Finally, a user not falling into these categories is granted world permissions. Generally, permissions become more restrictive as you go from owner to world.

For example, an owner of a file may be able to read, write, and update records, and delete the file if necessary. Another user who is a member of the group for that file could have permission to read, write, and update records, but not to delete the file. Finally, a user who is not an owner, and not a member of the file’s group, may have permission only to read the file.

Permission Mask

When the file is created you will provide a permission mask for that file. The permission mask is formed by OR-ing the appropriate permission constants (found in ctport.h) from the following list:

• “READ” permission grants access to data, but no ability to change the data.
• “WRITE” permission grants update abilities.
• “DEF” permission allows the user to change or set file definition characteristics such as alternative collating sequences and record schemas.
• “DELETE” permission grants the ability to delete the entire file.

For example, to grant the owner all permissions, to grant group members read and update permissions, and to permit no access outside of the group, the permission mask should be defined as:

(OPF_ALL | GPF_READ | GPF_WRITE | WPF_NONE)

If no permission mask is assigned to the file, owner, group and world are granted all permissions. Permission masks, file ownership and group assignment may also be changed by using the Security() function, or by the system administrator.

File Passwords

In addition to permission masks, files may be protected by file passwords. File passwords can be assigned at the time the file is created, by using the Security() function, or by the system administrator. The file password is a 10-byte ASCIIZ string.

A user cannot access the file, regardless of the file’s permission mask, if the correct password is not supplied when the file is opened.

The ADMIN superuser always has full access to files. They do not need to know the file password to access a file.

User Profile

In the InitISAMXtd(), InitCTreeXtd(), CreateISAMXtd(), and OpenISAMXtd() extended functions there is a user profile mask parameter, userprof. We will discuss it here even though it is not a security parameter. You can combine the following values by OR-ing them together.

Automatic TransformKey

When using extended functions to initialize the ISAM system, FairCom DB automatically invokes TransformKey(). A userprof value of USERPRF_NTKEY disables the automatic TransformKey() feature.

Save Current ISAM Record

If you assign the value USERPRF_SAVENV to userprof, each Begin() call automatically includes the ctSAVENV mode. In this case, each Begin() and SetSavePoint() call saves the current ISAM record information, so that a subsequent RestoreSavePoint() or Abort() call automatically restores the current ISAM record information.

Allow ADMIN Access When Max User Limit Hit

One ADMIN group user may connect to FairCom DB when the maximum number of authorized users limit is reached. One client belonging to the ADMIN group can log on to a server, even if the maximum number of connections is used, by setting the USERPRF_ADMSPCL bit of the user profile.

This capability allows the Server Administrator to perform administration tasks even when the server is at its maximum licensed connections. The ctadmn and ctstop utilities take advantage of this feature.

Logon Control Options

FairCom DB security offers several options for controlling attempted logons.

For controlling user password requirements, see Setting password requirements.

Logon Strike-Out Options

FairCom Server configuration options set the default number of consecutive logon failures to allow, the default time to deny logon attempts after the failure limit is reached, and to set beginning and ending dates for User IDs. Options in the Security() function can reset the logon limit and the deny time.

The Server Administrator can set an optional limit on the number of consecutive failed logons that will cause subsequent logon attempts to fail for a specified time interval. The default logon limit is zero (0), which implies the feature is not active. Logons are blocked for 5 minutes by default after exceeding the limit. A logon during this period returns LRSM_ERR (584). Set the logon limit with LOGON_FAIL_LIMIT <logon limit> in the FairCom Server configuration file. The length of time the logons are blocked is set by LOGON_FAIL_TIME <minutes> in the configuration file. Set LOGON_FAIL_TIME -1 to block logon attempts permanently or until reset by an Administrator.

To require user logons within a given period and ensure all users log on “at-least-once” within the defined time (e.g: at least once a week), add the LOGON_MUST_TIME <minutes> keyword in the Server configuration file, ctsrvr.cfg, where <minutes> is the period in minutes during which the user must logon. If the time expires for a specific user, their profile will be deactivated, preventing access to the FairCom Server. The Server Administrator, or other ADMIN group user, must re-set the user’s account once the time limit has elapsed.

A client belonging to the ADMIN group can call Security to reset the values for these logon control options as follows:

A call of the form below, where value is a LONG holding the new LOGON_FAIL_LIMIT value, changes the limit in real time regardless of setting file or configuration file entries. The value is stored in FAIRCOM.FCS and can only be changed by a subsequent call to Security or by replacing FAIRCOM.FCS.

SECURITY(0,&value,(VRLEN) sizeof(value),SEC_FAIL_LIMIT)

A call of the form below, where value is a LONG holding the new LOGON_FAIL_TIME value in minutes, changes the time interval regardless of setting file or configuration file entries. The value is stored in FAIRCOM.FCS and can only be changed by a subsequent call to Security or by replacing FAIRCOM.FCS.

SECURITY(0,&value,(VRLEN) sizeof(value),SEC_FAIL_TIME)

A call of the form below, where value is a LONG holding the new LOGON_MUST_TIME value in minutes, changes the time interval regardless of setting file or configuration file entries. The value is stored in FAIRCOM.FCS and can only be changed by a subsequent call to Security or by replacing FAIRCOM.FCS.

SECURITY(0,&value,(VRLEN) sizeof(value),SEC_MUST_TIME)

The ctadmn utility supports the addition and maintenance of the logon control attributes on a user-by-user basis.

Retrieve the current Server logon control values with the SystemConfiguration() function. The desired values return in the cfgLOGON_FAIL_LIMIT, cfgLOGON_FAIL_TIME, and cfgLOGON_MUST_TIME parameters.

Suspend Server - Block User Logins

This feature blocks non-ADMIN users with the FairCom DB Security function. Four modes support the ability of an ADMIN user to block logons by non-ADMIN users:

The following Security() error and warning code returns apply to these modes:

Note: Only the ADMIN user can upgrade the block from NONADM to NONSUP mode or relax the block from NONSUP to NONADM mode. Other members of the ADMIN group can only use the NONADM block.

Once the block is turned on, a user that attempts to logon, but who does not satisfy the ADMIN requirements, is rejected with an error XUSR_ERR (593)

Suspend the Server

As noted above, the Security() function has the ability to kill all existing clients when setting a logon block. SEC_BLOCK_KILL acts like SEC_BLOCK_NONSUP by blocking logins by any user other than ADMIN, but also kills any clients already logged in.

Only ADMIN can make this call, and only ADMIN can resume logins with mode SEC_BLOCK_OFF. The internal threads that might open files are also suspended:

• The delete node thread and SYSLOG threads are suspended.
• The variable-length space management thread is suspended.
• The checkpoint thread is not suspended since it only searches for open files, it does not open them.

The COMPATIBILITY NO_BLOCK_KILL FairCom Server configuration keyword removes this capability. When COMPATIBILITY NO_BLOCK_KILL is in the configuration file, a call by ADMIN with mode SEC_BLOCK_KILL fails with NSUP_ERR (454).

The ability to suspend the Server gives remote ADMIN clients an added dimension. As an example, say a remote administrator needs to perform some type of operation in which it might be desirable to shut down the FairCom Server. An ADMIN client program can be authored to stop the Server, however restarting the server in a “non-telnet” type of environment might be impossible. By giving the administrator the ability to block new logins and kill existing clients (suspending the server) and then re-allow logins (like a restart) provides a solution to this problem.

Server Keyword: STARTUP_BLOCK_LOGONS

The STARTUP_BLOCK_LOGONS FairCom Server configuration keyword prevents non-ADMIN user logons when the server is started. Only users in the ADMIN group are allowed to logon.

This feature allows the server to start for ADMIN purposes before authorizing access by non-ADMIN users. This could include creating or adjusting files, adjusting security options, or any other operations that require a functioning Server but are more conveniently accomplished when users are not connected to the Server.

Once the ADMIN work is finished, call Security() with the following command to allow all user logons:

SECURITY((COUNT)-1,(pVOID)0,(VRLEN)0,SEC_BLOCK_OFF);

Allow SIGNAL_READY and ADMIN SQL Connections

A revision changes the behavior of STARTUP_BLOCK_LOGON for FairCom DB SQL Server so that SIGNAL_READY and the SQL listener threads run immediately. For SQL connections there is a choice of two behaviors when logons are blocked at server startup:

1. The configuration option STARTUP_BLOCK_LOGON YES only allows ISAM connections by the ADMIN user. SQL connections are rejected with the new error code 1158 (SQL_LOGON_BLOCKED_ERR, "SQL connection is blocked from logon due to server startup logon block").
2. The configuration option STARTUP_BLOCK_LOGON YES:ALLOW_SQL allows both ISAM and SQL connections by the ADMIN user.

Compatibility Change: This revision changes the behavior of STARTUP_BLOCK_LOGON for FairCom DB SQL Server.

Affected Components: c-treeSQL Server

Advanced File Encryption

FairCom DB supports encryption of data, index, and transaction log files. This technology provides the means to add an extra level of confidentiality to an application’s data. Once encrypted, it becomes difficult for a user to “dump” or “inspect” the data.

FairCom DB File and User Security are available only when using the client/server operational model.

Advanced File Encryption includes a suite of protocols that will protect user data by what is loosely called strong encryption with a certain amount of performance overhead. Historically, enabling encryption impacted database performance, however with modern hardware performance, the impact of data encryption is approaching negligible levels for most applications. The algorithms and protocols used are based on three primitives:

• Secure One-Way Hash Function (MD5)
• Block Ciphers (DES and AES)
• Pseudo-Random Number Generators

File-Specific Encryption Control

In systems supporting file encryption, control over whether or not to encrypt a file is available on a file-by-file basis.

If file encryption has been enabled by a call to SetEncryption(), an individual file can disable encryption by OR-ing ctNOENCRYP into the x8mode member of the XCREblk structure.

Whether or not SetEncryption() has been called, setting the filkey member of the XCREblk structure to a non-zero value causes the file to be encrypted using the value of the filkey member as the encryption key.

Using Advanced Encryption

Advanced Encryption requires a master encryption key to access encrypted data. This key must be presented at server startup for any access to encrypted data, thereby ensuring ONLY the intended client applications that have access and knowledge of the master encryption key can view the data.

Also note that FairCom DB supports passwords on files, which may further help protect against unwanted access. Proper implementation of user-access controls within FairCom DB is also recommended to prevent unauthorized access to data, even if it is not protected by other means.

FairCom DB File and User Security are available only when using the client/server operational model.

Current customers: Please be sure to see additional application-level security details in the Knowledge Base in the Support Portal.

Setting Up Encryption

For a FairCom DB file to be protected by Advanced Encryption, it must be created as an encrypted file. The FairCom utility ctcmpcif.exe provides a mechanism for adding encryption support to an existing file. To create a file as encrypted you must call SetEncryption() before your file creation calls:

SetEncryption(pTEXT mod, pTEXT key, VRLEN keylen)

• mod -- reserved for Advanced Encryption options and should be NULL for default encryption.
• key -- pointer to a byte array containing the developer-specified encryption key of length keylen.
• keylen -- length of key

For more details about using this function, see SetEncryption.

SetEncryption() does NOT assume that key points to a null-terminated ASCII string. The key can be any arbitrary array of bytes and is determined by the programmer. Key lengths of 7 bytes or more should be adequate. All create operations performed after SetEncryption() will result in encrypted files.

To stop encrypting new files, call SetEncryption() with key set to NULL and/or keylen set to zero.

SetEncryption() only affects file creation operations. All files created after a given call to SetEncryption(), with a non-NULL key and a keylen greater than zero, will be encrypted with the same key. Therefore, at the ISAM level, a data file and its associated indexes will be created with the same encryption key. Turning encryption on and off through calls to SetEncryption() only affects whether new files are encrypted. Once a file is set for encryption, it is always encrypted and no special calls are required to read from or write to the file.

Note: key may be thought of as a public key that changes the nature of the encryption, however, is not required to open the file. File passwords should be used to protect the ability to open a file. As described in Hidden Key Support, a hidden key may be embedded within a client executable and FairCom Server to give your application an exclusive ability to decrypt the file.

The following pseudo-code results in the first ISAM data file and its indexes to be encrypted with AES32 advanced encryption and the second ISAM data file and its indexes not to be encrypted:

InitISAM(...)

SetEncryption( (pTEXT)ctAES32,key,(VRLEN) 23)

CreateIFile(..1..)

SetEncryption(NULL,NULL,(VRLEN) 0)

CreateIFile(..2..)

To encode index and data files with a parameter file, use the user profile bit, USERPRF_ENCRYPT, in CreateISAMXtd() to enable encryption. All files created by, and after, CreateISAMXtd() will be encrypted with the same key, selected automatically by FairCom DB. New data and index files created after a call to SetEncryption() with a NULL key and/or zero keylen are not encrypted.

Errors

Two error codes have been defined: DCOD_ERR (606) is returned when an application cannot decode an encrypted file. RCOD_ERR (607) is returned when automatic recovery cannot decode an encrypted file.

Advanced Encryption Configuration

Follow these steps to enable advanced encryption support:

1. When Advanced Encryption is enabled, FairCom DB requires a master password at server startup. Run the ctcpvf utility to generate a new master password for use when launching the Advanced Encryption enabled Server. This will generate the file ctsrvr.pvf. See Master Password Verification Options.

Note: FairCom DB looks for the file ctsrvr.pvf in the server binary area, so this file name should be specified. ctcpvf.exe creates the ctsrvr.pvf file in that same directory where it is run (e.g., the tools directory). On launch, the server looks for ctsrvr.pvf in the server directory, so ctsrvr.pvf needs to be moved or copied to the server directory.

Note: Developers can use the FairCom DB SDK to replace this prompt with an application-specific method of retrieving the master password. See the "Key Store Option" discussion in the ctcpvf utility.

1. To enable Advanced Encryption for the database server, place the following keyword in the ctsrvr.cfg configuration file prior to launching:

ADVANCED_ENCRYPTION YES

To enable Advanced Encryption for standalone models, call ctSetAdvancedEncryption(YES), then call InitIsam()

Important: Advanced Encryption is disabled by default. Any time you change the advanced encryption setting, you should delete the FAIRCOM.FCS file (which contains user and group information) before restarting FairCom DB so user and group information is encrypted for protection. All user and group information must be recreated if the FAIRCOM.FCS file is deleted. Alternatively, ctcv67 can be used with option E to encrypt an existing FAIRCOM.FCS.

See Also

• Master Password Verification File Utility - ctcpvf
• Change Master Password Utility - ctencrypt

Master Password Verification Options

FairCom DB advanced encryption (AES, Blowfish, Twofish, 3DES) requires a master password to protect encrypted file access. Before starting FairCom DB for the first time with Advanced Encryption enabled, the Administrator must use the ctcpvf utility to create the master password verification file. Each time FairCom DB starts, it prompts for the master password to allow it to open encrypted files.

ctcpvf creates the master password verification file. It accepts optional parameters: filename (the file name to create) and password (the master password). If the parameters are not given, ctcpvf will prompt for the required information.

Usage

ctcpvf  [-c <cipher>] [-f <filename>] [-k <key>] [-s <store>]

Where:

• -c <cipher> - Use encryption cipher <cipher>. Supported ciphers: aes256 and aes128. Default is aes256.
• -f <filename> - Create password verification file <filename>. Default is ctsrvr.pvf.
• -k <key> - Use <key> as the master key.
• -s [<store>] - Store key in encrypted file <store>. Default is ctsrvr.fkf.
• -syslevel - Create encrypted store file with system-level encryption: all user accounts on the system can decrypt it.

Note: If you don't use the -syslevel switch, you must run the FairCom Server under the same user account that was used to run the ctcpvf utility that created the master key store file. Using the ‑syslevel switch creates the master key store file so that it can be opened by any user account on that machine, which allows you to run the FairCom Server under any user account on the system. (See Advanced encryption master key store encrypted at system level on Windows.)

Note: FairCom DB looks for the file ctsrvr.pvf in the server binary area, so this file name should be specified. ctcpvf.exe creates the ctsrvr.pvf file in that same directory where it is run (e.g., the tools directory). On launch, the server looks for ctsrvr.pvf in the server directory, so ctsrvr.pvf needs to be moved or copied to the server directory.

Key Store Option

By default, this master key must be presented to FairCom DB on startup as prompted. However, this prompted interaction is not always possible. Consider the case of a failover strategy for business continuity, or the case where no single person should ever know the complete key as keys are built from random secure key generators. FairCom DB supports a key store file to provide this key value at startup.

The ctcpvf utility -s option is used to select the master key length, and to write the master key to an encrypted keystore file <store>.

The FairCom DB configuration option MASTER_KEY_FILE specifies the key store file, <store>, from which FairCom DB reads the master encryption key. On Linux and Unix systems, the master key is stored AES encrypted in a file on disk, with permissions set such that only the user that created the file can read it (permissions are set to 400). For complete security, it is important to use file system access safeguards to fully protect this key store file.

Non-server applications must set the environment variable CTREE_MASTER_KEY_FILE=<keystore> to enable using a key store rather than prompting for the master password at FairCom DB initialization.

Note: The key file (or user key on Linux and Unix) is encrypted using AES. The encryption is intended to only prevent casual inspection of the data when the file's contents are viewed. The permissions on the file are the defense against an unauthorized user reading the file. The Windows master key approach uses the Microsoft DPAPI to encrypt data with user credentials, and only that user can decrypt the file. Unix support is a bit weaker in this regard as it relies on file permissions, which can potentially be changed such that another user could read and decrypt the key.

ctencrypt - Utility to Change Master Password

The FairCom DB advanced encryption feature uses a master password to encrypt the file-specific advanced encryption key in c-tree data, index, and transaction log files that are encrypted using advanced encryption. ctencrypt is a standalone utility that can be used to change this master password for specified c-tree data, index, and transaction log files.

Operational Model:

• Standalone

Usage:

ctencrypt <options> <command>

Available Options:

• -n <sect> - Node sector size. The default is 64, which corresponds to PAGE_SIZE of 8192.

Available Commands (only one at a time may be specified):

• -chgmpw <filelist> - Change master password for the files whose names are listed in the file <filelist>. <filelist> is the name of a text file created by the end user that lists the names of the files (data and index), one per line, that are to be processed.

ctencrypt requires a password verification file named ctsrvr.pvf that was created using the current master password to exist in its working directory. ctencrypt prompts the user for the current master password and for the new master password (prompting twice in order to confirm that the new password was properly entered).

Note: ctencrypt does not change the master password file, ctsrvr.pvf. The ctcpvf utility will need to create a new file for server startup in coordination with the new password used to re-encrypt the encryption key for the files. Failure to do so will result in DCOD_ERR errors (606, failure to decode file) when opening files.

ctencrypt processes the specified files, indicating the status of each file and the total of successful and failed operations. Note that the FairCom Server must be shut down while these file modifications take place.

ctencrypt creates a temporary directory named temp\ctencrypt.tmp.<process_id> to store its transaction logs. This directory is normally deleted when ctencrypt shuts down.

Important: ctencrypt does not undo any changes in case of error. The files that it lists as successfully updated will use the new master password even if the utility failed to update other files.

Example File List

A semicolon can be specified at the start of a line to indicate a comment which is ignored.

; c-tree Advanced Encryption Conversion Listing File

; -----------------------------------------------------

; Created Wed Dec 01 01:38:00 2010
 

; transaction log files

L0000000.FCT

L0000002.FCA

L0000003.FCA

L0000004.FCA

L0000005.FCA

L0000006.FCS

L0000007.FCS

L0000008.FCS

L0000009.FCS

L0000010.FCT

; data files

  mydatafile.dat

C:\My Documents\test.dat 

vcusti

Note: All physical encrypted files, data and index files, must be specified in order to be modified. No attempt is made to determine associated files.

If the server was cleanly shutdown in such a manner that its transaction logs are no longer necessary, then they will not need to be included as part of this password change. If you wish to use the ctencrypt utility to modify any existing encrypted transaction logs (for example, archive logs for replication), their names must be specified in the list file. ctencrypt does not attempt to locate any transaction log files on its own.

Example Output

c-tree file encryption utility
 

This utility requires a master password in order to start.

Please enter master password:

Enter new master password   :

Confirm new master password :

Changing master password for the specified files...

[  OK   ] SYSLOGDT.FCS

[  OK   ] vcusti

[  OK   ] L0000000.FCT

[  OK   ] L0000002.FCA

[  OK   ] L0000003.FCA

[  OK   ] L0000004.FCA

[  OK   ] L0000005.FCA

[  OK   ] L0000006.FCS

[  OK   ] L0000007.FCS

[  OK   ] L0000008.FCS

[  OK   ] L0000009.FCS

[  OK   ] L0000010.FCT

12 succeeded, 0 failed

Successfully changed master password for all specified files

Error Returns

Two new error codes have been added related new password management features:

• BMPW_ERR (932) - The specified encryption master password is incorrect.
• ICOD_ERR (933) - An encryption operation failed due to an unexpected internal error. See CTSTATUS.FCS for details.

c-tree Client Implementation

Client implementation of Advanced Encryption is accomplished through the use of the SetEncryption() function on a per-file basis.

See Also

• Master Password Verification File Utility - ctcpvf
• Change Master Password Utility - ctencrypt

FairCom DB File and User Security are available only when using the client/server operational model.

To encrypt files, simply call SetEncryption() before calling the function to create the file. The mod parameter should point to a text string containing one of the constants in the table below (for example., ctENCR to use the default encryption method or ctDES24 to use DES encoding with a 24-byte key. See ctport.h for constants.)

Note: For Advanced Encryption key is unused and keylen should be a non-zero value.

SetEncryption() is only required to create encrypted files. Any standard client can access files encrypted by a FairCom Server configured for advanced file encryption. Encryption and decryption occurs server side, and is not part of the client application.

Example

This example demonstrates full AES32 advanced encryption for a single file:

InitISAM(...)

SetEncryption( (pTEXT)ctAES32, key, (VRLEN) 23)

CreateIFile(..1..)

SetEncryption(NULL, NULL, (VRLEN) 0)

The possible mod values are defined in ctport.h:

Client Operation

When using Advanced File Encryption, SetEncryption() is only required to create encrypted files. Any standard client can access files encrypted by a Custom Server configured for Advanced File Encryption. Encryption and decryption happen behind the scenes, invisible to the client application.

To protect files further, add a file password to each file. Only applications with the file password can open the file. See Security and Encryption (File Security and Encryption, /doc/ctreeplus/FileSecurityandEncryption.htm) for more details.

Transport Layer Security Secures Data in Transit between Network FairCom DB Clients and Servers

Note: FairCom DB File and User Security are available only when using the client/server operational model.

FairCom applications can now secure data in transit between c-tree network clients and FairCom servers. Transport Layer Security (TLS, also commonly referred to as its predecessor SSL, Secure Sockets Layer) is a cryptographic protocol designed for secure network communications using public key cryptography for authentication of the communicating party. Symmetric cryptography is used to encrypt transmitted data over the wire. FairCom DB TLS relies on OpenSSL toolkit support and implements TLS protocol V1.3. Earlier versions of TLS (and predecessor SSL) protocols contain known and exploited vulnerabilities. FairCom DB only supports TLS via TCP/IP communications protocols (IPv4 and IPv6). (For more about TLS, see https://en.wikipedia.org/wiki/Transport_Layer_Security.)

Two modes of TLS connections are available: basic and peer authenticated. Basic TLS connections are encrypted using only a server-side certificate; there is no local certificate requirement for a client. This makes deployment and management of secured connections easy.

TLS Certificates

It is the server administrator’s responsibility to ensure a correct and valid certificate pair, as well as proper configuration of allowed TLS connections.

Creation and management of TLS certificates, as well as use of a Certification Authority (CA), is beyond the scope of this document. Consult OpenSSL and other TLS supporting documentation and be sure you firmly grasp all details regarding use of TLS for network security before deploying.

Server certificates may be created and provided as two separate files: a certificate and a key. They can also be combined into a single file. There is no required file naming convention. Certificate files are usually created and/or provided as Base64 encoded X.509 certificate files and denoted with a .pem extension (Privacy Enhanced Mail). They can be identified with this set of surrounding identifiers within the file:

-----BEGIN CERTIFICATE-----

-----END CERTIFICATE-----

The private key is likewise identified:

-----BEGIN PRIVATE KEY-----

-----END PRIVATE KEY-----

The private key is often included in the same certificate file or as a separate .key file.

A certificate key can be optionally passphrase protected. However, this then requires the passphrase to be presented at server startup. FairCom key store files provide this ability.

Always securely maintain key files. Store key files only in permissions protected server areas and never distribute.

Server-Side Configuration

To enable TLS (SSL), see keywords for TLS.

Standard c-tree TCP/IP ports are used for connections regardless of TLS configuration. That is, a single ISAM port will handle both TLS encrypted and non-encrypted connections, and likewise for the SQL port. There is no need for separate port configurations.

For the HTTPD plugin you can verify ciphers in use using the system nmap command (Linux) against the HTTPD port when no ciphers are specified in cthttpd.json. This can be done with the linux command:

nmap --script ssl-enum-ciphers -Pn -p 8443

Client-Side Configuration

Both FairCom ISAM and SQL clients support TLS connections.

Peer Authentication - TLS connection with server certificate validation:

By default, a c-tree client requires a PEM file containing the server public certificate—ONLY the public certificate, not the server private key.

Important: The server private key should be securely maintained only at the FairCom Server location at all times.

By default, ISAM and SQL client libraries use the file ctsrvr.pem in the client process' working directory when connecting. An ISAM client can change the file name that the client library searches for by calling the ctSetCommProtocolOption() function with the option ctCOMMOPT_FSSLTCP_SERVER_CERTIFICATE:

ctSetCommProtocolOption(ctCOMMOPT_FSSLTCP_SERVER_CERTIFICATE, "myservercert.pem");

Basic - TLS connection without server certificate validation (ISAM only):

It is also possible to establish a TLS connection from an ISAM client without validating the FairCom certificate. Such a connection is encrypted but there is no guarantee of the server’s identity. To use this option, call the ctSetCommProtocolOption() function with the option ctCOMMOPT_FSSLTCP_SERVER_CERTIFICATE with an empty certificate name before connecting:

ctSetCommProtocolOption(ctCOMMOPT_FSSLTCP_SERVER_CERTIFICATE, "");

When the client does not use a server certificate, the connection is encrypted, but there is no guarantee that the client is connected to that specific server. This implies that a "man in the middle" attack could be possible.

If an error occurs when connecting using SSL, the connection attempt returns error 1104 (SSLCONN_ERR). To get more detailed information enable SSL logging by either:

• calling ctSetCommProtocolOption() with the ctCOMMOPT_FSSLTCP_DEBUG_LOG option:

ctSetCommProtocolOption(ctCOMMOPT_FSSLTCP_DEBUG_LOG, "ssldebug.log");

or

• setting the environment variable CTSSL_DEBUG_LOG to the name of the SSL debug log file.

To request a TLS-enabled ISAM connection, append ^fssltcp to the server name. For example:

ctadmn ADMIN ADMIN "" "FAIRCOMS@localhost^fssltcp"

Note: When you append ^fssltcp to the server name you must quote the entire connection string, for example:

"FAIRCOMS@localhost^f_tcpipv6"

Windows normally interprets the carat as an escape character or line continuation, so the entire server connection string must be quoted when it includes a caret.

 
Other examples showing how to specify a desired communication protocol when connecting:

To connect using shared memory (without falling back to TCP/IP if the shared memory connection fails):

FAIRCOMS@localhost^fsharemm 

To connect using TCP/IP v4 without SSL:

FAIRCOMS@localhost^f_tcpip

To connect using TCP/IP v6 without SSL:

FAIRCOMS@localhost^f_tcpipv6

To connect using TCP/IP v6 with SSL:

FAIRCOMS@localhost^fssltcpv6

To request a TLS-enabled connection for a SQL connection, prepend ssl: to the connection string. For example:

isql -u admin -p ADMIN ssl:6597@localhost:ctreesql

Standard FairCom DB SQL interfaces (JDBC, ADO.NET, ODBC, PHP, Python) each have independent standards for connection strings. JDBC and ADO.NET are specifically described later in this chapter.

ctadmn and FairCom DB Monitor show the communication protocol that is in use, including whether or not the connection is using TLS (SSL):

F_TCPIP     indicates an unencrypted ISAM TCP/IP connection.

FSSLTCP     indicates an SSL-enabled ISAM TCP/IP connection.

SQL_TCPIP   indicates an unencrypted SQL TCP/IP connection.

SQL_SSLTCP  indicates an SSL-enabled ISAM TCP/IP connection.

X.509 Support

Support has been added for X.509 client/server authentication. For now, only X.509 authentication when using TLS-encrypted TCP/IP for ctree is supported. Future revisions will extend this to SQL and shared memory protocols.

X.509 authentication is not supported in combination with LDAP authentication.

To use X.509 authentication, the client must provide a PEM formatted X.509 certificate with a complete certificate chain using the same root CA as the server certificate. If the client's certificate is successfully validated by the server, the subject field on the client X.509 will be parsed by the server to extract a username. This username is used to determine the database permissions to be granted. A complete certificate chain consists of a PEM formatted file with the certificate for the user, followed by the certificate for the issuer, followed by the certificate for that issuer, and so on, ending in the root CA certificate.

When X.509 authentication is enabled and a certificate is provided by the client, the username and password arguments passed to InitISAMX() are ignored.

New Security keywords have been added to enable X.509. See Client Communications Keywords for TLS.

The following functions have been enhanced for configuring X.509 authentication

• ctSetCommProtocolOption
• InitISAMXtd

Expected TLS Performance

The resource-intensive portion of a TLS connection is the initial creation. A secure communications channel is established via asymmetric encryption requiring a session key exchange. This initial key exchange and encryption process is the slowest computational epoch. Once connected, ongoing connection overhead is negligible. Further, most modern hardware contains dedicated CPU instructions for enhanced encryption performance. Thus, it is important to avoid repeated connections and maintain an established TLS connection when at all possible.

Compatibility

COMPATIBILITY TCPIP_CHECK_DEAD_CLIENTS

Advanced SSL Certificate Options

The ssl_certificate keyword in the config/cthttpd.json web server plug-in configuration supports the fccert.pem, which is a self-signed certificate we supplied. To use your own certificate, use the following keywords to config/cthttpd.json:

• ssl_key: SSL private key - This can be embedded in the same file provided the in ssl_certificate. For example, our default fccert.pem certificate file has both the certificate and the private key, so, ssl_key is not required.
• ssl_ca: SSL Certificate Authority - External authority that issues and validates the certificate.
• ssl_cipher_suites - Colon-delimited list of SSL cipher suites.

Troubleshooting

There is a client-side environment variable for debugging. To enable SSL logging for the client, set the CTSSL_DEBUG_LOG environment variable to the log file name.

For server side SSL debugging, add DEBUG_LOG <logfile> to the SSL subsection.

Testing with Default FairCom DB Certificates

For testing and evaluation purposes only, a self-signed X.509 certificate is included in your default FairCom DB package. OpenSSL was used to create this certificate.

Security Note: It is critical this included certificate is never used in a production setting.

In the example below, a certificate and private key are both included in the file ctree_ssl.pem. Unencrypted TCP/IP connections are allowed, and the specified ciphers are the ones that are allowed to be used in encrypting the SSL connection:

SUBSYSTEM COMM_PROTOCOL SSL {

 SERVER_CERTIFICATE_FILE ctree_ssl.pem

 SSL_CONNECTIONS_ONLY NO

 SSL_CIPHERS ALL:!aNULL:!eNULL:!SSLv2:!LOW:!EXP:!RC4:!MD5:@STRENGTH

}

To enable only TLS-encrypted communications for all connections, change SSL_CONNECTIONS_ONLY to YES, and, optionally, comment Shared Memory communications support to prevent local unencrypted shared memory connections. (FairCom DB Shared Memory connections are not supported for TLS encryption.)

;COMM_PROTOCOL  FSHAREMM

Finally, for peer authentication, an additional cross-check validation against the server certificate, copy ctsrvr.pem to your client working directory. FairCom DB management and administration tools already include this local client certificate file in their working folder:

<faircom>\server

All GUI tool connection dialogs contain specific parameter options for enabling TLS connections. support.faircom.com was specified as the Common Name in provided FairCom DB default certificates. Specify support.faircom.com when testing FairCom DB SQL Explorer for TLS authentication using ADO.NET to succeed using these included certs.

ADO.NET Support for TLS

In V11.5 and later, the FairCom DB ADO.NET provider supports TLS/SSL connections per Microsoft specifications.

The ADO.NET provider uses the local certificate store to locate certificates when using peerAuthentication. The server's CA (or a self-signed) certificate ctsrvr.pem must be added to the trusted root certificate store on the client machine for the .NET framework's certificate authentication to succeed:

CertMgr.exe /add ctsrvr.pem /c /s /r localMachine root

Note that the Common Name specified in the server certificate is the name that the application must specify in the ADO.NET connection string for the TLS option.

For this certificate, we used support.faircom.com as the Common Name, and so the ADO.NET connection string must specify sslcert=support.faircom.com for the TLS authentication to succeed.

If client certificate authentication is desired (V13.1 and later), a certificate chain and private key for the client must first be added to the current user's personal certificate store. This can be done using a PKCS #12 file containing the desired certificate chain and private key.

Connection String

The ADO.NET connection string is similar to the JDBC string. The connection string accepts a new property:

ssl=<value>

which can have two values:

• basic - Basic SSL setting, no peer certificate authentication, only communication encryption as requested by server certificate
• peerAuthentication - Server certificate authentication.

In the case of peerAuthentication the server certificate Common Name must be provided by the new property:

sslcert=<value>

If this property is not specified, the value of the Server setting is used to match the certificate.

If peerAuthentication is enabled, client authentication may also be attempted. If X509_AUTHENTICATION is enabled by the server then a client certificate can replace the normal password based authentication. Identify the Common Name for the client certificate to use:

ClientSSLCert=<value>

The current user's personal certificate store is searched first for this certificate. The localmachine store will also be searched if a matching certificate is not found in the current user's store and the process has permission to access the localmachine's personal store.

If X509_AUTHENTICATION is enabled by the server and the ClientSSLCert is accepted, any UID and PWD values included in the connection string are ignored.

Examples:

"UID=ADMIN;PWD=ADMIN;Database=CtreeSQL;Server=localhost;Service=6597;ssl=basic";

"UID=ADMIN;PWD=ADMIN;Database=CtreeSQL;Server=localhost;Service=6597;ssl=peerAuthentication;sslcert=support.faircom.com";

"Database=CtreeSQL;Server=localhost;Service=6597;ssl=peerAuthentication;sslcert=support.faircom.com;ClientSSLCert=JohnDoe";

ODBC Support for SSL

You can use SSL with an ODBC data source. To configure SSL, open the ODBC Data Source Administrator.

Once you invoke the ODBC Data Source Administrator:

1. In the dialog box for the type of data source you choose, choose the Add button. The Add Data Source dialog box appears.
2. Select FairCom DB SQL from the list of installed drivers and choose Finish. The FairCom DB ODBC Setup dialog box appears.
3. Fill in the dialog box fields as shown in the following figure and choose OK. The ODBC Data Source Administrator writes the values you supply to ODBC.INI or to the DSN file you indicated.

Data Source Name - A local name for the FairCom DB SQL data source for use in connect calls and by the ODBC Administrator.

Description - Optional descriptive text.

Host - Specify the machine name on which the FairCom DB SQL Server is running.

Database - The name of the database where the FairCom DB SQL data source resides.

User ID / Password - User name and password for connecting to the database. The driver uses those values if the application does not supply them in the call. You can leave these fields blank if you want the driver to use the defaults on the server. If no defaults are defined and you leave these fields blank, the user will be prompted when the application connects.

Service - The name of the Service FairCom DB SQL listens to. If empty, sqlnw is used.

Default Fetch Size - This value is the size (in bytes) used by the driver to fetch multiple rows from the server. It reduces network requests resulting in performance gains. If not set, the internal buffer size is 5000 bytes.

• In your connection string, set the attribute "FETCH_SIZE=[number of bytes]"
• In your ODBC.INI file, set the attribute "Default Fetch Size=[number of bytes]"
• In the connection string, the attribute is: QUERY_TIMEOUT=[number of seconds]
• In the ODBC.INI file, the attribute is: Default Query Timeout=[number of seconds]

1. The Data Source Dialog box reappears, and now includes the newly-added data source.

Configuring SSL in Your Connection String

In the ODBC connection string, it is possible to add "SSL=xyz" where "xyz" is one of the options 2 or 3 from the SSL parameters listed above in the FairCom DB ODBC Setup dialog box.

You can add one of the following to your existing ODBC connection strings to enable TLS/SSL:
"SSL=BASIC" - Encryption with default server certificate.
"SSL=ctsrvr.pem" - Use Peer authentication with explicitly named cert located in the local directory.
"SSL=C*\certs\ctsrvr.pem" - Use Peer authentication with an explicitly named cert with a full path.

JDBC Support for TLS

In FairCom DB V11.2 and later, FairCom DB SQL JDBC supports TLS connections per the JDBC standard. Enable TLS in a JDBC connection URL using the ssl=value parameter string.

TLS connections are enabled in the JDBC connection URL using the new format (it is not supported on the old URL format) and a new parameter ssl.

The new URL format is:

jdbc:ctree://<host>[:portnumber]/<dbname>[?param=value[&param=value]...]

The valid param values are:

• characterEncoding - Replace encoding with a valid Java encoding name (e.g., US‑ASCII, ISO‑8859-1, UTF‑8, etc.).
• password
• user
• ssl - The valid values for ssl are:

basic

peerAuthentication

• trustStore - The name of a local trust store that includes the server's CA certificate.
• trustStorePassword - The password for the specified trust store.
• keyStore - The name of a local key store that has the user certificate and private key.
• keyStorePassword - The password for the specified keyStore.

NOTE: For backward compatibility, the older format ("jdbc:ctree:6597@localhost:ctreeSQL", "ADMIN", "ADMIN") is still supported but should be considered deprecated.

Basic TLS with JDBC clients

Traffic to the server is encrypted, but there is no assurance of the server's identity.

Basic SSL encryption on the client is enabled by the URL parameter ssl, for example:

Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?ssl=basic");

Peer Authenticated TLS with JDBC clients using System properties

If the client wants to authenticate the server, then the client's trust store must contain the server's CA certificate (or a self-signed server certificate). A Java keystore must first be created that contains this CA certificate.

For example, the following adds the trusted CA certificate ctsrvr.pem to a keystore named server.store

keytool -importcert -file ctsrvr.pem -keystore server.store

keytool is part of the Java distribution, and will prompt you for a password used to encrypt the trust store. In this example we used mypassword as the password.

Client SSL with server authentication is enabled by the URL parameter ssl set to peerAuthentication.

You must set the system properties javax.net.ssl.trustStore and javax.net.ssl.trustStorePassword to reference the trust store for the desired database server.

Example:

System.setProperty("javax.net.ssl.trustStore","server.store");

System.setProperty("javax.net.ssl.trustStorePassword","mypassword");

Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?ssl=peerAuthentication");

Full TLS authentication with JDBC clients

The FairCom database server may be configured to allow or require client TLS authentication in place of password based authentication. The client needs to both authenticate the server (with the same requirements as for peerAuthentication), and provide proof of identity to the server by providing a client certificate that is trusted by the server. This may be set in the connection string by specifying a trustStore for the server and keyStore for the user.

Example:

Create the trust store with the trusted CA certificate ctsrvr.pem to a keystore named server.store. Keytool is part of the Java distribution, and will prompt you for a password used to encrypt the trust store. In this example we use mypassword as the password.

keytool -importcert -file ctsrvr.pem -keystore server.store

The client must possess a keystore containing their certificate and private key. Here we assume a keystore exists named johndoe.pkcs12 protected with password secret.

NOTE: The default keystore format is controlled by the java.security configuration file under the property keystore.type. The PKCS12 keystore format is supported by all Java implementations, but prior to Java9 JKS was the default format. You may need to adjust this configuration for your keystore to be accessed by Java.

The following connection string will attempt to connect using these certificate sets for TLS authentication.

Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?trustStore=server.store&trustStorePassword=mypassword&keyStore=johndoe.pkcs12&keyStorePassword=secret");

See Also:

Java keytool documentation provides examples of how to generate a key pair, request a certificate from a CA, or generate certificates for a server.

Allow ISAM Client to Use SSL without Client Having Server Certificate

The c-tree ISAM client now supports establishing an SSL connection without requiring the client to have the server certificate in the file ctsrvr.pem. By default, the client requires the certificate file, but it can disable this requirement by calling the ctSetCommProtocolOption() function with the following parameters after registering a c-tree instance and before connecting to c-tree Server:

ctSetCommProtocolOption(ctCOMMOPT_FSSLTCP_SERVER_CERTIFICATE, "");

When the client does not use a server certificate, the connection is encrypted, but there is no guarantee that the client is connected to that specific server. This implies that a "man in the middle" attack could be possible.

OpenSSL Headers for Linking FairCom DB Client Applications

The OpenSSL headers have been included in this package for your convenience. These headers must be compiled and linked into your project. Examine your opensslv.h header in the ctree.drivers/include/openssl folder for the current version included in your build.

The OpenSSL include files are located under /ctree/include.

Libraries are provided for multiple build platforms and are located in /ctree.drivers/lib/License.Lib/openssl, where you'll find:

• libeay32.lib
• ssleay32.lib

Choose the appropriate build platform from the provided versions.

OpenSSL Now Provides Default Faster AES Encryption

FairCom now supports an updated AES algorithm based on the OpenSSL standard by default. Previous algorithm implementation can be restored by specifying in ctsrvr.cfg the keyword OPENSSL_ENCRYPTION NO.

This change is expected to provide security and, with internal testing, we have seen an up to 20% performance improvements.

This modification changes file passwords encrypted on the client-side for secure transmission to use OpenSSL.

TLS/SSL Tutorials

As an example of linking with OpenSSL libraries, you can view the Tutorial makefiles and IDE Projects in the SDK directories. For example:

/linux.x64.64bit/sdk/ctree.ctdb/tutorials/cmdline/Makefile

AWS Security

(This technology is not included in the default package. It is available upon request.)

Master Encryption Key management is challenging. Keys must be remembered or securely retained, or data is permanently lost. To aid secure master key storage, FairCom Server can now integrate with an AWS Secrets Manager® service. This support requires an AWS account and a configured Secrets Manger service, which is not included nor maintained by FairCom.

Please contact FairCom with your specific Advanced Encryption Key management request for Amazon AWS support.

Support for Using AWS Secrets Manager as External Encryption key Store

FairCom Server now supports retrieving its master encryption key from AWS Secrets Manager.® This option is an alternative to FairCom Server's MASTER_KEY_FILE option. It has the advantage that the key is not stored locally. To logon to the AWS Secrets Manager, FairCom Server displays a dialog box that prompts the user to enter the AWS Secrets Manager credentials.

To configure FairCom Server to use AWS Secrets Manager, add the following option to ctsrvr.cfg:

SUBSYSTEM EXTERNAL_KEY_STORE AWS {

 KEY_ID <key_id>

 REGION <region>

 TIMEOUT <timeout>

}

<key_id> is the key ID that you assign to the encryption key when you store it in AWS Secrets Manager.

<region> is the AWS region where the AWS Secrets Manager stores your key.

<timeout> is the maximum amount of time, in seconds, to wait for the user to enter the AWS Secrets Manager credentials when FairCom Server starts. The default is 30 seconds. If the timeout period passes before the user enters the credentials, FairCom Server logs the following messages to CTSTATUS.FCS and shuts down:

 - User# 00001  Failed to retrieve AWS credentials: CTAWS(324): abandoning wait for AWS credential prompt to complete after <seconds> second(s) due to timeout

 - User# 00001  The master password must be entered in order to start this server.  The server will now shut down.

How to use AWS Secrets Manager to store the master encryption key for c-tree Server:

1. Login to AWS Secrets Manager.
2. Select "Store a new secret."
3. Select secret type of "Other type of secrets."
4. Enter the key and its value. For the key, use the name ctreeServerMasterEncryptionKey. For the value, enter the encryption master key.
5. Select encryption key of "DefaultEncryptionKey" and click Next.
6. Give the secret a name. This name is the value that you will specify for the KEY_ID value in the ctsrvr.cfg file.
7. Optionally add a description. Click Next.
8. Disable automatic rotation and click Next.
9. Click Store.

In standalone mode (ctrdmp utility for example), the configuration options are set by setting these environment variables to the desired values:

CTAWS_KEY_ID

CTAWS_REGION

CTAWS_TIMEOUT

If FairCom Server is configured to use AWS Secrets Manager and the DLL cannot be loaded, an error is logged to CTSTATUS.FCS:

- User# 00001  Could not load AWS support: CTDLL_LOAD: Failed to load module ctaws.dll: The specified module could not be found.: 981

FairCom Server supports changing the master encryption key when it is stored in AWS Secrets Manager.

Support added for changing FairCom Server master encryption key in AWS Secrets Manager

FairCom Server now supports changing the master encryption key when it is stored in AWS Secrets Manager.

DLL for FairCom Server to Access AWS Secrets Manager

A new DLL, ctaws.dll, exports a set of API functions that FairCom Server uses to access AWS Secrets Manager on Windows. FairCom Server dynamically loads this DLL when it is configured to use AWS Secrets Manager to store its master encryption key.

This DLL requires the Amazon provided AWS C++ SDK DLLs aws-cpp-sdk-core.dll and aws-cpp-sdk-secretsmanager.dll, which contain the AWS core and Secrets Manager functions respectively.

Product Installation Requirements:

To use AWS Secrets Manager as FairCom Server’s encryption key store, the DLLs ctaws.dll, aws-cpp-sdk-core.dll, and aws-cpp-sdk-secretsmanager.dll must be installed in the same directory as the FairCom Server binary.

Note: When using AWS key store support on servers that are licensed for multiple remote desktop sessions, the password prompt may not be presented unless the database process is run as a normal process (not a Windows service). Because this might involve file permissions changes, it is recommended to install the database in this non‑service configuration on such systems.

Visual Prompt Utility for AWS Credentials

To read FairCom Server's master encryption key from AWS Secrets Manager, we prompt the user to enter the AWS credentials using a graphical interface on Windows.

The ctawssmp utility is a process that displays a dialog prompting the user to enter the AWS Secrets Manager login credentials. FairCom Server launches this process when it starts up if it is configured to use AWS Secrets Manager as its encryption key store.

This Windows application displays the following message which prompts the user to enter the AWS Secrets Manager credentials:

Access Key ID and Secret Access Key are randomly-generated values created by an AWS account administrator. They require the proper access permissions to the AWS Secrets Manager to be able to read the secret referenced by the KEY_ID. For details about these values, see the Amazon document: https://docs.aws.amazon.com/IAM/latest/UserGuide/introduction.html

Product Installation Requirements:

To use AWS Secrets Manager as FairCom Server’s encryption key store, ctawssmp.exe must be installed in the same directory as the FairCom Server binary.

Ensure recoverability if server terminates while changing master encryption key

The changing of the master encryption key involves multiple steps. If an error occurs, the work is undone, but if the server terminates we did not ensure that the changes were all undone. This could render the server unable to start or unable to decrypt files.

If FairCom Server terminates while the master encryption key is being changed, the encrypted files (data and index files and transaction logs) might have been left in a state that is inconsistent with the current master key, causing them to fail to open with errors 606, 607, or 66 on the next restart of FairCom Server.

A write-ahead recovery log has been implemented for the master encryption key change. The log file, named CMPRECOV.FCS, is created in the same directory as the transaction logs. It records the change we are about to make to a file before we apply the change. When FairCom Server starts up, if this log file exists FairCom Server uses the information in this recovery log to undo all the changes to the files so the data, index, and transaction log files are all using the original master encryption key. This log also allows c-tree to undo the changes to non-transaction files, which are not recorded in c-tree's transaction logs.

Encrypted Data Master Key Library

It is now possible to implement custom solutions for retrieving the advanced encryption master key from an arbitrary library. This feature eases the way the developers can customize the master key prompt.

The new ctsrvr.cfg configuration keyword, MASTER_KEY_LIB, takes a string defining the complete library name to load, for example:

MASTER_KEY_LIB maskeylib.dll 

or

MASTER_KEY_LIB libmaskey.so

The master key library must link with the OpenSSL libraries that are used to secure the master key exchange and implement the following functions:

• int ctGetSecretVersion(void) - returns the version of the master key library SDK used to implement it.
• int ctGetSecret(ctGetSecretParams_t * GetSecretParams) - returns the master key encrypted by calling ctSecureMasterKey as a member of the ctGetSecretParams_t structure.

Both functions are called by the server code in ctcryp.c. If the version does not match or ctGetSecret returns something different than 0, the master key will not be loaded and the server will be shut down.

To correctly return the encrypted master key, the following must be called to encrypt the master key before returning 0 in ctGetSecret:

int ctSecureMasterKey(ctSecureMasterKeyParams_t *SecureMasterKeyParams)