How do I use advanced admin views to manage object visibility and permissions in Amazon Redshift?

Short description

Note: You must have administrative access to create and use admin views.

Admin views show the following information:

• Database object metadata details
• User access rights and permission levels
• System performance metrics
• Current resource usage
• Active security settings and configurations

For more information, see Amazon Redshift admin views on the GitHub website.

Resolution

To verify that an admin schema exists in each target database, run the following query in each database:

SHOW SCHEMAS FROM DATABASE databaseName LIKE 'admin'

Note: Replace databaseName with your database name.

Manage user permissions

To create a view that shows grant or revoke permissions for users and groups in the database, see v_generate_user_grant_revoke_ddl.sql on the GitHub website. Use this view to recreate user or group permissions or to revoke permissions before you drop a user or group.

To get the data definition language (DDL) for a user's permissions to tables and views, see v_generate_user_object_permissions.sql on the GitHub website.

Audit object access

To get the table or views that a user has access to, see v_get_obj_priv_by_user.sql on the GitHub website.

To get the schemas that a user has access to, see v_get_schema_priv_by_user.sql on the GitHub website.

To get the views that a user has access, see v_get_view_priv_by_user.sql on the GitHub website.

Manage group access

To get the tables that a user group has access to, see v_get_tbl_priv_by_group.sql on the GitHub website.

Related information

How do I resolve the "user cannot be dropped" error in Amazon Redshift?