如何查询Snowflake中某架构下所有视图的用户及权限

以下是主流数据库系统对应的查询语句，替换语句中的你的架构名为目标架构即可：

MySQL #

通过information_schema.TABLE_PRIVILEGES直接获取视图的权限信息：

SELECT
  TABLE_SCHEMA AS 架构名,
  TABLE_NAME AS 视图名,
  GRANTEE AS 用户,
  PRIVILEGE_TYPE AS 权限
FROM
  information_schema.TABLE_PRIVILEGES
WHERE
  TABLE_SCHEMA = '你的架构名'
  AND TABLE_TYPE = 'VIEW';

PostgreSQL #

利用系统视图pg_class、pg_namespace和pg_privs关联查询：

SELECT
  n.nspname AS 架构名,
  c.relname AS 视图名,
  pg_get_userbyid(p.grantee) AS 用户,
  p.privilege_type AS 权限
FROM
  pg_class c
JOIN
  pg_namespace n ON c.relnamespace = n.oid
JOIN
  pg_privs p ON c.oid = p.objoid
WHERE
  n.nspname = '你的架构名'
  AND c.relkind = 'v'; -- relkind='v'代表视图

SQL Server #

关联sys.views、sys.database_permissions和sys.database_principals系统视图：

SELECT
  SCHEMA_NAME(v.schema_id) AS 架构名,
  v.name AS 视图名,
  dp.name AS 用户,
  perm.permission_name AS 权限
FROM
  sys.views v
JOIN
  sys.database_permissions perm ON v.object_id = perm.major_id
JOIN
  sys.database_principals dp ON perm.grantee_principal_id = dp.principal_id
WHERE
  SCHEMA_NAME(v.schema_id) = '你的架构名';

内容的提问来源于stack exchange，提问作者Priya d