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.
CREATE USER <user_name>@'%' IDENTIFIED BY <password>;GRANT SELECT, SHOW VIEW ON <DB_NAME>.* TO <user_name>@'%';
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>;
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>;
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;
- 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.