Row- & Column-Level Security in the Reporting Layer

Row-Level Security & Column-Level Security

In modern BI and Big Data architectures, security is no longer something you “add later”. If you build a data warehouse, a Data Vault, or even a smaller reporting solution without a clear security concept, you will almost certainly run into problems down the road.

One of the most common and most important questions we get in BI projects is: How do you actually implement row-level and column-level security in the reporting layer?

In this article, we’ll walk through the reasoning behind row- and column-level security, explain why hard-coded rules don’t scale, and show a proven, practical approach using access control lists (ACLs) directly in the data warehouse reporting layer.

Why Row- and Column-Level Security Matters

Let’s start with the basics. Why do we even need row-level and column-level security in a data warehouse or reporting layer?

The answer is simple: not all users should see all data.

Here are two very common examples from real-world projects:

• Row-level security: A sales representative in Germany should only see customers from Germany (or the DACH region) and not customers from France, Spain, or other regions.
• Column-level (attribute-level) security: Sensitive fields like revenue, margin, salary, or bonus information should only be visible to specific roles, such as finance or management.

These requirements exist in almost every company, regardless of size or industry. Yet, many teams still struggle to implement them in a clean, scalable way.

The Problem with Hard-Coded Security Rules

A common first approach is to implement security rules directly in reporting tools like Power BI, Tableau, or Looker. While this might work for a small number of reports, it quickly becomes a nightmare as your BI landscape grows.

Here’s why hard-coded security does not scale:

• High maintenance effort: Every report or dashboard needs to be updated whenever security rules change.
• Inconsistent logic: Different reports may implement slightly different rules, leading to confusion and errors.
• Frequent changes: Users change departments, teams get reorganized, and access rules evolve over time.
• Risk of mistakes: Forgetting to apply a rule in one report can expose sensitive data.

In short: implementing row- and column-level security repeatedly in every reporting tool is inefficient and risky.

The Core Idea: Access Control Lists (ACLs)

A scalable and proven approach is to use Access Control Lists (ACLs). This is a well-known concept in IT security and works extremely well in data warehousing and BI environments.

The idea is straightforward:

• Maintain centralized tables (or files) that define who is allowed to see what.
• Map users or user groups to business attributes, such as regions, countries, or access rights.
• Apply these rules once in the reporting layer of the data warehouse.

Instead of implementing security in every report, you implement it in the data warehouse views that your reporting tools consume.

Users vs. User Groups: Always Think in Groups

One very important design decision: always work with user groups, not individual users.

Managing security on a per-user basis creates a lot of overhead and quickly becomes unmanageable. Groups, on the other hand, scale well and align nicely with how companies organize access rights.

A typical setup might look like this:

• corp\\bi-read-DACH
• corp\\bi-read-EMEA
• corp\\bi-read-FINANCE

These groups are usually managed in Active Directory, Azure AD, or a similar identity provider. Your data warehouse then simply needs to know which group a user belongs to.

Implementing Row-Level Security with ACLs

Row-level security controls which rows a user is allowed to see. The ACL table for this typically maps user groups to business attributes.

A simplified example of a row-level ACL table could look like this:

USER_GROUP          | REGION_CODe --------------------|-------------
bi-read-DACH        | DACh bi-read-EMEA        | EMEa

This table says:

• Users in the DACH group can see data for the DACH region.
• Users in the EMEA group can see data for the EMEA region.

Where does this table live? Ideally:

• In a master data system, if your organization has one.
• In a reference data schema in the data warehouse.
• For smaller setups, even an Excel file that is ingested regularly can work.

Applying Row-Level Security in Views

Once the ACL exists, applying it in the reporting layer is straightforward. In your Information Mart or reporting views, you simply filter based on the current user’s group.

Most modern databases allow you to access session context information, such as:

• The current user
• The current role
• The current group

Conceptually, the SQL logic looks like this:

SELECT *
FROM customer c WHERE c.region_code IN (
    SELECT region_code     FROM row_level_acl     WHERE user_group = CURRENT_USER_GROUP()
)

The exact syntax depends on your database, but the concept is universal. The result: users only ever see rows they are allowed to see, no matter which reporting tool they use.

Implementing Column-Level (Attribute-Level) Security

Column-level security works slightly differently. Instead of filtering rows, you control whether a column is visible or not.

Typical use cases include:

• Revenue
• Margin
• Salary
• Bonus

Again, the foundation is an ACL table. A simplified example:

USER_GROUP          | COLUMN_NAME | CAN_REAd --------------------|-------------|---------
bi-read-DACH        | revenue     | false bi-read-EMEA        | revenue     | true

In this example:

• The DACH sales team cannot see the revenue column.
• The EMEA finance team can see the revenue column.

Applying Column-Level Security in Views

In the reporting view, you typically implement column-level security using a CASE WHEN statement:

CASe     WHEN EXISTS (
        SELECT 1
        FROM column_level_acl         WHERE user_group = CURRENT_USER_GROUP()
          AND column_name = 'revenue'
          AND can_read = true     )
    THEN revenue     ELSE NULl END AS revenue

If the user is allowed to see the column, they get the value. If not, they get NULL. From the reporting tool’s perspective, the column exists but contains no sensitive data.

Who Should Manage the Security Rules?

One important organizational point: the data warehouse team should not manually manage ACLs.

Security rules change frequently, and they are usually driven by business or governance decisions. Ideally:

• Reporting or data governance teams own the rules.
• Business users can maintain ACLs via a master data system or controlled interface.
• The data warehouse simply consumes these rules.

This separation of responsibilities reduces operational overhead and avoids constant change requests to the IT or data engineering team.

Automation Is Key

In modern data stacks, manual SQL coding should be the exception, not the rule. Security logic is no different.

If you write row- and column-level security logic manually for every single view, you will:

• Forget to apply it in some places.
• Introduce inconsistencies.
• Create unnecessary technical debt.

The better approach is to standardize and automate.

For example:

• Use dbt macros to apply security logic consistently.
• Enable or disable security with a simple configuration flag.
• Automatically apply security to all views in a specific schema.

In one project, we implemented a dbt security macro that could be activated with a single line of code. Depending on the configuration, the macro automatically injected the row- and column-level ACL logic into the view.

This ensures:

• Consistency across the entire reporting layer.
• Minimal manual effort.
• Much lower risk of security gaps.

Where Should Security Be Applied?

Best practice is to apply row- and column-level security in the final reporting layer
of your data warehouse:

• Information Marts
• Presentation Layer
• Semantic Layer

This keeps your raw and integration layers clean and flexible while ensuring that everything exposed to BI tools is properly secured.

Key Takeaways

• Row- and column-level security is a foundational requirement in BI projects.
• Hard-coded security in reports does not scale.
• Access Control Lists provide a clean, centralized solution.
• Always work with user groups, not individual users.
• Apply security in the reporting layer of the data warehouse.
• Automate everything using modern data tooling.

If you get these basics right early in your data warehouse or Data Vault project, you will save yourself a lot of pain, rework, and risk later on.

Watch the Video

Meet the Speaker