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)
			DEFAULT_ROLE
			IsSSOEnabled

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.

Roles.ShowRoles

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

RoleMembers

Members of Roles (other Roles and Users).

RoleMembers.CreatedTimeline

Timeline of Role Member assignment/creation.

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.

Grants.Type.Counts

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

Grants.Type.Privilege

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

Grants.Role.Privilege

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

Grants.Lst.[ObjectType]

List of Grants for this specific Object Type. Identical to contents of Grants.

Grants.Tbl.[ObjectType]

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

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