oracle-deep-data-security-lab/scenarios/06-rag-vector-classified-docs/WORKSHOP.md

# Workshop - Secure RAG Retrieval With Oracle Deep Data Security

## About This Workshop

This workshop demonstrates how Oracle Deep Data Security can protect a Retrieval-Augmented Generation (RAG) workflow by filtering vector search results before retrieved chunks are sent to an LLM.

The lab simulates an internal enterprise copilot that searches classified document chunks stored in Oracle AI Database. Without database-enforced authorization, a regular employee can retrieve HR, legal, and executive confidential chunks. With Oracle Deep Data Security, the same vector search returns only the chunks authorized for the end-user persona.

## Workshop Goals

By the end of this workshop, you will be able to:

- Explain why RAG security must be enforced before context reaches the LLM.
- Create a table with classified document chunks and vector embeddings.
- Simulate a vulnerable vector search that over-retrieves sensitive content.
- Apply Oracle Deep Data Security data grants by document classification.
- Validate that each user persona retrieves only authorized chunks.

## Estimated Time

30 to 45 minutes.

## Audience

- Product managers and solution engineers evaluating secure enterprise AI.
- Database security architects.
- OCI and Oracle Database consultants.
- Application teams building RAG, copilots, AI agents, or semantic search.

## Scenario Summary

The company has an internal copilot that answers questions using RAG. The copilot retrieves document chunks by semantic similarity using vector search.

The data includes five document chunks:

| Classification | Example Document | Who Should See It |
| --- | --- | --- |
| `PUBLIC` | Company Travel Guide | Everyone |
| `INTERNAL` | Benefits Policy | Employees |
| `HR_CONFIDENTIAL` | Executive Compensation Plan | HR |
| `LEGAL_CONFIDENTIAL` | Contract Renewal Risk | Legal |
| `EXECUTIVE_CONFIDENTIAL` | Board M&A Briefing | Executives |

The lab uses four personas:

| Persona | Business Role | Expected Access After DDS |
| --- | --- | --- |
| `nina` | Regular employee | `PUBLIC`, `INTERNAL` |
| `heitor` | HR user | `PUBLIC`, `INTERNAL`, `HR_CONFIDENTIAL` |
| `sofia` | Legal user | `PUBLIC`, `INTERNAL`, `LEGAL_CONFIDENTIAL` |
| `carlos` | Executive user | All classifications |

## Architecture Flow

```text
User question
    |
    v
Internal copilot / RAG service
    |
    v
Vector search in Oracle AI Database
    |
    v
Oracle Deep Data Security filters rows by persona and classification
    |
    v
Only authorized chunks are sent to the LLM
```

## Prerequisites

- Oracle AI Database with support for `VECTOR`, `TO_VECTOR`, and `VECTOR_DISTANCE`.
- Oracle Deep Data Security available in the database.
- SQLcl configured to connect to the lab Autonomous Database.
- A working wallet and `TNS_ADMIN` environment variable.
- The lab repository synchronized to your WSL or Linux/macOS environment.

## Before You Begin

Run all commands from the repository root:

```bash
cd <repo-root>
export TNS_ADMIN=<wallet-directory>
```

Connect as the lab administrator:

```bash
sql admin@ddslab_tunnel
```

SQLcl note: when you run a script with `@file.sql`, press Enter once and wait for the output. Do not type `/` afterward; `/` reruns the last command in the SQLcl buffer.


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

Run:

```sql
@scenarios/06-rag-vector-classified-docs/sql/99_reset.sql
```

This removes previous users, data roles, data grants, and test tables.

### Task 1.2 - Create The Chunk Table

Run:

```sql
@scenarios/06-rag-vector-classified-docs/sql/00_schema.sql
```

This creates `DDS_RAG_CHUNKS`, which stores document chunks, classification labels, text, and vector embeddings.

The table created by this task has the following shape:

| Column | Example Value | Purpose |
| --- | --- | --- |
| `CHUNK_ID` | `3` | Unique identifier for the retrieved chunk. |
| `DOCUMENT_TITLE` | `Contract Renewal Risk` | Human-readable source document title. |
| `DEPARTMENT` | `LEGAL` | Business owner or source department. |
| `CLASSIFICATION` | `LEGAL_CONFIDENTIAL` | Security label used by DDS to filter access. |
| `CHUNK_TEXT` | `Legal risk on renewal clauses...` | Text that could be sent to the LLM as RAG context. |
| `EMBEDDING` | `[0.80,0.10,0.20]` | Vector representation used for semantic similarity search. |

