Snowflake data steward discovery based on role hierarchy

Question

Snowflake follows the role-based access control (RBAC) paradigm. Best practice for RBAC is, to have functional and access roles managing either user and clients or access privileges. This creates in worst-case a variety of roles that inherits permissions from and to each other. By nature, one can easily lose sight.

In snowflake, grants to roles and users are stored in ACCESS_USAGE.GRANTS_TO_ROLES and ACCESS_USAGE.GRANTS_TO_USERS. What is a proper approach to identify the data stewards/owner of a role automatically (if not labeled explicitly in a 3rd party tooling)?

Options I thought of:

• recursive lookup of OWNERSHIP privileges of roles of roles (will generate a lot of false positives)
• recursive discovery of a service account that has advanced permission to a role and lookup the service account owner
• lookup over usage pattern of executed queries (might be actually more consumers than producers)

Answer 1

A couple of options:

1. Populate the role’s comment field with the relevant Data Steward information
2. Use Tags (in public preview)