查询以获取雪花中所有角色及其关联用户的列表 [英] Query to get list of all roles and their associated users in snowflake
本文介绍了查询以获取雪花中所有角色及其关联用户的列表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我希望在Snowflake中的单个查询中获得所有用户的列表以及分配给这些用户的角色。
SHOW GRANTS TO ROLE1; --> This gives me list of all users assigned to ROLE1
但是我不能做下面这样的事情-
SHOW GRANTS TO ROLE1
UNION ALL
SHOW GRANTS TO ROLE2
UNION ALL
SHOW GRANTS TO ROLE3;
目标是在雪花的单个查询中实现上述功能。
问候您, 瑜伽瑜伽
推荐答案
我写了一篇博文here,解释如何获取角色层次结构(每个用户都是其中成员的角色列表)和有效权限(所有授予类型的每个用户的完整权限列表)。我认为第一个查询可能更接近您的要求,但我将两者都包括在内:
-- The data returned by both queries is in the
-- SNOWFLAKE database, which has latency of up
-- to 3 hours to reflect changes
-- Get the effective role hierarchy for each user.
with
-- CTE gets all the roles each role is granted
ROLE_MEMBERSHIPS(ROLE_GRANTEE, ROLE_GRANTED_THROUGH_ROLE)
as
(
select GRANTEE_NAME, "NAME"
from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
where GRANTED_TO = 'ROLE' and
GRANTED_ON = 'ROLE' and
DELETED_ON is null
),
-- CTE gets all roles a user is granted
USER_MEMBERSHIPS(ROLE_GRANTED_TO_USER, USER_GRANTEE, GRANTED_BY)
as
(
select ROLE,
GRANTEE_NAME,
GRANTED_BY
from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
where DELETED_ON is null
)
--
select
USER_GRANTEE,
case
when ROLE_GRANTED_THROUGH_ROLE is null
then ROLE_GRANTED_TO_USER
else ROLE_GRANTED_THROUGH_ROLE
end
EFFECTIVE_ROLE,
GRANTED_BY,
ROLE_GRANTEE,
ROLE_GRANTED_TO_USER,
ROLE_GRANTED_THROUGH_ROLE
from USER_MEMBERSHIPS U
left join ROLE_MEMBERSHIPS R
on U.ROLE_GRANTED_TO_USER = R.ROLE_GRANTEE
;
--------------------------------------------------------------------------------------------------
-- This gets all the grants for all of the users:
with
ROLE_MEMBERSHIPS
(
ROLE_GRANTEE,
ROLE_GRANTED_THROUGH_ROLE
)
as
(
-- This lists all the roles a role is in
select GRANTEE_NAME, "NAME"
from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
where GRANTED_TO = 'ROLE' and
GRANTED_ON = 'ROLE' and
DELETED_ON is null
),
USER_MEMBERSHIPS
(
ROLE_GRANTED_TO_USER,
USER_GRANTEE,
GRANTED_BY
)
as
(
select ROLE,GRANTEE_NAME,GRANTED_BY
from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
where DELETED_ON is null
),
EFFECTIVE_ROLES
(
USER_GRANTEE,
EFFECTIVE_ROLE,
GRANTED_BY,
ROLE_GRANTEE,
ROLE_GRANTED_TO_USER,
ROLE_GRANTED_THROUGH_ROLE
)
as
(
select
USER_GRANTEE,
case
when ROLE_GRANTED_THROUGH_ROLE is null
then ROLE_GRANTED_TO_USER
else ROLE_GRANTED_THROUGH_ROLE
end
EFFECTIVE_ROLE,
GRANTED_BY,
ROLE_GRANTEE,
ROLE_GRANTED_TO_USER,
ROLE_GRANTED_THROUGH_ROLE
from USER_MEMBERSHIPS U
left join ROLE_MEMBERSHIPS R
on U.ROLE_GRANTED_TO_USER = R.ROLE_GRANTEE
),
GRANT_LIST
(
CREATED_ON,
MODIFIED_ON,
PRIVILEGE,
GRANTED_ON,
"NAME",
TABLE_CATALOG,
TABLE_SCHEMA,
GRANTED_TO,
GRANTEE_NAME,
GRANT_OPTION
)
as
(
-- This shows all the grants (other than to roles)
select CREATED_ON,
MODIFIED_ON,
PRIVILEGE,
"NAME",
TABLE_CATALOG,
TABLE_SCHEMA,
GRANTED_TO,
GRANTEE_NAME,
GRANT_OPTION,
GRANTED_ON
from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
where GRANTED_ON <> 'ROLE' and
PRIVILEGE <> 'USAGE' and
DELETED_ON is null
)
select * from EFFECTIVE_ROLES R
left join GRANT_LIST G
on G.GRANTED_TO = R.EFFECTIVE_ROLE
where G.PRIVILEGE is not null
;
这篇关于查询以获取雪花中所有角色及其关联用户的列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文