In customer terms: this table is the RAG knowledge base. The sensitive part is not only the document title, but the text chunk that may be sent to the model.

### Task 1.3 - Load Sample Classified Chunks

Run:

```sql
@scenarios/06-rag-vector-classified-docs/sql/01_seed_data.sql
```

This loads public, internal, HR confidential, legal confidential, and executive confidential content.

Examples inserted by this task:

| Chunk | Classification | Example Text |
| --- | --- | --- |
| `Benefits Policy` | `INTERNAL` | General benefits policy available to employees. |
| `Executive Compensation Plan` | `HR_CONFIDENTIAL` | Compensation calibration for executives and retention risks. |
| `Contract Renewal Risk` | `LEGAL_CONFIDENTIAL` | Legal risk on renewal clauses for strategic accounts. |
| `Company Travel Guide` | `PUBLIC` | Public travel and expense guidance for all employees. |
| `Board M&A Briefing` | `EXECUTIVE_CONFIDENTIAL` | Potential acquisition targets and board-level financial exposure. |

The important point for the demo is that these chunks live in the same vector table. DDS lets us avoid creating separate vector stores for each audience.

### Task 1.4 - Create Personas And Baseline Access

Run:

```sql
@scenarios/06-rag-vector-classified-docs/sql/02_identities.sql
```

This creates the end users, data roles, and a broad legacy retrieval role used only to demonstrate the vulnerable "before" state.

The personas and roles are created in `02_identities.sql`.

First, the lab creates four DDS end users:

```sql
CREATE END USER nina IDENTIFIED BY "Welcome1_DDS!";
CREATE END USER heitor IDENTIFIED BY "Welcome1_DDS!";
CREATE END USER sofia IDENTIFIED BY "Welcome1_DDS!";
CREATE END USER carlos IDENTIFIED BY "Welcome1_DDS!";
```

These are lightweight end-user identities used by DDS to evaluate who is asking the RAG question.

Next, the lab creates one data role for each business profile:

```sql
CREATE DATA ROLE rag_employee_role;
CREATE DATA ROLE rag_hr_role;
CREATE DATA ROLE rag_legal_role;
CREATE DATA ROLE rag_exec_role;
```

These data roles are the authorization profiles used later by the Data Grants.

The lab also creates a normal database role that allows the DDS end users to open SQLcl sessions during the demo:

```sql
CREATE ROLE rag_session_role;
GRANT CREATE SESSION TO rag_session_role;
GRANT rag_session_role TO rag_employee_role;
GRANT rag_session_role TO rag_hr_role;
GRANT rag_session_role TO rag_legal_role;
GRANT rag_session_role TO rag_exec_role;
```

This is a lab convenience. In a real application, the connection pool would usually be maintained by a technical application account while the application propagates the end-user security context.

To demonstrate the vulnerable state before DDS, the lab creates a broad legacy retrieval role:

```sql
CREATE ROLE rag_legacy_retrieval_role;
GRANT SELECT ON dds_rag_chunks TO rag_legacy_retrieval_role;
GRANT rag_legacy_retrieval_role TO rag_employee_role;
GRANT rag_legacy_retrieval_role TO rag_hr_role;
GRANT rag_legacy_retrieval_role TO rag_legal_role;
GRANT rag_legacy_retrieval_role TO rag_exec_role;
```

This role simulates an initial RAG implementation where the retrieval service can query the entire vector table. It is intentionally broad so the "before" demo can show over-retrieval.

Finally, each end user receives the appropriate data role:

```sql
GRANT DATA ROLE rag_employee_role TO nina;
GRANT DATA ROLE rag_hr_role TO heitor;
GRANT DATA ROLE rag_legal_role TO sofia;
GRANT DATA ROLE rag_exec_role TO carlos;
```

At this point, the identities exist, but DDS enforcement is not enabled yet. That means the broad legacy retrieval role still allows confidential chunks to appear in Nina's vector search results.

### Task 1.5 - Review The Classified Corpus

Run:

```sql
SELECT chunk_id, document_title, department, classification, chunk_text
FROM dds_rag_chunks
ORDER BY chunk_id;
```

Expected result: five chunks with different classifications.

## Lab 2 - Demonstrate Vulnerable RAG Retrieval

### Task 2.1 - Connect As Nina

Exit the administrator session:

```sql
exit
```

Connect as Nina:

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

Nina represents a regular employee using an internal copilot.

