User management- Creating groups , users , owners in Amazon redshift

User management- Creating groups , users , owners in Amazon redshift 

 Below are some sample commands to create user groups, adding users, managing permission on table in Amazon redshift.

 1) To create a user use the below command in amazon redshift:

create user my_user password 'anaconda123'; 

In the above example, user with name my_user is created with password anaconda123.

You can later change the password using the command:

alter user my_user password 'mynewpassword123'

 2) To create a user_group use the below command in amazon redshift:

create group my_group with user my_user;

In the above command, a new group with name my_group is created and the user my_user is added to that group.

 3) To create a database schema use the below command in Amazon redshift:

 create schema myschema authorization my_user;

In the above example, schema myschema is created with my_user being provided all the permission on the schema.

 4) To assign user group to a schema use the below command in  Amazon redshift:

 grant all on schema myschema to group my_group;

 5) To add or drop users to a user group use the below command in Amazon redshift:

ALTER GROUP group_name ADD USER username;

ALTER GROUP group_name DROP USER username;

ALTER GROUP group_name  RENAME TO new_name;

 6) To check permissions on the tables in amazon redshift:

select * from pg_tables /* provides all table owner info */
select * from pg_views  /* provided table owner info */
select * from pg_shadow  /* provides user info */
select * from pg_group  /* provided user group info */

 7) To add permissions on the tables in amazon redshift:

Use sample below to add permissions on a table in redshift

Create table MYTABLE (
    COLUMN1          CHAR(1)                       NOT NULL,
    COLUMN2     CHARACTER VARYING(100)        NOT NULL)
distkey (COLUMN1);

GRANT  ALL ON TABLE MYTABLE TO mstr_user;

ALTER TABLE  MYTABLE owner TO my_user;