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);
}
}
