public override bool Execute(ProgramOptions programOptions) { Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); StepTiming stepTimingFunction = new StepTiming(); stepTimingFunction.JobFileName = programOptions.ReportJobFilePath; stepTimingFunction.StepName = programOptions.ReportJob.Status.ToString(); stepTimingFunction.StepID = (int)programOptions.ReportJob.Status; stepTimingFunction.StartTime = DateTime.Now; stepTimingFunction.NumEntities = 0; this.DisplayJobStepStartingStatus(programOptions); this.FilePathMap = new FilePathMap(programOptions); SnowSQLDriver snowSQLDriver = null; try { snowSQLDriver = new SnowSQLDriver(programOptions.ConnectionName); if (snowSQLDriver.ValidateToolInstalled() == false) { return(false); } ; FileIOHelper.CreateFolder(this.FilePathMap.Data_FolderPath()); FileIOHelper.CreateFolder(this.FilePathMap.Data_User_FolderPath()); FileIOHelper.CreateFolder(this.FilePathMap.Data_Role_FolderPath()); FileIOHelper.CreateFolder(this.FilePathMap.Data_Grant_FolderPath()); #region List of roles and users loggerConsole.Info("Retrieving list of roles and users"); StringBuilder sb = new StringBuilder(1024); sb.AppendFormat("ALTER SESSION SET QUERY_TAG='Snowflake Grant Report Version {0}';", Assembly.GetEntryAssembly().GetName().Version); sb.AppendLine(); sb.AppendLine("!set output_format=csv"); sb.AppendLine("!set header=true"); sb.AppendLine("USE ROLE SECURITYADMIN;"); sb.AppendFormat("!spool \"{0}\"", FilePathMap.Data_ShowRoles_FilePath()); sb.AppendLine(); sb.AppendLine("SHOW ROLES;"); sb.AppendLine(@"!spool off"); sb.AppendFormat("!spool \"{0}\"", FilePathMap.Data_ShowUsers_FilePath()); sb.AppendLine(); sb.AppendLine("SHOW USERS;"); sb.AppendLine(@"!spool off"); FileIOHelper.SaveFileToPath(sb.ToString(), FilePathMap.Data_UsersAndRoles_SQLQuery_FilePath(), false); snowSQLDriver.ExecuteSQLStatementsInFile(this.FilePathMap.Data_UsersAndRoles_SQLQuery_FilePath(), programOptions.ReportFolderPath); #endregion #region User details List <User> usersList = FileIOHelper.ReadListFromCSVFile <User>(FilePathMap.Data_ShowUsers_FilePath(), new UserShowUsersMinimalMap()); if (usersList != null) { loggerConsole.Info("Retrieving user details for {0} users", usersList.Count); sb = new StringBuilder(256 * usersList.Count); sb.AppendFormat("ALTER SESSION SET QUERY_TAG='Snowflake Grant Report Version {0}';", Assembly.GetEntryAssembly().GetName().Version); sb.AppendLine(); sb.AppendLine("!set output_format=csv"); sb.AppendLine("!set header=true"); sb.AppendLine("USE ROLE SECURITYADMIN;"); sb.AppendLine("USE ROLE ACCOUNTADMIN;"); for (int i = 0; i < usersList.Count; i++) { User user = usersList[i]; sb.AppendFormat("!spool \"{0}\"", FilePathMap.Data_DescribeUser_FilePath(user.NAME)); sb.AppendLine(); sb.AppendFormat("DESCRIBE USER {0};", quoteObjectIdentifier(user.NAME)); sb.AppendLine(); sb.AppendLine(@"!spool off"); } FileIOHelper.SaveFileToPath(sb.ToString(), FilePathMap.DescribeUserSQLQuery_FilePath(), false); snowSQLDriver.ExecuteSQLStatementsInFile(FilePathMap.DescribeUserSQLQuery_FilePath(), programOptions.ReportFolderPath); } #endregion #region Role Grants List <Role> rolesList = FileIOHelper.ReadListFromCSVFile <Role>(FilePathMap.Data_ShowRoles_FilePath(), new RoleShowRolesMinimalMap()); if (rolesList != null) { #region Role Grants On loggerConsole.Info("Retrieving role grants ON for {0} roles", rolesList.Count); sb = new StringBuilder(256 * rolesList.Count); sb.AppendFormat("ALTER SESSION SET QUERY_TAG='Snowflake Grant Report Version {0}';", Assembly.GetEntryAssembly().GetName().Version); sb.AppendLine(); sb.AppendLine("!set output_format=csv"); sb.AppendLine("!set header=true"); sb.AppendLine("USE ROLE SECURITYADMIN;"); sb.AppendFormat("!spool \"{0}\"", FilePathMap.Data_RoleShowGrantsOn_FilePath()); sb.AppendLine(); for (int i = 0; i < rolesList.Count; i++) { Role role = rolesList[i]; sb.AppendFormat("SHOW GRANTS ON ROLE {0};", quoteObjectIdentifier(role.Name)); sb.AppendLine(); if (i == 0) { sb.AppendLine("!set header=false"); } } sb.AppendLine(@"!spool off"); FileIOHelper.SaveFileToPath(sb.ToString(), FilePathMap.Data_RoleGrantsOn_SQLQuery_FilePath(), false); snowSQLDriver.ExecuteSQLStatementsInFile(FilePathMap.Data_RoleGrantsOn_SQLQuery_FilePath(), programOptions.ReportFolderPath); #endregion #region Role Grants To loggerConsole.Info("Retrieving role grants TO for {0} roles", rolesList.Count); sb = new StringBuilder(256 * rolesList.Count); sb.AppendFormat("ALTER SESSION SET QUERY_TAG='Snowflake Grant Report Version {0}';", Assembly.GetEntryAssembly().GetName().Version); sb.AppendLine(); sb.AppendLine("!set output_format=csv"); sb.AppendLine("!set header=true"); sb.AppendLine("USE ROLE SECURITYADMIN;"); sb.AppendFormat("!spool \"{0}\"", FilePathMap.Data_RoleShowGrantsTo_FilePath()); sb.AppendLine(); for (int i = 0; i < rolesList.Count; i++) { Role role = rolesList[i]; sb.AppendFormat("SHOW GRANTS TO ROLE {0};", quoteObjectIdentifier(role.Name)); sb.AppendLine(); if (i == 0) { sb.AppendLine("!set header=false"); } } sb.AppendLine(@"!spool off"); FileIOHelper.SaveFileToPath(sb.ToString(), FilePathMap.Data_RoleGrantsTo_SQLQuery_FilePath(), false); snowSQLDriver.ExecuteSQLStatementsInFile(FilePathMap.Data_RoleGrantsTo_SQLQuery_FilePath(), programOptions.ReportFolderPath); #endregion #region Role Grants Of loggerConsole.Info("Retrieving role grants OF for {0} roles", rolesList.Count); sb = new StringBuilder(256 * rolesList.Count); sb.AppendFormat("ALTER SESSION SET QUERY_TAG='Snowflake Grant Report Version {0}';", Assembly.GetEntryAssembly().GetName().Version); sb.AppendLine(); sb.AppendLine("!set output_format=csv"); sb.AppendLine("!set header=true"); sb.AppendLine("USE ROLE SECURITYADMIN;"); sb.AppendFormat("!spool \"{0}\"", FilePathMap.Data_RoleShowGrantsOf_FilePath()); sb.AppendLine(); for (int i = 0; i < rolesList.Count; i++) { Role role = rolesList[i]; // Output header for only the first item sb.AppendFormat("SHOW GRANTS OF ROLE {0};", quoteObjectIdentifier(role.Name)); sb.AppendLine(); if (i == 0) { sb.AppendLine("!set header=false"); } } sb.AppendLine(@"!spool off"); FileIOHelper.SaveFileToPath(sb.ToString(), FilePathMap.Data_RoleGrantsOf_SQLQuery_FilePath(), false); snowSQLDriver.ExecuteSQLStatementsInFile(FilePathMap.Data_RoleGrantsOf_SQLQuery_FilePath(), programOptions.ReportFolderPath); #endregion } #endregion return(true); } catch (Exception ex) { logger.Error(ex); loggerConsole.Error(ex); return(false); } finally { stopWatch.Stop(); this.DisplayJobStepEndedStatus(programOptions, stopWatch); stepTimingFunction.EndTime = DateTime.Now; stepTimingFunction.Duration = stopWatch.Elapsed; stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds; List <StepTiming> stepTimings = new List <StepTiming>(1); stepTimings.Add(stepTimingFunction); FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true); } }
public override bool Execute(ProgramOptions programOptions) { Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); StepTiming stepTimingFunction = new StepTiming(); stepTimingFunction.JobFileName = programOptions.ReportJobFilePath; stepTimingFunction.StepName = programOptions.ReportJob.Status.ToString(); stepTimingFunction.StepID = (int)programOptions.ReportJob.Status; stepTimingFunction.StartTime = DateTime.Now; stepTimingFunction.NumEntities = 0; this.DisplayJobStepStartingStatus(programOptions); this.FilePathMap = new FilePathMap(programOptions); try { FileIOHelper.CreateFolder(this.FilePathMap.Report_FolderPath()); FileIOHelper.CreateFolder(this.FilePathMap.Report_User_FolderPath()); List <User> usersList = FileIOHelper.ReadListFromCSVFile <User>(FilePathMap.Data_ShowUsers_FilePath(), new UserShowUsersMap()); if (usersList != null) { loggerConsole.Info("Parsing user details for {0} users", usersList.Count); int j = 0; foreach (User user in usersList) { logger.Trace("Parsing details for user {0}", user.LOGIN_NAME); List <UserProperty> userPropertiesList = FileIOHelper.ReadListFromCSVFile <UserProperty>(FilePathMap.Data_DescribeUser_FilePath(user.NAME), new UserPropertyMap()); if (userPropertiesList != null) { foreach (UserProperty userProperty in userPropertiesList) { if (userProperty.PropValue == "null") { userProperty.PropValue = String.Empty; } switch (userProperty.PropName) { case "COMMENT": user.COMMENT = userProperty.PropValue; break; case "DAYS_TO_EXPIRY": if (userProperty.PropValue != null && userProperty.PropValue.Length > 0) { try { user.DAYS_TO_EXPIRY = Convert.ToInt32(userProperty.PropValue); } catch {} } break; case "DEFAULT_NAMESPACE": user.DEFAULT_NAMESPACE = userProperty.PropValue; break; case "DEFAULT_ROLE": user.DEFAULT_ROLE = userProperty.PropValue; break; case "DEFAULT_WAREHOUSE": user.DEFAULT_WAREHOUSE = userProperty.PropValue; break; case "DISABLED": user.DISABLED = Convert.ToBoolean(userProperty.PropValue); break; case "DISPLAY_NAME": user.DISPLAY_NAME = userProperty.PropValue; break; case "EMAIL": user.EMAIL = userProperty.PropValue; break; case "EXT_AUTHN_DUO": user.EXT_AUTHN_DUO = Convert.ToBoolean(userProperty.PropValue); break; case "EXT_AUTHN_UID": user.EXT_AUTHN_UID = userProperty.PropValue; break; case "FIRST_NAME": user.FIRST_NAME = userProperty.PropValue; break; case "LAST_NAME": user.LAST_NAME = userProperty.PropValue; break; case "LOGIN_NAME": // Already parsed break; case "MIDDLE_NAME": user.MIDDLE_NAME = userProperty.PropValue; break; case "MINS_TO_BYPASS_MFA": if (userProperty.PropValue != null && userProperty.PropValue.Length > 0) { try { user.MINS_TO_BYPASS_MFA = Convert.ToInt32(userProperty.PropValue); } catch {} } break; case "MINS_TO_BYPASS_NETWORK_POLICY": if (userProperty.PropValue != null && userProperty.PropValue.Length > 0) { try { user.MINS_TO_BYPASS_NETWORK_POLICY = Convert.ToInt32(userProperty.PropValue); } catch {} } break; case "MINS_TO_UNLOCK": if (userProperty.PropValue != null && userProperty.PropValue.Length > 0) { try { user.MINS_TO_UNLOCK = Convert.ToInt32(userProperty.PropValue); } catch {} } break; case "MUST_CHANGE_PASSWORD": user.MUST_CHANGE_PASSWORD = Convert.ToBoolean(userProperty.PropValue); break; case "NAME": // Already parsed, but check for special characters if (String.Compare(userProperty.PropValue, quoteObjectIdentifier(userProperty.PropValue), true, CultureInfo.InvariantCulture) == 0) { user.IsObjectIdentifierSpecialCharacters = false; } else { user.IsObjectIdentifierSpecialCharacters = true; } break; case "PASSWORD": // Not parsing break; case "PASSWORD_LAST_SET_TIME": if (userProperty.PropValue != null && userProperty.PropValue.Length > 0) { try { // https://community.snowflake.com/s/article/4-26-Release-Notes-July-28-30-2020 // The timestamp on which the last non-null password was set for the user. // Default to null if no password has been set yet. // Values of 292278994-08-17 07:12:55.807 or 1969-12-31 23:59:59.999 indicate the password was set before the inclusion of this row. A value of 1969-12-31 23:59:59.999 can also indicate an expired password and the user needs to change their password. if (userProperty.PropValue == "292278994-08-17 07:12:55.807") { // This date 300 million year in the future is not parseable // Set it back to good old 1969 userProperty.PropValue = "1969-12-31 23:59:59.999"; } user.PASSWORD_LAST_SET_TIME = Convert.ToDateTime(userProperty.PropValue); } catch {} } break; case "RSA_PUBLIC_KEY_2_FP": user.RSA_PUBLIC_KEY_2_FP = userProperty.PropValue; break; case "RSA_PUBLIC_KEY_FP": user.RSA_PUBLIC_KEY_FP = userProperty.PropValue; break; case "SNOWFLAKE_LOCK": user.SNOWFLAKE_LOCK = Convert.ToBoolean(userProperty.PropValue); break; case "SNOWFLAKE_SUPPORT": user.SNOWFLAKE_SUPPORT = Convert.ToBoolean(userProperty.PropValue); break; default: logger.Warn("Unknown user property {0} for user {1}", userProperty, user); break; } } } j++; if (j % 10 == 0) { Console.Write("{0}.", j); } } loggerConsole.Info("Done {0} items", usersList.Count); FileIOHelper.WriteListToCSVFile <User>(usersList, new UserDetailsMap(), FilePathMap.Report_UserDetail_FilePath()); } return(true); } catch (Exception ex) { logger.Error(ex); loggerConsole.Error(ex); return(false); } finally { stopWatch.Stop(); this.DisplayJobStepEndedStatus(programOptions, stopWatch); stepTimingFunction.EndTime = DateTime.Now; stepTimingFunction.Duration = stopWatch.Elapsed; stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds; List <StepTiming> stepTimings = new List <StepTiming>(1); stepTimings.Add(stepTimingFunction); FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true); } }