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