Specific MySQL server permissions are necessary for AYS users to access the data and run reports. Some additional permissions may be necessary for those that administer the AYS database on the server. This article will assist you in configuring the security on MySQL for both of these cases.
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.
The new database will be created on the server with an automatic prefix of AYS_, so the database named “yourdatabase” can be found on the server as “AYS_yourdatabase”.
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.
Details of this section is coming soon