Table: snowflake_role_grant - Query Snowflake Role Grants using SQL

Snowflake Role Grant is a feature within Snowflake that allows you to manage and control access to database objects. It provides a way to assign privileges to roles, which can then be granted to users or other roles. Snowflake Role Grant helps you ensure appropriate access levels and permissions across your Snowflake resources.

Table Usage Guide

The snowflake_role_grant table provides insights into Role Grants within Snowflake. As a Database Administrator, you can use this table to explore details about roles and the privileges granted to them. This information can be used to manage access control, enforce security policies, and audit role-based permissions in your Snowflake environment.

Examples

List users granted the ACCOUNTADMIN role

Determine the users who have been granted the highest level of access within your system. This is useful for auditing security and managing permissions.

select
  role,
  granted_to,
  grantee_name,
  granted_by,
  created_on
from
  snowflake_role_grant
where
  role = 'ACCOUNTADMIN'
  and granted_to = 'USER';

select
  role,
  granted_to,
  grantee_name,
  granted_by,
  created_on
from
  snowflake_role_grant
where
  role = 'ACCOUNTADMIN'
  and granted_to = 'USER';

List roles granted the SYSADMIN role

Identify instances where the SYSADMIN role has been granted. This query is useful for understanding who has been given this high-level access and by whom, helping maintain security and manage permissions effectively.

select
  role,
  granted_to,
  grantee_name,
  granted_by,
  created_on
from
  snowflake_role_grant
where
  role = 'SYSADMIN'
  and granted_to = 'ROLE';

select
  role,
  granted_to,
  grantee_name,
  granted_by,
  created_on
from
  snowflake_role_grant
where
  role = 'SYSADMIN'
  and granted_to = 'ROLE';

Control examples

Schema for snowflake_role_grant

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
account	text	=, !=, ~~, ~~, !~~, !~~	The Snowflake account ID.
created_on	timestamp with time zone		Date and time when the role was granted to the user/role.
granted_by	text		Name of the object that granted access on the role.
granted_to	text		Type of the object. Valid values USER and ROLE.
grantee_name	text		Name of the object role has been granted.
region	text		The Snowflake region in which the account is located.
role	text	=	Name of the role on that access has been granted.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- snowflake

You can pass the configuration to the command with the --config argument:

steampipe_export_snowflake --config '<your_config>' snowflake_role_grant