280 lines
6.7 KiB
Markdown
Executable File
280 lines
6.7 KiB
Markdown
Executable File
# Workshop - Prevent View Bypass With Oracle Deep Data Security
|
|
|
|
## About This Workshop
|
|
|
|
This workshop demonstrates why access rules should be enforced on the protected data, not only in application SQL or views. Before DDS, a legacy view can apply the expected owner filter, but direct table access can bypass that path and expose accounts outside the user's ownership. After DDS, the base table and alternate access paths respect the same boundary.
|
|
|
|
## Workshop Goals
|
|
|
|
- Create an account table and a legacy view.
|
|
- Show how a view can become an alternate access path.
|
|
- Apply DDS to enforce account ownership at the table boundary.
|
|
- Validate that table and view access return the same authorized subset.
|
|
|
|
## Estimated Time
|
|
|
|
20 to 30 minutes.
|
|
|
|
## Scenario Summary
|
|
|
|
| Persona | Business Role | Expected Access After DDS |
|
|
| --- | --- | --- |
|
|
| `emma` | Account owner | Only accounts owned by Emma. |
|
|
| `marvin` | Account owner | Only accounts owned by Marvin. |
|
|
| `erik` | Account owner | Only accounts owned by Erik. |
|
|
|
|
## Before You Begin
|
|
|
|
```bash
|
|
cd <repo-root>
|
|
export TNS_ADMIN=<wallet-directory>
|
|
sql admin@ddslab_tunnel
|
|
```
|
|
|
|
SQLcl note: after running `@file.sql`, do not type `/`.
|
|
|
|
|
|
Connection alias note: ddslab_tunnel is the TNS alias configured in the wallet `tnsnames.ora` for this lab. If your wallet uses another alias, replace ddslab_tunnel with your own service alias.
|
|
|
|
## Lab 1 - Prepare The Environment
|
|
|
|
### Task 1.1 - Reset The Scenario
|
|
|
|
```sql
|
|
@scenarios/04-view-bypass-mac/sql/99_reset.sql
|
|
```
|
|
|
|
### Task 1.2 - Create Table And View
|
|
|
|
```sql
|
|
@scenarios/04-view-bypass-mac/sql/00_schema.sql
|
|
```
|
|
|
|
The script creates:
|
|
|
|
| Object | Purpose |
|
|
| --- | --- |
|
|
| `DDS_MAC_ACCOUNTS` | Protected base account table. |
|
|
| `DDS_MAC_ACCOUNTS_VIEW` | Legacy view over the base table with an owner filter. |
|
|
|
|
### Task 1.3 - Load Accounts
|
|
|
|
```sql
|
|
@scenarios/04-view-bypass-mac/sql/01_seed_data.sql
|
|
```
|
|
|
|
Example accounts include owners `emma`, `marvin`, and `erik`.
|
|
|
|
### Task 1.4 - Create Personas And Roles
|
|
|
|
```sql
|
|
@scenarios/04-view-bypass-mac/sql/02_identities.sql
|
|
```
|
|
|
|
The script creates:
|
|
|
|
```sql
|
|
CREATE END USER emma IDENTIFIED BY "Welcome1_DDS!";
|
|
CREATE END USER marvin IDENTIFIED BY "Welcome1_DDS!";
|
|
CREATE END USER erik IDENTIFIED BY "Welcome1_DDS!";
|
|
CREATE DATA ROLE account_owner_role;
|
|
```
|
|
|
|
## Lab 2 - Demonstrate The View Bypass Risk
|
|
|
|
### Task 2.1 - Connect As Emma Before DDS
|
|
|
|
Exit the administrator session:
|
|
|
|
```sql
|
|
exit
|
|
```
|
|
|
|
Connect as Emma:
|
|
|
|
```bash
|
|
sql 'emma/Welcome1_DDS!@ddslab_tunnel'
|
|
```
|
|
|
|
Emma represents an account owner. Before DDS enforcement, she still has a broad legacy role, so this section demonstrates why view-based controls and object grants can be risky.
|
|
|
|
### Task 2.2 - Query The Base Table Directly Before DDS
|
|
|
|
```sql
|
|
ALTER SESSION SET CURRENT_SCHEMA = ADMIN;
|
|
|
|
SELECT account_id, account_name, owner_name, region, balance
|
|
FROM dds_mac_accounts
|
|
ORDER BY account_id;
|
|
```
|
|
|
|
Expected result before DDS: Emma can see `Account Alpha`, `Account Beta`, and `Account Gamma`, even though only `Account Alpha` belongs to her.
|
|
|
|
This is the direct table access path.
|
|
|
|
### Task 2.3 - Query The Legacy View Before DDS
|
|
|
|
```sql
|
|
SELECT account_id, account_name, owner_name, region, balance
|
|
FROM dds_mac_accounts_view
|
|
ORDER BY account_id;
|
|
```
|
|
|
|
Expected result before DDS: the view returns only `Account Alpha`, because the view contains the expected owner filter.
|
|
|
|
This is the intended application access path. The view is doing the right thing, but the direct table query above still bypassed the view-based control.
|
|
|
|
### Customer Message
|
|
|
|
Before DDS, the result depends on which path Emma uses:
|
|
|
|
```text
|
|
Direct table query -> all accounts
|
|
Legacy view query -> only Account Alpha
|
|
```
|
|
|
|
The business rule "Emma should only see Emma's account" exists in the view, but it is not enforced at the protected data boundary yet.
|
|
|
|
## Lab 3 - Apply Oracle Deep Data Security
|
|
|
|
### Task 3.1 - Reconnect As ADMIN
|
|
|
|
```sql
|
|
exit
|
|
```
|
|
|
|
```bash
|
|
sql admin@ddslab_tunnel
|
|
```
|
|
|
|
### Task 3.2 - Apply Data Grants
|
|
|
|
```sql
|
|
@scenarios/04-view-bypass-mac/sql/03_data_grants.sql
|
|
```
|
|
|
|
The main grant is:
|
|
|
|
```sql
|
|
CREATE OR REPLACE DATA GRANT mac_account_owner
|
|
AS SELECT
|
|
ON dds_mac_accounts
|
|
WHERE owner_name = ORA_END_USER_CONTEXT.username
|
|
TO account_owner_role;
|
|
```
|
|
|
|
This filters accounts to the authenticated owner. The script enables DDS on `DDS_MAC_ACCOUNTS`.
|
|
|
|
In this lab, the predicate compares the account owner with the active DDS end-user context:
|
|
|
|
```sql
|
|
WHERE UPPER(owner_name) = ORA_END_USER_CONTEXT.username
|
|
```
|
|
|
|
That means Emma, Marvin, and Erik can run the same SQL, but the database returns different rows for each persona.
|
|
|
|
## Lab 4 - Validate Table And View Access
|
|
|
|
### Task 4.1 - Test Emma After DDS
|
|
|
|
```sql
|
|
exit
|
|
```
|
|
|
|
```bash
|
|
sql 'emma/Welcome1_DDS!@ddslab_tunnel'
|
|
```
|
|
|
|
```sql
|
|
@scenarios/04-view-bypass-mac/sql/04_test_queries.sql
|
|
```
|
|
|
|
Expected result: Emma sees only `Account Alpha` from both table and view paths.
|
|
|
|
The same two access paths are tested again:
|
|
|
|
```text
|
|
Direct table query -> only Account Alpha
|
|
Legacy view query -> only Account Alpha
|
|
```
|
|
|
|
The SQL did not become smarter. The database security boundary became mandatory.
|
|
|
|
### Task 4.2 - Quick Validation With Marvin
|
|
|
|
```sql
|
|
exit
|
|
```
|
|
|
|
```bash
|
|
sql 'marvin/Welcome1_DDS!@ddslab_tunnel'
|
|
```
|
|
|
|
```sql
|
|
@scenarios/04-view-bypass-mac/sql/04_test_queries.sql
|
|
```
|
|
|
|
Expected result: Marvin sees only `Account Beta` from both paths.
|
|
|
|
### Task 4.3 - Quick Validation With Erik
|
|
|
|
```sql
|
|
exit
|
|
```
|
|
|
|
```bash
|
|
sql 'erik/Welcome1_DDS!@ddslab_tunnel'
|
|
```
|
|
|
|
```sql
|
|
@scenarios/04-view-bypass-mac/sql/04_test_queries.sql
|
|
```
|
|
|
|
Expected result: Erik sees only `Account Gamma` from both paths.
|
|
|
|
### Customer Message
|
|
|
|
After DDS, the result is consistent regardless of the path:
|
|
|
|
```text
|
|
Emma -> Account Alpha only
|
|
Marvin -> Account Beta only
|
|
Erik -> Account Gamma only
|
|
```
|
|
|
|
This shows the value of DDS versus relying only on view logic, application SQL, BI filters, or agent-generated SQL.
|
|
|
|
## Lab 5 - Clean Up
|
|
|
|
```sql
|
|
exit
|
|
```
|
|
|
|
```bash
|
|
sql admin@ddslab_tunnel
|
|
```
|
|
|
|
```sql
|
|
@scenarios/04-view-bypass-mac/sql/99_reset.sql
|
|
exit
|
|
```
|
|
|
|
## What You Built
|
|
|
|
| Component | Purpose |
|
|
| --- | --- |
|
|
| `DDS_MAC_ACCOUNTS` | Protected base table. |
|
|
| `DDS_MAC_ACCOUNTS_VIEW` | Legacy view with the intended owner filter. |
|
|
| `END USER` | `emma`, `marvin`, `erik`; account owner personas. |
|
|
| `DATA ROLE` | `account_owner_role`; owner authorization profile. |
|
|
| `DATA GRANT` | Filters rows by `owner_name = ORA_END_USER_CONTEXT.username`. |
|
|
| `SET USE DATA GRANTS ONLY` | Enforces DDS on the base table. |
|
|
|
|
The trust chain is: **end-user identity -> account owner role -> owner data grant -> protected table access**.
|
|
|
|
## Product Manager Talking Points
|
|
|
|
- Views are useful, but they should not be the only security boundary.
|
|
- DDS protects the data regardless of the access path.
|
|
- This reduces bypass risk from direct SQL, legacy views, and reporting tools.
|