Technologies:

• Dynamics 365 CRM
• Azure SQL
• Power BI
  
  

Providing the right data access at the right time to the right people is a recurring and familiar issue for many businesses. On one hand, generalised access is a low-maintenance solution, but on the other, this approach presents a data security risk, the implications of which range from general governance and administration concerns to the possibility of improper access or even a data breach.

The guiding principle for businesses should be the principle of least privilege, whereby access is only granted to systems or information that an individual requires to perform their role. In parallel, data access issues can be a frustrating user experience, and mistakes can result in incorrect reporting and reduce overall trust in organisational data.

Complexity arises where different roles or tasks for an individual require varying access levels that can conflict and overlap cross system. Further issues can arise when systems are utilised differently between departments, or departments may be using entirely disparate systems.

The Brief

A One51 client wished to secure data within their organisation and avoid a high maintenance overhead. The client needed to implement a unified data security solution for reporting across two systems – Finance and CRM – which have entirely different security rules.

There were two critical concerns that the solution must address:

1. How can we ensure that when a user interacts with a report, they are not denied legitimate access to data from either system?
2. How can we ensure that financial reports embedded back to the CRM system show the expected result, even when the user might not be part of the finance system?

Design Challenges

1. Security rules are completely unique for each user with conflicting and overlapping access to branch or customer data.
2. Distinct entities within each system drive the security, and these entities do not exist in parallel in both
   • Branches drive security for Finance
   • User groups (Organisation units, Teams or individual record access granted within CRM).
3. Reporting system required to be high performance with fast query returns, however the extreme granularity of security control would certainly result in a large security data volume, which inhibits

The Solution

One51 undertook a detailed discovery phase with Dynamics 365 CRM and Finance SMEs to understand and document each system and its security. We were then able to calculate a unified permissions table based on these rules.

• The permissions table can be recalculated automatically and regularly to respond to changes in the organisation as they occur.
• The permissions table produced was extremely large as it included all users in the organisation with the users’ specific
• This dynamic permissions table was used to apply Role-based Dynamic Row Level Security to the semantic data model in Power BI.

We worked with a group of test users to confirm that the system was working as intended and security rules were being correctly applied.

As query performance was a high-priority requirement, the optimisation phase was critical to ensure the best user experience. The initial security table was extremely large, so the team worked to identify opportunities to enhance performance.

• We identified a large group of Finance-only users and removed these users from the combined security permissions table. Their security permissions were simplified into a separate role. In doing so, we reduced the security permissions table size by 20%.

• Through this reduction, we were able to incorporate the security permissions table directly into the data model, which reduced the lag associated with a direct
• With the larger permissions table moved to import mode, Query caching could also be utilised, such that the larger permissions group was only queried once per user per session.

The resulting solution was a finely controlled, dynamic, multidimensional security layer. Additionally, it requires minimal maintenance for the organisation as it updates regularly to reflect changes from each system and applies automatically to all reports in the business portfolio. Overall, the team are extremely pleased with the query-by-query performance. Complex queries on the organisations large granular fact tables are running satisfactorily despite a 40m+ row security table being applied to every query.

Reflections and Takeaways

1. Fine-tuning of data access is not an impossible task for a business, particularly if we are willing to leverage the subject matter experts and their system knowledge to unpick and understand the underlying security
2. We do not need to sacrifice performance to achieve this
3. Role-based access control does not need to be system-specific, can be amalgamated and applied globally across a business for reporting