Security Design Patterns in Data Warehousing

Security is the top priority in the digital economy. We are plagued with issues if we build systems that are silo because we cannot make relationships in the data. If we create connected systems, we need to create logical boundaries and constrict how this valuable insight is accessed and shared. The approach is to give the right information at the right level to the people who need it at the right time. In simple language, you get information on a need-to-know basis. Segmented security means we must stratify security levels into key categories or become segmented as needed. The standard security design issues fall into the following categories.

1. The user does not have access to the information they need (usually a binary approach of you have access or not)
2. Cross Silo Analysis is difficult and impossible in some cases (usual security to multiple applications)
3. Multi-Hierarchy views of the same data make reporting and security hard to be aligned (If the security is tied to a hierarchy, then what happens when different lines of business use multiple hierarchies)
4. Special Case processing is hard to segment and can create a disconnect.
5. Administer and Re-Certification, keeping pace with attrition, mergers, and transfers. Inter-organization type restructuring must be easily accounted for.
6. Transactional and Analytic security synch. Analytic applications of the future need to consume data and spew embedded knowledge in the reports or aggregated data back to the user. Data capture of transactional systems and reporting of analytic systems security will need to synchronize if they are different data models. They are usually separate for the performance of both functions.

Benefits of Good Security and Data Democracy Design Pattern

• Reduce the cost of delivering data by making the user more self-sufficient and will reduce the time to get the data, the turn around time between the request and data given to the user
  • Leverage the same security model for both the transaction and the reporting systems by reducing delivery costs, training costs, and complexity to maintain and sync both.
  • There is also less administrative cost over the long haul to administer the security.
• Good data democracy means high access and high frequency of use.  Give more visibility to the data and give the end user a higher value of the application as they will come to trust the data and use it more. The more data is used, the more the data and the processes will change. It is the insight gained that will drive the change.

Most enterprise designs have between 10-20 levels of hierarchy. This level of granularity tends to satisfy the most demanding requests but is still flexible for a large enterprise. A starting point in thinking of how to segment the levels into security archetypes can be as follows:

1. The single-user profile
2. The line manager who manages a few users to a few dozen users
3. The functional manager, who manages a few line managers to a few dozen line managers, typically needs to see the data of any or everybody in their direct or indirect command.
4. The executive who manages more than one functional manager or the entire organization
5. Peers visibility: for security levels 2-5 (line manager, functional manager, and executive), there should be security for seeing peers and not seeing peers subgroups. This requirement depends on the company culture.
6. Exclusions by Exceptions: the design must account for exceptions that the business will want to shield from general viewing for whatever reason at the level.
7. Cross LOB and Geographic: the design must account for both who you can see in the LOB but with the European restrictions of countries not able to see each other’s data. General Data Protection Regulation (GDPR) means even within the LOB, geographic limits must be nested into the underlying LOB security.

A general design principle is to create groups for each level and assign all the rights to these groups as needed. This way, you can add people and remove them from the groups with little regard to the security rights of the group. Group-level security will allow inheritance and make the model more flexible if group nesting is supported.

One thing to consider is if a user becomes associated with more than one group, then how do you want to manage this edge case? There are a few approaches to do the most restrictive of the two groups, thereby “AND” ing the rights with the lowest common denominator or rights. The second way is to “OR” the rights and give the most rights to the two groups. This is to be decided as you go about the design.

I hope these design patterns help to mold your next build. I always appreciate some feedback, so feel free to share your thoughts.

If you are interested in seeing many design patterns to consider in the Data Warehousing build process, then consider reading Design Patterns in Data Warehousing

Stephen Choo Quan

Thanks for reading ❤