Insights Analytics User Creation

During the installation, ConnectALL allows you to provide two separate users—a read-write ConnectALL database user to set up the ConnectALL DB and another user (read-only) for Insights Analytics that allows ConnectALL Insights Analytics to connect to the database. We have provided the scripts below to create the Insights Analytics read-only user in various databases. Replace <db_name>/<scheme_name> (should probably be 'Insights'), <user_name>, and <password> with appropriate values.

MySQL

CREATE USER <user_name>@'%' IDENTIFIED BY <password>;GRANT SELECT, SHOW VIEW ON <DB_NAME>.* TO <user_name>@'%';

CODE

PostgreSQL

CREATE ROLE <user_name> LOGIN PASSWORD <password>;
GRANT CONNECT ON DATABASE <DB_NAME> to <user_name>;
GRANT USAGE ON SCHEMA <SCHEMA_NAME> TO <user_name>;
GRANT SELECT ON ALL TABLES IN SCHEMA <SCHEMA_NAME> TO <user_name>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <SCHEMA_NAME> 
GRANT SELECT ON TABLES TO <user_name>;

CODE

MSSQL

USE <DB_NAME>;
CREATE LOGIN <user_name> WITH PASSWORD = '<password>';
CREATE USER <user_name> FOR LOGIN <user_name>;
USE <DB_NAME>;
EXEC sp_addrolemember db_datareader, <user name>;

CODE

Viewing Hierarchy Plugin

When creating a user for ConnectALL Insights Analytics, note that the read-only user created for Insights Analytics may need additional privileges to view the Insights Hierarchy plugin and to populate the Days table. In such a scenario, you need to grant further specific access. To give that access, you need to run the below script (after the installation program has finished).

GRANT EXECUTE ON FUNCTION <DB_NAME>.discrete_percent_done TO <user_name>@'%'; 
GRANT EXECUTE ON FUNCTION <DB_NAME>.populate_days TO <user_name>@'%';
FLUSH PRIVILEGES;

CODE

Note that,

In the above script, you need to replace <DBName> & <username> with appropriate values.
You can execute this script only after creating the Insights schema and the read-only user.