Question 396582 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – Database specific user in AWS Redshift

I’ve created 2 databases in Redshift engine:

  • database_production
  • database_development

Each database have 2 schemas: public and custom.

I want to create user called developer who has all privileges in database_development database but can’t connect and modify any schema in database_production.

How to create developer users and grand/revoke desired permissions on database_development only?

Tags:

2

Answers


  1. 0

    You can use GRANT, you can check here how works grant. Following the documentation, the line to be executed would be:

    GRANT ALL PRIVILEGES ON DATABASE database_development TO developer
    Login or Signup to reply.
  2. 0

    Connect to Redshift and create/grant user:

    CREATE USER developer WITH PASSWORD 'testingtesitng';

#Grant access to database_development
GRANT CONNECT ON DATABASE database_development TO developer;
GRANT USAGE ON SCHEMA public TO developer;
GRANT USAGE ON SCHEMA custom TO developer;

#Grant privileges on tables in the schemas
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO developer;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA custom TO developer;

#Grant privileges on sequences in the schemas
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO developer;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA custom TO developer;

    Also you may double-check if this user can’t access to database_production just try to connect to this DB and do smth there.

    And revoke desired permissions:

    # Revoke any access to database_production
REVOKE ALL PRIVILEGES ON DATABASE database_production FROM developer;
REVOKE CONNECT ON DATABASE database_production FROM developer;
REVOKE USAGE ON SCHEMA public FROM developer;
REVOKE USAGE ON SCHEMA custom FROM developer;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search