Authentication

EMG can use the database when authenticating incoming connections. This is enabled by replacing the "USERS" keyword in the connector configuration with the "USERDB" keyword. The tables involved are "emguser" and "emguseraccess", where "emguser" contains user credentials and "emguseraccess" contains IP addresses from which the user is allowed to connect.

Please note that there must exist entries for the specific user in both tables, or the authentication will fail.

EMG validates the provided username and password combination towards the fields "username" and "password" in "emguser" and at the same time joins in entries from "emguseraccess". If username and password matches, the entries from "emguseraccess" will be traversed to look for an entry matching the source IP address of the user. Entries in "emguseraccess" contains the "userid" (id from "emguser" table), "ipaddress", "ipmask" and "connector". Fields "ipaddress" and "mask" contains the IP address and the network mask (a value from 0 to 32), so that "192.168.1.0" and "24" matches the class C network "192.168.1.0/255.255.255.0". The value for "connector" can either be a specific connector name or the special value "*" which means any connector.

In order to allow access on any connector from any IP address for a specific user the values would be "0.0.0.0" (ipaddress), "0" (ipmask) and "*" (connector) respectively. However, this is the least secure option and would not be recommended if it can be avoided.

User information is never cached within EMG and therefore any updates of the information in the user tables will take effect immediately (on next user login attempt). However, session already active are not affected.

Logging

The message log was initially written to the table "connectorlog" in the same way as the connector log files are created in the file system. However, it is no longer recommended to use "connectorlog" but instead to use the "routelog" table which is more powerful and efficient.

Use of the "routelog" table is enabled by adding "ROUTELOGDB" to the general part of server.cfg. In addition to "routelog" there are two more tables used for message logging, "messageoption" and "messagebody". In "messageoption" all message options that do not have their own field in "routelog" are added "key-value" style while the actual message body is written to "messagebody".

When a message is first received over a connector it is assigned a message id and an entry is created in "routelog" including "msgid", "starttime" (second resolution), "startmsecs" (msec resolution), "inconnector" (where message is received) and some message options (source and destination address etc). Also the field "username" specified the authenticated user that submitted the message and the field "messagetype" contains a "1" to indicate a normal message, while "5" indicates a delivery report. After the message has been routed and sent out the "outconnector", "endsecs" and "endmsecs" fields in "routelog" table are updated.

Usually a delivery report (DLR) is requested and when such a delivery report is received a new entry is created for the DLR in "routelog" with message type "5" and the original message is also updated with the "status" as indicated in the DLR plus the timestamps "lastdlrsecs" and "lastdlrmsecs". In the DLR entry the "origid" field is set to the message id of the original message to facilitate matching of messages and DLRs in the logs.

Sometimes a message received is split within EMG and sent as out multiple message parts (for example when EMG acts as a e-mail to SMS gateway). When that happens the "npdus" field is set to the number of message parts sent out so that billing can be performed correctly.

Some SMSCs can be configured to send back delivery reports even if the message has not reached its final status, for example when handset is switched off and first delivery attempt fails. EMG can handle these intermediate delivery reports and update the "bufferedstatus" field in "routelog". The actual values of that status is protocol and SMSC specific.

In order to determine delivery time it is important to understand that the difference between "endsecs" and "starttime" usually indicates the time the message spent within EMG while the difference between "lastdlrsecs" and "starttime" indicates the approximate delivery time when the message has reached it final state ("delivered"). This applies to SMSC protocols (SMPP, UCP, CIMD2 etc) when delivery reports are used while for HTTP and SMTP there are no delivery reports involved and therefore only "endsecs" will be available and "lastdlrsecs" will never be updated.

The "routelog" table should provide enough information to provide powerful tools for message tracking and statistics. When joined with "messageoption" and "messagebody" even more detailed message information is available. It is generally wise to "trim" the "messageoption" and/or "messagebody" regularly if it is not needed any more. It may also be a good idea to replicate the information onto a second database instance if complex queries are done frequently in order to ensure that the production environment is not overloaded.