英文:
what privileges do we need to grant to a user to access table constraints in public schema in redshift
问题
我已登录为非管理员用户。information_schema.tables、information_schema.columns、information_schema.views这些都没问题。问题似乎出现在information_schema.table_constraints和information_schema.key_column_usage，它们没有提供任何数据。
我尝试授予用户对public和information_schema模式的select权限。
英文:
I have logged in as a non-admin user. information_schema.tables, information_schema.columns, information_schema.views, these are fine. The Problem seems to be with information_schema.table_constraints, information_schema.key_column_usage, they are not giving any data.
I tried granting select permissions on public and information_schema schemas to the user.
答案1
得分: 1
在Amazon Redshift中，不是所有用户都能查看他们不拥有的对象的元数据。在您的情况下，虽然您已经授予了public和information_schema模式上的SELECT权限，但这些权限不一定适用于目录表和视图。
为了访问information_schema.table_constraints和information_schema.key_column_usage，用户需要对这些视图所引用的对象具有特定的访问权限。
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO youruser;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO youruser;
授予这些权限后，您的非管理员用户应该能够获取所需的信息。
您可以查看官方文档来管理权限。
英文:
In Amazon Redshift, not all users can view metadata about objects that they do not own. In your case, while you've granted the SELECT permissions on the public and information_schema schemas, these permissions do not necessarily apply to catalog tables and views.
In order to access information_schema.table_constraints and information_schema.key_column_usage, the user needs to have specific access rights for the objects that these views refer to.
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO youruser;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO youruser;
After granting these privileges, your non-admin user should be able to get the desired information
You can check out the official Docs for managing permission.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论