Skip to content
This repository has been archived by the owner on Sep 13, 2023. It is now read-only.

Table Reports

Daniel Odievich edited this page Feb 9, 2021 · 15 revisions

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

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

Full Size

Clone this wiki locally