Creating Dedicated Database User for Holistics

It is recommended that you create a new database user/credential to connect to Holistics. This makes it easy to control permission that you want to grant Holistics.

Read-only or write permission?

To function properly, Holistics only requires a read-only permission to your database.

However, if you want to use Imports and Persistence Transforms functionalities, you will need to grant additional write permission. The write permission can be limited to a specific schema in your database.

• Import Models allow you to load data from simple 3rd party places (MongoDB, Google sheets, CSV files) into your SQL database.
• Transform Models (persistent) allow you to pre-aggregate/summarize data and write the results back to your SQL database.

Create user & grant permissions to tables

Assuming that the DB Username is holistics, the below section shows you how to create the DB user, and grant necessary access to it.

MySQL

-- create user
CREATE USER 'holistics'@'%' IDENTIFIED BY 'USE_A_NICE_STRONG_PASSWORD_PLEASE';

-- grant select for this user
GRANT SELECT ON mydb.* TO 'holistics'@'%';

Fail to select data: Access denied for user...

Sometimes when executing the query, you may encounter this error:

Access denied for user 'holistics'@'%' to database 'mydb'

The reason is that behind the scene Holistics also needs permission to run a CREATE TEMPORARY TABLE query to interpret your query result's data types.

CREATE TEMPORARY TABLE `TEMP_TYPES` SELECT * FROM (<you query here>) A LIMIT 0;
DESCRIBE `TEMP_TYPES`;

If Holistics's DB user is not allowed such permission, the query will fail and throw an error. In this case, please grant the user permission to create the temporary table using this command:

GRANT CREATE TEMPORARY TABLES ON mydb.* TO 'holistics'@'%';

-- grant all for this user
GRANT ALL PRIVILEGES ON mydb.* TO 'holistics'@'%';

PostgreSQL

-- create user
CREATE ROLE holistics WITH LOGIN ENCRYPTED PASSWORD 'USE_A_NICE_STRONG_PASSWORD_PLEASE';

-- grant connect privilege
GRANT CONNECT ON DATABASE mydb TO holistics;

-- repeat this for other schemas too
GRANT USAGE ON SCHEMA <your new schema name> TO holistics;
GRANT SELECT ON ALL TABLES IN SCHEMA <your new schema name> TO holistics;

-- remember to repeat this for other schemas too
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <your new schema name> TO holistics;

Oracle

• Oracle's schema is basically the set of all tables and other objects owned by user account, so it is roughly equivalent to a user account.
• Oracle supports four different character data types namely: CHAR, VARCHAR2, NCHAR, NVARCHAR2.

-- create user
CREATE USER holistics IDENTIFIED BY 'USE_A_NICE_STRONG_PASSWORD_PLEASE';

-- grant select for this user
GRANT CONNECT TO holistics;

Amazon Redshift

-- create user
CREATE USER holistics PASSWORD 'USE_A_NICE_STRONG_PASSWORD_PLEASE';

-- repeat this for other schemas too
GRANT USAGE ON SCHEMA <your new schema name> TO holistics;
GRANT SELECT ON ALL TABLES IN SCHEMA <your new schema name> TO holistics;

-- remember to repeat this for other schemas too
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <your new schema name> TO holistics;