Table of Content

1. Introduction: Democratizing Reporting in Power BU using Microsoft Fabric

2. The Challenge: Direct Database Connections and Data Access Issues

3. Advantages of Row-Level Security (RLS) & Object Level Security (OLS) for Organization

4. Implementation Guide: Enabling Row-Level Security (RLS) & Object Level Security (OLS) in Microsoft Fabric

5. Case Study: How Row-Level Security (RLS) & Object Level Security (OLS) Empowers Citizen Developers

6. Conclusion

Introduction: Democratizing Reporting in BI using Microsoft Fabric

In today’s Data-Driven business landscape, the ability to access, analyze, and act on data in time is crucial for making informed decisions and maintaining a competitive edge. However, managing data access effectively presents significant challenges, particularly in environments where data must be both accessible and secure. One of the key concerns in Business Intelligence (BI) is ensuring that data security and accessibility are balanced. Businesses need to protect sensitive information from unauthorized access while enabling business users, often referred to as Citizen Developers, to create their own Reports and Dashboards.

Traditional methods of data access control, such as direct connections to Data Sources, often fall short in providing the necessary granularity and flexibility. These methods can lead to a heavy reliance on IT departments to create and manage data views, causing bottlenecks, Data Silos and limiting the agility of business operations. The inability to provide tailored access controls can result in either overly restrictive data access, hindering Decision Making and implementing innovative thoughts, or insufficient security measures, exposing sensitive data to risks.

Introducing Row-Level Security (RLS) & Object Level Security (OLS) as a solution for Empowering Citizen Developers

To address concerns of Citizen Developers making data driven decision, Microsoft Fabric introduces robust security features, namely Row-Level Security (RLS) and Object Level Security (OLS).

RLS allows the control of data access at the row level, ensuring that users can only see data relevant to their role. This fine-grained control helps in safeguarding sensitive information while providing necessary data visibility to authorized users. On the other hand, OLS restricts access to specific Entities, Attributes or Measures within a Semantic Model, adding an additional layer of security. This means, users interact with the data objects they are permitted to see, enhancing both security and data governance.

Together, RLS and OLS empower Citizen Developers by enabling them to access the data they need without compromising security. By implementing these security measures, organizations can Democratize business reporting, allowing Citizen Developers to generate insights independently while adhering to strict Data Governance. This shift free up IT departments from developing Reports and Dashboards and focus on Data Quality and Integrity. In this blog, we will explore how RLS and OLS work, their benefits, and how they can be implemented to Empower Citizen Developers, ultimately driving better Data Driven Decision making.

The Challenge: Direct Database Connections and Data Access Issues

Exploring the limitations and challenges of direct database connections for business users

Direct connections to a database is a common practice in many organizations, allowing users to access and query data for their analytical needs. However, this approach presents several limitations and challenges, particularly for Citizen Developers who may not have extensive technical expertise.

First, direct database connections often require a deep understanding of the underlying data structure and query languages such as SQL. Citizen Developers, who typically focus on leveraging data for insights rather than managing it, can find this requirement daunting and time-consuming. This dependency on technical skills can significantly slow down the process of generating valuable insights

Second, direct database connections can pose significant security risks. Without proper access controls, users might gain access to sensitive or irrelevant data, leading to potential data breaches and compliance issues. The lack of granular control over who can see what often results in an all-or-nothing approach to data access, which is far from ideal in a business environment where data sensitivity varies.

Moreover, direct database connections can lead to performance issues. When multiple users simultaneously access large datasets, it can strain the database server, resulting in application performance there by affecting application users. This is particularly problematic in large organizations with numerous online users using the application.

Highlighting the need for granular data access control and the complexities involved in managing access permissions.

The need for granular data access control is paramount in today’s business environment, where data security and privacy are critical concerns. Managing these permissions manually in direct database connections is complex and resource-intensive. IT departments often have to create and maintain numerous database views, and user roles to restrict access to sensitive data, which leads to Data Silos. Each change in business needs or user roles might necessitate adjustments in the database permissions, creating a significant administrative burden and introducing potential for errors. This not only consumes valuable IT resources but also increases the risk of misconfigurations and inconsistencies in access controls, leading to either overexposure or overly restrictive data access.

This is where a Semantic Model with RLS & OLS comes in as a comprehensive solution. By implementing RLS and OLS, organizations can automate and centralize data access controls, ensuring that users only see data relevant to their roles, which empowers citizen developers to create secure, independent and innovative reports, addressing the limitations and challenges of direct database connections.

Advantages of Row-Level Security (RLS) & Object Level Security (OLS) for Organization

Enable citizen developers to access and analyze data within their domain expertise.

• RLS tailors data visibility to individual users based on their roles, ensuring focused and relevant access.
• OLS restricts access to specific tables, columns, or measures, protecting sensitive information.
• Using semantic models, we can get various data sources into a single model, avoiding data silos and creating a single source of truth.

Facilitating citizen developers to generate insights to make data-driven decisions.

• RLS and OLS empower users to create custom reports and dashboards independently, reducing reliance on IT departments.
• Accelerates decision-making processes by providing secure, self-service data access.
• The direct lake model connection to the semantic model allows for faster query performance and near real-time data access, improving responsiveness and handling large datasets efficiently.

Implementation Guide: Enabling Row-Level Security (RLS) & Object Level Security (OLS) in Microsoft Fabric

Step-by-step instructions for implementing RLS & OLS to enable self-service reporting.

• Create a role and define your filter logic in it. This we can create it directly in the Fabric Semantic Model using the Manage Security Roles option. You can create multiple roles and have multiple filter conditions inside a role.

