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