{{ section.section }}

  1. Connecting to Holistics
  2. Create Database User

Creating Database User

Permissions needed for the Holistics user as below:

  1. Holistics Data Reporting: Read access is required
  2. Holistics Data Preparation: Both Read and Write access is needed, as Holistics will write to your destination table

Creating User & Granting Read Permissions to Your Tables

Depending on your set up, you may have opted to create a new user to connect exclusively to Holistics as a security measure. The connected user must have been granted SELECT permissions on these tables in order to access certain tables in Holistics.

Assuming that the DB user is named holistics, the below shows you how to create the DB user, and grant read 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'@'%';

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;

Oracle Database

  • Oracle's schema, basically the set of all tables and other objects owned by user account, so 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;

Granting Write Permissions

If you're using Data Preparation module, write permission is needed since it will create/drop tables and insert/update/delete data into tables.

First, follow guide above, and then do the below steps for granting write access:

MySQL 

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

PostgreSQL 

-- remember to repeat this for other schemas too
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO holistics;

Amazon Redshift 

-- remember to repeat this for other schemas too
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO holistics;