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