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 { #region Prepare the report package // Prepare package ExcelPackage excelReport = new ExcelPackage(); excelReport.Workbook.Properties.Author = String.Format("Snowflake Grant Report Version {0}", Assembly.GetEntryAssembly().GetName().Version); excelReport.Workbook.Properties.Title = "Snowflake Grant Differences Report"; excelReport.Workbook.Properties.Subject = String.Format("{0}<->{1}", programOptions.LeftReportFolderPath, programOptions.RightReportFolderPath); excelReport.Workbook.Properties.Comments = String.Format("Command line {0}", programOptions); #endregion #region Parameters sheet // Parameters sheet ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS); var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle"); hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single; hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks); var objectToRolePermissionCellStyle = sheet.Workbook.Styles.CreateNamedStyle("ShortPermissionStyle"); objectToRolePermissionCellStyle.Style.Font.Size = 8; fillReportParametersSheet(sheet, programOptions, excelReport.Workbook.Properties.Title); #endregion #region TOC sheet // Navigation sheet with link to other sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC); #endregion #region Entity sheets and their associated pivots sheet = excelReport.Workbook.Worksheets.Add(SHEET_DIFFERENCES); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_DIFFERENCES_TYPE); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_DIFFERENCES_TYPE); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Converted Data"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_DIFFERENCES); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); #endregion #region Report file variables ExcelRangeBase range = null; ExcelTable table = null; #endregion #region Differences sheet sheet = excelReport.Workbook.Worksheets[SHEET_DIFFERENCES]; logger.Info("{0} Sheet", sheet.Name); loggerConsole.Info("{0} Sheet", sheet.Name); EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.Report_RoleGrant_Differences_FilePath(), 0, typeof(GrantDifference), sheet, LIST_SHEET_START_TABLE_AT, 1); logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_DIFFERENCES); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Privilege"].Position + 1).Width = 20; sheet.Column(table.Columns["ObjectType"].Position + 1).Width = 20; sheet.Column(table.Columns["ObjectName"].Position + 1).Width = 30; sheet.Column(table.Columns["GrantedTo"].Position + 1).Width = 30; sheet.Column(table.Columns["Difference"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUTCLeft"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUTCRight"].Position + 1).Width = 20; ExcelAddress cfAddressDifference = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["Difference"].Position + 1, sheet.Dimension.Rows, table.Columns["Difference"].Position + 1); var cfUserExperience = sheet.ConditionalFormatting.AddEqual(cfAddressDifference); cfUserExperience.Style.Font.Color.Color = Color.Black; cfUserExperience.Style.Fill.BackgroundColor.Color = colorDifferent; cfUserExperience.Formula = String.Format(@"=""{0}""", DIFFERENCE_DIFFERENT); cfUserExperience = sheet.ConditionalFormatting.AddEqual(cfAddressDifference); cfUserExperience.Style.Font.Color.Color = Color.Black; cfUserExperience.Style.Fill.BackgroundColor.Color = colorExtra; cfUserExperience.Formula = String.Format(@"=""{0}""", DIFFERENCE_EXTRA); cfUserExperience = sheet.ConditionalFormatting.AddEqual(cfAddressDifference); cfUserExperience.Style.Font.Color.Color = Color.Black; cfUserExperience.Style.Fill.BackgroundColor.Color = colorMissing; cfUserExperience.Formula = String.Format(@"=""{0}""", DIFFERENCE_MISSING); sheet = excelReport.Workbook.Worksheets[SHEET_DIFFERENCES_TYPE]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_DIFFERENCES_TYPE); setDefaultPivotTableSettings(pivot); addRowFieldToPivot(pivot, "ObjectType", eSortType.Ascending); addRowFieldToPivot(pivot, "ObjectName", eSortType.Ascending); addRowFieldToPivot(pivot, "Privilege", eSortType.Ascending); addRowFieldToPivot(pivot, "GrantedTo", eSortType.Ascending); addRowFieldToPivot(pivot, "DifferenceDetails", eSortType.Ascending); addColumnFieldToPivot(pivot, "Difference", eSortType.Ascending); addDataFieldToPivot(pivot, "UniqueIdentifier", DataFieldFunctions.Count, "NumDifferences"); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; sheet.Column(5).Width = 20; ExcelChart chart = sheet.Drawings.AddChart(GRAPH_DIFFERENCES_TYPE, eChartType.ColumnStacked, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); } #endregion #region Objects / Roles Differences sheet // Build the table // Object | Role 1 | Role 2 | ... | Role N // ----------------------------------------------------- // DB1 | +U, O | | | +U // DB2 | -U, O | ~U | | // DB2 | -U, O | U | | List <GrantDifference> grantDifferencesList = FileIOHelper.ReadListFromCSVFile <GrantDifference>(FilePathMap.Report_RoleGrant_Differences_FilePath(), new GrantDifferenceMap()); if (grantDifferencesList != null) { var groupObjectTypesGrouped = grantDifferencesList.GroupBy(g => g.ObjectType); foreach (var groupObjectType in groupObjectTypesGrouped) { string objectType = groupObjectType.Key; Dictionary <string, int> roleToHeaderMapping = new Dictionary <string, int>(); loggerConsole.Info("Processing grants differences for {0}", objectType); List <GrantDifference> grantDifferencesOfObjectTypeList = groupObjectType.ToList(); sheet = excelReport.Workbook.Worksheets.Add(getShortenedNameForExcelSheet(String.Format(SHEET_GRANT_DIFFERENCES_PER_OBJECT_TYPE, objectType))); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "Left"; sheet.Cells[2, 2].Value = programOptions.LeftReportFolderPath; sheet.Cells[3, 1].Value = "Right"; sheet.Cells[3, 2].Value = programOptions.RightReportFolderPath; sheet.Cells[4, 1].Value = "Type"; sheet.Cells[4, 2].Value = objectType; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 2, 3); logger.Info("{0} Sheet", sheet.Name); loggerConsole.Info("{0} Sheet", sheet.Name); int headerRowIndex = LIST_SHEET_START_TABLE_AT + 1; int roleColumnBeginIndex = 3; int roleColumnMaxIndex = roleColumnBeginIndex; // Header row sheet.Cells[headerRowIndex, 1].Value = "Full Name"; sheet.Cells[headerRowIndex, 2].Value = "Short Name"; int currentRowIndex = headerRowIndex; currentRowIndex++; var groupObjectNameGrouped = grantDifferencesOfObjectTypeList.GroupBy(g => g.ObjectName); foreach (var groupObjectName in groupObjectNameGrouped) { GrantDifference grantDifferenceObjectToOperateOn = groupObjectName.First(); sheet.Cells[currentRowIndex, 1].Value = grantDifferenceObjectToOperateOn.ObjectName; sheet.Cells[currentRowIndex, 2].Value = grantDifferenceObjectToOperateOn.EntityName; List <GrantDifference> grantDifferencesOfThisObjectList = groupObjectName.ToList(); var grantsByRoleNameGroups = grantDifferencesOfThisObjectList.GroupBy(g => g.GrantedTo); foreach (var grantsByRoleNameGroup in grantsByRoleNameGroups) { GrantDifference firstGrantDifference = grantsByRoleNameGroup.First(); int thisRoleColumnIndex = 0; if (roleToHeaderMapping.ContainsKey(firstGrantDifference.GrantedTo) == false) { // Add another Role to the header thisRoleColumnIndex = roleColumnMaxIndex; roleToHeaderMapping.Add(firstGrantDifference.GrantedTo, thisRoleColumnIndex); sheet.Cells[headerRowIndex, thisRoleColumnIndex].Value = firstGrantDifference.GrantedTo; roleColumnMaxIndex++; } else { // Previously seen thisRoleColumnIndex = roleToHeaderMapping[firstGrantDifference.GrantedTo]; } sheet.Cells[currentRowIndex, thisRoleColumnIndex].Value = grantsByRoleNameGroup.ToList().Count(); outputGrantDifferencestToCell(sheet.Cells[currentRowIndex, thisRoleColumnIndex], grantsByRoleNameGroup.ToList()); } currentRowIndex++; } range = sheet.Cells[headerRowIndex, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; try { table = sheet.Tables.Add(range, getExcelTableOrSheetSafeString(String.Format(TABLE_GRANT_DIFFERENCES_PER_OBJECT_TYPE, objectType))); } catch (ArgumentException ex) { if (ex.Message == "Tablename is not unique") { table = sheet.Tables.Add(range, String.Format("{0}_1", getExcelTableOrSheetSafeString(String.Format(TABLE_GRANT_DIFFERENCES_PER_OBJECT_TYPE, objectType)))); } } table.ShowHeader = true; table.TableStyle = TableStyles.Light18; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(1).Width = 30; sheet.Column(2).Width = 20; // Make the column for permissions headers angled downwards 45 degrees for (int i = roleColumnBeginIndex; i <= table.Columns.Count; i++) { sheet.Cells[headerRowIndex, i].Style.TextRotation = 135; sheet.Column(i).Width = 7; } // Format the cells ExcelRangeBase rangeToFormat = sheet.Cells[headerRowIndex + 1, 3, sheet.Dimension.Rows, sheet.Dimension.Columns]; rangeToFormat.StyleName = "ShortDifferencesStyle"; var cfMoreThanOne = sheet.ConditionalFormatting.AddContainsText(rangeToFormat); cfMoreThanOne.Style.Font.Color.Color = Color.Black; cfMoreThanOne.Style.Fill.BackgroundColor.Color = Color.MediumOrchid; cfMoreThanOne.Text = "-and-"; cfMoreThanOne.StopIfTrue = true; var cfMissing = sheet.ConditionalFormatting.AddContainsText(rangeToFormat); cfMissing.Style.Font.Color.Color = Color.Black; cfMissing.Style.Fill.BackgroundColor.Color = colorMissing; cfMissing.Text = "<<"; var cfExtra = sheet.ConditionalFormatting.AddContainsText(rangeToFormat); cfExtra.Style.Font.Color.Color = Color.Black; cfExtra.Style.Fill.BackgroundColor.Color = colorExtra; cfExtra.Text = ">>"; var cfDifferent = sheet.ConditionalFormatting.AddContainsText(rangeToFormat); cfDifferent.Style.Font.Color.Color = Color.Black; cfDifferent.Style.Fill.BackgroundColor.Color = colorDifferent; cfDifferent.Text = "~~"; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); } } #endregion #region TOC sheet // TOC sheet again sheet = excelReport.Workbook.Worksheets[SHEET_TOC]; fillTableOfContentsSheet(sheet, excelReport); #endregion #region Save file string reportFilePath = FilePathMap.GrantsDifferencesExcelReportFilePath(); logger.Info("Saving Excel report {0}", reportFilePath); loggerConsole.Info("Saving Excel report {0}", reportFilePath); try { // Save full report Excel files excelReport.SaveAs(new FileInfo(reportFilePath)); } catch (InvalidOperationException ex) { logger.Warn("Unable to save Excel file {0}", reportFilePath); logger.Warn(ex); loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath); } #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.Data_FolderPath()); FileIOHelper.CreateFolder(this.FilePathMap.Data_Role_FolderPath()); FileIOHelper.CreateFolder(this.FilePathMap.Report_FolderPath()); FileIOHelper.CreateFolder(this.FilePathMap.Report_Grant_FolderPath()); FileIOHelper.CreateFolder(this.FilePathMap.Report_Role_FolderPath()); #region Grants ON and Grants TO grants for everything loggerConsole.Info("Process Grants ON and TO"); List <RoleMember> grantsOfRolesList = new List <RoleMember>(); List <Grant> grantsOnRolesList = FileIOHelper.ReadListFromCSVFile <Grant>(FilePathMap.Input_RoleShowGrantsToAndOn_FilePath(), new GrantGrantToRolesMap(), new string[] { "Initiating login request with your identity provider" }); if (grantsOnRolesList != null) { loggerConsole.Info("Loaded {0} ON and TO grants", grantsOnRolesList.Count); // Unescape special names of objects foreach (Grant grant in grantsOnRolesList) { grant.GrantedTo = grant.GrantedTo.Trim('"'); grant.GrantedBy = grant.GrantedBy.Trim('"'); // Apparently the ACCOUNT_USAGE casts 'NOTIFICATION_SUBSCRIPTION' to 'NOTIFICATION SUBSCRIPTION' // And for others that have space if (grant.ObjectType.Contains(' ') == true) { grant.ObjectType = grant.ObjectType.Replace(' ', '_'); } // Escape periods if (grant.EntityName.Contains('.') == true) { grant.EntityName = String.Format("\"{0}\"", grant.EntityName); } if (grant.DBName.Contains('.') == true) { grant.DBName = String.Format("\"{0}\"", grant.DBName); } if (grant.SchemaName.Contains('.') == true) { grant.SchemaName = String.Format("\"{0}\"", grant.SchemaName); } // Come up with ObjectName from combination of EntityName, etc. if (grant.DBName.Length == 0) { // Account level object grant.ObjectName = grant.EntityName; } else { if (grant.SchemaName.Length == 0) { // DATABASE grant.ObjectName = grant.EntityName; grant.DBName = grant.EntityName; } else { if (grant.ObjectType == "SCHEMA") { grant.ObjectName = String.Format("{0}.{1}", grant.DBName, grant.EntityName); } else { grant.ObjectName = String.Format("{0}.{1}.{2}", grant.DBName, grant.SchemaName, grant.EntityName); } } } } grantsOnRolesList.RemoveAll(g => g.DeletedOn.HasValue == true); grantsOnRolesList = grantsOnRolesList.OrderBy(g => g.ObjectType).ThenBy(g => g.ObjectName).ThenBy(g => g.GrantedTo).ToList(); FileIOHelper.WriteListToCSVFile <Grant>(grantsOnRolesList, new GrantMap(), FilePathMap.Report_RoleGrant_FilePath()); List <Grant> roleUsageGrantsList = grantsOnRolesList.Where(g => g.ObjectType == "ROLE" && g.Privilege == "USAGE").ToList(); if (roleUsageGrantsList != null) { foreach (Grant grant in roleUsageGrantsList) { RoleMember roleMember = new RoleMember(); roleMember.CreatedOn = grant.CreatedOn; roleMember.Name = grant.ObjectName; roleMember.GrantedBy = grant.GrantedBy; roleMember.GrantedTo = grant.GrantedTo; roleMember.ObjectType = grant.ObjectType; grantsOfRolesList.Add(roleMember); } grantsOfRolesList = grantsOfRolesList.OrderBy(g => g.Name).ToList(); } #region Individual Object Types loggerConsole.Info("Processing individual Object Types"); // Break them up by the type var groupObjectTypesGrouped = grantsOnRolesList.GroupBy(g => g.ObjectType); List <SingleStringRow> objectTypesList = new List <SingleStringRow>(groupObjectTypesGrouped.Count()); foreach (var group in groupObjectTypesGrouped) { loggerConsole.Info("Processing grants for {0}", group.Key); SingleStringRow objectType = new SingleStringRow(); objectType.Value = group.Key; objectTypesList.Add(objectType); #region Save this set of grants for Object Type List <Grant> grantsOfObjectTypeList = group.ToList(); // Save this set as is for one of the tables in report FileIOHelper.WriteListToCSVFile <Grant>(grantsOfObjectTypeList, new GrantMap(), FilePathMap.Report_RoleGrant_ObjectType_FilePath(group.Key)); // Pivot each section into this kind of table // // ObjectType | ObjectName | GrantedTo | OWNERSHIP | USAGE | REFERENCE | GrantN // DATABASE | SomeDB | SomeRole | X | x+ | | // Where X+ means WithGrantOption=True // X means WithGrantOption=False List <ObjectTypeGrant> objectGrantsList = new List <ObjectTypeGrant>(grantsOfObjectTypeList.Count / 5); Dictionary <string, int> privilegeToColumnDictionary = new Dictionary <string, int>(20); #endregion #region Convert this set into pivot List <string> listOfPrivileges = grantsOfObjectTypeList.Select(g => g.Privilege).Distinct().OrderBy(g => g).ToList(); // Make USAGE and OWNERSHIP be the first columns switch (group.Key) { case "ACCOUNT": break; case "DATABASE": case "FILE_FORMAT": case "FUNCTION": case "INTEGRATION": case "PROCEDURE": case "ROLE": case "SCHEMA": case "SEQUENCE": case "WAREHOUSE": listOfPrivileges.Remove("OWNERSHIP"); listOfPrivileges.Insert(0, "OWNERSHIP"); listOfPrivileges.Remove("USAGE"); listOfPrivileges.Insert(1, "USAGE"); break; case "EXTERNAL_TABLE": case "MANAGED_ACCOUNT": case "MASKING_POLICY": case "MATERIALIZED_VIEW": case "NETWORK_POLICY": case "NOTIFICATION_SUBSCRIPTION": case "PIPE": case "RESOURCE_MONITOR": case "SHARE": case "STAGE": case "STREAM": case "TABLE": case "TASK": case "USER": case "VIEW": listOfPrivileges.Remove("OWNERSHIP"); listOfPrivileges.Insert(0, "OWNERSHIP"); break; default: break; } for (int i = 0; i < listOfPrivileges.Count; i++) { privilegeToColumnDictionary.Add(listOfPrivileges[i], i); } ObjectTypeGrant latestGrantRow = new ObjectTypeGrant(); foreach (Grant grant in grantsOfObjectTypeList) { // Loop through rows, starting new objects for each combination of ObjectType+ObjectName+GrantedTo when necessary // ObjectType is always the same in this grouping // ObjectName if (latestGrantRow.ObjectType != grant.ObjectType || latestGrantRow.ObjectName != grant.ObjectName || latestGrantRow.GrantedTo != grant.GrantedTo) { // Need to start new row latestGrantRow = new ObjectTypeGrant(); latestGrantRow.ObjectType = grant.ObjectType; latestGrantRow.ObjectName = grant.ObjectName; latestGrantRow.DBName = grant.DBName; latestGrantRow.SchemaName = grant.SchemaName; latestGrantRow.EntityName = grant.EntityName; latestGrantRow.GrantedTo = grant.GrantedTo; objectGrantsList.Add(latestGrantRow); } // Find out which column to use int privilegeColumnNumber = privilegeToColumnDictionary[grant.Privilege]; switch (privilegeColumnNumber) { case 0: latestGrantRow.Privilege0 = grant.DisplaySettingWithGrantOption; break; case 1: latestGrantRow.Privilege1 = grant.DisplaySettingWithGrantOption; break; case 2: latestGrantRow.Privilege2 = grant.DisplaySettingWithGrantOption; break; case 3: latestGrantRow.Privilege3 = grant.DisplaySettingWithGrantOption; break; case 4: latestGrantRow.Privilege4 = grant.DisplaySettingWithGrantOption; break; case 5: latestGrantRow.Privilege5 = grant.DisplaySettingWithGrantOption; break; case 6: latestGrantRow.Privilege6 = grant.DisplaySettingWithGrantOption; break; case 7: latestGrantRow.Privilege7 = grant.DisplaySettingWithGrantOption; break; case 8: latestGrantRow.Privilege8 = grant.DisplaySettingWithGrantOption; break; case 9: latestGrantRow.Privilege9 = grant.DisplaySettingWithGrantOption; break; case 10: latestGrantRow.Privilege10 = grant.DisplaySettingWithGrantOption; break; case 11: latestGrantRow.Privilege11 = grant.DisplaySettingWithGrantOption; break; case 12: latestGrantRow.Privilege12 = grant.DisplaySettingWithGrantOption; break; case 13: latestGrantRow.Privilege13 = grant.DisplaySettingWithGrantOption; break; case 14: latestGrantRow.Privilege14 = grant.DisplaySettingWithGrantOption; break; case 15: latestGrantRow.Privilege15 = grant.DisplaySettingWithGrantOption; break; case 16: latestGrantRow.Privilege16 = grant.DisplaySettingWithGrantOption; break; case 17: latestGrantRow.Privilege17 = grant.DisplaySettingWithGrantOption; break; case 18: latestGrantRow.Privilege18 = grant.DisplaySettingWithGrantOption; break; case 19: latestGrantRow.Privilege19 = grant.DisplaySettingWithGrantOption; break; default: // Can't fit more than 20 privileges logger.Warn("More then 20 Privileges reached with {0} privilege for object type {1}", grant.Privilege, grant.ObjectType); break; } } List <string> privilegeColumnNames = new List <string>(privilegeToColumnDictionary.Count); for (int i = 0; i < privilegeToColumnDictionary.Count; i++) { privilegeColumnNames.Add(String.Empty); } foreach (var entry in privilegeToColumnDictionary) { privilegeColumnNames[entry.Value] = entry.Key; } // Save the pivot FileIOHelper.WriteListToCSVFile <ObjectTypeGrant>(objectGrantsList, new ObjectTypeGrantMap(privilegeColumnNames), FilePathMap.Report_RoleGrant_ObjectType_Pivoted_FilePath(group.Key)); #endregion } FileIOHelper.WriteListToCSVFile <SingleStringRow>(objectTypesList, new SingleStringRowMap(), FilePathMap.Report_RoleGrant_ObjectTypes_FilePath()); #endregion } #endregion #region Grants OF - Members of Roles (Roles and Users) loggerConsole.Info("Process Grants OF Users"); List <RoleMember> grantsOfUsersList = FileIOHelper.ReadListFromCSVFile <RoleMember>(FilePathMap.Input_RoleShowGrantsOf_FilePath(), new RoleMemberGrantsToUsersMap(), new string[] { "Initiating login request with your identity provider" }); if (grantsOfUsersList != null) { foreach (RoleMember roleMember in grantsOfUsersList) { // Unescape special names of roles roleMember.Name = roleMember.Name.Trim('"'); roleMember.GrantedTo = roleMember.GrantedTo.Trim('"'); roleMember.GrantedBy = roleMember.GrantedBy.Trim('"'); } // Remove deleted items grantsOfUsersList.RemoveAll(g => g.DeletedOn.HasValue == true); grantsOfUsersList = grantsOfUsersList.OrderBy(g => g.Name).ToList(); List <RoleMember> grantsOfRolesAndUsersList = new List <RoleMember>(); grantsOfRolesAndUsersList.AddRange(grantsOfRolesList); grantsOfRolesAndUsersList.AddRange(grantsOfUsersList); FileIOHelper.WriteListToCSVFile <RoleMember>(grantsOfRolesAndUsersList, new RoleMemberMap(), FilePathMap.Report_RoleMember_FilePath()); } #endregion // Come up with roles list for later steps too if (grantsOnRolesList == null) { grantsOnRolesList = new List <Grant>(); } List <Role> rolesList = new List <Role>(); List <string> rolesInThisAccountList = grantsOnRolesList.Where(g => g.ObjectType == "ROLE").Select(g => g.ObjectName).Distinct().ToList(); foreach (string roleName in rolesInThisAccountList) { Role role = new Role(); role.CreatedOn = DateTime.Now; role.Name = roleName; rolesList.Add(role); } if (rolesList.Where(r => r.Name == "ACCOUNTADMIN").Count() == 0) { Role role = new Role(); role.CreatedOn = DateTime.Now; role.Name = "ACCOUNTADMIN"; rolesList.Add(role); } if (rolesList.Where(r => r.Name == "PUBLIC").Count() == 0) { Role role = new Role(); role.CreatedOn = DateTime.Now; role.Name = "PUBLIC"; rolesList.Add(role); } FileIOHelper.WriteListToCSVFile <Role>(rolesList, new RoleShowRolesMap(), FilePathMap.Data_ShowRoles_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); } }
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); } }
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_Connection_FolderPath()); FileIOHelper.CreateFolder(this.FilePathMap.Data_Account_FolderPath()); 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.AppendFormat("!spool \"{0}\"", FilePathMap.Data_CurrentAccount_FilePath()); sb.AppendLine(); sb.AppendLine("SELECT CURRENT_ACCOUNT() AS CURRENT_ACCOUNT;"); sb.AppendLine(@"!spool off"); sb.AppendFormat("!spool \"{0}\"", FilePathMap.Data_CurrentRegion_FilePath()); sb.AppendLine(); sb.AppendLine("SELECT CURRENT_REGION() AS CURRENT_REGION;"); sb.AppendLine(@"!spool off"); sb.AppendFormat("!spool \"{0}\"", FilePathMap.Data_CurrentVersion_FilePath()); sb.AppendLine(); sb.AppendLine("SELECT CURRENT_VERSION() AS CURRENT_VERSION;"); sb.AppendLine(@"!spool off"); sb.AppendFormat("!spool \"{0}\"", FilePathMap.Data_CurrentClient_FilePath()); sb.AppendLine(); sb.AppendLine("SELECT CURRENT_CLIENT() AS CURRENT_VERSION;"); sb.AppendLine(@"!spool off"); sb.AppendFormat("!spool \"{0}\"", FilePathMap.Data_CurrentUser_FilePath()); sb.AppendLine(); sb.AppendLine("SELECT CURRENT_USER() AS CURRENT_USER;"); sb.AppendLine(@"!spool off"); sb.AppendFormat("!spool \"{0}\"", FilePathMap.Data_CurrentRole_FilePath()); sb.AppendLine(); sb.AppendLine("SELECT CURRENT_ROLE() AS CURRENT_ROLE;"); sb.AppendLine(@"!spool off"); sb.AppendFormat("!spool \"{0}\"", FilePathMap.Data_CurrentWarehouse_FilePath()); sb.AppendLine(); sb.AppendLine("SELECT CURRENT_WAREHOUSE() AS CURRENT_WAREHOUSE;"); sb.AppendLine(@"!spool off"); sb.AppendFormat("!spool \"{0}\"", FilePathMap.Data_CurrentDatabase_FilePath()); sb.AppendLine(); sb.AppendLine("SELECT CURRENT_DATABASE() AS CURRENT_DATABASE;"); sb.AppendLine(@"!spool off"); sb.AppendFormat("!spool \"{0}\"", FilePathMap.Data_CurrentSchema_FilePath()); sb.AppendLine(); sb.AppendLine("SELECT CURRENT_SCHEMA() AS CURRENT_SCHEMA;"); sb.AppendLine(@"!spool off"); FileIOHelper.SaveFileToPath(sb.ToString(), FilePathMap.Data_CurrentContext_SQLQuery_FilePath(), false); loggerConsole.Info("Retrieving current connection context info"); snowSQLDriver.ExecuteSQLStatementsInFile(this.FilePathMap.Data_CurrentContext_SQLQuery_FilePath(), programOptions.ReportFolderPath); 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_GraphViz_FolderPath()); FileIOHelper.CreateFolder(this.FilePathMap.Report_Diagram_SVG_FolderPath()); FileIOHelper.CreateFolder(this.FilePathMap.Report_Diagram_PNG_FolderPath()); FileIOHelper.CreateFolder(this.FilePathMap.Report_Diagram_PDF_FolderPath()); #region Construct object hierarchy with grants Account account = buildObjectHierarchyWithGrants(); #endregion List <Role> rolesList = FileIOHelper.ReadListFromCSVFile <Role>(FilePathMap.Report_RoleDetail_FilePath(), new RoleMap()); if (rolesList != null) { #region Make GraphViz charts loggerConsole.Info("Creating visualizations for {0} roles", rolesList.Count); Role syntheticRoleAll = new Role(); syntheticRoleAll.Name = "ALL_ROLES_TOGETHER_SYNTHETIC"; rolesList.Insert(0, syntheticRoleAll); ParallelOptions parallelOptions = new ParallelOptions(); if (programOptions.ProcessSequentially == true) { parallelOptions.MaxDegreeOfParallelism = 1; } int j = 0; Parallel.ForEach <Role, int>( rolesList, parallelOptions, () => 0, (role, loop, subtotal) => { logger.Info("Processing visualization for {0}", role); List <RoleHierarchy> thisRoleAndItsRelationsHierarchiesList = FileIOHelper.ReadListFromCSVFile <RoleHierarchy>(FilePathMap.Report_RoleHierarchy_RoleAndItsRelations_FilePath(role.Name), new RoleHierarchyMap());; List <Role> thisRoleAndItsRelationsList = FileIOHelper.ReadListFromCSVFile <Role>(FilePathMap.Report_RoleDetail_RoleAndItsRelations_FilePath(role.Name), new RoleMap()); if (role == syntheticRoleAll) { thisRoleAndItsRelationsHierarchiesList = FileIOHelper.ReadListFromCSVFile <RoleHierarchy>(FilePathMap.Report_RoleHierarchy_FilePath(), new RoleHierarchyMap());; thisRoleAndItsRelationsList = FileIOHelper.ReadListFromCSVFile <Role>(FilePathMap.Report_RoleDetail_FilePath(), new RoleMap()); } if (thisRoleAndItsRelationsList != null && thisRoleAndItsRelationsHierarchiesList != null) { Dictionary <string, Role> rolesDict = thisRoleAndItsRelationsList.ToDictionary(k => k.Name, r => r); Dictionary <string, Role> roleNamesOutput = new Dictionary <string, Role>(thisRoleAndItsRelationsList.Count); Role roleBeingOutput = null; StringBuilder sbGraphViz = new StringBuilder(64 * thisRoleAndItsRelationsHierarchiesList.Count + 128); // Start the graph and set its default settings sbGraphViz.AppendLine("digraph {"); sbGraphViz.AppendLine(" layout=\"dot\";"); sbGraphViz.AppendLine(" rankdir=\"TB\";"); sbGraphViz.AppendLine(" center=true;"); sbGraphViz.AppendLine(" splines=\"ortho\";"); sbGraphViz.AppendLine(" overlap=false;"); //sbGraphViz.AppendLine(" colorscheme=\"SVG\";"); sbGraphViz.AppendLine(" node [shape=\"rect\" style=\"filled,rounded\" fontname=\"Courier New\"];"); sbGraphViz.AppendLine(" edge [fontname=\"Courier New\"];"); sbGraphViz.AppendFormat(" // Graph for the Role {0}", role); sbGraphViz.AppendLine(); #region Role boxes // Role boxes sbGraphViz.AppendLine(); sbGraphViz.AppendLine(" // Roles"); sbGraphViz.AppendLine(" subgraph cluster_roles {"); sbGraphViz.AppendFormat(" label = \"roles related to: {0}\";", role); sbGraphViz.AppendLine(); foreach (RoleHierarchy roleHierarchy in thisRoleAndItsRelationsHierarchiesList) { if (roleHierarchy.GrantedTo != "<NOTHING>" && roleNamesOutput.ContainsKey(roleHierarchy.GrantedTo) == false) { // Name of the role with color rolesDict.TryGetValue(roleHierarchy.GrantedTo, out roleBeingOutput); sbGraphViz.AppendFormat(" \"{0}\"{1};", roleHierarchy.GrantedTo.Replace("\"", "\\\""), getRoleStyleAttribute(roleBeingOutput)); sbGraphViz.AppendLine(); roleNamesOutput.Add(roleHierarchy.GrantedTo, roleBeingOutput); } if (roleNamesOutput.ContainsKey(roleHierarchy.Name) == false) { // Name of the role with color rolesDict.TryGetValue(roleHierarchy.Name, out roleBeingOutput); sbGraphViz.AppendFormat(" \"{0}\"{1};", roleHierarchy.Name.Replace("\"", "\\\""), getRoleStyleAttribute(roleBeingOutput)); sbGraphViz.AppendLine(); roleNamesOutput.Add(roleHierarchy.Name, roleBeingOutput); } } sbGraphViz.AppendLine(" }// /Roles"); #endregion #region Role hierachy // Role connections sbGraphViz.AppendLine(); sbGraphViz.AppendLine(" // Role hierarchy"); foreach (RoleHierarchy roleHierarchy in thisRoleAndItsRelationsHierarchiesList) { if (roleHierarchy.GrantedTo == "<NOTHING>") { continue; } // Role to role connector sbGraphViz.AppendFormat(" \"{0}\"->\"{1}\";", roleHierarchy.GrantedTo.Replace("\"", "\\\""), roleHierarchy.Name.Replace("\"", "\\\"")); sbGraphViz.AppendLine(); } sbGraphViz.AppendLine(" // /Role hierarchy"); #endregion if (role != syntheticRoleAll) { #region Databases, Schemas, Tables and Views sbGraphViz.AppendLine(); sbGraphViz.AppendLine(" // Databases"); sbGraphViz.AppendLine(" subgraph cluster_db_wrapper {"); sbGraphViz.AppendLine(" label = \"Databases\";"); sbGraphViz.AppendLine(); int databaseIndex = 0; foreach (Database database in account.Databases) { // Should output database bool isDatabaseRelatedToSelectedRole = false; foreach (Grant grant in database.Grants) { if (grant.Privilege == "USAGE" || grant.Privilege == "OWNERSHIP") { if (roleNamesOutput.ContainsKey(grant.GrantedTo) == true) { isDatabaseRelatedToSelectedRole = true; break; } } } if (isDatabaseRelatedToSelectedRole == false) { continue; } // Output database sbGraphViz.AppendFormat(" // Database {0}", database.FullName); sbGraphViz.AppendLine(); sbGraphViz.AppendFormat(" subgraph cluster_db_{0} {{", databaseIndex); sbGraphViz.AppendLine(); sbGraphViz.AppendLine(" style=\"filled\";"); sbGraphViz.AppendLine(" fillcolor=\"snow\";"); sbGraphViz.AppendFormat(" label = \"db: {0}\";", database.ShortName); sbGraphViz.AppendLine(); sbGraphViz.AppendLine(" node [shape=\"cylinder\" fillcolor=\"darkkhaki\"];"); sbGraphViz.AppendLine(); sbGraphViz.AppendFormat(" \"{0}\";", database.FullName); sbGraphViz.AppendLine(); sbGraphViz.AppendLine(); // List of schemas with number of tables and views sbGraphViz.AppendFormat(" \"{0}.schema\" [shape=\"folder\" label=<", database.FullName); sbGraphViz.AppendLine(); sbGraphViz.AppendLine(" <table border=\"0\" cellborder=\"1\" bgcolor=\"white\">"); sbGraphViz.AppendLine(" <tr><td>S</td><td>T</td><td>V</td></tr>"); int schemaLimit = 0; foreach (Schema schema in database.Schemas) { // Only output if (schemaLimit >= 10) { sbGraphViz.AppendFormat(" <tr><td align=\"left\">Up to {0}</td><td align=\"right\">...</td><td align=\"right\">...</td></tr>", database.Schemas.Count); sbGraphViz.AppendLine(); break; } sbGraphViz.AppendFormat(" <tr><td align=\"left\">{0}</td><td align=\"right\">{1}</td><td align=\"right\">{2}</td></tr>", schema.ShortName, schema.Tables.Count, schema.Views.Count); sbGraphViz.AppendLine(); schemaLimit++; } sbGraphViz.AppendLine(" </table>>];"); // Connect database to schemas sbGraphViz.AppendFormat(" \"{0}\"->\"{0}.schema\" [style=\"invis\"];", database.FullName); sbGraphViz.AppendLine(); sbGraphViz.AppendFormat(" }} // /Database {0}", database.FullName); sbGraphViz.AppendLine(); databaseIndex++; } sbGraphViz.AppendLine(" } // /Databases"); #endregion #region Roles using databases sbGraphViz.AppendLine(); sbGraphViz.AppendLine(" // Roles using databases"); // Output connectors from roles USAGE'ing databases foreach (Database database in account.Databases) { foreach (Grant grant in database.Grants) { if (grant.Privilege == "USAGE" || grant.Privilege == "OWNERSHIP") { if (roleNamesOutput.ContainsKey(grant.GrantedTo) == true) { sbGraphViz.AppendFormat(" \"{0}\"->\"{1}\" [color=\"darkkhaki\"];", grant.GrantedTo, grant.ObjectNameUnquoted); sbGraphViz.AppendLine(); } } } } sbGraphViz.AppendLine(" // /Roles using databases"); #endregion } #region Legend // Output Legend sbGraphViz.AppendLine(); string legend = @" // Legend ""legend"" [label=< <table border=""0"" cellborder=""0"" bgcolor=""white""> <tr><td align=""center"">Legend</td></tr> <tr><td align=""left"" bgcolor=""lightgray"">BUILT IN</td></tr> <tr><td align=""left"" bgcolor=""beige"">SCIM</td></tr> <tr><td align=""left"" bgcolor=""wheat"">ROLE MANAGEMENT</td></tr> <tr><td align=""left"" bgcolor=""orchid"">FUNCTIONAL</td></tr> <tr><td align=""left"" bgcolor=""plum"">FUNCTIONAL NOT UNDER SYSADMIN</td></tr> <tr><td align=""left"" bgcolor=""lightblue"">ACCESS</td></tr> <tr><td align=""left"" bgcolor=""azure"">ACCESS NOT UNDER SYSADMIN</td></tr> <tr><td align=""left"" bgcolor=""orange"">NOT UNDER ACCOUNTADMIN</td></tr> </table>>];"; sbGraphViz.AppendLine(legend); #endregion // Close the graph sbGraphViz.AppendLine("}"); FileIOHelper.SaveFileToPath(sbGraphViz.ToString(), FilePathMap.Report_GraphViz_RoleAndItsRelationsGrants_FilePath(role.Name), false); } return(1); }, (finalResult) => { Interlocked.Add(ref j, finalResult); if (j % 50 == 0) { Console.Write("[{0}].", j); } } ); loggerConsole.Info("Completed {0} Roles", rolesList.Count); #endregion #region HTML file with Links to Files loggerConsole.Info("Creating HTML links for {0} roles", rolesList.Count); // Create the HTML page with links for all the images XmlWriterSettings xmlWriterSettings = new XmlWriterSettings(); xmlWriterSettings.OmitXmlDeclaration = true; xmlWriterSettings.Indent = true; using (XmlWriter xmlWriter = XmlWriter.Create(FilePathMap.UsersRolesAndGrantsWebReportFilePath(), xmlWriterSettings)) { xmlWriter.WriteDocType("html", null, null, null); xmlWriter.WriteStartElement("html"); xmlWriter.WriteStartElement("head"); xmlWriter.WriteStartElement("title"); xmlWriter.WriteString(String.Format("Snowflake Grants Report {0} {1} roles", programOptions.ReportJob.Connection, rolesList.Count)); xmlWriter.WriteEndElement(); // </title> xmlWriter.WriteEndElement(); // </head> xmlWriter.WriteStartElement("body"); xmlWriter.WriteStartElement("table"); xmlWriter.WriteAttributeString("border", "1"); // Header row xmlWriter.WriteStartElement("tr"); xmlWriter.WriteStartElement("th"); xmlWriter.WriteString("Role"); xmlWriter.WriteEndElement(); xmlWriter.WriteStartElement("th"); xmlWriter.WriteString("Type"); xmlWriter.WriteEndElement(); xmlWriter.WriteStartElement("th"); xmlWriter.WriteString("# Parents"); xmlWriter.WriteEndElement(); xmlWriter.WriteStartElement("th"); xmlWriter.WriteString("# Children"); xmlWriter.WriteEndElement(); xmlWriter.WriteStartElement("th"); xmlWriter.WriteString("# Ancestry Paths"); xmlWriter.WriteEndElement(); xmlWriter.WriteStartElement("th"); xmlWriter.WriteString("Online"); xmlWriter.WriteEndElement(); xmlWriter.WriteStartElement("th"); xmlWriter.WriteString("SVG"); xmlWriter.WriteEndElement(); xmlWriter.WriteStartElement("th"); xmlWriter.WriteString("PNG"); xmlWriter.WriteEndElement(); xmlWriter.WriteStartElement("th"); xmlWriter.WriteString("PDF"); xmlWriter.WriteEndElement(); xmlWriter.WriteEndElement(); // </tr> foreach (Role role in rolesList) { xmlWriter.WriteStartElement("tr"); xmlWriter.WriteStartElement("td"); xmlWriter.WriteString(role.Name); xmlWriter.WriteEndElement(); xmlWriter.WriteStartElement("td"); xmlWriter.WriteString(role.Type.ToString()); xmlWriter.WriteEndElement(); xmlWriter.WriteStartElement("td"); xmlWriter.WriteString(role.NumParentRoles.ToString()); xmlWriter.WriteEndElement(); xmlWriter.WriteStartElement("td"); xmlWriter.WriteString(role.NumChildRoles.ToString()); xmlWriter.WriteEndElement(); xmlWriter.WriteStartElement("td"); xmlWriter.WriteString(role.NumAncestryPaths.ToString()); xmlWriter.WriteEndElement(); string graphText = FileIOHelper.ReadFileFromPath(FilePathMap.Report_GraphViz_RoleAndItsRelationsGrants_FilePath(role.Name)); xmlWriter.WriteStartElement("td"); // https://edotor.net xmlWriter.WriteStartElement("a"); xmlWriter.WriteAttributeString("href", String.Format("https://edotor.net/?#{0}", Uri.EscapeDataString(graphText))); xmlWriter.WriteString("Online"); xmlWriter.WriteEndElement(); // </a> // // http://magjac.com/graphviz-visual-editor // xmlWriter.WriteStartElement("a"); // xmlWriter.WriteAttributeString("href", String.Format("http://magjac.com/graphviz-visual-editor/?dot={0}", Uri.EscapeDataString(graphText))); // xmlWriter.WriteString("Opt2"); // xmlWriter.WriteEndElement(); // </a> // // https://stamm-wilbrandt.de/GraphvizFiddle/2.1.2/index.html // xmlWriter.WriteStartElement("a"); // xmlWriter.WriteAttributeString("href", String.Format("https://stamm-wilbrandt.de/GraphvizFiddle/2.1.2/index.html?#{0}", Uri.EscapeDataString(graphText))); // xmlWriter.WriteString("Opt3"); // xmlWriter.WriteEndElement(); // </a> // // https://dreampuf.github.io/GraphvizOnline // xmlWriter.WriteStartElement("a"); // xmlWriter.WriteAttributeString("href", String.Format("https://dreampuf.github.io/GraphvizOnline/#{0}", Uri.EscapeDataString(graphText))); // xmlWriter.WriteString("Opt4"); // xmlWriter.WriteEndElement(); // </a> xmlWriter.WriteEndElement(); // </td> xmlWriter.WriteStartElement("td"); xmlWriter.WriteStartElement("a"); xmlWriter.WriteAttributeString("href", FilePathMap.Report_Diagram_SVG_RoleAndItsRelationsGrants_FilePath(role.Name, false)); xmlWriter.WriteString("SVG"); xmlWriter.WriteEndElement(); // </a> xmlWriter.WriteEndElement(); // </td> xmlWriter.WriteStartElement("td"); xmlWriter.WriteStartElement("a"); xmlWriter.WriteAttributeString("href", FilePathMap.Report_Diagram_PNG_RoleAndItsRelationsGrants_FilePath(role.Name, false)); xmlWriter.WriteString("PNG"); xmlWriter.WriteEndElement(); // </a> xmlWriter.WriteEndElement(); // </td> xmlWriter.WriteStartElement("td"); xmlWriter.WriteStartElement("a"); xmlWriter.WriteAttributeString("href", FilePathMap.Report_Diagram_PDF_RoleAndItsRelationsGrants_FilePath(role.Name, false)); xmlWriter.WriteString("PDF"); xmlWriter.WriteEndElement(); // </a> xmlWriter.WriteEndElement(); // </td> xmlWriter.WriteEndElement(); // </tr> } xmlWriter.WriteEndElement(); // </table> xmlWriter.WriteEndElement(); // </body> xmlWriter.WriteEndElement(); // </html> } #endregion #region Make SVG, PNG and PDF Files with GraphViz binaries loggerConsole.Info("Making picture files for {0} roles", rolesList.Count); GraphVizDriver graphVizDriver = new GraphVizDriver(); graphVizDriver.ValidateToolInstalled(programOptions); if (graphVizDriver.ExecutableFilePath.Length > 0) { j = 0; Parallel.ForEach <Role, int>( rolesList, parallelOptions, () => 0, (role, loop, subtotal) => { loggerConsole.Info("Rendering graphs for {0}", role); graphVizDriver.ConvertGraphVizToFile( FilePathMap.Report_GraphViz_RoleAndItsRelationsGrants_FilePath(role.Name), FilePathMap.Report_Diagram_SVG_RoleAndItsRelationsGrants_FilePath(role.Name, true), "svg"); graphVizDriver.ConvertGraphVizToFile( FilePathMap.Report_GraphViz_RoleAndItsRelationsGrants_FilePath(role.Name), FilePathMap.Report_Diagram_PNG_RoleAndItsRelationsGrants_FilePath(role.Name, true), "png"); graphVizDriver.ConvertGraphVizToFile( FilePathMap.Report_GraphViz_RoleAndItsRelationsGrants_FilePath(role.Name), FilePathMap.Report_Diagram_PDF_RoleAndItsRelationsGrants_FilePath(role.Name, true), "pdf"); return(1); }, (finalResult) => { Interlocked.Add(ref j, finalResult); if (j % 10 == 0) { Console.Write("[{0}].", j); } } ); loggerConsole.Info("Completed {0} Roles", rolesList.Count); } #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_Grant_FolderPath()); FileIOHelper.CreateFolder(this.FilePathMap.Report_Role_FolderPath()); #region Grants OF - Members of Roles (Roles and Users) loggerConsole.Info("Process Grants OF"); List <RoleMember> grantsOfRolesAndUsersList = FileIOHelper.ReadListFromCSVFile <RoleMember>(FilePathMap.Data_RoleShowGrantsOf_FilePath(), new RoleMemberShowGrantsMap(), new string[] { "No data returned", "SQL compilation error", "does not exist" }); if (grantsOfRolesAndUsersList != null) { foreach (RoleMember roleMember in grantsOfRolesAndUsersList) { // Unescape special names of roles roleMember.Name = roleMember.Name.Trim('"'); roleMember.GrantedTo = roleMember.GrantedTo.Trim('"'); roleMember.GrantedBy = roleMember.GrantedBy.Trim('"'); } grantsOfRolesAndUsersList = grantsOfRolesAndUsersList.OrderBy(g => g.ObjectType).ThenBy(g => g.Name).ToList(); FileIOHelper.WriteListToCSVFile <RoleMember>(grantsOfRolesAndUsersList, new RoleMemberMap(), FilePathMap.Report_RoleMember_FilePath()); } #endregion #region Grants ON and Grants TO grants for everything loggerConsole.Info("Process Grants ON, TO and FUTURE grants"); List <Grant> grantsNonUniqueList = new List <Grant>(); List <Grant> grantsOnRolesList = FileIOHelper.ReadListFromCSVFile <Grant>(FilePathMap.Data_RoleShowGrantsOn_FilePath(), new GrantShowGrantsMap(), new string[] { "No data returned", "SQL compilation error", "does not exist" }); if (grantsOnRolesList != null) { loggerConsole.Info("Granted ON {0} grants", grantsOnRolesList.Count); grantsNonUniqueList.AddRange(grantsOnRolesList); } List <Grant> grantsToRolesList = FileIOHelper.ReadListFromCSVFile <Grant>(FilePathMap.Data_RoleShowGrantsTo_FilePath(), new GrantShowGrantsMap(), new string[] { "No data returned", "SQL compilation error", "does not exist" }); if (grantsToRolesList != null) { loggerConsole.Info("Granted TO {0} grants", grantsToRolesList.Count); grantsNonUniqueList.AddRange(grantsToRolesList); } List <Grant> grantsFutureDatabasesList = FileIOHelper.ReadListFromCSVFile <Grant>(FilePathMap.Data_FutureGrantsInDatabases_FilePath(), new GrantShowFutureGrantsMap(), new string[] { "No data returned", "SQL compilation error", "does not exist" }); if (grantsFutureDatabasesList != null) { loggerConsole.Info("Future Grants on Databases {0} grants", grantsFutureDatabasesList.Count); grantsNonUniqueList.AddRange(grantsFutureDatabasesList); } List <Grant> grantsFutureSchemasList = FileIOHelper.ReadListFromCSVFile <Grant>(FilePathMap.Data_FutureGrantsInSchemas_FilePath(), new GrantShowFutureGrantsMap(), new string[] { "No data returned", "SQL compilation error", "does not exist" }); if (grantsFutureSchemasList != null) { loggerConsole.Info("Future Grants on Schemas {0} grants", grantsFutureSchemasList.Count); grantsNonUniqueList.AddRange(grantsFutureSchemasList); } loggerConsole.Info("All Grants on Schemas {0} grants", grantsNonUniqueList.Count); #region Remove duplicates loggerConsole.Info("Removing duplicate grants"); // Now remove duplicate USAGE and OWNERSHIP rows using these kinds of IDs // OWNERSHIP-ROLE-AAD_PROVISIONER-USERADMIN // USAGE-ROLE-AAD_PROVISIONER-USERADMIN // These occur only on ROLEs and because a role in hierarchy can be seen when parent says SHOW GRANTS ON and child says SHOW GRANTS TO List <Grant> grantsUniqueList = new List <Grant>(grantsNonUniqueList.Count); var uniqueGrantsGrouped = grantsNonUniqueList.GroupBy(g => g.UniqueIdentifier); foreach (var group in uniqueGrantsGrouped) { grantsUniqueList.Add(group.First()); } // Unescape special names of objects foreach (Grant grant in grantsUniqueList) { grant.GrantedTo = grant.GrantedTo.Trim('"'); if (grant.GrantedBy != null) { grant.GrantedBy = grant.GrantedBy.Trim('"'); } } grantsUniqueList = grantsUniqueList.OrderBy(g => g.ObjectType).ThenBy(g => g.ObjectName).ThenBy(g => g.GrantedTo).ToList(); FileIOHelper.WriteListToCSVFile <Grant>(grantsUniqueList, new GrantMap(), FilePathMap.Report_RoleGrant_FilePath()); #endregion #region Individual Object Types loggerConsole.Info("Processing individual Object Types"); // Break them up by the type var groupObjectTypesGrouped = grantsUniqueList.GroupBy(g => g.ObjectType); List <SingleStringRow> objectTypesList = new List <SingleStringRow>(groupObjectTypesGrouped.Count()); foreach (var group in groupObjectTypesGrouped) { loggerConsole.Info("Processing grants for {0}", group.Key); SingleStringRow objectType = new SingleStringRow(); objectType.Value = group.Key; objectTypesList.Add(objectType); #region Save this set of grants for Object Type List <Grant> grantsOfObjectTypeList = group.ToList(); // Save this set as is for one of the tables in report FileIOHelper.WriteListToCSVFile <Grant>(grantsOfObjectTypeList, new GrantMap(), FilePathMap.Report_RoleGrant_ObjectType_FilePath(group.Key)); // Pivot each section into this kind of table // // ObjectType | ObjectName | GrantedTo | OWNERSHIP | USAGE | REFERENCE | GrantN // DATABASE | SomeDB | SomeRole | X | x+ | | // Where X+ means WithGrantOption=True // X means WithGrantOption=False List <ObjectTypeGrant> objectGrantsList = new List <ObjectTypeGrant>(grantsOfObjectTypeList.Count / 5); Dictionary <string, int> privilegeToColumnDictionary = new Dictionary <string, int>(20); #endregion #region Convert this set into pivot List <string> listOfPrivileges = grantsOfObjectTypeList.Select(g => g.Privilege).Distinct().OrderBy(g => g).ToList(); // Make USAGE and OWNERSHIP be the first columns switch (group.Key) { case "ACCOUNT": break; case "DATABASE": case "FILE_FORMAT": case "FUNCTION": case "INTEGRATION": case "PROCEDURE": case "ROLE": case "SCHEMA": case "SEQUENCE": case "WAREHOUSE": listOfPrivileges.Remove("OWNERSHIP"); listOfPrivileges.Insert(0, "OWNERSHIP"); listOfPrivileges.Remove("USAGE"); listOfPrivileges.Insert(1, "USAGE"); break; case "EXTERNAL_TABLE": case "MANAGED_ACCOUNT": case "MASKING_POLICY": case "MATERIALIZED_VIEW": case "NETWORK_POLICY": case "NOTIFICATION_SUBSCRIPTION": case "PIPE": case "RESOURCE_MONITOR": case "SHARE": case "STAGE": case "STREAM": case "TABLE": case "TASK": case "USER": case "VIEW": listOfPrivileges.Remove("OWNERSHIP"); listOfPrivileges.Insert(0, "OWNERSHIP"); break; default: break; } for (int i = 0; i < listOfPrivileges.Count; i++) { privilegeToColumnDictionary.Add(listOfPrivileges[i], i); } ObjectTypeGrant latestGrantRow = new ObjectTypeGrant(); foreach (Grant grant in grantsOfObjectTypeList) { // Loop through rows, starting new objects for each combination of ObjectType+ObjectName+GrantedTo when necessary // ObjectType is always the same in this grouping // ObjectName if (latestGrantRow.ObjectType != grant.ObjectType || latestGrantRow.ObjectName != grant.ObjectName || latestGrantRow.GrantedTo != grant.GrantedTo) { // Need to start new row latestGrantRow = new ObjectTypeGrant(); latestGrantRow.ObjectType = grant.ObjectType; latestGrantRow.ObjectName = grant.ObjectName; latestGrantRow.DBName = grant.DBName; latestGrantRow.SchemaName = grant.SchemaName; latestGrantRow.EntityName = grant.EntityName; latestGrantRow.GrantedTo = grant.GrantedTo; objectGrantsList.Add(latestGrantRow); } // Find out which column to use int privilegeColumnNumber = privilegeToColumnDictionary[grant.Privilege]; switch (privilegeColumnNumber) { case 0: latestGrantRow.Privilege0 = grant.DisplaySettingWithGrantOption; break; case 1: latestGrantRow.Privilege1 = grant.DisplaySettingWithGrantOption; break; case 2: latestGrantRow.Privilege2 = grant.DisplaySettingWithGrantOption; break; case 3: latestGrantRow.Privilege3 = grant.DisplaySettingWithGrantOption; break; case 4: latestGrantRow.Privilege4 = grant.DisplaySettingWithGrantOption; break; case 5: latestGrantRow.Privilege5 = grant.DisplaySettingWithGrantOption; break; case 6: latestGrantRow.Privilege6 = grant.DisplaySettingWithGrantOption; break; case 7: latestGrantRow.Privilege7 = grant.DisplaySettingWithGrantOption; break; case 8: latestGrantRow.Privilege8 = grant.DisplaySettingWithGrantOption; break; case 9: latestGrantRow.Privilege9 = grant.DisplaySettingWithGrantOption; break; case 10: latestGrantRow.Privilege10 = grant.DisplaySettingWithGrantOption; break; case 11: latestGrantRow.Privilege11 = grant.DisplaySettingWithGrantOption; break; case 12: latestGrantRow.Privilege12 = grant.DisplaySettingWithGrantOption; break; case 13: latestGrantRow.Privilege13 = grant.DisplaySettingWithGrantOption; break; case 14: latestGrantRow.Privilege14 = grant.DisplaySettingWithGrantOption; break; case 15: latestGrantRow.Privilege15 = grant.DisplaySettingWithGrantOption; break; case 16: latestGrantRow.Privilege16 = grant.DisplaySettingWithGrantOption; break; case 17: latestGrantRow.Privilege17 = grant.DisplaySettingWithGrantOption; break; case 18: latestGrantRow.Privilege18 = grant.DisplaySettingWithGrantOption; break; case 19: latestGrantRow.Privilege19 = grant.DisplaySettingWithGrantOption; break; default: // Can't fit more than 20 privileges logger.Warn("More then 20 Privileges reached with {0} privilege for object type {1}", grant.Privilege, grant.ObjectType); break; } } List <string> privilegeColumnNames = new List <string>(privilegeToColumnDictionary.Count); for (int i = 0; i < privilegeToColumnDictionary.Count; i++) { privilegeColumnNames.Add(String.Empty); } foreach (var entry in privilegeToColumnDictionary) { privilegeColumnNames[entry.Value] = entry.Key; } // Save the pivot FileIOHelper.WriteListToCSVFile <ObjectTypeGrant>(objectGrantsList, new ObjectTypeGrantMap(privilegeColumnNames), FilePathMap.Report_RoleGrant_ObjectType_Pivoted_FilePath(group.Key)); #endregion } FileIOHelper.WriteListToCSVFile <SingleStringRow>(objectTypesList, new SingleStringRowMap(), FilePathMap.Report_RoleGrant_ObjectTypes_FilePath()); #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); ProgramOptions programOptionsLeft = new ProgramOptions(); programOptionsLeft.ReportFolderPath = programOptions.LeftReportFolderPath; FilePathMap filePathMapLeft = new FilePathMap(programOptionsLeft); ProgramOptions programOptionsRight = new ProgramOptions(); programOptionsRight.ReportFolderPath = programOptions.RightReportFolderPath; FilePathMap filePathMapRight = new FilePathMap(programOptionsRight); try { // Load all the grants from both sides loggerConsole.Info("Loading Grants for Left side {0}", programOptionsLeft.ReportFolderPath); List <Grant> grantsAllLeftList = FileIOHelper.ReadListFromCSVFile <Grant>(filePathMapLeft.Report_RoleGrant_FilePath(), new GrantMap()); if (grantsAllLeftList == null || grantsAllLeftList.Count == 0) { loggerConsole.Warn("No grants to compare on the Left side"); return(false); } loggerConsole.Info("Loading Grants for Right side {0}", programOptionsRight.ReportFolderPath); List <Grant> grantsAllRightList = FileIOHelper.ReadListFromCSVFile <Grant>(filePathMapRight.Report_RoleGrant_FilePath(), new GrantMap()); if (grantsAllRightList == null || grantsAllRightList.Count == 0) { loggerConsole.Warn("No grants to compare with on the Right side"); return(false); } // If we got here, we potentially have a large list of Grants on both sides all in memory, begin comparison logger.Trace("Left side list of Grants {0} items", grantsAllLeftList.Count); logger.Trace("Right side list of Grants {0} items", grantsAllRightList.Count); loggerConsole.Info("Left side {0} grants <-> Right side {1} Grants", grantsAllLeftList.Count, grantsAllRightList.Count); // Avoid duplicate grants by Grouping first // Really could only happen when converting from the spreadsheets, not with any dumps from Snowflake Dictionary <string, Grant> grantsAllLeftDict = new Dictionary <string, Grant>(grantsAllLeftList.Count); foreach (Grant grant in grantsAllLeftList) { if (grantsAllLeftDict.ContainsKey(grant.UniqueIdentifier) == false) { grantsAllLeftDict.Add(grant.UniqueIdentifier, grant); } } Dictionary <string, Grant> grantsAllRightDict = new Dictionary <string, Grant>(grantsAllRightList.Count); foreach (Grant grant in grantsAllRightList) { if (grantsAllRightDict.ContainsKey(grant.UniqueIdentifier) == false) { grantsAllRightDict.Add(grant.UniqueIdentifier, grant); } } // Here is what we get in the reference and difference lists // List List // Reference Difference Action // AAA AAA Compare items // BBB item in Difference is MISSING // CCC item in Difference is EXTRA // The columns of Grants are: // Privilege,ObjectType,ObjectName,GrantedTo,DBName,SchemaName,EntityName,GrantedBy,WithGrantOption,CreatedOn,CreatedOnUTC // Out of those, the identifying combination of a Grant is: // Privilege,ObjectType,ObjectName,GrantedTo // And it is stored in Grant.UniqueIdentifier // Assume 1% differences List <GrantDifference> grantDifferencesList = new List <GrantDifference>(grantsAllLeftList.Count / 100); loggerConsole.Info("Comparing Left side -> Right Side"); int j = 0; // First loop through Reference list looking for matches foreach (Grant grantLeft in grantsAllLeftDict.Values) { Grant grantRight = null; if (grantsAllRightDict.TryGetValue(grantLeft.UniqueIdentifier, out grantRight) == true) { // Found matching entity AAA. Let's compare them against each other List <string> differentPropertiesList = new List <string>(2); // Only compare if GrantedBy is non-empty if ((grantLeft.GrantedBy.Length > 0 && grantRight.GrantedBy.Length > 0) && grantLeft.GrantedBy != grantRight.GrantedBy) { differentPropertiesList.Add("GrantedBy"); } // Only compare of CreatedOn is a real date if ((grantLeft.CreatedOn != DateTime.MinValue && grantRight.CreatedOn != DateTime.MinValue) && grantLeft.CreatedOn != grantRight.CreatedOn) { // Sometimes the CreatedOn is only different just a tiny little bit like here: // CreatedOnUTCLeft CreatedOnUTCRight // 2020-12-01T01:12:16.1360000Z 2020-12-01T01:12:16.3940000Z // As you can see it is different only in milliseconds. Must be an FDB thing // Going to ignore sub-second differences TimeSpan timeDifference = grantLeft.CreatedOn - grantRight.CreatedOn; if (Math.Abs(timeDifference.TotalSeconds) > 1) { differentPropertiesList.Add("CreatedOn"); } } if (grantLeft.WithGrantOption != grantRight.WithGrantOption) { differentPropertiesList.Add("WithGrantOption"); } if (differentPropertiesList.Count > 0) { GrantDifference grantDifference = new GrantDifference(); grantDifference.UniqueIdentifier = grantRight.UniqueIdentifier; grantDifference.Privilege = grantRight.Privilege; grantDifference.ObjectType = grantRight.ObjectType; grantDifference.ObjectName = grantRight.ObjectName; grantDifference.GrantedTo = grantRight.GrantedTo; grantDifference.DBName = grantRight.DBName; grantDifference.SchemaName = grantRight.SchemaName; grantDifference.EntityName = grantRight.EntityName; grantDifference.ReportLeft = programOptions.LeftReportFolderPath; grantDifference.ReportRight = programOptions.RightReportFolderPath; grantDifference.Difference = DIFFERENCE_DIFFERENT; grantDifference.DifferenceDetails = String.Join(',', differentPropertiesList.ToArray()); grantDifference.GrantedByLeft = grantLeft.GrantedBy; grantDifference.CreatedOnUTCLeft = grantLeft.CreatedOnUTC; grantDifference.WithGrantOptionLeft = grantLeft.WithGrantOption; grantDifference.GrantedByRight = grantRight.GrantedBy; grantDifference.CreatedOnUTCRight = grantRight.CreatedOnUTC; grantDifference.WithGrantOptionRight = grantRight.WithGrantOption; grantDifferencesList.Add(grantDifference); } // Remove this object as already considered grantsAllRightDict[grantRight.UniqueIdentifier] = null; } else { // No match. This must be entity BBB, where item in Difference is MISSING GrantDifference grantDifference = new GrantDifference(); grantDifference.UniqueIdentifier = grantLeft.UniqueIdentifier; grantDifference.Privilege = grantLeft.Privilege; grantDifference.ObjectType = grantLeft.ObjectType; grantDifference.ObjectName = grantLeft.ObjectName; grantDifference.GrantedTo = grantLeft.GrantedTo; grantDifference.DBName = grantLeft.DBName; grantDifference.SchemaName = grantLeft.SchemaName; grantDifference.EntityName = grantLeft.EntityName; grantDifference.ReportLeft = programOptions.LeftReportFolderPath; grantDifference.ReportRight = programOptions.RightReportFolderPath; grantDifference.Difference = DIFFERENCE_MISSING; grantDifference.DifferenceDetails = PROPERTY_ENTIRE_OBJECT; grantDifference.GrantedByLeft = grantLeft.GrantedBy; grantDifference.CreatedOnUTCLeft = grantLeft.CreatedOnUTC; grantDifference.WithGrantOptionLeft = grantLeft.WithGrantOption; grantDifferencesList.Add(grantDifference); } // Remove this object as already considered grantsAllLeftDict[grantLeft.UniqueIdentifier] = null; j++; if (j % 1000 == 0) { Console.Write("{0}.", j); } } Console.WriteLine("Processed {0} comparisons", grantsAllLeftDict.Count); loggerConsole.Info("Comparing Right side -> Left Side"); j = 0; foreach (Grant grantRight in grantsAllRightDict.Values) { if (grantRight != null) { GrantDifference grantDifference = new GrantDifference(); grantDifference.UniqueIdentifier = grantRight.UniqueIdentifier; grantDifference.Privilege = grantRight.Privilege; grantDifference.ObjectType = grantRight.ObjectType; grantDifference.ObjectName = grantRight.ObjectName; grantDifference.GrantedTo = grantRight.GrantedTo; grantDifference.DBName = grantRight.DBName; grantDifference.SchemaName = grantRight.SchemaName; grantDifference.EntityName = grantRight.EntityName; grantDifference.ReportLeft = programOptions.LeftReportFolderPath; grantDifference.ReportRight = programOptions.RightReportFolderPath; grantDifference.Difference = DIFFERENCE_EXTRA; grantDifference.DifferenceDetails = PROPERTY_ENTIRE_OBJECT; grantDifference.GrantedByRight = grantRight.GrantedBy; grantDifference.CreatedOnUTCRight = grantRight.CreatedOnUTC; grantDifference.WithGrantOptionRight = grantRight.WithGrantOption; grantDifferencesList.Add(grantDifference); } j++; if (j % 1000 == 0) { Console.Write("{0}.", j); } } loggerConsole.Info("Found {0} differences", grantDifferencesList.Count); FileIOHelper.WriteListToCSVFile <GrantDifference>(grantDifferencesList, new GrantDifferenceMap(), FilePathMap.Report_RoleGrant_Differences_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); } }
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_Role_FolderPath()); FileIOHelper.CreateFolder(this.FilePathMap.Report_Grant_FolderPath()); List <Role> rolesList = FileIOHelper.ReadListFromCSVFile <Role>(FilePathMap.Data_ShowRoles_FilePath(), new RoleShowRolesMap()); List <RoleMember> grantsOfRolesList = FileIOHelper.ReadListFromCSVFile <RoleMember>(FilePathMap.Report_RoleMember_FilePath(), new RoleMemberMap()); if (rolesList != null) { Dictionary <string, Role> rolesDict = rolesList.ToDictionary(k => k.Name, r => r); loggerConsole.Info("Parsing role details for {0} roles", rolesList.Count); int j = 0; foreach (Role role in rolesList) { logger.Trace("Parsing details for role {0}", role.Name); if (String.Compare(role.Name, quoteObjectIdentifier(role.Name), true, CultureInfo.InvariantCulture) == 0) { role.IsObjectIdentifierSpecialCharacters = false; } else { role.IsObjectIdentifierSpecialCharacters = true; } // Parse the list of users and child roles if (grantsOfRolesList != null) { List <RoleMember> grantsOfRoleToUserList = grantsOfRolesList.Where(g => g.Name == role.Name && g.ObjectType == "USER").ToList(); if (grantsOfRoleToUserList != null && grantsOfRoleToUserList.Count > 0) { role.AssignedUsers = String.Join(',', grantsOfRoleToUserList.Select(g => g.GrantedTo).ToArray()); } } j++; if (j % 50 == 0) { Console.Write("{0}.", j); } } Console.WriteLine("Done {0} items", rolesList.Count); loggerConsole.Info("Parsing role usage hierarchy"); // Now load the all the Roles and their USAGE permission to build parent and child hierarchy List <Grant> grantsToRolesList = FileIOHelper.ReadListFromCSVFile <Grant>(FilePathMap.Report_RoleGrant_ObjectType_FilePath("ROLE"), new GrantMap()); if (grantsToRolesList != null) { List <Grant> grantsToRolesUsageList = grantsToRolesList.Where(g => g.Privilege == "USAGE").ToList(); if (grantsToRolesUsageList != null) { foreach (Grant grant in grantsToRolesUsageList) { Role roleBeingGranted; Role grantedToRole; if (rolesDict.TryGetValue(grant.ObjectName, out roleBeingGranted) == true && rolesDict.TryGetValue(grant.GrantedTo, out grantedToRole) == true) { grantedToRole.ChildRoles.Add(roleBeingGranted); roleBeingGranted.ParentRoles.Add(grantedToRole); } } } } loggerConsole.Info("Analyzing role types"); // Load selected types of grants to use to determine whether the role is Functional or Access // Going to just use SCHEMA and TABLE List <Grant> grantsToSchemaAllList = FileIOHelper.ReadListFromCSVFile <Grant>(FilePathMap.Report_RoleGrant_ObjectType_FilePath("SCHEMA"), new GrantMap()); List <Grant> grantsToTableAllList = FileIOHelper.ReadListFromCSVFile <Grant>(FilePathMap.Report_RoleGrant_ObjectType_FilePath("TABLE"), new GrantMap()); List <Grant> grantsToViewAllList = FileIOHelper.ReadListFromCSVFile <Grant>(FilePathMap.Report_RoleGrant_ObjectType_FilePath("VIEW"), new GrantMap()); List <Grant> grantsToRoleAllList = FileIOHelper.ReadListFromCSVFile <Grant>(FilePathMap.Report_RoleGrant_ObjectType_FilePath("ROLE"), new GrantMap()); Dictionary <string, List <Grant> > grantsToSchemaDict = new Dictionary <string, List <Grant> >(); Dictionary <string, List <Grant> > grantsToTableDict = new Dictionary <string, List <Grant> >(); Dictionary <string, List <Grant> > grantsToViewDict = new Dictionary <string, List <Grant> >(); Dictionary <string, List <Grant> > grantsToRoleDict = new Dictionary <string, List <Grant> >(); if (grantsToSchemaAllList != null) { var grantsToSchemaGroups = grantsToSchemaAllList.GroupBy(g => g.GrantedTo); foreach (var grantsGroup in grantsToSchemaGroups) { grantsToSchemaDict.Add(grantsGroup.Key, grantsGroup.ToList()); } } if (grantsToTableAllList != null) { var grantsToTableGroups = grantsToTableAllList.GroupBy(g => g.GrantedTo); foreach (var grantsGroup in grantsToTableGroups) { grantsToTableDict.Add(grantsGroup.Key, grantsGroup.ToList()); } } if (grantsToViewAllList != null) { var grantsToViewGroups = grantsToViewAllList.GroupBy(g => g.GrantedTo); foreach (var grantsGroup in grantsToViewGroups) { grantsToViewDict.Add(grantsGroup.Key, grantsGroup.ToList()); } } if (grantsToRoleAllList != null) { var grantsToRoleGroups = grantsToRoleAllList.GroupBy(g => g.GrantedTo); foreach (var grantsGroup in grantsToRoleGroups) { grantsToRoleDict.Add(grantsGroup.Key, grantsGroup.ToList()); } } j = 0; // Detect role types and inheritance rollups foreach (Role role in rolesList) { // Detect built-in roles and if (role.Name == "ACCOUNTADMIN" || role.Name == "SECURITYADMIN" || role.Name == "USERADMIN" || role.Name == "SYSADMIN" || role.Name == "PUBLIC") { role.Type = RoleType.BuiltIn; } else if (role.Name == "AAD_PROVISIONER" || role.Name == "OKTA_PROVISIONER" || role.Name == "GENERIC_SCIM_PROVISIONER") { role.Type = RoleType.SCIM; } else { // Detect other types of roles Role roleSECURITYADMIN; Role roleUSERADMIN; Role roleSYSADMIN; Role roleACCOUNTADMIN; if (rolesDict.TryGetValue("ACCOUNTADMIN", out roleACCOUNTADMIN) == true && rolesDict.TryGetValue("SECURITYADMIN", out roleSECURITYADMIN) == true && rolesDict.TryGetValue("USERADMIN", out roleUSERADMIN) && rolesDict.TryGetValue("SYSADMIN", out roleSYSADMIN)) { // Check what we are rooted to if ((role.RollsUpTo(roleUSERADMIN) == true || role.RollsUpTo(roleSECURITYADMIN) == true) && role.RollsUpTo(roleSYSADMIN) == false) { role.Type = RoleType.RoleManagement; } // Check between Functional and Access List <Grant> grantsToSchemaList = null; List <Grant> grantsToTableList = null; List <Grant> grantsToViewList = null; List <Grant> grantsToRoleList = null; grantsToSchemaDict.TryGetValue(role.Name, out grantsToSchemaList); grantsToTableDict.TryGetValue(role.Name, out grantsToTableList); grantsToViewDict.TryGetValue(role.Name, out grantsToViewList); grantsToRoleDict.TryGetValue(role.Name, out grantsToRoleList); // Schemas first if (role.Type == RoleType.Unknown && grantsToSchemaList != null) { List <Grant> grantsToSchemaForThisRoleList = grantsToSchemaList.Where( g => g.GrantedTo == role.Name && g.Privilege != "USAGE" && g.Privilege != "OWNERSHIP" && g.Privilege != "MONITOR").ToList(); if (grantsToSchemaForThisRoleList != null && grantsToSchemaForThisRoleList.Count > 0) { role.Type = RoleType.Access; } } // Tables second, and only if the role type is still undetermined if (role.Type == RoleType.Unknown && grantsToTableList != null) { List <Grant> grantsToTableForThisRoleList = grantsToTableList.Where( g => g.GrantedTo == role.Name && g.Privilege != "USAGE" && g.Privilege != "OWNERSHIP" && g.Privilege != "REFERENCES" && g.Privilege != "REBUILD").ToList(); if (grantsToTableForThisRoleList != null && grantsToTableForThisRoleList.Count > 0) { role.Type = RoleType.Access; } } // Views third, and only if the role type is still undetermined if (role.Type == RoleType.Unknown && grantsToViewList != null) { List <Grant> grantsToViewForThisRoleList = grantsToViewList.Where( g => g.GrantedTo == role.Name && g.Privilege != "USAGE" && g.Privilege != "OWNERSHIP" && g.Privilege != "REFERENCES" && g.Privilege != "REBUILD").ToList(); if (grantsToViewForThisRoleList != null && grantsToViewForThisRoleList.Count > 0) { role.Type = RoleType.Access; } } // After comparing to schema, table and view, it is still unknown. Does it have any other roles below which would make it Functional? if (role.Type == RoleType.Unknown && grantsToRoleList != null) { List <Grant> grantsToRoleForThisRoleList = grantsToRoleList.Where( g => g.GrantedTo == role.Name && g.Privilege == "USAGE").ToList(); if (grantsToRoleForThisRoleList != null && grantsToRoleForThisRoleList.Count > 0) { role.Type = RoleType.Functional; } } if (role.Type == RoleType.Unknown && role.RollsUpTo(roleACCOUNTADMIN) == false) { // This role is not connected to the proper hierarchy role.Type = RoleType.UnknownNotUnderAccountAdmin; } if (role.Type == RoleType.Functional && role.RollsUpTo(roleSYSADMIN) == false) { // Functional but not in the right hierarchy role.Type = RoleType.FunctionalNotUnderSysadmin; } if (role.Type == RoleType.Access && role.RollsUpTo(roleSYSADMIN) == false) { // Access but not in the right hierarchy role.Type = RoleType.AccessNotUnderSysadmin; } } } j++; if (j % 50 == 0) { Console.Write("{0}.", j); } } Console.WriteLine("Done {0} items", rolesList.Count); loggerConsole.Info("Building role ancestry paths"); // Now create role usage hiearchy if (grantsToRolesList != null) { List <Grant> grantsToRolesUsageList = grantsToRolesList.Where(g => g.Privilege == "USAGE").ToList(); if (grantsToRolesUsageList != null) { List <RoleHierarchy> roleHierarchiesList = new List <RoleHierarchy>(grantsToRolesUsageList.Count); loggerConsole.Info("Processing Role Usage for {0} hierarchy records", grantsToRolesUsageList.Count); j = 0; // Build stuff for flow diagrams using the USAGE rights and the role hierarchy foreach (Grant grant in grantsToRolesUsageList) { Role roleBeingGranted; if (rolesDict.TryGetValue(grant.ObjectName, out roleBeingGranted) == true) { RoleHierarchy roleHierarchy = new RoleHierarchy(); roleHierarchy.Name = roleBeingGranted.Name; roleHierarchy.Type = roleBeingGranted.Type; roleHierarchy.AncestryPaths = roleBeingGranted.AncestryPaths; roleHierarchy.NumAncestryPaths = roleHierarchy.AncestryPaths.Split('\n').Count(); roleHierarchy.GrantedTo = grant.GrantedTo; if (roleHierarchy.AncestryPaths.StartsWith("ACCOUNTADMIN", true, CultureInfo.InvariantCulture) == false) { // Everything should roll up to ACCOUNTADMIN // But when it doesn't, highlight this by pointing the highest unconnected role roleHierarchy.ImportantAncestor = String.Format("{0}", roleHierarchy.AncestryPaths.Split('\\')[0]); } else if (roleBeingGranted.Type == RoleType.Access || roleBeingGranted.Type == RoleType.Functional) { // Walk up to the last Functional role in the hierarchy going up bool keepGoing = true; Role currentRole = roleBeingGranted; while (keepGoing) { if (currentRole.ParentRoles.Count == 0) { keepGoing = false; } else { // only go up on the primary path Role parentRole = currentRole.ParentRoles[0]; if (parentRole.Type == RoleType.Access || parentRole.Type == RoleType.Functional) { currentRole = parentRole; } else { keepGoing = false; } } } roleHierarchy.ImportantAncestor = currentRole.Name; } else { // Default for all others to the root roleHierarchy.ImportantAncestor = "ACCOUNTADMIN"; } roleHierarchiesList.Add(roleHierarchy); } j++; if (j % 1000 == 0) { Console.Write("{0}.", j); } } Console.WriteLine("Done {0} items", grantsToRolesUsageList.Count); loggerConsole.Info("Looking for stragglers without parents or children for {0} roles", rolesList.Count); j = 0; // Now loop through the list of roles looking for the stragglers that have no other roles below them or aren't parented to any foreach (Role role in rolesList) { if (roleHierarchiesList.Count(r => r.Name == role.Name) == 0 && roleHierarchiesList.Count(r => r.GrantedTo == role.Name) == 0) { // Add this role explicily RoleHierarchy roleHierarchy = new RoleHierarchy(); roleHierarchy.Name = role.Name; roleHierarchy.Type = role.Type; roleHierarchy.AncestryPaths = role.AncestryPaths; roleHierarchy.NumAncestryPaths = roleHierarchy.AncestryPaths.Split('\n').Count(); roleHierarchy.GrantedTo = "<NOTHING>"; roleHierarchiesList.Add(roleHierarchy); } j++; if (j % 50 == 0) { Console.Write("{0}.", j); } } Console.WriteLine("Done {0} items", rolesList.Count); roleHierarchiesList = roleHierarchiesList.OrderBy(r => r.Name).ThenBy(r => r.GrantedTo).ToList(); FileIOHelper.WriteListToCSVFile <RoleHierarchy>(roleHierarchiesList, new RoleHierarchyMap(), FilePathMap.Report_RoleHierarchy_FilePath()); roleHierarchiesList = null; GC.Collect(); j = 0; loggerConsole.Info("Processing Role hierarchy for {0} roles", rolesList.Count); // For each role, output the hierarchy records that relate to its parents and children foreach (Role role in rolesList) { // Get list of Roles List <Role> thisRoleAndItsRelationsNonUniqueList = new List <Role>(10); thisRoleAndItsRelationsNonUniqueList.Add(role); role.GetAllParentRoles(role, thisRoleAndItsRelationsNonUniqueList); role.GetAllChildRoles(role, thisRoleAndItsRelationsNonUniqueList); // Filter to only unique items var thisRoleAndItsRelationsListGrouped = thisRoleAndItsRelationsNonUniqueList.GroupBy(r => r.Name); List <Role> thisRoleAndItsRelationsList = new List <Role>(thisRoleAndItsRelationsListGrouped.Count()); foreach (var roleGroup in thisRoleAndItsRelationsListGrouped) { thisRoleAndItsRelationsList.Add(roleGroup.First()); } // Get hierarchy of roles List <RoleHierarchy> thisRoleAndItsRelationsHierarchiesNonUniqueList = new List <RoleHierarchy>(100); role.GetAllParentRoleHierarchies(role, thisRoleAndItsRelationsHierarchiesNonUniqueList, 10000); role.GetAllChildRoleHierarchies(role, thisRoleAndItsRelationsHierarchiesNonUniqueList, 10000); // Filter to only unique items var thisRoleAndItsRelationsHierarchiesGrouped = thisRoleAndItsRelationsHierarchiesNonUniqueList.GroupBy(r => String.Format("{0}-{1}", r.Name, r.GrantedTo)); List <RoleHierarchy> thisRoleAndItsRelationsHierarchiesList = new List <RoleHierarchy>(thisRoleAndItsRelationsHierarchiesGrouped.Count()); foreach (var roleHierarchyGroup in thisRoleAndItsRelationsHierarchiesGrouped) { thisRoleAndItsRelationsHierarchiesList.Add(roleHierarchyGroup.First()); } thisRoleAndItsRelationsHierarchiesList = thisRoleAndItsRelationsHierarchiesList.OrderBy(r => r.Name).ThenBy(r => r.GrantedTo).ToList(); FileIOHelper.WriteListToCSVFile <Role>(thisRoleAndItsRelationsList, new RoleMap(), FilePathMap.Report_RoleDetail_RoleAndItsRelations_FilePath(role.Name)); FileIOHelper.WriteListToCSVFile <RoleHierarchy>(thisRoleAndItsRelationsHierarchiesList, new RoleHierarchyMap(), FilePathMap.Report_RoleHierarchy_RoleAndItsRelations_FilePath(role.Name)); j++; if (j % 50 == 0) { Console.Write("{0}.", j); } } Console.WriteLine("Done {0} items", rolesList.Count); } } rolesList = rolesList.OrderBy(r => r.Name).ToList(); FileIOHelper.WriteListToCSVFile <Role>(rolesList, new RoleMap(), FilePathMap.Report_RoleDetail_FilePath()); FileIOHelper.WriteListToCSVFile <Role>(rolesList, new RoleOwnerSankeyMap(), FilePathMap.Report_RoleOwnerSankey_FilePath(), false, false); } 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); } }