-- ============================================================
-- 18_agent_ords_security_local_vpd_test.sql
-- Run as CB_ORDS.
--
-- Expected:
--   no context        -> 0 rows
--   cb_hr_key         -> HR rows only, 3 rows
--   cb_fin_key        -> owner_emp_no = E2001 only, 1 row
--   cb_all_key        -> all rows, 6 rows
--   contents column   -> NULL unless CAN_READ_CONTENTS = Y
--   invalid key       -> ORA-20002, then 0 rows
--   direct base table -> ORA-00942
-- ============================================================
WHENEVER SQLERROR CONTINUE
SET ECHO OFF
SET FEEDBACK ON
SET LINESIZE 220
SET PAGESIZE 100
COLUMN ctx_user_id FORMAT A12
COLUMN ctx_emp_no FORMAT A12
COLUMN ctx_dept_code FORMAT A12
COLUMN ctx_can_read_contents FORMAT A22
COLUMN title FORMAT A28
COLUMN owner_emp_no FORMAT A12
COLUMN dept_code FORMAT A10
COLUMN contents FORMAT A35

PROMPT
PROMPT === VPD 1. No Bearer key / context: fail closed ===
BEGIN
  admin.cb_agent_ctx_pkg.clear_user;
END;
/

SELECT SYS_CONTEXT('CB_AGENT_CTX','USER_ID') AS ctx_user_id,
       SYS_CONTEXT('CB_AGENT_CTX','EMP_NO') AS ctx_emp_no,
       SYS_CONTEXT('CB_AGENT_CTX','DEPT_CODE') AS ctx_dept_code,
       SYS_CONTEXT('CB_AGENT_CTX','CAN_READ_CONTENTS') AS ctx_can_read_contents
FROM   dual;

SELECT COUNT(*) AS rows_visible
FROM   admin.cb_v_search_documents;

PROMPT
PROMPT === VPD 2. Authorization: Bearer cb_hr_key -> HR department rows ===
BEGIN
  admin.cb_agent_ctx_pkg.set_user_by_bearer('cb_hr_key');
END;
/

SELECT SYS_CONTEXT('CB_AGENT_CTX','USER_ID') AS ctx_user_id,
       SYS_CONTEXT('CB_AGENT_CTX','EMP_NO') AS ctx_emp_no,
       SYS_CONTEXT('CB_AGENT_CTX','DEPT_CODE') AS ctx_dept_code,
       SYS_CONTEXT('CB_AGENT_CTX','CAN_READ_CONTENTS') AS ctx_can_read_contents
FROM   dual;

SELECT doc_id, title, owner_emp_no, dept_code, contents
FROM   admin.cb_v_search_documents
ORDER  BY doc_id;

SELECT COUNT(*) AS rows_visible
FROM   admin.cb_v_search_documents;

PROMPT
PROMPT === VPD 3. Authorization: Bearer cb_fin_key -> self row only ===
BEGIN
  admin.cb_agent_ctx_pkg.set_user_by_bearer('cb_fin_key');
END;
/

SELECT SYS_CONTEXT('CB_AGENT_CTX','USER_ID') AS ctx_user_id,
       SYS_CONTEXT('CB_AGENT_CTX','EMP_NO') AS ctx_emp_no,
       SYS_CONTEXT('CB_AGENT_CTX','DEPT_CODE') AS ctx_dept_code,
       SYS_CONTEXT('CB_AGENT_CTX','CAN_READ_CONTENTS') AS ctx_can_read_contents
FROM   dual;

SELECT doc_id, title, owner_emp_no, dept_code, contents
FROM   admin.cb_v_search_documents
ORDER  BY doc_id;

SELECT COUNT(*) AS rows_visible
FROM   admin.cb_v_search_documents;

SELECT doc_id, title, owner_emp_no, dept_code, contents
FROM   admin.cb_v_search_documents
ORDER  BY doc_id
FETCH FIRST 3 ROWS ONLY;

PROMPT
PROMPT === VPD 4. Authorization: Bearer cb_all_key -> all rows ===
BEGIN
  admin.cb_agent_ctx_pkg.set_user_by_bearer('cb_all_key');
END;
/

SELECT COUNT(*) AS rows_visible
FROM   admin.cb_v_search_documents;

PROMPT
PROMPT === VPD 5. Invalid Bearer key -> ORA-20002 and context cleared ===
BEGIN
  admin.cb_agent_ctx_pkg.set_user_by_bearer('wrong_key');
END;
/

SELECT COUNT(*) AS rows_visible_after_invalid_key
FROM   admin.cb_v_search_documents;

PROMPT
PROMPT === VPD 6. Bypass attempts: direct base/permission tables are not granted ===
SELECT COUNT(*) FROM admin.cb_search_documents;
SELECT COUNT(*) FROM admin.cb_app_user;
SELECT COUNT(*) FROM admin.cb_agent_bearer_key;

EXIT;
