Sensitive Data Matrix and Column-Level Security

Column-level security (CLS) protects financially sensitive columns within otherwise non-sensitive tables. This is the sixth layer in the 7-layer RBAC stack, providing finer-grained access control beyond table-level GRANT/DENY.

Why Column-Level Security?

Some non-sensitive tables (invoicing, inventory) contain a mix of operational columns (quantities, dates, references) and financially sensitive columns (prices, costs, exchange rates). Rather than blocking the entire table, CLS allows restricted roles to query the operational data while protecting the financial data.

Design principle: When in doubt, restrict. It is safer to start restricted and loosen access later via PR than to expose data that should have been hidden.

22 Sensitive Tables (Table-Level DENY)

These tables are entirely blocked for analyst_gold and app_consumer roles via DENY SELECT. No column-level restrictions are needed because the entire table is inaccessible.

Column-Level Restrictions (CLS)

Column-level DENY SELECT is applied to financial columns in two non-sensitive tables. Only the app_consumer role is restricted at column level.

fact_customerinvoice -- 9 Restricted Columns

The app_consumer role can query operational columns (dates, quantities, references, parties) but cannot see financial amounts.

fact_vendinvoice -- 5 Restricted Columns

The app_consumer role can query vendor invoice operational data (quantities, dates, references) but not procurement costs.

Which Roles Can See Which Columns

graph TD
    subgraph "fact_customerinvoice / fact_vendinvoice"
        OP["Operational Columns<br/>dates, quantities, references"]
        FIN["Financial Columns<br/>prices, amounts, exchange rates"]
    end
    PA[platform_admin] -->|Full access| OP
    PA -->|Full access| FIN
    AF[analyst_full] -->|Full access| OP
    AF -->|Full access| FIN
    AG[analyst_gold] -->|Full access| OP
    AG -->|Full access| FIN
    AC[app_consumer] -->|Read| OP
    AC -.->|DENY SELECT| FIN

Why no CLS for analyst_gold? The analyst_gold role has schema-level GRANT SELECT ON SCHEMA::dbo with table-level DENY on all 22 sensitive tables. The non-sensitive tables accessible to analyst_gold do not contain columns that need further restriction -- analysts need full invoice data including financial columns for their analysis work. Only app_consumer (service accounts/applications) should be restricted from financial columns in non-sensitive tables.

DENY SELECT Implementation

Column-level security is implemented as individual DENY SELECT statements on specific columns, not via column masks or dynamic data masking. This is the approach supported by Fabric Warehouse.

-- Example from security/apply-all.sql (Step 9)
DENY SELECT ON dbo.fact_customerinvoice (invoice_amount) TO [app_consumer];
DENY SELECT ON dbo.fact_customerinvoice (line_price) TO [app_consumer];
-- ... (9 columns total)

DENY SELECT ON dbo.fact_vendinvoice (invoice_amount) TO [app_consumer];
DENY SELECT ON dbo.fact_vendinvoice (line_price) TO [app_consumer];
-- ... (5 columns total)

The grant files are:

• security/grants/cls_app_consumer.sql -- the 14 column-level DENY statements
• security/grants/cls_analyst_gold.sql -- empty (table-level DENY covers all sensitive data)

How to Add New Column Restrictions

When adding new columns to dbt models in non-sensitive tables:

1. Check if the column contains pricing, cost, margin, or exchange rate data
2. If yes, add it to the appropriate table section in security/column-restrictions.md
3. Add a DENY SELECT statement to the CLS script in security/grants/cls_app_consumer.sql
4. Update security/apply-all.sql to include the new DENY in Step 9
5. Open a PR -- the security-deploy.yml pipeline applies the change on merge

When adding entirely new sensitive tables:

1. Add the table to the sensitive tables list in security/sensitive-tables.md
2. Add DENY SELECT ON dbo.<table> TO [analyst_gold] in security/grants/analyst_gold.sql
3. Add DENY SELECT ON dbo.<table> TO [app_consumer] in security/grants/app_consumer.sql
4. Update security/apply-all.sql

Related Pages

• RBAC Architecture -- 7-layer overview, full data access matrix
• Warehouse Roles -- role definitions and grant files
• Row-Level Security -- budget data filtering