Create Holistics DB User

Permissions Required

Permissions needed for the Holistics database user:

1. Data Reporting: Read Access
2. Data Preparation: Read Access + Write Access
3. Data Modeling: Read Access + Write Access

Creating a new DB User

Depending on your setup and security requirements, you may have to create a new user account to connect exclusively to Holistics.

To use Data Reporting module, the user must have been granted SELECT permissions on necessary tables. For Data Preparation and Data Modeling modules, write permission is needed since they will create/drop tables, as well as perform data insert/update/delete.

Granting Write Permissions

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'@'%';

Then privileges for the new user

-- grant all for this user

GRANT ALL PRIVILEGES ON mydb.* TO 'holistics'@'%';

Error Connecting: Authentication plugin 'caching_sha2_password' cannot be loaded

If you are unlucky to see this error when trying to connect to MySQL

Error Connecting: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

Please change the password to mysql_native_password:

ALTER USER 'holistics'@'%' IDENTIFIED WITH mysql_native_password BY 'USE_A_NICE_STRONG_PASSWORD_PLEASE';

And try again.

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 public TO holistics;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO holistics;

-- remember to repeat this for other schemas too

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO holistics;

Oracle Database

• 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 public TO holistics;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO holistics;

-- remember to repeat this for other schemas too

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO holistics;