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

In this blog, we explain how to Setup Database and Create a Read-Only user in AWS Redshift and Mysql. 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 and Create a Read-Only user in AWS Redshift and 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];

Refer to Amazon Redshift.
  • Create read-only user on MySQLSetup Database and Create a Read-Only user in AWS Redshift and 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;
End of the article, You have learned how to Setup Database and Create a Read-Only user in AWS Redshift and Mysql.

You will also like to read the below articles.






2 thoughts on “Setup Database and Create a Read-Only user in AWS Redshift and Mysql”

Comments are closed.