Setup Database and Create a Read-Only user in AWS Redshift and Mysql

In the case of Amazon’s Redshift, it is not very straightforward.  Because sometimes, flexibility comes with complexity. And that’s what we encountered when we tried to create a user with read-only access to a specific schema.  Here’s what you will need to achieve this task:

• Setup Database in Redshift 

  Contents Collapse

  Setup Database in Redshift

  Create Read-only User in Redshift

  Create read-only user on MySQL

create database [db-name];

revoke all on schema public from public;

create schema [schema-name];

create table [schema-name].[table-name](

  ...

) 

• Create Read-only User in Redshift 

create user [user-name] password '[password]';

grant usage on schema [schema-name] to [user-name];

alter user [user-name] set search_path to [schema-name];

grant select on table [schema-name].[table-name] to [user-name];

• Create read-only user on MySQL

CREATE USER r_only_user identified by 'password';
GRANT SELECT, SHOW VIEW, PROCESS, REPLICATION CLIENT ON *.* TO 'r_only_user'@'%' IDENTIFIED BY 'password';
GRANT SELECT, SHOW VIEW, PROCESS, REPLICATION CLIENT ON *.* TO 'r_only_user'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;