### Task 2.2 - Run The RAG Retrieval Query Before DDS

Run:

```sql
@scenarios/06-rag-vector-classified-docs/sql/04_test_queries.sql
```

The simulated RAG question is:

```text
Summarize critical documents about renewals, people, and legal risks.
```

In a real RAG application, the copilot would transform this question into an embedding and run a similarity search. In this lab, we use a fixed example vector to keep the demo repeatable.

The SQL executed by Nina is:

```sql
ALTER SESSION SET CURRENT_SCHEMA = ADMIN;

SELECT chunk_id,
       document_title,
       department,
       classification,
       chunk_text,
       VECTOR_DISTANCE(embedding, TO_VECTOR('[0.85,0.15,0.25]'), COSINE) AS distance
FROM dds_rag_chunks
ORDER BY distance
FETCH FIRST 5 ROWS ONLY;
```

The `VECTOR_DISTANCE` function ranks chunks by semantic proximity to the question embedding. Before DDS, this ranking can return sensitive chunks even when the user is a regular employee.

Expected result before protection: Nina may retrieve sensitive chunks such as:

- `HR_CONFIDENTIAL`
- `LEGAL_CONFIDENTIAL`
- `EXECUTIVE_CONFIDENTIAL`

### Customer Message

Similarity search alone does not understand business authorization. If the retrieval layer returns confidential chunks, the LLM may receive sensitive context before it generates an answer.

## Lab 3 - Apply Oracle Deep Data Security

### Task 3.1 - Reconnect As ADMIN

Exit Nina's session:

```sql
exit
```

Connect as administrator:

```bash
sql admin@ddslab_tunnel
```

### Task 3.2 - Apply Data Grants

Run:

```sql
@scenarios/06-rag-vector-classified-docs/sql/03_data_grants.sql
```

This applies classification-based data grants:

- Employee role: `PUBLIC`, `INTERNAL`
- HR role: `PUBLIC`, `INTERNAL`, `HR_CONFIDENTIAL`
- Legal role: `PUBLIC`, `INTERNAL`, `LEGAL_CONFIDENTIAL`
- Executive role: all classifications

The grants applied by the script are:

```sql
CREATE OR REPLACE DATA GRANT rag_public_internal_docs
  AS SELECT (chunk_id, document_title, department, classification, chunk_text, embedding)
  ON dds_rag_chunks
  WHERE classification IN ('PUBLIC', 'INTERNAL')
  TO rag_employee_role;
```

This is Nina's rule. It allows regular employees to retrieve only public and internal chunks. Even if the vector search finds confidential chunks as semantically relevant, DDS removes them from Nina's result set.

```sql
CREATE OR REPLACE DATA GRANT rag_hr_docs
  AS SELECT
  ON dds_rag_chunks
  WHERE classification IN ('PUBLIC', 'INTERNAL', 'HR_CONFIDENTIAL')
  TO rag_hr_role;
```

This is Heitor's rule. HR can retrieve regular employee content plus HR confidential chunks, such as compensation or people-risk material.

```sql
CREATE OR REPLACE DATA GRANT rag_legal_docs
  AS SELECT
  ON dds_rag_chunks
  WHERE classification IN ('PUBLIC', 'INTERNAL', 'LEGAL_CONFIDENTIAL')
  TO rag_legal_role;
```

This is Sofia's rule. Legal can retrieve public/internal content plus legal confidential chunks, such as renewal risks and contractual exposure.

```sql
CREATE OR REPLACE DATA GRANT rag_exec_docs
  AS SELECT
  ON dds_rag_chunks
  TO rag_exec_role;
```

This is Carlos's rule. Executives can retrieve all classifications, including board-level and strategic content.

Finally, the script enables DDS enforcement on the table:

```sql
BEGIN
  EXECUTE IMMEDIATE 'SET USE DATA GRANTS ONLY ON dds_rag_chunks ENABLED';
END;
/
```

This is the key DDS behavior: direct table privileges no longer decide the final result by themselves. The database returns only the rows and columns allowed by the active data grants for the end-user persona.

### Customer Message

Oracle Deep Data Security filters the rows returned by vector search before the RAG layer sends context to the LLM.

## Lab 4 - Validate Persona-Based Retrieval

### Task 4.1 - Validate Nina

Connect as Nina:

```sql
exit
```

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

Run:

```sql
@scenarios/06-rag-vector-classified-docs/sql/04_test_queries.sql
```

Expected result: Nina retrieves only `PUBLIC` and `INTERNAL` chunks.

