Designing a Dynamic Multi-Hierarchy Security Model for Analytics and Decision Support Systems

A simple access check uncovered something alarming: several dashboards still showed employee compensation based on an organizational hierarchy that was no longer relevant. Our row-level security framework stopped synchronizing because of a Workday HCM API timeout issue, but meanwhile, the entitlements on those dashboards did not get adjusted to reflect the new organization setup. No permission changes were made. Despite changes in the hierarchy, the access layer was unable to adapt to the changes.

This is the reason why this project became necessary. First of all, we completely redesigned the access layer that sits behind all BI tools, Adaptive Planning models, and Snowflake shares used by our FP&A and risk departments. It was done not using any static table with roles, but with the help of tracking constantly changing hierarchies of organizations, cost centers, legal entities, and products that include deal closures and divisions' separations. Let me explain how it was accomplished: first, I will describe the process of keeping hierarchies up-to-date in the ingestion layer; then I will explain the visibility engine and its policies of providing access at a row level. Finally, I will show how to keep access scopes the same in Power BI, Tableau, and Adaptive Planning.

Overview

The system obtains hierarchical information from Workday HCM, Salesforce, and the internal table of the legal entities in order to reconcile these three conflicting lists into a single tagged entity graph. Visibility between users and the entities is calculated upon any change of the hierarchy edges, and not after a periodic re-access review as in previous attempts. The visibility enforcement occurs on the Snowflake row level, guaranteeing that Power BI, Tableau, and custom SQL queries obtain the same limited results set, which helps avoid discrepancies because of individual permission tables per tool. Consistent entity scope is provided for the Workday Adaptive Planning security groups; as a result, there will be no mismatches between allowed viewers of the planning sheets and the dashboards. Orphan nodes in the hierarchy are identified before their contribution to visibility gaps appears.

Components

The framework is made up of four main parts: hierarchy ingestion layer, dynamic security mapping engine, Snowflake row-level access control, and synchronization layer that relays outcomes to BI and planning software applications. These parts produced practical insights during difficult experiential learning.

Hierarchy Ingestion Layer

Four sources have been used for this hierarchical workflow. They include Workday HCM organizational hierarchy and cost centers hierarchy, location level hierarchy that groups individual branches/plants/facilities according to regions, account hierarchy of Salesforce accessed using the Bulk API and a legal entities reference table maintained manually in Snowflake. The four hierarchical sources have been transformed into a single closure table having fields as entity_id, parent_id, hierarchy_type, and effective_date.

While one of the first considerations was to use only the organizational hierarchy in Workday HCM, the reason was mainly because of its completeness. But it is simply impossible to rely on just this hierarchy because there could be instances where a single cost center reports itself to two legal entities due to allocation of services. Additionally, the location-level hierarchy of any branch does not have anything to do with the organizational hierarchy position of its manager – it could be a branch within one region reporting to a manager in another region. So, maintaining each hierarchy separately and ensuring that configurations are retained helps.

One of the difficult aspects of this entire process was the fact that a complete extract of the Workday HCM hierarchy was extremely slow, such that it caused issues with the synchronization to adaptive planning. To avoid this problem, it was necessary to implement change detection instead.

Dynamic Security Mapping Engine

This process goes through the closure table starting at each user’s home node and translates all the descendant entity_ids into a flatter structure of USER_ENTITY_ACCESS, along with the effective dates. Key to the design is the fact that a user’s home node is not a simple identifier but a collection of (user, hierarchy_type, home_node). Thus, the same user can have a User + Location Hierarchy as well as a User + Organization Hierarchy, each handled in the same fashion.

Access was previously managed through manually maintained grants in Snowflake for each business unit. That worked fine until some organizational change would occur, at which time many grants had to be updated. Moving to the recursive resolution from a single source-of-truth hierarchy allowed this to become unnecessary; there is no grant to maintain when a cost center moves.

We found that the effect of one edge change could be more far-reaching than expected. As an example, when a cost center moved to a different regional vice president, the map engine would do a traversal of the closure table starting at this node and then down the whole chain and then write out all the affected USER_ENTITY_ACCESS entries for anyone who mapped back to this node. In one typical move, over two thousand downstream entries in the USER_ENTITY_ACCESS table were affected by changing just one edge in the hierarchy. To solve this problem, the original map engine did a full recalculation of all the accesses in the table for each pass through the process. This was a brute force solution but one that put enough of a strain on the system that it locked up the table in business hours. The second map engine solves this issue by updating only the affected subtree.

