Documentation

Read Only User

Every time you create a new chart or open a dashboard in datapine the respective SQL queries will be sent to your database. In order to be able to send those requests to your database we need a read-only user, bound to one of our IP addresses. This user may have access to all data in your database or have only limited access rights to specific tables or columns within your data schema. In this tutorial, you will learn how to create a new read-only user in your database and how to grant him the necessary privileges to be able to connect to datapine.

 

Create a read-only user in PostgreSQL

 

1) Create a new User

 

Connect to your PostgreSQL server with the following command.

SUDO –U POSTGRES SQL  

Select the database you want to connect to datapine. 

\c <db name>

Create a new user to connect to datapine. 

CREATE ROLE <user name> LOGIN PASSWORD <password>;

 

2) Assign the necessary privileges to the new user

 

Grant connect to database

GRANT CONNECT ON DATABASE <db name>  to  <user name>;

Grant usage on schema

GRANT USAGE ON SCHEMA <schema name> TO <user name>;

Grant select on all tables in the schema

 GRANT SELECT ON ALL TABLES IN SCHEMA <schema name> TO <user>

Grant the user the access to all new tables added in future

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema name> GRANT SELECT ON TABLES TO <user name>;

 

Create a read-only user in SQL SERVER

 

1) Create a new User to connect to datapine

 

Select the database you want to connect to datapine.

USE <db name>;

 Create a Login for your user.

CREATE LOGIN <user name> WITH PASSWORD = “<password>”;

Create new user to connect to datapine.

CREATE USER <user name> FOR LOGIN <user name>;

2) Grant the read-only access to your user 

USE database_name;

EXEC sp_addrolemember db_datareader, <user name>;

Create a read-only user in MySQL

 

1) Create a new user in your MySQL shell

 

Select the database you want to connect to datapine

USE ‘db name’;

Create the user you want to use to connect to datapine. 

CREATE USER ‘user name’@’localhost’ IDENTIFIED BY ‘password’;

 2) Grant your user access to your database

GRANT SELECT ON ‘db name’.* TO ‘user name’@’localhost’;

Create a read-only user in Oracle

 

1) Create a new user in your Oracle instance 

CREATE USER ‘user name’ IDENTIFIED BY ‘password’;

GRANT CREATE SESSION TO datapine_user;

GRANT CONNECT TO datapine_user;

2) Grant your user access to your Oracle database

 

After you have created a user you will need to grant permission to all the database tables one by one. Doing so you can use a loop:

BEGIN

FOR R IN (SELECT owner, table_name FROM dba_tables WHERE owner=’schema_name’) LOOP

EXECUTE IMMEDIATE ‘grant select on ‘||R.owner||’.”‘||R.table_name||’” to ‘user name’;

END LOOP;

END;