3. Grant Database Access to Zoola User – Zoola

Now that the firewall and database are ready to accept connections from Zoola, you will need to create a user, which can used for accessing your LMS data.To do this you will need to log into the database and execute a command, which creates the Zoola user and assigns them the suitable permissions for access to your data. Zoola requires READ ACCESS ONLY (SELECT ACCESS) as it is used purely for data retrieval and inspection.

This process can be executed through a Command Line Interface (CLI) or via a User Interface that your database may use. Below are the commands that need to be executed for MySQL and PostgreSQL database systems.

MySQL

From the Moodle or Totara server, access the database that is associated with Moodle or Totara. If you don’t know the database that is used for your LMS you can view Moodle or Totara’s config.php file, which details the database, database user and database password.

You can access the database by using the following command (please change ##DBNAME##, ##DBUSER## and ##DBPASSWORD## for the information associated with your configuration).

mysql -h ##DBNAME## -u ##DBUSER## -p

You will be prompted for the password after executing this command.

Once you have accessed the database you can input the three commands to create the Zoola User and assign the appropriate permissions. Please assign this user a new password (##PASSWORD##). These commands create a user called ‘zoolauser’ with password ##PASSWORD##. This user is granted SELECT (Read Access permissions), required to use SSL and restrict so they can only access for the three IP addresses.

GRANT SELECT ON ##DBNAME##.* TO 'zoolauser'@'52.60.195.209' IDENTIFIED BY '##PASSWORD##' REQUIRE SSL;
GRANT SELECT ON ##DBNAME##.* TO 'zoolauser'@'52.60.242.178' IDENTIFIED BY '##PASSWORD##'  REQUIRE SSL;

In order to execute these commands your database user must have GRANT permissions. The most suitable user for executing these steps is the administrator or superuser account.

For more information to grant permissions, please visit: https://dev.mysql.com/doc/refman/5.7/en/grant.html

After the commands have been executed your database is now ready to allow connections from Zoola and allow queries to be executed.

PostgreSQL

You can access the database by using the following command (please change ##DBNAME##, ##DBUSER## and ##DBPASSWORD## for the information associated with your configuration).

psql ##DBNAME## ##DBUSER##

You will be prompted for the password after executing this command.

Once you have accessed the database you can input the three commands to create the Zoola User and assign the appropriate permissions. Please assign this user a new password (##PASSWORD##).

GRANT SELECT ON ALL TABLES IN SCHEMA ##DBNAME## TO zoolauser;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA ##DBNAME## TO zoolauser;

To restrict the zoolauser to work with specific IP addresses you will need to edit the pg_hba.conf file with the following Zoola IP addresses.

52.60.195.209
52.60.242.178

This will ensure that the zoolauser is only accessible by Zoola Analytics.

In order to execute these commands your database user must have GRANT permissions. The most suitable user for executing these steps is the administrator or superuser account.

For more information to grant permissions, please visit: https://www.postgresql.org/docs/current/static/sql-grant.html

After the commands have been executed your database is now ready to allow connections from Zoola and allow queries to be executed.