Create user with access to view in redshift

Question

I’m pulling data from mysql ec2 instances, to s3 buckets, then creating views in redshift. I want to create database users who can only query and see certain views created specifically for them in Redshift. I have example code below that I use to create the user, view, and grant access. The issue I have is that I also have to grant access to the underlying schema the view is created from, which means the user can see and query tables in that schema. Also the user can see other schemas in the database, even ones they can’t query. Is there a way to only grant users to specific views, and make it so they can’t see other schemas they don’t have access to?

Code:

--create schema
create schema tst_user_schema;

--create view in schema
create view tst_user_schema.inventory_report_customer as (
  select * from user341.inventory_report_customer
  )
with no schema binding;


--creating user
CREATE USER tstuser PASSWORD 'tstPassword';

--grant access
GRANT USAGE ON SCHEMA tst_user_schema TO tstuser;

--grant read access to all tables in schema
GRANT SELECT ON ALL TABLES IN SCHEMA tst_user_schema TO tstuser;

--grant access
GRANT USAGE ON SCHEMA user341 TO tstuser;

--grant read access to all tables in schema
GRANT SELECT ON ALL TABLES IN SCHEMA user341 TO tstuser;


--grant access
GRANT USAGE ON SCHEMA tst_user_schema TO tstuser;

--grant read access to all tables in schema
GRANT SELECT ON ALL TABLES IN SCHEMA tst_user_schema TO tstuser;

Answer 1

to recap:

• schema user341 - contains source tables, user should not be able to select from tables in this schema. You also want to hide it form the user
• tst_user_schema - contains views user is supposed to be able to select from.

Looking at your GRANT statements, you're granting user unnecessarily SELECT permission on ALL TABLES IN SCHEMA user341. For views to work you only need to GRANT USAGE on that schema.

So REVOKE those permissions, and user should not be able to select.

REVOKE SELECT ON ALL TABLES IN SCHEMA user341 FROM tstuser;

Tip: to easily test permissions, you can start a session as tstuser using SET SESSION AUTHORIZATION directive and then test which statements are allowed and which not.

SET SESSION AUTHORIZATION tstuser

Regarding schema visibility - unfortunately there's no way to hide or forbid user from seening all tables and columns in all schemas. One can only restrict access to data.