vpd-permission-poc/database/adb/47_dds_ad_identity_test_setup.sql

-- ============================================================
-- 47_dds_ad_identity_test_setup.sql
--
-- Test-only bridge for the isolated dds.test Active Directory domain.
-- Maps immutable AD objectGUID values to the existing HMM application users
-- 1 and 2, then publishes only their passwordless local DDS END USERs.
--
-- This does NOT validate an AD/OIDC JWT. A verified issuer + subject must be
-- resolved by the MCP application before it uses this bridge.
-- ============================================================
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET ECHO OFF
SET FEEDBACK ON
SET DEFINE OFF

PROMPT === 1. Creating external identity bridge ===
BEGIN
  EXECUTE IMMEDIATE q'[
    CREATE TABLE cb_external_identity_binding (
      issuer                 VARCHAR2(512) NOT NULL,
      subject                VARCHAR2(512) NOT NULL,
      application_user_id    NUMBER NOT NULL,
      display_name            VARCHAR2(256),
      active                 CHAR(1) DEFAULT 'Y' NOT NULL
        CHECK (active IN ('Y', 'N')),
      created_at             TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
      updated_at             TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
      CONSTRAINT cb_external_identity_binding_pk PRIMARY KEY (issuer, subject)
    )]';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE <> -955 THEN RAISE; END IF;
END;
/

BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX cb_external_identity_binding_user_ix '
    || 'ON cb_external_identity_binding (application_user_id)';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE <> -955 THEN RAISE; END IF;
END;
/

PROMPT === 2. Creating DDS END USER map ===
BEGIN
  EXECUTE IMMEDIATE q'[
    CREATE TABLE cb_dds_end_user_map (
      application_user_id NUMBER PRIMARY KEY,
      end_user_name       VARCHAR2(128) NOT NULL UNIQUE,
      data_role_name      VARCHAR2(128) NOT NULL UNIQUE,
      lookup_key_ref      VARCHAR2(128) NOT NULL,
      grant_name          VARCHAR2(128) NOT NULL,
      publish_status      VARCHAR2(20) NOT NULL
        CHECK (publish_status IN ('PENDING', 'PUBLISHED', 'REVOKED', 'FAILED')),
      published_at        TIMESTAMP,
      last_error          VARCHAR2(1000)
    )]';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE <> -955 THEN RAISE; END IF;
END;
/

DECLARE
  v_active_users NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_active_users
  FROM cb_app_user
  WHERE user_id IN (1, 2) AND active = 'Y';
  IF v_active_users <> 2 THEN
    RAISE_APPLICATION_ERROR(-20947, 'Expected active HMM application users 1 and 2.');
  END IF;
END;
/

PROMPT === 3. Binding dds.test immutable subjects to HMM users ===
MERGE INTO cb_external_identity_binding target
USING (
  SELECT 'urn:dds-ad:test' AS issuer,
         'fa7ebe4b-aca8-4049-a436-7f3da2d27a9f' AS subject,
         1 AS application_user_id,
         'dds-alice' AS display_name
  FROM dual
  UNION ALL
  SELECT 'urn:dds-ad:test',
         '128546cf-ec0a-4e99-8f51-dfce29280c91',
         2,
         'dds-bob'
  FROM dual
) source
ON (target.issuer = source.issuer AND target.subject = source.subject)
WHEN MATCHED THEN UPDATE SET
  target.application_user_id = source.application_user_id,
  target.display_name = source.display_name,
  target.active = 'Y',
  target.updated_at = SYSTIMESTAMP
WHEN NOT MATCHED THEN INSERT (
  issuer, subject, application_user_id, display_name, active
) VALUES (
  source.issuer, source.subject, source.application_user_id, source.display_name, 'Y'
);

MERGE INTO cb_dds_end_user_map target
USING (
  SELECT user_id AS application_user_id,
         'DDS_U_' || TO_CHAR(user_id) AS end_user_name,
         'DDS_U_' || TO_CHAR(user_id) || '_ROLE' AS data_role_name,
         'DDS_OCI_IAM_CLIENT_SECRET_DERIVED_V1' AS lookup_key_ref,
         'DDS_MCP_U_' || TO_CHAR(user_id) || '_VECTOR_GRANT' AS grant_name
  FROM cb_app_user
  WHERE user_id IN (1, 2)
) source
ON (target.application_user_id = source.application_user_id)
WHEN MATCHED THEN UPDATE SET
  target.end_user_name = source.end_user_name,
  target.data_role_name = source.data_role_name,
  target.lookup_key_ref = source.lookup_key_ref,
  target.grant_name = source.grant_name,
  target.publish_status = 'PENDING',
  target.last_error = NULL
WHEN NOT MATCHED THEN INSERT (
  application_user_id, end_user_name, data_role_name, lookup_key_ref,
  grant_name, publish_status
) VALUES (
  source.application_user_id, source.end_user_name, source.data_role_name,
  source.lookup_key_ref, source.grant_name, 'PENDING'
);

PROMPT === 4. Publishing DDS identities only (default deny until data grants exist) ===
BEGIN
  FOR mapped_user IN (
    SELECT application_user_id, end_user_name, data_role_name
    FROM cb_dds_end_user_map
    WHERE application_user_id IN (1, 2)
    ORDER BY application_user_id
  ) LOOP
    EXECUTE IMMEDIATE 'CREATE END USER IF NOT EXISTS "' || mapped_user.end_user_name || '"';
    EXECUTE IMMEDIATE 'CREATE DATA ROLE IF NOT EXISTS ' || mapped_user.data_role_name;
    EXECUTE IMMEDIATE 'GRANT DATA ROLE ' || mapped_user.data_role_name
      || ' TO "' || mapped_user.end_user_name || '"';
    UPDATE cb_dds_end_user_map
    SET publish_status = 'PUBLISHED', published_at = SYSTIMESTAMP, last_error = NULL
    WHERE application_user_id = mapped_user.application_user_id;
  END LOOP;
  COMMIT;
END;
/

PROMPT === 5. Identity bridge inventory ===
SELECT b.issuer, b.subject, b.display_name, b.application_user_id,
       m.end_user_name, m.data_role_name, m.publish_status
FROM cb_external_identity_binding b
JOIN cb_dds_end_user_map m ON m.application_user_id = b.application_user_id
WHERE b.issuer = 'urn:dds-ad:test'
ORDER BY b.application_user_id;

PROMPT === AD identity to DDS END USER test setup complete ===
EXIT;