Designing Row-Level Security Rules for an IT Dashboard

NexGenIT's new operations dashboard shows incident data for all 5 teams — but each team should see only its own records. Managers should see their team's records, and directors should see everything. The IT manager must design role-based filtering rules and manually trace through 8 test queries to verify no data leaks before deployment.

# Aplly.xyz Case Study Submission

## Title
Designing Row-Level Security Rules for an IT Dashboard

## Type
Technology/IT

## Difficulty
Advanced

## Estimated Time
75 minutes

## Overview
NexGenIT's new operations dashboard shows incident data for all 5 teams — but each team should see only its own records. Managers should see their team's records, and directors should see everything. The IT manager must design role-based filtering rules and manually trace through 8 test queries to verify no data leaks before deployment.

## Case Details

Function Focus: Role-based access control rule design, row-level security logic, multi-tenant data isolation, permission trace verification

Scenario:
NexGenIT built a shared operations dashboard with incident records from all 5 teams. Before going live, the security team requires row-level security (RLS): each team member must see only incidents belonging to their team, team managers must see their team's incidents plus cross-team incidents they are tagged in, and directors must see all records. No role can see records from teams they have no relationship with. The IT manager has designed draft RLS rules and must manually trace through 8 sample queries (different users with different roles) to verify that every query returns exactly the right set of records — no more, no less.

Organization Structure:
- 5 teams: Infra, Security, DevOps, Database, Network
- 3 roles per team: Member, Manager
- 2 Directors (cross-team): Director of IT (sees all), Director of Security (sees Security + Infra only)
- Each incident record is owned by exactly one team
- Some incidents have a "tagged_teams" field for cross-team collaboration

Draft RLS Rules:
1. Team Member: sees records WHERE owner_team = user_team
2. Team Manager: sees records WHERE owner_team = user_team OR tagged_teams CONTAINS user_team
3. Director of IT: sees ALL records
4. Director of Security: sees records WHERE owner_team IN (Security, Infra) OR tagged_teams CONTAINS Security

Tasks:
1. Before tracing, read the draft rules and identify at least 2 potential problems (e.g., can a Manager from Team A see records from Team B that tagged Team A? Is that intended? What about a Director of Security — should they see Database incidents that tagged Security?)
2. For each of the 8 test queries, manually apply the RLS rules to the 10 records — produce a table showing which records each query returns and explain the rule that allowed or denied each record
3. Identify at least 1 test query where the draft rules return an incorrect result (too many or too few records) — explain what the correct result should be and why the rule failed
4. Propose a revised RLS rule set that fixes the problems you identified — then re-run the 8 test queries against your revised rules and show that all return correct results
5. Only after submitting your manual trace, compare your RLS decisions against a tool-based simulation (spreadsheet logic or a simple program) and report any discrepancies in your access control decisions

Expected Output:
An RLS design packet containing: (a) critique of the draft rules with at least 2 problems identified, (b) 8-query trace table against draft rules, (c) identification of the query that returned incorrect results and why, (d) revised rules and re-trace showing all 8 queries now correct, and (e) a round-trip discrepancy note.

Evaluation Criteria:
Accurate application of rules to all 8 queries × 10 records, correct identification of at least one rule failure with a clear explanation, a revised rule set that fixes the problem without breaking other queries, and honest discrepancy reporting.

## Data Sources

Teams and Roles:
| User ID | Name | Role | Team |
|---|---|---|---|
| U01 | Arjun | Member | Infra |
| U02 | Bhavna | Manager | Security |
| U03 | Chen | Member | DevOps |
| U04 | Deepa | Manager | Database |
| U05 | Eswar | Director | IT (all teams) |
| U06 | Fatima | Director | Security (Security + Infra) |
| U07 | Girish | Member | Network |
| U08 | Hema | Manager | DevOps |

