Database User - acli/ATutor GitHub Wiki

In Step 2 during the installation process, the wizard asks you for the name of a Database User without explaining what kind of database user this is. This “database user” is in fact a non-admin mysql user with the Select, Insert, Update, Create, Alter, and Delete privileges.

After going through the installation process a few dozen times, a few observations can be made:

1. It needs to be able to create the atutor database and populate it with tables.
2. Its name and password are saved, in plaintext, in include/config.inc.php

In the top-level ATutor directory there is a file newuser.sql which is not referenced by any file. However, it is reasonable to assume that this is a template for us to create the “Database User” that the installer asks us to create. So it might be worth noting that newuser.sql will create a user which only has the following privileges:

• Select_priv
• Insert_priv
• Update_priv
• Create_priv
• Alter_priv
• Delete_priv

Note that Create_priv includes the ability to create databases, so a user created in this way would be able to successfully create the atutor table.

Based on the above observations, we should not be using a real mysql admin user in step 2, but a user specifically created to run ATutor. This user should only have the six privileges mentioned in newuser.sql.

Note that newuser.sql is syntactically invalid. It specifies six _priv fields but only five 'Y' values. The correct command should therefore look like this:

 USE mysql;
 INSERT INTO user (Host,User,Password,Select_priv,Insert_priv,Update_priv,Create_priv, Alter_priv, Delete_priv)
 VALUES('localhost','NEWUSER_MYSQL',PASSWORD('NEWPWD_MYSQL'),'Y','Y','Y','Y','Y','Y');

On MacOS X (or any MySQL installation with “strict SQL” mode turned on), you might run into an “ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value”. If that happens, your version of MySQL is expecting three extra fields — viz. ssl_cipher, x509_issuer, and x509_subject — and the command should look like this:

 USE mysql;
 INSERT INTO user (Host,User,Password,ssl_cipher,x509_issuer,x509_subject,Select_priv,Insert_priv,Update_priv,Create_priv, Alter_priv, Delete_priv)
 VALUES('localhost','NEWUSER_MYSQL',PASSWORD('NEWPWD_MYSQL'),'','','','Y','Y','Y','Y','Y','Y');

The above discussion points to several gaps in the installer’s usability:

• The installer does not refer to newuser.sql as a template to create a special mysql user for ATutor use.