MySQL insufficient permissions

Overview

For appliance version 2.09, CMI 0.9w the permissions on the MySQL database for the user pinsafe need to be configured to use the MySQL database.

Prerequisites

PINsafe appliance hardware or VMWare build 2.09

Symptoms

Cannot create MySQL Database

192.168.0.1 admin:Unable to open the database: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: INDEX command denied to user 'pinsafe'@'localhost' for table 'PINSAFEM'

192.168.0.1 admin:PINsafe data migration failed! com.swiveltechnologies.pinsafe.user.database.DatabaseException: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: INDEX command denied to user 'pinsafe'@'localhost' for table 'PINSAFEM'

Solution

For production systems, the PINSAFEK table may need to be edited to reflect the current version, see PINsafe upgrade fails on MySQL appliance

An alternative is to recreate the database, as below, however THIS SHOULD NOT BE USED ON PRODUCTION SYSTEMS WITH DATA

Login to the appliance webmin function default: https://192.168.0.35:10000/

To remove the pinsafe database Select Servers/MySQL Database Server. Under MySQL Databases put a tick into the pinsafe database, then click on Drop Selected Databases. A message appears;

Are you sure you want to drop the 1 selected databases? 5 tables containing 0 rows of data will be deleted.

Click on Drop Databases to confirm. The pinsafe database will be removed.

To create the pinsafe database Select Servers/MySQL Database Server. Under MySQL Databases select Create a new database. Enter Database name pinsafe and leave all other options as default then click on create. The PINsafe database will now be listed as a MySQL Database.

To change the pinsafe user permissions Select Servers/MySQL Database Server

Under Global Options select User Permissions

The pinsafe user has Permissions set to None

Select pinsafe user

Grant permissions to the pinsafe user by clicking on the top entry in Permissions (Select table Data), and pressing shift to select the bottom value (Create User), all the Permissions should be highlighted.

Click on Save to save the values.

The User Permissions screen should now show the PINsafe user as having Permissions All.

Users can now be migrated to the pinsafe database. If errors persist regarding table 'PINSAFEL' already exists it may be necessary to recreate the pinsafe database, see MySQL Removing and Creating a database