How to Configure MySQL for AYS

Step 1. Create a new login

Launch MySQL Workbench and create a new login, or create the user using SQL CREATE LOGIN statements. This is one example of how you can configure your user:

You should choose Authentication Type according to your network configuration. “Standard” is not required for this and AYS will work with any of the MySQL password options.

The corresponding SQL statements are:

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

Step 2. Grant required rights to create databases to the new user

At least one new user will need rights to create databases so that AYS can construct the correct database structure. Choose “Administrative Roles” from the tabs at the top and choose the following privileges from the right side:

The following equivalent SQL command may be used at the MySQL command line instead:

GRANT ALTER, CREATE, CREATE VIEW, DELETE, INDEX, INSERT, SELECT, SHOW DATABASES, SHOW VIEW, UPDATE on *.* to 'username'@'%';

All of these privileges are for the administrative user, and will not be necessary for all users if they simply connect to the database and enter or edit information.

Step 3.  Create the AYS database

Open AYS and select File – New Database – Empty MySQL Database… enter your SQL login information.  User ID and password will be obscured with asterisks.

If you have an existing Access database, you can instead choose File – New Database – Import AYS Database to New MySQL Database.

Click Login and choose a database name.   When you click “Create” the database should be created on the server.

4. Grant basic database rights to all the users

The user ID used to create the database will be the database owner and will have all the rights necessary on the SQL database.  For other users, you can grant them more restricted permissions on the database, rather than globally across the server.