Even if you’re the only user of your data warehouse, it is not advised to use the root or admin password.  As a rule and as a precaution you should create additional credentials and a profile for any user that will have access to your DW.  Some systems provide an easier way of doing it than others.

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 
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;