0

I am looking into a system table: stl_load_errors and I have created a view on top of stl_load_errors to restrict the data.

sample view: 

create view vw_sample_load_errors
as
select * from stl_load_errors where filename like 'sample123%'

Now, when a regular user queries the view, the user is unable to see any rows.
How do I give him access to this view alone.

I do not want to give syslog access which will give user unlimited access to all data in all system tables including stl_load_errors.

Let me know if it is clear enough.

Til
  • 5,150
  • 13
  • 26
  • 34
Shreepal Bhandari
  • 3
  • 1

1 Answers1

0

There is no way to achieve what you want using Redshift permissions. Users can either see only their own rows in system tables (the default) or they can see all rows in all system tables (if SYSLOG ACCESS is set to UNRESTRICTED). There is no way to grant the equivalent of SYSLOG ACCESS UNRESTRICTED for a single system table.

As Jon suggested in the comments, you could create a process that copied data out of stl_load_errors into another table every minute or so and grant user permissions on that.

Nathan Griffiths
  • 12,277
  • 2
  • 34
  • 51
  • Thanks for your answer, one quick follow-up.. How do I copy into another table every minute, I get the insert script but how do I make it run every other minute? – Shreepal Bhandari Jan 17 '19 at 05:17
  • You'll need some external tool or scheduler to execute the script on a schedule. There are some suggestions here: https://stackoverflow.com/questions/42564910/how-to-execute-scheduled-sql-script-on-amazon-redshift – Nathan Griffiths Jan 17 '19 at 09:42