Getting a Complete List of User Privileges in Snowflake

Snowflake in the Carolinas > Security > RBAC > Getting a Complete List of User Privileges in Snowflake
Posted on by Greg Pavlik

These queries don’t need much explanation. I’ve had some customers request how to get a complete list of user privileges, often for auditing purposes. The two queries below will show the role hierarchy (which roles have been granted which other roles) and a complete list of effective permissions for each user.

For instance, if someone grants user ‘MARY’ the ‘PLAN_9’ role, and that role has a privilege to select from ‘TABLE_X”, then one row in the result will show that MARY can select from TABLE_X because she’s been granted the PLAN_9 role. All other users in the PLAN_9 role will also show a row with this set of user, role granting the privilege, and then the privilege itself.

Snowflake enforces a best practice for security and governance called RBAC, role based access control. Privileges go to roles, not directly to users. To grant a user a privilege, add the user to a role with the privilege.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
-- 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
;

Leave a Reply

Your email address will not be published. Required fields are marked *

Theme: Overlay by Kaira.