Foe example, one engine solves any hierarchy, not just one. Think about a regional operations manager. Their User + Location Hierarchy assignment specifies the regional level home node, meaning that all locations below that region within the location hierarchy become part of their assignment – all branches and facilities belonging to that region. The User + Org Hierarchy assignment is a distinct and separate home node, further down the chain: exactly their own team and subordinates within it because their responsibility is not over all employees within that region but those reporting to them directly. Both assignments live in the same USER_ROLE_ASSIGNMENT table, distinguished only by hierarchy_type. They are both solved by the very same recursive procedure. Start from the home node, go over all descendants in that hierarchy chain within the closure table, and write into the USER_ENTITY_ACCESS table. This technique also allows modeling of hierarchies that do not exist yet – say, Product Line hierarchy – because we simply add another hierarchy type to the closure table, specify a home node for it, and the same engine will recognize it on next runs.

Snowflake Row-Access Enforcement

In Snowflake, a policy is added on the fact tables: GL detail, planning actuals, and HR costs, joining them against USER_ENTITY_ACCESS by the current session’s user ID mapped into the tenant. Hence, irrespective of who queries the data using what software tool, the result set will be automatically filtered for authorized rows only.

Another option considered included creating a secure view per each business unit, running into the centralized control plane problem described by GFT's Azure Synapse Analytics – New Insights Into Data Security on DZone. The view-per-unit solution would not scale above dozens of units and required having a different Power BI dataset for each view. Adding a single row-access policy allowed setting up proper restrictions at the table level without making any changes in consumer applications.

The design principle used for access restriction at runtime based on an explicit row filter by identity context and not through application-layer WHERE clauses is not exclusive to Snowflake. Another example of implementing similar logic in PostgreSQL by means of its native row-level security and session-wide tenant identifier can be found in Multi-Tenant Data Isolation and Row Level Security on DZone. Even though the approach differs from a closure table self-join, the idea behind it is the same: enforce data security in the database itself, not in the application code of consumers.

A technical problem emerged with the initial policy function that used entity codes. Once Finance renamed a number of those, some of the codes were retained in cached data extracts and silently caused access restrictions due to missing codes. Therefore, a surrogate keys level was implemented to avoid invalidating already computed access rights after renames.

For instance, the process illustrates that a certain policy works consistently throughout all tiers of roles: simply ask the fact table by means of an arbitrary user in the corresponding role tier and compare the entity count with the scope for that particular tier. Cost center managers, for example, would not have any access beyond their own cost center, which includes only the subordinates of that cost center; access beyond those two scopes would be considered a flaw in the policy, and not a change in the business itself, unless proven otherwise.

BI and Planning Sync Layer

The sync process takes USER_ENTITY_ACCESS and passes it to three recipients: Power BI Row-Level Security (RLS) role membership, Tableau user filters, and security groups of Workday Adaptive Planning. This process guarantees the matching of the access control scope between a planning sheet and a dashboard built using the very same data.

In the past, each BI team kept the maintenance of its RLS roles separate. Thus, there was an inherent possibility of the situation described in the introduction of this document, when Power BI roles were delayed compared to the Snowflake policy, causing the misalignment period and the related risks. The centralization of all pushes into one mapping table reduced the likelihood of such an incident.

One issue came up during the development phase of the project because of the need for trial-and-error solutions. Namely, the Adaptive Planning API sets the rate limit for security group updates, making the mass updates impracticable. The successful implementation of the batch update method, where two hundred users could be updated in one request, became evident after trying the five hundred per request solution and failing because of the throttle errors.

Prerequisites

• Read access to Workday HCM reports' web service, restricted by organization and using a special service account.
• Profile-level access to Salesforce bulk API that queries account hierarchy fields.
• Snowflake database edition enabling row access policies, along with a role capable of creating and attaching them.
• Credentials for Workday Adaptive Planning Integration API, with write access to security groups.
• Python version 3.10 or later, the snowflake-connector-python library, and job orchestration, where we used a scheduled container, but any other scheduler could have done the job.
• A concise list of hierarchy types required for the job. We had wasted much time dealing with hierarchy types nobody used further downstream.

