Access Control System – Database and Linux
Administration
V 1.00 5/8/2010 Ben Davis
MySQL Database Administration
The MySQL database is the heart of the Access Control System. It holds all the users,
settings, and authorized cards hashes necessary for system operation. For the most part,
setup and administration of the database can be accomplished through the web server
interface for routine tasks such as adding and removing users. However, some advanced
tasks may require direct database access.
Logging into the Database
Command line control of the MySQL database software is accomplished through the
‘mysql’ client utility. Database access has been restricted to localhost connections only,
so you must first SSH into the eBox. Once you have a command shell, execute the
following command:
“mysql –uroot –p buzzcard”
A prompt will appear requesting you enter the root password. Enter the password and
press enter and you will be greeted with the MySQL prompt “mysql>”.
Administering the Database
Once logged in as root, you can do literally anything you desire to the database. Be
careful! If you know the SQL language, you can reference the document “SQL Database
Schema – Specification and Description” for the details of the buzzcard database format
and execute whatever commands/queries you like.
However, a few critical commands are summarized here for those unfamiliar with SQL.
Clearing the Log
The system log is stored in the table SYSTEM_LOG. From time to time you may want to
“Dumping/Backing Up Database Tables” section of this document. Clearing the log can
by performed using the following SQL statement:
DELETE FROM SYSTEM_LOG;
The semicolon signifies the end of the statement and should not be neglected.
Fixing the Root User
While precautions have been taken in the system design to help prevent accidental
lockout/removal of the root user, in the case of such issues the root user can be restored in
the following ways.
If the root user still exists but has been locked out:
UPDATE USER_LIST SET P_HASH= '63upJAC07TpcU', IS_ADMIN=1,
CAN_LOCK=1 WHERE USER_ID='root';
If the root user has somehow been deleted:
INSERT INTO USER_LIST (USER_ID, NAME, P_HASH, IS_ADMIN, CAN_LOCK)
VALUES ('root', 'Root User', '63upJAC07TpcU', 1, 1);
These commands will restore all privileges to root and reset the root password to
“rootroot”.
Dumping/Backing Up Database Tables
A special utility is provided for making database table dumps which is separate from the
‘mysql’ client program. This command is ‘mysqldump’. It has a format similar to the
‘mysql’ command.
“mysqldump –uroot –p buzzcard [table names]”
Again, you will be prompted for the password. If you do not specify specific tables in
Executed as is, this command will just print output to the screen. It may be useful to
redirect output to a file.
“mysqldump –uroot –p buzzcard [table names] > <filename>”
For example:
“mysqldump –uroot –p buzzcard SYSTEM_LOG > log.sql”
This will back up the system log to the file “log.sql”.
Restoring the Database to “Factory” Condition
In the event of disaster, a script “DBTables.sql” is provided in the website source archive.
This script will wipe out the buzzcard database and restore it in an empty state. All users,
cards, events, and settings will be lost if not backed up.
The script can be executed in the following way:
“mysql –uroot –p buzzcard < <path to script>”
So if you placed DBTables.sql in /root:
“mysql –uroot –p buzzcard < /root/DBTables.sql”
Alternatively, you can login to the MySQL client and type:
“\. <path to script>”
Backslash followed by a period commands the client to execute a script file. This
command is also useful for restoring the database from saved backup scripts (created
with mysqldump).
Serial Daemon Configuration
The serial bridge daemon program “basd” is located in /usr/sbin. Its configuration file
configuration of which is found in /etc/conf.d/local.start. The following command is
used.
“/usr/sbin/basd /etc/basd.conf 2>> /var/log/basderr.log > /dev/null &”
Note that the configuration file location is a command line parameter to the basd
program. If it is not supplied, basd will look in the current directory for the config file
and if not found, adopt a hard coded default configuration (see source code “settings.c”).
Most of the options in /etc/basd.conf should not have to be changed. Some things are
tweakable, such as door open duration, command timeout interval, and number of times a
command should be retried on failure. Changes to the database setup or credentials may
require updating the database parameters in this file. A default commented basd.conf is
provided in the source package on the project website.
ODBC DSN Configuration
An ODBC DSN is a way to save preconfigured settings for a particular database under a
simple name. It also provides a standard interface for any number of distinct DBMS
system type, such as MySQL, PostgreSQL, MSSQL, etc.
The eBox uses the unixODBC package to run ODBC. The configuration files are located
in /etc/unixODBC. The file odbc.ini defines DSNs, and the file odbcinst.ini defines
drivers that are installed in the system.
The ACS database is currently defined as the “buzzcard” DSN in odbc.ini. This should
not need to be changed, but if the need arises consult ODBC/MySQL documentation for
instructions on how to make the DSN do what you want.
If the DSN name is changed, it will need to be updated in /etc/basd.conf and
