Table Reports

Report Contents

Data	Pivot
Contents
Users	Users.CreatedTimeline
Users.ShowUsers
Roles	Role Type, Roles.CreatedTimeline
Roles.Hierarchy
Roles.ShowRoles
RoleMembers	RoleMembers.CreatedTimeline
RolesMembers.ShowGrants
Grants	Grants.Type.Counts, Grants.Type.Privilege, Grants.Role.Privilege
Grants.Lst.[ObjectType]
Grants.Tbl.[ObjectType]
DB.[DatabaseName]

Contents

This sheet provides quick access to the contents of this report and provides at-a-glance number of rows in each of the sheet's tables.

Column	Data Type	Purpose
Sheet Name	String	Name of the sheet in the report
Num Entities	Integer	Number of Entities in that sheet
Link	Hyperlink	Link to the sheet in the report

Users

List of Users and their properties.

Produced by running SHOW USERS (https://docs.snowflake.com/en/sql-reference/sql/show-users.html) and DESCRIBE USER (https://docs.snowflake.com/en/sql-reference/sql/desc-user.html) commands.

Properties in UPPERCASE are documented in ALTER USER (https://docs.snowflake.com/en/sql-reference/sql/alter-user.html) command.

Column	Data Type	Purpose
NAME	String	Name of the User object
LOGIN_NAME	String	Login name of the User
Owner	String	Role that owns this User object
IsSSOEnabled	Boolean	Is User SSO enabled (no password)
IsObjectIdentifierSpecialCharacters	Boolean	Does User name include special characters that would cause it to need to be quoted
CreatedOn	Datetime	When User was created on, local time
CreatedOnUTC	Datetime	When User was created on, UTC time
FIRST_NAME	String
LAST_NAME	String
MIDDLE_NAME	String
DISPLAY_NAME	String
EMAIL	String
COMMENT	String
DEFAULT_ROLE	String
DEFAULT_NAMESPACE	String
DEFAULT_WAREHOUSE	String
DISABLED	Boolean
MUST_CHANGE_PASSWORD	Boolean
DAYS_TO_EXPIRY	Integer
MINS_TO_BYPASS_MFA	Integer
MINS_TO_BYPASS_NETWORK_POLICY	Integer
MINS_TO_UNLOCK	Integer
SNOWFLAKE_LOCK	String
SNOWFLAKE_SUPPORT	String
EXT_AUTHN_DUO	Boolean
EXT_AUTHN_UID	String
RSA_PUBLIC_KEY_FP	String
RSA_PUBLIC_KEY_2_FP	String
PASSWORD_LAST_SET_TIME	Datetime
PASSWORD_LAST_SET_TIMEUTC	Datetime

Users.CreatedTimeline

Timeline of User creation.

This sheet uses data in Users sheet as source for pivot table, showing Counts of Users created by Owners over time.

Columns	Rows	Values	Filters
Owner
	Years
	Months
	Days
	Hours
	CreatedOn
		NumUsers (Count)
			DEFAULT_ROLE
			IsSSOEnabled

In this example, we are showing all Users created over years by different Owner Roles:

Full Size

Users.ShowUsers

List of Users that is a direct result of SHOW USERS command.

Roles

List of Roles and their properties.

Produced by running SHOW ROLES (https://docs.snowflake.com/en/sql-reference/sql/show-roles.html) and combining it with results of SHOW GRANTS TO and SHOW GRANTS ON commands.

Column	Data Type	Purpose
Name	String	Name of the Role object
Owner	String	Role that owns this Role object
Type	String	Type of this Role (see Role Type section below)
IsInherited	Boolean	Is this Role inherited
NumAssignedUsers	Integer	Number of Users assigned to this Role
NumChildRoles	Integer	Number of Roles that this Role contains
NumParentRoles	Integer	Number of Roles that contain this Role
AssignedUsers	String	List of Users assigned to this Role
ChildRoles	String	List of Roles that this Role contains
ParentRoles	String	List of Roles that contain this Role
AncestryPaths	String	List of all the unique paths to the parent Role
NumAncestryPaths	Integer	Number of all the unique paths to the parent Role
Comment	String
IsObjectIdentifierSpecialCharacters	String	Does Role name include special characters that would cause it to need to be quoted
CreatedOn	Datetime	When Role was created on, local time
CreatedOnUTC	Datetime	When Role was created on, UTC time

Role Type

Role type is determined by examining Role Names, hierarchy and grants.

BuiltIn

Set for ACCOUNTADMIN, SECURITYADMIN, USERADMIN, SYSADMIN roles.

SCIM

Set for children of USERADMIN roles that are named OKTA_PROVISIONER, AAD_PROVISIONER and GENERIC_SCIM_PROVISIONER.

RoleManagement

Set for children of SECURITYADMIN or USERADMIN roles that are not already classified as SCIM.

Access and AccessNotUnderSysadmin

Set to Access if the role Grants include at least one of each:

• SCHEMA permissions other than USAGE, OWNERSHIP and MONITOR
• TABLE permisions other than USAGE, OWNERSHIP, REFERENCES or REBUILD
• VIEW permisions other than USAGE, OWNERSHIP, REFERENCES or REBUILD

If the role is Access but does not roll up to SYSADMIN, set to AccessNotUnderSysadmin.

Functional and FunctionalNotUnderSysadmin

Set to Functional is role is not Access and includes at least one USAGE grant on another Role.

If the role is Functional but does not roll up to SYSADMIN, set to FunctionalNotUnderSysadmin.

NotUnderAccountAdmin

When none of the above checks succeed and role does not rolls up to ACCOUNTADMIN.

Unknown

When none of the above checks succeed and role rolls up to ACCOUNTADMIN.

Roles.CreatedTimeline

Timeline of Role creation.

This sheet uses data in Roles sheet as source for pivot table, showing Counts of Roles created by Owners over time.

Columns	Rows	Values	Filters
Owner
	Years
	Months
	Days
	Hours
	CreatedOn
		NumRoles (Count)
			Type

In this example, we are showing all Roles created over years and months by different Owner Roles:

Full Size

Roles.Hierarchy

Hierarchy of Roles in tabular form.

Column	Data Type	Purpose
Name	String	Name of the Role object
GrantedTo	String	Role that this Role object is granted to
DirectAncestry	String	TODO
AncestryPaths	String	List of all the unique paths to the parent Role
NumAncestryPaths	Integer	Number of all the unique paths to the parent Role
ImportantAncestor	String	TODO
Type	String	Type of this Role (see Role Type section below)

Roles.ShowRoles

List of Roles that is a direct result of SHOW ROLES command.

RoleMembers

Members of Roles (other Roles and Users).

Column	Data Type	Purpose
Name	String	Name of the Role being granted
ObjectType	String	Type of the object being granted (ROLE or USER)
GrantedTo	String	Role or User is being granted to the Role
GrantedBy	String	Role doing the granting
CreatedOn	Datetime	When Role Membership was created on, local time
CreatedOnUTC	Datetime	When Role Membership was created on, UTC time

RoleMembers.CreatedTimeline

Timeline of Role Member assignment.

This sheet uses data in RoleMembers sheet as source for pivot table, showing Counts of Roles and Users assigned by GrantedBy over time.

Columns	Rows	Values	Filters
GrantedBy
	Years
	Months
	Days
	Hours
	CreatedOn
		NumMembers (Count)
			ObjectType

In this example, we are showing all Users created over years and months by different granting Roles:

Full Size

RolesMembers.ShowGrants

List of Role Members that is a direct result of SHOW GRANTS OF command.

Grants

List of Grants from SHOW GRANTS TO and SHOW GRANTS ON commands.

Column	Data Type	Purpose
Privilege	String	Name of the Privilege being granted
ObjectType	String	Type of the object that the Privilege is being granted on
ObjectName	String	Full name of the object that the Privilege is being granted on
GrantedTo	String	Role that the Privilege is being granted to
DBName	String	Name of Database if the object is inside of one
SchemaName	String	Name of Schema if the object is inside of one
EntityName	String	Short name of the object that the Privilege is being granted on
GrantedBy	String	Role doing the granting
WithGrantOption	Bool	Is the Privilege granted with Grant Option
CreatedOn	Datetime	When Grant was created on, local time
CreatedOnUTC	Datetime	When Grant was created on, UTC time

Grants.Type.Counts

Pivot table of Grants showing Counts by Object Type/Object Name/Privilege/Granted To.

Columns	Rows	Values	Filters
	ObjectType
	ObjectName
	Privilege
	GrantedTo
		NumGrants (Count)
			GrantedBy

In this example, we are showing all Grants granted by specific Role to, grouped by Object Type and Object being granted on:

Full Size

Grants.Type.Privilege

Pivot of Grants showing Counts of Privileges by Object Name/Granted To.

Columns	Rows	Values	Filters
Privilege
	ObjectName
	GrantedTo
		NumGrants (Count)
			GrantedBy
			ObjectType

In this example, we are showing all Grants granted to specific Object Type, grouped by Object being granted and showing distinct Privileges:

Full Size

Grants.Role.Privilege

Pivot of Grants showing Counts of Privileges by Granted To/Object Type/Object Name.

Columns	Rows	Values	Filters
Privilege
	GrantedTo
	ObjectType
	ObjectName
		NumGrants (Count)
			GrantedBy

In this example, we are showing all Grants granted to specific Roles, filtered by Object Type, showing distinct Privileges on those Objects:

Full Size

Grants.Lst.[ObjectType]

List of Grants for this specific Object Type. Structure is identical that of Grants.

Grants.Tbl.[ObjectType]

Table of Grants for this specific Object Type, with Roles in the rows and Privileges in columns.

Column	Data Type	Purpose
ObjectType	String	Type of the object that the Privilege is being granted on
ObjectName	String	Full name of the object that the Privilege is being granted on
GrantedTo	String	Role that the Privilege is being granted to
DBName	String	Name of Database if the object is inside of one
SchemaName	String	Name of Schema if the object is inside of one
EntityName	String	Short name of the object that the Privilege is being granted on
Privilege	String	Privilege being granted. X means Granted, X+ means Granted with Grant Option

In this example, we are showing all Grants for the TABLE Object Type:

Full Size

DB.[DatabaseName]

Single screen view of all permissions in Database, including collapsable groups under each Schema, color-coding of permissions, and popups for long lists of permissions

Column	Data Type	Purpose
Type	String	Type of the object (DATABASE, SCHEMA, TABLE or VIEW)
Full Name	String	Full name of the object
Short Name	String	Short name of the object
Role Name	String	Role that is being used

In this example, we are showing all Grants for the TABLE Object Type:

Full Size