### Task 4.2 - Validate Heitor

Connect as Heitor:

```sql
exit
```

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

Run:

```sql
@scenarios/06-rag-vector-classified-docs/sql/04_test_queries.sql
```

Expected result: Heitor retrieves public/internal chunks plus `HR_CONFIDENTIAL`.

### Task 4.3 - Validate Sofia

Connect as Sofia:

```sql
exit
```

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

Run:

```sql
@scenarios/06-rag-vector-classified-docs/sql/04_test_queries.sql
```

Expected result: Sofia retrieves public/internal chunks plus `LEGAL_CONFIDENTIAL`.

### Task 4.4 - Validate Carlos

Connect as Carlos:

```sql
exit
```

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

Run:

```sql
@scenarios/06-rag-vector-classified-docs/sql/04_test_queries.sql
```

Expected result: Carlos retrieves all classifications, including executive confidential chunks.

## Lab 5 - Clean Up

Connect as ADMIN:

```sql
exit
```

```bash
sql admin@ddslab_tunnel
```

Run:

```sql
@scenarios/06-rag-vector-classified-docs/sql/99_reset.sql
exit
```

## Workshop Wrap-Up

This workshop showed that:

- RAG retrieval can overexpose sensitive context if vector search is not authorization-aware.
- Oracle AI Database can store vectors and run semantic search directly in SQL.
- Oracle Deep Data Security can enforce fine-grained access to rows and columns through data grants.
- The LLM receives only the chunks the end user is authorized to access.

## What You Built

You configured database-level security for a RAG copilot so each user retrieves only the document chunks they are authorized to access, regardless of how broad the vector search query might be.

| Component | Purpose |
| --- | --- |
| `DDS_RAG_CHUNKS` | Vector-enabled table that stores document chunks, classifications, text, and embeddings. |
| `END USER` | `nina`, `heitor`, `sofia`, and `carlos`; DDS end-user identities used to evaluate the real user behind a RAG request. |
| `DATA ROLE` | `rag_employee_role`, `rag_hr_role`, `rag_legal_role`, and `rag_exec_role`; named authorization profiles for each business persona. |
| `DATA GRANT` | `rag_public_internal_docs`; allows regular employees to retrieve only `PUBLIC` and `INTERNAL` chunks. |
| `DATA GRANT` | `rag_hr_docs`; allows HR users to retrieve `PUBLIC`, `INTERNAL`, and `HR_CONFIDENTIAL` chunks. |
| `DATA GRANT` | `rag_legal_docs`; allows legal users to retrieve `PUBLIC`, `INTERNAL`, and `LEGAL_CONFIDENTIAL` chunks. |
| `DATA GRANT` | `rag_exec_docs`; allows executives to retrieve all classifications. |
| `VECTOR_DISTANCE` | SQL function used to rank chunks by semantic similarity to the RAG question embedding. |
| `SET USE DATA GRANTS ONLY` | DDS enforcement switch that makes data grants the active authorization boundary for `DDS_RAG_CHUNKS`. |
| `rag_legacy_retrieval_role` | Broad legacy role used only to demonstrate the vulnerable "before" state. |
| `rag_session_role` | Lab convenience role that grants `CREATE SESSION` so end users can connect directly with SQLcl for demo purposes. |

Oracle Database restricted RAG retrieval to authorized chunks before the LLM received context. No duplicate vector stores, no application-only filtering, and no reliance on prompt instructions to hide confidential information.

The trust chain is: **end-user authentication -> DATA ROLE -> DATA GRANT enforcement -> authorized vector retrieval**.

The database enforces the boundary at query time, so the same semantic search can safely return different context for Nina, Heitor, Sofia, and Carlos.

## Product Manager Talking Points

- The security control is applied at the data source, before model invocation.
- The RAG application does not need to duplicate classified documents across separate vector stores.
- The same vector query returns different authorized contexts for different personas.
- This reduces the risk of confidential content leaking through AI-generated answers.

## Official References

- Oracle Deep Data Security Guide: https://docs.oracle.com/en/database/oracle/oracle-database/26/ddscg/index.html
- About Data Grants: https://docs.oracle.com/en/database/oracle/oracle-database/26/ddscg/data-grants.html
- Create Data Grants: https://docs.oracle.com/en/database/oracle/oracle-database/26/ddscg/create-data-grants.html
- TO_VECTOR SQL Reference: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/to_vector.html
- Oracle AI Vector Search FAQ: https://www.oracle.com/database/ai-vector-search/faq/