CODEWITHSUNDEEP
sqlamazon-redshift
user2728024
user2728024

Reputation: 1546

how to view the list of users who have access to a redshift table?

How do i get a list of users who have access to a specific redshift table/schema?

Upvotes: 0

Views: 5767

Answers (2)

demircioglu
demircioglu

Reputation: 3455

Here is a SQL gives you select/insert/update/delete privileges for each object

You can use schema, table, user filter while using/commenting the ones you don't at the end of the SQL.

    SELECT * 
    FROM 
        (
        SELECT 
            schemaname
            ,objectname
            ,usename
            ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'select') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS sel
            ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'insert') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS ins
            ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'update') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS upd
            ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'delete') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS del
            ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'references') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS ref
        FROM
            (
            SELECT schemaname, 't' AS obj_type, tablename AS objectname, schemaname + '.' + tablename AS fullobj FROM pg_tables
            UNION
            SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views
            ) AS objs
            ,(SELECT * FROM pg_user) AS usrs
        ORDER BY fullobj
        )
    WHERE (sel = true or ins = true or upd = true or del = true or ref = true)
    and usename = '<user>'.  -- for a user filter
    and schemaname = '<schema>'. -- for a schema filter
    and objectname = '<table or view>'. -- for a table filter

Output would look like this

schemaname          objectname          usename sel     ins     upd     del     ref
information_schema  applicable_roles    user1   true    false   false   false   false
information_schema  check_constraints   user1   true    false   false   false   false
information_schema  column_domain_usage user1   true    false   false   false   false

If you like to see only users with access to specifics schema/table use the required filter(s) and change the first line to

SELECT distinct usename

Upvotes: 1

Moseleyi
Moseleyi

Reputation: 2859

PostgreSQL has something called System Information Functions, which you can read about here: https://www.postgresql.org/docs/9.1/static/functions-info.html

The function that could be of interest to you is has_table_privilege, which takes three arguments:

user
table name,
privelege

When I need to find out for example what roles can insert on my users table I execute the following:

SELECT rolname FROM pg_roles WHERE has_table_privilege(rolname, '<table_name>', 'INSERT')

However these functions in conjunction with tables pg_roles, pg_user, and other tables can give you very detailed information about privileges.

Also you didn't specify what kind of access (INSERT, SELECT, DELETE) but thanks to the third argument you can either combine it or get a list of users that have any.

Upvotes: 0

Related Questions