script to check if the schema part of the data share or not in aws Redshift database:
SELECT * FROM SVV_DATASHARE_OBJECTS where object_type='schema' and object_name='<schema_name>';
share_type|share_name |object_type|object_name|producer_account|producer_namespace
----------+-----------------+-----------+-----------+----------------+--------------------
outbound |abcprod_k1222_ds|schema |schema_name|122234555555 |1234567890
script to check select access for entire schema in AWS Redshift database.
SELECT
tablename,
usename
FROM
pg_catalog.pg_tables AS tables,
pg_catalog.pg_user AS users
WHERE
tables.schemaname = '<schema_name>'
AND users.usename = 'username@abc.com'
AND tables.schemaname not like 'pg_%'
AND NOT HAS_TABLE_PRIVILEGE(users.usename, tables.tablename, 'select');
script to check user has select,insert,update,delete access on specific schema in AWS Redshift Database:
SELECT
tablename
,usename
,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'select') AS sel
,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'insert') AS ins
,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'update') AS upd
,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'delete') AS del
FROM
(SELECT * from FROM pg_catalog.pg_tables
WHERE schemaname = '<schema_name>' ) as tables
,(SELECT * FROM pg_catalog.pg_user where usename='username@abc.com') AS users;
script to check user has select,insert,update,delete access on specific table in schema in AWS Redshift Database
SELECT
tablename
,usename
,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'select') AS sel
,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'insert') AS ins
,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'update') AS upd
,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'delete') AS del
FROM
(SELECT * from FROM pg_catalog.pg_tables
WHERE schemaname = '<schema_name>' and tablename in ('dm_sales_rptg_ecomm_new')) as tables
,(SELECT * FROM pg_catalog.pg_user where usename='username@abc.com') AS users;
