Understanding EMG database usage (EMG 5.2 and earlier) Print
Monday, 17 October 2011

This document applies to EMG 5.2 and earlier. For EMG 5.3 and later, click here.

EMG can be used together with a MySQL database for improved flexibility with user authentication and for logging and statistics.

In order to use a database it must be set up and a database profile must be added to the "server.cfg" configuration file.

This procedure is described in the EMG User's Guide.

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" (or "md5password", if set) 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 (as an integer) and the network mask (a value from 0 to 32), so that 3232235776 ("192.168.1.0" as an integer) 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 (ipaddress "0.0.0.0" as an integer, "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.

To convert an IP address to integer the following formula is used:

IP address: x.y.z.n
IP address as integer: x * 256^3 + y * 256^2 + z * 256 + n

So, for 192.168.1.0 we calculate 192 * 256^3 + 168 * 256^2 + 1 * 256 + n =3232235776

Hashed passwords

If you want to use hashed passwords, the field "md5password" should be set to the MD5 hash of the password. The field password should be set to NULL.

For example MD5 hash for string "secret" is "5ebe2294ecd0e0f08eab7690d2a6ee69".

Message credits and prepaid vs postpaid

When users are authenticated from database the credits handling will be used when sending messages. For each message the authenticated user sends the "creditssend" column will be decreased. If balance (value of creditssend) goes below 0 and allowpostpaid is not set, 0 or NULL, further sending will be rejected until balance is updated to a value > 0.

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.

Database columns

Table emguser

Column Description Updated by EMG?
userid Auto-generated unique id (primary key)
created Timestamp when user created
username Username
password Password (clear text)
md5password Password (MD5 hash)
creditssend Credits for sending, integer part
Yes
creditssenddec Credits for sending, decimal part in 1 / 1 000 000
creditsreceive NOT USED
maxsessions Max number of simultaneous session
throughput Max throughput
lastlogin Last successful login Yes
lastfailedlogin Last failed login Yes
lastip IP address last seen Yes
lockeduntil Account locked until time specified
allowpostpaid Is creditssend < 0 allowed? (1 = Yes)
idletimeout

failedlogins NOT USED
usergroup User is administrator if set to 'ADMIN'
fullname Name of customer contact person
company Company name
route User-specific route (connector name)
routedlr User specific route (connector name) for delivery reports
routing File name for user-specific routing file to use
routesat User-specific route for SAT replies
phone Customer phone number
foresourceaddr Forced source address for messages received from customer
satpoolcreate User-specific SAT pool
charges File name for charges (billing info)
extra1 Extra field for information of choice
extra2 Extra field for information of choice
extra3 Extra field for information of choice
extra4 Extra field for information of choice
extra5 Extra field for information of choice
extra6 Extra field for information of choice
extra7 Extra field for information of choice
extra8 Extra field for information of choice
extra9 Extra field for information of choice

Table routelog

All columns are written by EMG.

Column Description
msgid EMGs unique message id
starttime Timestamp when entry created (message received)
startmsecs Millisecs for starttime
endtime Timestamp when message sent out
endmsecs Miilisecs for endtime
inconnector Connector name where message received
outconnector Connector name where message sent out
sourceaddr Sender address
destaddr Recipient address
status Message status (MGP status code)
reason Protocol error code on errir
startsecs Timestamp when message received (secs since Jan 1, 1970)
endsecs Timestamp when message sent out (secs since Jan 1, 1970)
note Message note (option MGP_OPTION_NOTE)
username Authenticated user when message received by EMG
smscid Message id received when sent out
reasontext Reason text from delivery report, if any
lastdlrsecs Timestamp when last delivery report received for message (secs since Jan 1, 1970)
lastdlrmsecs Millisecs for lastdlrsecs
npdus Actual pdus when message sent out (message parts)
bufferedstatus Status reported by intermediate delivery report
msgtype Message type (1 = SMS)
origid Id of original message in SAT conversations