Redshift – Grant SELECT on All Tables

permissionsredshift

I am trying to assign SELECT privilege to a group in Redshift. So I created a group and a user in that group:

CREATE GROUP data_viewers;
CREATE USER <user> PASSWORD '<password>' IN GROUP data_viewers;

Now I would like to allow this group to be able to read data from any table:

GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO GROUP data_viewers;

The command returns GRANT. Now when I connect to Redshift as my newly created user and issue SELECT * FROM something.something; I get:

permission denied for schema something

I tried granting permissions to something: GRANT SELECT ON ALL TABLES IN SCHEMA something TO GROUP data_viewers; but this has not changed anything.

How can I allow users from my group to SELECT data from any table in the schema?

Best Answer

You need the USAGE privilege (at least) for the schema as well:

GRANT USAGE ON SCHEMA something TO GROUP data_viewers;

Related Postgres example:

Permission for sequence in another schema

Remember you only granted permissions to already existing tables. Does not apply to tables created later. To cover those, too:

ALTER DEFAULT PRIVILEGES FOR USER role_that_creates_tables
IN SCHEMA public
GRANT SELECT ON TABLES TO GROUP data_viewers;

Amazon Redshift implemented DEFAULT PRIVILEGES as well.

Here is a complete cookbook for Postgres:

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

Be aware of some differences between mainline Postgres and Redshift! Redshift sticks to separate users and groups, while Postgres replaced that with the universal concept of roles:

Why did PostgreSQL merge users and groups into roles?

And I am not sure how Redshift handles sequences ...

SUGGESTION #1

Check the user in the Stored Procedure and leave if it is not the right user

DELIMITER //
CREATE DEFINER=`billy`@`%` PROCEDURE `fetchData`(IN `id` INT UNSIGNED)
  LANGUAGE SQL
  NOT DETERMINISTIC
  READS SQL DATA
  SQL SECURITY DEFINER
  COMMENT 'Accepts an ID and returns the record'
ThisStoredProcedure:BEGIN
  SET @cur_user = CURRENT_USER();
  IF LEFT(@cur_user,LOCATE('@',@cur_user) - 1) = 'otherUser' THEN
    LEAVE ThisStoredProcedure;
  END IF:
  SELECT T.id, T.name
    FROM table AS T
    WHERE T.id = id;
END;
//
DELIMITER ;

SUGGESTION #2

Create New Proc with SQL Security to Invoker and change grants of otherUser to match

DELIMITER //
CREATE DEFINER=`billy`@`%` PROCEDURE `fetchDataForOthers`(IN `id` INT UNSIGNED)
  LANGUAGE SQL
  NOT DETERMINISTIC
  READS SQL DATA
  SQL SECURITY INVOKER
  COMMENT 'Accepts an ID and returns the record'
BEGIN
  SELECT T.id, T.name
    FROM table AS T
    WHERE T.id = id;
END;
//
DELIMITER ;

Best Answer

Related Solutions

MySQL Permissions – How to Grant Additional Permissions

Mysql – GRANT EXECUTE ON PROCEDURE unable to USE database

SUGGESTION #1

SUGGESTION #2

Related Question