-- ============================================================
-- 20_agent_ords_security_local_dds_test.sql
-- Run as cb_dds_hr / cb_dds_fin / cb_dds_all / cb_dds_none.
--
-- Expected:
--   cb_dds_hr    -> 3 rows from admin.cb_dds_v_search_documents
--   cb_dds_fin   -> 2 rows from admin.cb_dds_v_search_documents
--   cb_dds_all   -> 6 rows from admin.cb_dds_v_search_documents
--   contents      -> NULL for hr/fin grants, visible for all grant
--   cb_dds_none  -> ORA-00942 on admin.cb_dds_v_search_documents
--   base table   -> ORA-00942 for every DDS end user
-- ============================================================
WHENEVER SQLERROR CONTINUE
SET ECHO OFF
SET FEEDBACK ON
SET LINESIZE 220
SET PAGESIZE 100
COLUMN end_user_name FORMAT A16
COLUMN title FORMAT A28
COLUMN owner_emp_no FORMAT A12
COLUMN dept_code FORMAT A10
COLUMN contents FORMAT A35

PROMPT
PROMPT === DDS 1. End user context ===
SELECT ORA_END_USER_CONTEXT.username AS end_user_name
FROM   dual;

PROMPT
PROMPT === DDS 2. Protected view row count ===
SELECT COUNT(*) AS rows_visible
FROM   admin.cb_dds_v_search_documents;

PROMPT
PROMPT === DDS 3. Protected view sample ===
SELECT doc_id, title, owner_emp_no, dept_code, contents
FROM   admin.cb_dds_v_search_documents
ORDER  BY doc_id;

PROMPT
PROMPT === DDS 4. Bypass attempts: no DATA GRANT on base/VPD objects ===
SELECT COUNT(*) FROM admin.cb_dds_documents;
SELECT COUNT(*) FROM admin.cb_search_documents;
SELECT COUNT(*) FROM admin.cb_v_search_documents;

EXIT;
