oracle-deep-data-security-lab/scenarios/03-pii-row-column-cell/RUNBOOK.md

Runbook - 03 PII Row Column Cell

Objective

Show fine-grained PII controls across rows, columns, and cells: an employee sees their own record, a manager sees direct reports with SSN hidden, and HR sees sensitive data.

Security Value

• Protects PII and salary data.
• Demonstrates column and cell-level access, not only simple RBAC.
• Supports privacy, HR, LGPD/GDPR, and segregation-of-duties conversations.

Prerequisites

• Oracle AI Database compatible with Oracle Deep Data Security.
• SQLcl or SQL*Plus.
• User with privileges to create data grants.

Before - Vulnerable Environment

1. Reset the scenario:

   @scenarios/03-pii-row-column-cell/sql/99_reset.sql
   

2. Create data and personas without data grants:

   @scenarios/03-pii-row-column-cell/sql/00_schema.sql
   @scenarios/03-pii-row-column-cell/sql/01_seed_data.sql
   @scenarios/03-pii-row-column-cell/sql/02_identities.sql
   

3. Run the broad query:

   @scenarios/03-pii-row-column-cell/sql/04_test_queries.sql
   

Expected Result Before

• All employees are visible.
• SSN and SALARY are visible to users with broad object access.
• A manager or operational user could see more PII than required if the application fails.

After - Applying Deep Data Security

1. Apply employee, manager, and HR data grants:

   @scenarios/03-pii-row-column-cell/sql/03_data_grants.sql
   

2. Run the same query as emma, marvin, and victoria:

   @scenarios/03-pii-row-column-cell/sql/04_test_queries.sql
   

Expected Result After

• emma sees only her own record.
• marvin sees his own record and direct reports, but direct-report SSN appears as NULL.
• victoria sees all records through the HR role.
• Phone updates are allowed only for the authorized row.

Demo Evidence

• Screenshot showing SSN as NULL for a manager.
• Phone update query working only on the user's own record.
• Explanation of row, column, and cell-level access.

Official References

• Fine-Grained Data Authorization: https://docs.oracle.com/en/database/oracle/oracle-database/26/ddscg/fine-grained-data-authorization.html
• Create Data Grants: https://docs.oracle.com/en/database/oracle/oracle-database/26/ddscg/create-data-grants.html
• CREATE DATA GRANT SQL Reference: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/create-data-grant.html