OpengateMsql - Management database



Scripts are prepared in the archive.  Set up the database before the starting the system.

tar xzvf opengatem0.0.0.tar.gz
cd opengatem0.0.0/sqlscript

mysql < createtablescript
mysql < insertmacscript  (Optional)
php < ../phpsrc/updatevendortable.php

Tables and fields in Database

Tables in MySql databese is as follows.

TABLE opengatem.macaddrs = List of registered MAC addresses

 Field Name
 Meaning  Commentary
 macAddress  MAC Address

 The daemon just compare character string as [2 charactors(numeric/lowercase) separated with colon. e.g., 12:34:56:78:9a:bc].   The data format should be checked at insertion.
  It is not set as UNIQUE, because of inspecting usage of previous owner.  The MAC address having status other than 'D' should not overlap at registration.
 The count of registration for one user should be restricted.

 status  Status Flag

 'A'=Active, 'I'=Inactive, 'D'=Deleted, 'P'=Pause.
  If the delete address is found in log, the record is preserved with status 'D'. If not found, delete the record.
  Flag 'I' is set by the administrator by some reason. The record with 'I' cannot be modified by the user.
 The pause by user is realized by status 'P'. The record with 'P' can be modified by the user.

 device  Device Name
 Device name to distinguish devices. It is not a control parameter.
 Alpha, numeric and other few characters are permitted to prevent side effect of HTML and SQL.
 userId  User ID
 Save in usage log.
 extraId  Extra ID

  Optional ID for user. Do not use now.

 mailAddress  Mail address
 The user's mail address  The update system sends the limit warning mail to that address, if it is set.
 entryDate  Entry Date
 The date/time of registering this record.
 renewDate  Renewal Date
  The date/time of updating this record.
limitDate  Limit Date
The expiration date/time.  When you change status to 'D' or pause the device usage, set this value to now.
     This is the main table holding the relation of MAC address and the owner of each device.At removing the registration, the record does not delete, but is modified as (starus='D').  Thus the macAddress is not Unique.
 As the records increases gradually, it is recommended to detele old records periodially. The sample sql script is included in archive.

TABLE opengatem.sessionmd = Usage Log

 Field Name
 Meaning  Commentary
macAddress  MAC address
 MAC address for the terminal
ipAddress  IP address
 IP address for the terminal
gatewayName  Gateway Doain Name
 To distiguish the gateway to use.
openTime  Open Time
 The time adding firewall pass rule for the terminal.
closeTime  Close Time
 The time deleting firewall pass rule for the terminal.

  The table holds the log and is displayed in update page.
 Records are expired after 1 month. When this table is dropped, the log function is disabled automatically.

TABLE opengatem.macippair = Log of MAC-IP pair (Drop the table from opengatem1.1.0)

 Field Name
 Meaning  Commentary
macAddress  MAC address
 MAC address for the terminal
ipAddress  IP address
 IP address for the terminal
findTime  Find Time
 The time finding the address pair.

TABLE opengatem.macmodify = Modify log for MAC address registration table (expired after 1 day from opengatem1.1.0)

 Field Name
userId  User ID
 User ID requesting the modification
extraId  Extra ID
 Optional ID for the user. Do not use now
macAddress  MAC address
 Modified MAC address
modifyType  Modification Type
'R'=Register, 'E'=Extend, 'D'=Delete, 'I'=Inactivate, 'A'=Acitivate, 'P'=Pause
modifyDate Modification Date
 The date/time of modification
 The table is used to check the too many modification disturbing the service.
 The old records (before 1day from now) are removed.

TABLE opengatem.nicvendors = Vendor list of Network Interface card

 Field Name
oui Left half of MAC address
 The address field corresponding to the name of network card vendor
org  Vendor Name
 Organization name downloaded from IEEE site.

  In address check page, the information supports the discrimination of devices. This table is needless when the check page is not used.
 It is recommended to update when '?' is shown in check page. The data in IEEE site is updated daily. Update method is in archive.

TABLE opengatem.watchlist = Address list for specific watching

Field Name  Meaning Commentary
macAddress MAC address   If opengatemd detects a packet having this source MAC address,  the detection is reported via syslog.
Address format is 2 charactors(numeric/lowercase) separated with colon.
If a record equals to "ALL", all addresses are reported.
(use in daemon)
reporting reporting flag   If "Y", send report. Otherwise do not send report
(use in daemon).
memo memo   memo to write the reason of specific watching
     The table is needless in normal service.  Refer Specific addresses

VIEW opengatem.sessionview = View made from macaddrs and sessionmd (Drop view from opengatem1.1.0)

Field Name
userId, extraId, macAddress, device, openTime, closeTime, gatewayName