CODEWITHSUNDEEP
sqlamazon-redshiftrow-level-security
user11490966
user11490966

Reputation: 31

How to implement row level security (RLS) for raw sql access to amazon Redshift?

In the past I have implemented Row level security in Oracle and SQL Server databases using the security policies and predicate functions.

Now my organization is moving to Amazon Redshift as the data warehouse, looking for inputs on ways to implement RLS in Redshift for raw sql access by users.

example: table A has data for many regions. Columns "REGION" in table contains the region for which the row corresponds to. In the user_setup table, each user has the list of regions to which user should have access to.

table A:

account ID balance region

1 100 AUS

2 120 IND

3 50 EUR

table user_setup:

user region

Usr1 AUS

Usr2 AUS

Usr2 EUR

result expected: if Usr1 executes select * from A; Output should be:

account ID balance region

1 100 AUS

if Usr2 executes select * from A; Output should be:

account ID balance region

1 100 AUS

3 50 EUR

if Usr3 executes select * from A; Output should be: No rows returned.

Upvotes: 3

Views: 2540

Answers (1)

user1369384
user1369384

Reputation: 111

I'm guessing after a year you found a solution, but just in case...

Implementing row based security (or row based access control) in AWS Redshift is not straightforward, but doable by:

  1. Adding a table with the desired permissions mapping.
  2. Adding a view which filters the CURRENT_USER against that table, and against the table you want to restrict permission to.
  3. Revoking access to the table itself while granting access to the view you created.

You can find a proof of concept in this Redshift security guide I wrote.

Upvotes: 4

Related Questions