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

Amazon web services – Redshift Column Permission & "Select *"

According to AWS docs, a user with column permission on a table/view should still be able to perform "SELECT *" and retrieve the column that they have access to.

From Usage notes for column-level:

If you don’t have the SELECT privilege on all columns in a table, performing a SELECT * operation returns only those columns that you have access to.

I am getting denied when I try that.

-- test user

create user test password disable;


-- Column permissions

GRANT USAGE ON SCHEMA source,processed,curated TO test;

grant select (PERNR,TRFGR,BEGDA,ENDDA) on processed.PA0008 to test;


-- Test

set session authorization "test";

select current_user;


-- Test on permitted columns only

select begda,endda,pernr,trfgr from processed.pa0008 limit 100; -- Success

 
-- Test on "*" 

select * from processed.pa0008 limit 100;                       -- ERROR: permission denied for relation pa0008

So explicitly stating columns works, but using "*" fails.

ERROR: permission denied for relation

Does anyone know why?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    From AWS support.. When you specify column-level privileges for a user on a view, you HAVE to enumerate each permitted column's name. This is not the same case for a table, however, for a view this is regular behavior and we cannot use 'SELECT *' to return only the permitted columns. .... but a result set from a view results in a view, as explained in the document: "You can't create a regular view with only accessible columns using SELECT *" [1]


  2. 0

    I can’t recreate the error, so I’m going to assume this is caused by your default privs on the objects being created. It is possible that when you make the test table, the default privs for the user that created the object is restricting permissions behind the scenes.
    https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_DEFAULT_PRIVILEGES.html

    Try a couple of these:

    • Use your Redshift master user (the one that is created when you make your cluster) to create the test table, and try again. That master user may have different default privs
    • Check out the admin views that AWS Labs provides to see what permissions the user has. Specifically, try this view for your new test user and see what permissions it has: https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_user_grant_revoke_ddl.sql
    • Try making 2 new users, switch to one, make the table and grant permissions, then switch to the other and try selecting from the table. That new user may have different default privs.
    • Check to confirm your users aren’t in a group that has restricted or modified permissions.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search