Adds dds / dds-setup / dds-tests / dds-teardown subcommands so the
26ai Deep Data Security variant can be run from the same one-click
entry point. Not part of `./run.sh all` since DDS requires 26ai
(23.26.2+) which not every ADB has.
- sql/adb/14_tests_dds_user.sql: shared verification script for all
4 ddsuser_*; uses WHENEVER SQLERROR CONTINUE so the expected
ORA-00942 (deny-by-hiding) doesn't abort the script. Includes
bypass attempts against the underlying VPD views, raw DB Links,
and the VPD permission tables.
- sql/adb/15_dds_cleanup.sql: idempotent teardown for DDS objects
(data grants, end users, data roles, dds_db_role, DDS-only views).
- run.sh: do_dds_prereq / do_dds_setup / do_dds_tests /
do_dds_teardown helpers; dispatch case extended.
Also fixes a pre-existing secrets-leak gap: both 07_end_users.sql
and 13_dds_variant.sql had SET DEFINE ON without SET VERIFY OFF,
which causes sqlplus to echo the substituted DDL (including the
IDENTIFIED BY <password> clause) on the `new 1:` line. Added
SET VERIFY OFF.
E2E re-verified on ADB 23.26.2.2.0: matrix identical to manual run
(MY=17 / PG=12 / BOTH=12+17 / NONE=ORA-00942 on both), no password
in logs, dds-teardown leaves no residue.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
Real run against ADB 23.26.2.2.0 surfaced two issues:
- END USERs can't be direct grantees of a regular ROLE (ORA-01917).
Connection privilege must flow through a DATA ROLE — added
connect_only_role for ddsuser_none so it can authenticate
without holding any data grant (mirrors VPDUSER_NONE UX).
- DDS Data Grants on top of the shared v_customers_* views silently
returned 0 rows because the VPD policy on those views evaluates
1=0 for sessions whose LOGON trigger didn't load the VPD context
(i.e. all ddsuser_*). Created dedicated DDS-only views
(v_dds_customers_pg / v_dds_customers_my) so DDS Data Grants are
the sole authority.
E2E matrix now passes (ddsuser_my MY=17, ddsuser_pg PG=12,
ddsuser_both 12/17, ddsuser_none ORA-00942 on both). Notably DDS
returns ORA-00942 where VPD returned 0 rows — stronger object
hiding.
Expanded docs/05-dds-variant.md with:
- §1.1 capability matrix (End User, Data Role, Data Grant, MAC,
ORA_END_USER_CONTEXT, OAuth2 federation, End User Context Object,
ORA_IS_COLUMN_AUTHORIZED, dictionary views)
- §1.2 VPD/RAS-vs-DDS comparison
- §1.3 best-fit scenarios (multi-tenant SaaS, agentic AI, HR/PHI,
federated identity, compliance)
- §1.4 limitations
- §5 operation-level grant example (manager-only UPDATE salary)
- §8 actual E2E results table
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
Reimplements the same 4-user source-access matrix using Oracle AI
Database 26ai's Deep Data Security (DDS) — VPD's declarative SQL
successor. Coexists with the VPD demo (ddsuser_* / dds_* prefixes,
MAC intentionally not enabled on shared views).
- sql/adb/13_dds_variant.sql: CREATE END USER + CREATE DATA ROLE +
CREATE DATA GRANT for the same 4-user matrix; row-filter and
column-mask variants shown as commented examples.
- docs/05-dds-variant.md: prereqs (23.26.2+, COMPATIBLE>=20.0),
VPD <-> DDS 1:1 mapping table, run + teardown snippets.
- .env.example: DDSUSER_*_PASSWORD block (3b).
- README.md: tree + "더 깊이" link.
Not wired into run.sh — kept manual since DDS requires 26ai.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
Replaces the original APAC-vs-all 2-user demo (vpduser_a/b on
KR_ANALYSTS/GLOBAL_ADMINS groups) with a 2x2 source-access matrix:
vpduser_my -> MY_ONLY group -> MySQL view only
vpduser_pg -> PG_ONLY group -> Postgres view only
vpduser_both -> BOTH_SOURCES group -> both views
vpduser_none -> (no group) -> nothing (default deny)
Why: source-level segmentation is the more common production
permission story than region-level filtering. Region filtering
remains available as an opt-in variant via commented UPDATE in
sql/adb/03_seed.sql.
Key changes:
- 03_seed.sql, 07_end_users.sql, 00_cleanup.sql, .env.example,
run.sh updated for the new 4-user model. All 4 users get
identical view GRANTs; the only differentiator is the
permission table (proves the model is "data-driven, not
GRANT-driven").
- 08-11 split into one file per user: my (+ 5 bypass attempts),
pg, both, none (default-deny verification).
- 12_tests_admin_audit.sql uses LEFT JOIN so vpduser_none shows
up as NULL permissions, and filters by object_owner=USER to
exclude cross-schema policies.
- Removed inline "-- comment" after ";" lines in 03_seed.sql:
SQL*Plus silently skipped the inserts (documented gotcha).
- README.md + docs/01,02 updated for the 4-user matrix. docs/03
detailed guide keeps the region-filter example but now has a
preface explaining it's a variant of the default 4-user model.
- docs/04: db_type='mysql_community' note added (RDS MySQL).
E2E verified: PG=0/MY=17, PG=12/MY=0, PG=12/MY=17, PG=0/MY=0
plus all 5 bypass attempts blocked.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
ADB-centered row-level access control across heterogeneous DB sources
(AWS RDS Postgres + MySQL) using Oracle VPD + Data Redaction +
Secure Application Context, packaged as a one-click demo.
Mechanism:
- LOGON trigger calls ctx_pkg.init once per session to load the user's
allowed regions from the permission mapping tables into a Secure App
Context (VPD_CTX, USING ctx_pkg).
- VPD policy function vpd_region_filter reads SYS_CONTEXT and returns
an IN-list predicate (or '1=0' for fail-closed, NULL for '*'),
which Oracle injects into every SELECT on the protected views.
- Data Redaction reuses the same context to mask PII (email, full_name)
when the allowed-regions value is not '*'.
- 5 documented bypass attempts (direct DB link SELECT, SET_CONTEXT
spoof, DBMS_RLS drop, mapping table SELECT) all blocked by GRANT
scoping + DEFINER rights on ctx_pkg.
One-click entrypoint:
- ./run.sh {prereq|source|adb|tests|audit|all|teardown}
- Source DDL (Postgres + MySQL customers + 12-row seed each) is
applied via local psql/mysql; ADB-side setup via sqlplus with .env
values injected as SQL*Plus DEFINE substitutions.
Verified E2E on ADB 26ai + AWS RDS PG + RDS MySQL (mysql_community
gateway) on 2026-05-26: VPDUSER_A sees only APAC rows (PG 2 / MySQL 6,
PII masked), VPDUSER_B sees all (PG 12 / MySQL 17, PII unmasked).
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
