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