Roles: Changes to How Regrants Are Recorded in the GRANTS_TO_USERS View¶

The output of the GRANTS_TO_USERS view changed in terms of grants of the same role to the same user were recorded:

Previously:

The view included a row for each grant of the same role to the same user. The DELETED_ON column value is NULL for the row containing the active grant. When a regrant occurs, the row containing the previous grant has the DELETED_ON column value updated to the timestamp for when the regrant occurred.

Currently:

The view includes one row for the grant of the same role to the same user. Regrants of the same role to the same user are not recorded as new rows. The DELETED_ON column remains NULL while the grant is active, and the column value is updated when the role is REVOKED from the user.

After revoking the role from the user, a grant of the same role to the same user will be recorded in a new row. In this new row, the DELETED_ON column value is NULL because the grant is now active.

Use the following query to help identify whether your account has records in the view that will be affected:

• TRUE: There are records in the view that will be affected.

• FALSE: There are no records in the view that will be affected.

SELECT
    COUNT(*) > 0 AS IS_IMPACTED
FROM
    SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS AS GL
        INNER JOIN SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS AS GR
            ON GL.ROLE = GR.ROLE
            AND GL.GRANTED_TO = GR.GRANTED_TO
            AND GL.GRANTEE_NAME = GR.GRANTEE_NAME
            AND GL.GRANTED_BY = GR.GRANTED_BY
            AND GL.DELETED_ON = GR.CREATED_ON
            AND GR.DELETED_ON IS NOT NULL;

Ref: 1132