OpengateMsql - Management database

Summary

This is a MySql database system to integrate various data necessary for OpengateM.
This is accessed from gateways via network.
Beside this, SqLite3 database works on each gateway to hold temporary data.

Installation

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

createtablescript: To create tables.
insertmacscript: To insert preserved MAC addresses. It is only a sample. Edit this and run, if you needed.
../phpsrc/updatevendortable.php: To update table for NIC vendors

Tables and fields in Database

Tables in MySql databese is as follows. Some tables' capacity increase with use.

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.

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.

　　
It is necessary to delete old records periodically, because capacity increases with time.

TABLE opengatem.macippair = Log of MAC-IP pair

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.
		It is necessary to delete old records periodically, because capacity increases with time.

TABLE opengatem.macmodify = Modify log for MAC address registration table

Field Name
Meaning
Commentary

　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

　　 It is necessary to delete old records periodically, because capacity increases with time.

TABLE opengatem.nicvendors = Vendor list of Network Interface card 　

Field Name
Meaning
Commentary

　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.

　　　
It is recommended to update when '?' is shown in mchk 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. Refer Specific addresses 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 (not use in daemon)

VIEW opengatem.sessionview = View made from macaddrs and sessionmd

　Field Name

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