Roles: Changes to How Regrants Are Recorded in the GRANTS_TO_USERS View¶
This behavior change is in the 2023_04 bundle.
For the current status of the bundle, refer to Bundle History.
The output of the GRANTS_TO_USERS view changed in terms of grants of the same role to the same user were recorded:
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.
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;