oracle-deep-data-security-lab/scenarios/02-shared-app-account/WORKSHOP.md

# Workshop - Secure Shared Application Accounts With Oracle Deep Data Security

## About This Workshop

This workshop demonstrates how Oracle Deep Data Security protects data behind a shared application account or connection pool.

The lab shows a common enterprise pattern: the database sees only a technical user, `DDS_APP`, even though many business users are behind the application. Before DDS, the technical account can query all orders and margins. After DDS, access is evaluated by the real business persona.

## Workshop Goals

- Demonstrate the risk of broad connection pool privileges.
- Keep the application connection model while enforcing end-user authorization.
- Validate different results for Alice and Bruno.

## Estimated Time

25 to 35 minutes.

## Scenario Summary

| Persona | Business Role | Expected Access After DDS |
| --- | --- | --- |
| `dds_app` | Technical app account | Used to demonstrate broad application access before DDS. |
| `alice` | Sales representative | Own orders, no `MARGIN`. |
| `bruno` | LATAM manager | LATAM orders with `MARGIN`. |

## Architecture Flow

```text
End user -> application connection pool -> DDS_APP database session
                                      |
                                      v
                         DDS evaluates end-user data role
                                      |
                                      v
                         Authorized rows and columns only
```

## 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 `/`; it reruns the previous command.


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/02-shared-app-account/sql/99_reset.sql
```

### Task 1.2 - Create The Orders Table

```sql
@scenarios/02-shared-app-account/sql/00_schema.sql
```

| Column | Purpose |
| --- | --- |
| `ORDER_ID` | Order identifier. |
| `CUSTOMER_NAME` | Customer name. |
| `REGION` | Region used for manager filtering. |
| `SELLER` | Seller used for Alice's row filter. |
| `AMOUNT` | Order amount. |
| `MARGIN` | Sensitive commercial margin. |

### Task 1.3 - Load Sample Orders

```sql
@scenarios/02-shared-app-account/sql/01_seed_data.sql
```

Examples include LATAM orders owned by Alice plus NA and EMEA orders owned by other sellers.

### Task 1.4 - Create Personas And Shared App Access

```sql
@scenarios/02-shared-app-account/sql/02_identities.sql
```

Key objects created:

```sql
CREATE END USER alice IDENTIFIED BY "Welcome1_DDS!";
CREATE END USER bruno IDENTIFIED BY "Welcome1_DDS!";
CREATE USER dds_app IDENTIFIED BY "AppPool#2026Lab!" ACCOUNT UNLOCK;

CREATE DATA ROLE seller_role;
CREATE DATA ROLE latam_manager_role;
CREATE ROLE shared_app_legacy_access_role;
```

`shared_app_legacy_access_role` gives `DDS_APP` broad access before DDS, simulating a typical overprivileged connection pool.

## Lab 2 - Demonstrate The Vulnerable Shared Account

### Task 2.1 - Review Raw Orders

```sql
SELECT order_id, customer_name, region, seller, amount, margin
FROM dds_orders
ORDER BY order_id;
```

### Task 2.2 - Connect As DDS_APP

```sql
exit
```

```bash
sql 'dds_app/AppPool#2026Lab!@ddslab_tunnel'
```

### Task 2.3 - Run The Broad Application Query

```sql
@scenarios/02-shared-app-account/sql/04_test_queries.sql
```

Expected result before DDS: the shared account can see all regions and the sensitive `MARGIN` column.

## 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/02-shared-app-account/sql/03_data_grants.sql
```

The grants are:

| Data Grant | What It Allows |
| --- | --- |
| `seller_own_orders` | Sellers see only their own orders and approved columns. |
| `latam_manager_orders` | LATAM managers see LATAM orders with all columns. |

`SET USE DATA GRANTS ONLY` makes DDS enforce the data boundary on `DDS_ORDERS`.

## Lab 4 - Validate Alice And Bruno

### Task 4.1 - Alice Normal Query

```sql
exit
```

```bash
sql 'alice/Welcome1_DDS!@ddslab_tunnel'
```

```sql
ALTER SESSION SET CURRENT_SCHEMA = ADMIN;

SELECT order_id, customer_name, region, seller, amount
FROM dds_orders
ORDER BY order_id;
```

Expected result: Alice sees only her own orders and no `MARGIN`.

### Task 4.2 - Alice Tries To Force Margin

```sql
SELECT order_id, customer_name, region, seller, amount, margin
FROM dds_orders
ORDER BY order_id;
```

Expected result: `MARGIN` is not authorized for Alice.

### Task 4.3 - Bruno Manager Query

```sql
exit
```

```bash
sql 'bruno/Welcome1_DDS!@ddslab_tunnel'
```

```sql
ALTER SESSION SET CURRENT_SCHEMA = ADMIN;

SELECT order_id, customer_name, region, seller, amount, margin
FROM dds_orders
ORDER BY order_id;
```

Expected result: Bruno sees LATAM orders with `MARGIN`.

## Lab 5 - Clean Up

```sql
exit
```

```bash
sql admin@ddslab_tunnel
```

```sql
@scenarios/02-shared-app-account/sql/99_reset.sql
exit
```

## What You Built

| Component | Purpose |
| --- | --- |
| `DDS_ORDERS` | Orders table with sensitive margin. |
| `DDS_APP` | Shared application account used to demonstrate connection pool risk. |
| `END USER` | `alice`, `bruno`; business personas. |
| `DATA ROLE` | `seller_role`, `latam_manager_role`; authorization profiles. |
| `DATA GRANT` | Enforces row and column access by business role. |
| `shared_app_legacy_access_role` | Broad role used only for the vulnerable before state. |

The trust chain is: **application identity transport -> end-user persona -> DATA ROLE -> DATA GRANT enforcement**.

## Product Manager Talking Points

- DDS keeps the connection pool model.
- The database no longer treats the technical account as the final data authorization boundary.
- The same table returns different results for different business users.