Overall, the flow is straightforward and linear – hierarchy sources are responsible for populating the closure table, which gets flattened by the mapping engine to produce the access table, followed by enforcing it through row access policy and expanding the scope through the sync layer. Figure 5 illustrates the entire process briefly.

Design issues that continually arise in the context of such modeling include where each hierarchy comes from, and how an organizational level maps to a set of defined entities. These are two questions that can better be addressed by example.

For instance, choosing a hierarchy graph vs. choosing a tree graph: The organizational level hierarchy describes supervisory reporting lines within Workday HCM, in which an individual contributor reports to a manager, who in turn reports to a director, and so on, independent of their actual geographical location. The cost-center hierarchy can be maintained within Workday HCM but follows a financial reporting line instead of the personnel management line, thus allowing for a cost center to be assigned to two legal entities if different allocations need to be created by shared services. There is yet another hierarchy, which is called the location level hierarchy, according to which each individual branch, plant, or facility consolidates into a region and eventually into a business unit, although often a branch can be geographically located in one region while being organizationally assigned to a completely different region via a regional manager. Finally, there is a legal entity hierarchy kept by Corporate Finance that describes corporate regulations and taxes, and not reporting lines. As mentioned before, there is also the account hierarchy coming from Salesforce, which comes from how RevOps divides accounts during the closure process. Each node in the closure table is marked with its domain membership rather than assuming the same hierarchy for all domains. It is precisely the latter that does not work when a branch/cost center/account needs to have different rollups depending on its hierarchy type.

The following example illustrates how role tier relates to scope. The scope of the executive role is restricted to the entire organization and multiple tiers down within the organization. This allows for visibility over thousands of entities. On the other hand, the regional vice president's role allows for a scope restriction to a particular region at the location level as well as the entire organizational hierarchy below the individuals within that region. Hence, this leads to a large scope but still within limits, and one which is not the entire organization but rather a subset thereof. Similarly, the cost center manager's role limits scope to the cost center he is in as well as any individuals reporting directly to him. In turn, the analyst's role has a scope that is limited only to certain cost centers that have been defined in advance and does not inherit scope from any of the two hierarchies.

Troubleshooting / Lessons Learned

The closure table going stale without anyone noticing

This document explains the failure mode created at the beginning of the document: The API timeout made the closure table go out of date, and none of the components downstream had any way to check whether the data they were using was up to date before they started using it. To solve this problem, a mechanism was built to prevent synchronization after the closure table has aged past a certain point to alert the operation staff that outdated entitlements would not be served without notifying anyone first. Any aging at all in the closure table may create a picture of an organization that does not exist anymore due to a quick reorganization.

Orphaned nodes from in-flight Salesforce account merges

Merges may create the problem whereby the child is tied to an expired parent ID during the final propagation. During a normal run, this type of problem could involve from a few to several tens of entries, based on the level of mergers made prior to the cycle run. This type of problem creates the risk of attribution of wrong parents; thus, all the records involving this problem are isolated in a reviewing table.

The full-rebuild job locking the access table during business hours

In the first approach, the whole access table would be recomputed each time, which is very effective but leaves the USER_ENTITY_ACCESS locked for long enough to queue the BI refreshes after it. Since in the second approach recomputation occurs only in the subtree concerned—as shown by the cost center example—locks become almost negligible, even in cases of thousands of changes due to reorganization.

Adaptive Planning throttling the security group push

Individually pushing updates to the security groups became unsustainable due to the large number involved. This is as previously discussed, where a relatively small-sized batch was successful while significantly larger ones experienced errors of being throttled by the Integration API.

Conclusion

This article will describe the design of the dynamic multi-hierarchy security model that is based on Workday HCM, Salesforce, Snowflake, Power BI, Tableau, and Workday Adaptive Planning. This methodology allows one to reconcile the hierarchical data coming from four different sources, such as the organization hierarchy, cost center hierarchy, location hierarchy, and legal entity hierarchy, in addition to the account hierarchy coming from Salesforce, into a tagged graph rather than trees, which can be inconsistent. Moreover, visibility recomputation is achieved due to any actual change within these hierarchies and not depending on periodic reviews as in most cases. It will also be proven that having a single permission table per Snowflake row rather than permission tables for each BI tool helps reduce the visibility gap, which caused the initiation of this project.