• Next step is to assign people in this role, go to Assign section next to Create and add required people in the respective roles. If someone is not part of any role he/she won’t see any data (Unless there are admin, member or contributor to the workspace).

Object Level Security (OLS)

• Open Tabular Editor and connect to your Semantic Model. Select the Table/ Column/ Measure on which you want to implement OLS.
• Update the OLS role to “None” if you want to restrict access to the selected Table/ Column/ Measure.
• Save the changes, and published it to the Semantic Model.
• Last step is to add people in this role, which you can do by going to the Security properties of the Semantic Model.

Note – You’ll need to create a Shareable Cloud Connection (SCC) for this Semantic Model, by default its set to Single Sign-On (Entra ID).

Best practices for designing RLS & OLS policies that align with business requirements.

1. Implement the Principle of Least Privilege

Apply the principle of least privilege to your RLS and OLS policies. Grant users the minimum level of access necessary to perform their job functions. This approach minimizes the risk of unauthorized access and data breaches, ensuring that sensitive information is protected.

2. Understand Your Data and User Roles

Begin by thoroughly understanding your data landscape and the various user roles within your organization. Identify the types of data that each role needs to access and the sensitivity of this information. This understanding forms the foundation of your RLS and OLS policies.

3. Define Clear Requirements

Collaborate with business stakeholders to define clear access requirements for each role. Determine what data each role should have access to and at what level of granularity. Ensure these requirements are well-documented and agreed upon by all relevant parties.

4. Use Dynamic Security Filters

Leverage dynamic security filters in Fabric to create flexible and scalable RLS policies. Dynamic filters allow you to apply security rules based on user attributes, such as department or region, ensuring that security policies adapt to changes in user roles and organizational structure.

5. Regularly Review and Update Policies

Data access needs and organizational structures evolve over time. Regularly review and update your RLS and OLS policies to ensure they remain aligned with current business requirements. Schedule periodic audits and involve stakeholders in the review process to capture any changes in data access needs.

6. Test Security Policies Thoroughly

Before deploying RLS and OLS policies, thoroughly test them to ensure they work as intended. Create test scenarios that mimic real-world use cases and validate that users can only access the data they are authorized to see. Address any issues or gaps identified during testing.

By following these best practices, you can design RLS and OLS policies that effectively protect sensitive data, support business objectives, and empower citizen developers to make data-driven decisions.

Case Study-
How Row-Level Security (RLS) & Object Level Security (OLS) Empowers Citizen Developers

Company Overview: A Healthcare organization is a leading healthcare provider specializing in personalized patient care solutions. With a vast network of clinics and hospitals across the country, Healthcare organization is committed to delivering high-quality healthcare services while maintaining patient privacy and data security.

Challenge: As Healthcare organization expanded its operations, it faced the challenge of providing timely access to critical patient data while ensuring strict compliance with healthcare regulations such as HIPAA. Additionally, the company aimed to empower its frontline healthcare professionals, including doctors, nurses, and administrative staff, to generate insights from patient data to improve health care delivery.

Solution: Healthcare organization implemented Microsoft Fabric with Row-Level Security (RLS) and Object Level Security (OLS) to address its data access and security needs. RLS was utilized to restrict access to patient data based on user roles and responsibilities. For example, doctors could only access patient records from their respective clinics, while administrators had access to aggregated data for performance monitoring and resource allocation.

Additionally, OLS was employed to control access to sensitive patient information such as medical history and treatment plans. Only authorized healthcare professionals with specific roles, such as attending physicians or specialized nurses, could view detailed patient data, ensuring compliance with privacy regulations.

Results: The implementation of RLS and OLS in Microsoft Fabric yielded significant benefits for Healthcare organization:

1. Empowered Citizen Developers: Frontline healthcare professionals were empowered to create custom reports and dashboards tailored to their specific needs without relying on IT support. This self-service capability accelerated decision-making processes and enabled healthcare professionals to identify trends and patterns in patient data more efficiently.
2. Enhanced Data Security and Compliance: RLS and OLS ensured that patient data remained secure and compliant with healthcare regulations. By restricting access to sensitive information based on user roles and responsibilities, Healthcare organization minimized the risk of unauthorized data breaches and maintained patient trust.
3. Improved Operational Efficiency: With RLS and OLS in place, Healthcare organization reduced the time to production of Reports. This allowed IT resources to focus on strategic initiatives rather than routine data management tasks.

Case Study Conclusion: By leveraging RLS and OLS in Fabric, Healthcare organization successfully empowered its frontline healthcare professionals to access and analyze patient data securely and efficiently. The implementation not only improved operational efficiency but also enhanced data security and compliance, ultimately enabling Healthcare organization to deliver superior patient care while maintaining patient privacy and confidentiality.

Conclusion:

Empowering Citizen Developers with RLS & OLS

Summarizing the key benefits of RLS & OLS for business users and citizen developers

• RLS and OLS offer granular data access control, allowing users to access and analyze data tailored to their roles while maintaining compliance with regulatory requirements.
• By empowering citizen developers with self-service reporting capabilities, organizations can accelerate decision-making processes and reduce reliance on IT departments.
• Additionally, RLS and OLS help mitigate security risks by controlling access to sensitive data and ensuring data integrity.

Encouraging businesses to embrace RLS & OLS as a strategic tool for data security and empowerment in Business Intelligence initiatives

• Embracing RLS and OLS serves as a strategic move for enhancing data governance, driving data democratization, and mitigating security risks in business intelligence initiatives.
• These tools empower organizations to unlock the full potential of their data while maintaining security and compliance standards.
• By leveraging RLS and OLS in Fabric, businesses can drive innovation and efficiency while ensuring data security and empowerment for citizen developers.

Blog Author