Incident Records (10):
| Record ID | Description | Owner Team | Tagged Teams | Severity |
|---|---|---|---|---|
| R01 | Server rack power fluctuation | Infra | Security | High |
| R02 | Firewall rule misconfiguration | Security | Infra | Critical |
| R03 | Database replication lag | Database | DevOps | High |
| R04 | SSL certificate expiry | Security | None | Critical |
| R05 | Load balancer config error | Network | DevOps, Infra | Medium |
| R06 | CI/CD pipeline failure | DevOps | Database | High |
| R07 | VPN tunnel dropped | Network | Security | Medium |
| R08 | Backup job failure | Infra | Database | Low |
| R09 | API rate limit exceeded | DevOps | None | Low |
| R10 | DNS resolution failure | Network | Infra | High |

Test Queries (8 users):
| Test | User | Query |
|---|---|---|
| T01 | U01 (Arjun, Infra Member) | Show all incidents I can see |
| T02 | U02 (Bhavna, Security Manager) | Show all incidents I can see |
| T03 | U05 (Eswar, Director IT) | Show all incidents I can see |
| T04 | U06 (Fatima, Director Security) | Show all incidents I can see |
| T05 | U07 (Girish, Network Member) | Show all incidents I can see |
| T06 | U08 (Hema, DevOps Manager) | Show all incidents I can see |
| T07 | U04 (Deepa, Database Manager) | Show all incidents I can see |
| T08 | U03 (Chen, DevOps Member) | Show all incidents I can see |

## Solution Frameworks
Role-based access control (RBAC), row-level security (RLS) rule design, permission trace verification, multi-tenant data isolation, security auditing

## Solver Guidance & Tutorials
Link to: "Designing and Verifying Row-Level Security Rules" tutorial

## What You'll Learn
- Designing RLS rules for multi-team dashboards
- Manual trace verification before deploying security rules
- Detecting over-permission and under-permission in access rules
- Iteratively fixing rule logic without breaking existing permissions

## Tags
row-level security, RBAC, data isolation, IT dashboard, access control

## Registration Links
- Register as Solver
- Register as Evaluator

Teams and Roles:
| User ID | Name | Role | Team |
|---|---|---|---|
| U01 | Arjun | Member | Infra |
| U02 | Bhavna | Manager | Security |
| U03 | Chen | Member | DevOps |
| U04 | Deepa | Manager | Database |
| U05 | Eswar | Director | IT (all teams) |
| U06 | Fatima | Director | Security (Security + Infra) |
| U07 | Girish | Member | Network |
| U08 | Hema | Manager | DevOps |

Incident Records (10):
| Record ID | Description | Owner Team | Tagged Teams | Severity |
|---|---|---|---|---|
| R01 | Server rack power fluctuation | Infra | Security | High |
| R02 | Firewall rule misconfiguration | Security | Infra | Critical |
| R03 | Database replication lag | Database | DevOps | High |
| R04 | SSL certificate expiry | Security | None | Critical |
| R05 | Load balancer config error | Network | DevOps, Infra | Medium |
| R06 | CI/CD pipeline failure | DevOps | Database | High |
| R07 | VPN tunnel dropped | Network | Security | Medium |
| R08 | Backup job failure | Infra | Database | Low |
| R09 | API rate limit exceeded | DevOps | None | Low |
| R10 | DNS resolution failure | Network | Infra | High |

Test Queries (8 users):
| Test | User | Query |
|---|---|---|
| T01 | U01 (Arjun, Infra Member) | Show all incidents I can see |
| T02 | U02 (Bhavna, Security Manager) | Show all incidents I can see |
| T03 | U05 (Eswar, Director IT) | Show all incidents I can see |
| T04 | U06 (Fatima, Director Security) | Show all incidents I can see |
| T05 | U07 (Girish, Network Member) | Show all incidents I can see |
| T06 | U08 (Hema, DevOps Manager) | Show all incidents I can see |
| T07 | U04 (Deepa, Database Manager) | Show all incidents I can see |
| T08 | U03 (Chen, DevOps Member) | Show all incidents I can see |

Role-based access control (RBAC), row-level security (RLS) rule design, permission trace verification, multi-tenant data isolation, security auditing