-- ============================================================
-- 19_agent_ords_security_local_dds_setup.sql
-- Local-only executable DDS example for Agent ORDS security.
--
-- Scenario:
--   * DDS uses END USER -> DATA ROLE -> DATA GRANT.
--   * No permission table lookup is used.
--   * Each DATA GRANT declares the protected object, row filter,
--     and column restriction.
-- ============================================================
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET ECHO OFF
SET FEEDBACK ON
SET DEFINE OFF

PROMPT === 1. Creating DDS-only local table and view ===
CREATE TABLE cb_dds_documents (
  doc_id        NUMBER PRIMARY KEY,
  title         VARCHAR2(100) NOT NULL,
  owner_emp_no  VARCHAR2(20)  NOT NULL,
  dept_code     VARCHAR2(20)  NOT NULL,
  contents      VARCHAR2(4000),
  created_at    DATE DEFAULT SYSDATE NOT NULL
);

INSERT INTO cb_dds_documents VALUES (1, 'HR payroll guide',       'E1001', 'HR',    'Payroll policy and HR guide', SYSDATE);
INSERT INTO cb_dds_documents VALUES (2, 'HR recruiting plan',     'E1002', 'HR',    'Recruiting plan for HR team', SYSDATE);
INSERT INTO cb_dds_documents VALUES (3, 'Finance close checklist','E2001', 'FIN',   'Monthly close checklist', SYSDATE);
INSERT INTO cb_dds_documents VALUES (4, 'Finance audit memo',     'E2002', 'FIN',   'Audit memo for finance team', SYSDATE);
INSERT INTO cb_dds_documents VALUES (5, 'Sales forecast',         'E3001', 'SALES', 'Quarterly sales forecast', SYSDATE);
INSERT INTO cb_dds_documents VALUES (6, 'HR benefits notice',     'E1003', 'HR',    'Benefits notice for employees', SYSDATE);

CREATE OR REPLACE VIEW cb_dds_v_search_documents AS
SELECT doc_id,
       title,
       owner_emp_no,
       dept_code,
       contents,
       created_at
FROM   cb_dds_documents;

COMMIT;

PROMPT === 2. Creating DDS end users ===
CREATE END USER "cb_dds_hr"   IDENTIFIED BY "CbDds#Hr2026Local1";
CREATE END USER "cb_dds_fin"  IDENTIFIED BY "CbDds#Fin2026Local1";
CREATE END USER "cb_dds_all"  IDENTIFIED BY "CbDds#All2026Local1";
CREATE END USER "cb_dds_none" IDENTIFIED BY "CbDds#None2026Local1";

PROMPT === 3. Creating DATA ROLEs and connection carrier role ===
CREATE ROLE cb_dds_connect_role;
GRANT CREATE SESSION TO cb_dds_connect_role;

CREATE DATA ROLE cb_dds_hr_role;
CREATE DATA ROLE cb_dds_fin_role;
CREATE DATA ROLE cb_dds_all_role;
CREATE DATA ROLE cb_dds_connect_only_role;

GRANT cb_dds_connect_role TO cb_dds_hr_role;
GRANT cb_dds_connect_role TO cb_dds_fin_role;
GRANT cb_dds_connect_role TO cb_dds_all_role;
GRANT cb_dds_connect_role TO cb_dds_connect_only_role;

PROMPT === 4. Mapping END USERs to DATA ROLEs ===
GRANT DATA ROLE cb_dds_hr_role           TO "cb_dds_hr";
GRANT DATA ROLE cb_dds_fin_role          TO "cb_dds_fin";
GRANT DATA ROLE cb_dds_all_role          TO "cb_dds_all";
GRANT DATA ROLE cb_dds_connect_only_role TO "cb_dds_none";

PROMPT === 5. Creating DATA GRANTs ===
CREATE DATA GRANT admin.cb_dg_hr_docs
  AS SELECT (ALL COLUMNS EXCEPT contents)
  ON admin.cb_dds_v_search_documents
  WHERE dept_code = 'HR'
  TO cb_dds_hr_role;

CREATE DATA GRANT admin.cb_dg_fin_docs
  AS SELECT (ALL COLUMNS EXCEPT contents)
  ON admin.cb_dds_v_search_documents
  WHERE dept_code = 'FIN'
  TO cb_dds_fin_role;

CREATE DATA GRANT admin.cb_dg_all_docs
  AS SELECT
  ON admin.cb_dds_v_search_documents
  TO cb_dds_all_role;

PROMPT === DDS local setup complete ===
EXIT;
