Table: snowflake_role - Query Snowflake Roles using SQL

Snowflake Roles are a key component of the Snowflake data warehousing service's access control architecture. They dictate the level of access that a user has to Snowflake objects, such as databases, schemas, and warehouses. Roles can be assigned to users, other roles, and integration objects to facilitate granular, role-based access control.

Table Usage Guide

The snowflake_role table provides insights into the roles within Snowflake's access control architecture. If you are a security analyst or administrator, you can use this table to explore role-specific details, including the permissions assigned to each role and the users and roles to which each role is assigned. This table can be particularly useful for auditing access controls, identifying overly permissive roles, and ensuring compliance with your organization's access policies.

Examples

Basic info

Explore the roles within your Snowflake environment, including when they were created and their associated permissions. This can help enhance security by ensuring only necessary permissions are granted.

select
  name,
  created_on,
  granted_roles,
  granted_to_roles
from
  snowflake_role;

select
  name,
  created_on,
  granted_roles,
  granted_to_roles
from
  snowflake_role;

List idle roles

Discover the roles that are currently idle, meaning they are not assigned to any users. This can be useful for identifying potential areas of resource optimization or unnecessary access permissions.

select
  name,
  created_on,
  assigned_to_users
from
  snowflake_role
where
  assigned_to_users = 0;

select
  name,
  created_on,
  assigned_to_users
from
  snowflake_role
where
  assigned_to_users = 0;

List roles with assigned users

Explore which roles have been assigned to users in Snowflake, allowing you to manage user access and permissions effectively. This is useful in maintaining security and ensuring only authorized users have certain privileges.

select
  name as role_name,
  grantee_name
from
  snowflake_role
  inner join snowflake_role_grant on snowflake_role.name = snowflake_role_grant.role
where
  assigned_to_users > 0
  and granted_to = 'USER';

select
  name as role_name,
  grantee_name
from
  snowflake_role
  inner join snowflake_role_grant on snowflake_role.name = snowflake_role_grant.role
where
  assigned_to_users > 0
  and granted_to = 'USER';

Schema for snowflake_role

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
account	text	The Snowflake account ID.
assigned_to_users	bigint	Number of users the role is assigned.
comment	text	Comment for the role.
created_on	timestamp with time zone	Date and time when the role was created.
granted_roles	bigint	Number of roles inherited by this role.
granted_to_roles	bigint	Number of roles that inherit the privileges of this role.
is_current	text	"Y" if is the current role of authenticated user, otherwise "F".
is_default	text	"Y" if is the default role of authenticated user, otherwise "F".
is_inherited	text	"Y" if current role is inherited by authenticated user, otherwise "F".
name	text	Name of the role.
owner	text	Owner of the role.
region	text	The Snowflake region in which the account is located.

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