-- ============================================================
-- 26_agent_ords_security_dynamic_vpd_filter.sql
-- Replaces the demo VPD filter with a whitelist predicate builder.
--
-- The function does not hardcode object names or rule values. It reads:
--   CB_AGENT_CTX.USER_ID -> direct CB_USER_ROLE + group CB_USER_GROUP/CB_GROUP_ROLE
--   -> CB_PERMISSION -> CB_PERMISSION_RULE
-- and builds a row predicate for the object passed by DBMS_RLS.
-- A permission's rules are combined with AND.  Separate ALLOW permissions are
-- combined with OR and DENY permissions are subtracted at the end.  This lets
-- a token-context condition be narrowed by a literal static SQL condition.
-- ============================================================
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET ECHO OFF
SET FEEDBACK ON
SET DEFINE OFF

PROMPT === Creating dynamic whitelist VPD filter ===
CREATE OR REPLACE FUNCTION cb_agent_doc_vpd_filter(
  p_schema IN VARCHAR2,
  p_object IN VARCHAR2
) RETURN VARCHAR2
AUTHID DEFINER
AS
  -- This function is intentionally whitelist-first:
  -- no matched stored row rule means no rows are visible.
  v_user_id    NUMBER;
  v_target     VARCHAR2(128);
  v_allow_predicate VARCHAR2(32767);
  v_deny_predicate  VARCHAR2(32767);
  v_active_permission_id NUMBER;
  v_active_permission_effect VARCHAR2(10);
  v_permission_predicate VARCHAR2(32767);
  v_permission_invalid BOOLEAN := FALSE;

  FUNCTION quote_literal(p_value IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN '''' || REPLACE(NVL(p_value, ''), '''', '''''') || '''';
  END;

  FUNCTION safe_column(
    p_owner       IN VARCHAR2,
    p_table_name  IN VARCHAR2,
    p_column_name IN VARCHAR2
  ) RETURN VARCHAR2 IS
    v_column VARCHAR2(128);
    v_count  NUMBER;
  BEGIN
    IF p_column_name IS NULL THEN
      RETURN NULL;
    END IF;

    v_column := DBMS_ASSERT.SIMPLE_SQL_NAME(UPPER(TRIM(p_column_name)));

    SELECT COUNT(*)
    INTO   v_count
    FROM   all_tab_columns
    WHERE  owner = UPPER(p_owner)
    AND    table_name = UPPER(p_table_name)
    AND    column_name = v_column;

    IF v_count = 0 THEN
      RETURN NULL;
    END IF;

    RETURN v_column;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END;

  FUNCTION safe_static_predicate(
    p_owner       IN VARCHAR2,
    p_table_name  IN VARCHAR2,
    p_predicate   IN VARCHAR2
  ) RETURN VARCHAR2 IS
    v_predicate  VARCHAR2(32767) := TRIM(p_predicate);
    v_lexical    VARCHAR2(32767);
    v_token      VARCHAR2(128);
    v_index      PLS_INTEGER := 1;
    v_count      NUMBER;
    v_has_column BOOLEAN := FALSE;
  BEGIN
    -- STATIC_SQL is intentionally an expression only: no statement
    -- separator/comment/bind/database-link/qualified object is accepted.
    -- Every identifier outside a string literal must be a column of the
    -- protected object or an allowlisted SQL operator/function.
    IF v_predicate IS NULL
       OR LENGTH(v_predicate) > 1000
       OR INSTR(v_predicate, ';') > 0
       OR INSTR(v_predicate, '--') > 0
       OR INSTR(v_predicate, '/*') > 0
       OR INSTR(v_predicate, '*/') > 0
       OR INSTR(v_predicate, ':') > 0
       OR INSTR(v_predicate, '@') > 0
       OR INSTR(v_predicate, '||') > 0
       OR REGEXP_LIKE(v_predicate, '[[:cntrl:]]')
       OR MOD(LENGTH(v_predicate) - LENGTH(REPLACE(v_predicate, '''', '')), 2) <> 0 THEN
      RETURN NULL;
    END IF;

    -- The unconditional predicate is a deliberately narrow exception to the
    -- column-reference rule below. It is used by the explicit KB VPD admin
    -- permission and is rendered by the backoffice as the physical condition
    -- "1 = 1", not as an implicit bypass.
    IF REGEXP_LIKE(v_predicate, '^1[[:space:]]*=[[:space:]]*1$') THEN
      RETURN '1 = 1';
    END IF;

    -- Replace SQL string literals before extracting identifiers.  Doubled
    -- quotes in a literal are supported by the expression below.
    v_lexical := REGEXP_REPLACE(v_predicate, '''(''''|[^''])*''', ' ');
    IF REGEXP_LIKE(v_lexical, '[[:alpha:]_$#][[:alnum:]_$#]*[[:space:]]*\.') THEN
      RETURN NULL;
    END IF;

    LOOP
      v_token := UPPER(REGEXP_SUBSTR(
        v_lexical, '[[:alpha:]_$#][[:alnum:]_$#]*', 1, v_index
      ));
      EXIT WHEN v_token IS NULL;
      v_index := v_index + 1;

      IF v_token IN (
          'AND', 'OR', 'NOT', 'NULL', 'IS', 'IN', 'LIKE', 'BETWEEN', 'ESCAPE',
          'TRUE', 'FALSE', 'UPPER', 'LOWER', 'TRIM', 'NVL', 'COALESCE',
          'TO_CHAR', 'TO_DATE', 'REGEXP_LIKE', 'INSTR', 'LENGTH', 'SUBSTR',
          'REPLACE', 'CAST', 'AS', 'DATE', 'TIMESTAMP', 'NUMBER', 'VARCHAR2', 'CHAR'
      ) THEN
        NULL;
      ELSE
        SELECT COUNT(*)
        INTO   v_count
        FROM   all_tab_columns
        WHERE  owner = UPPER(p_owner)
        AND    table_name = UPPER(p_table_name)
        AND    column_name = v_token;

        IF v_count = 0 THEN
          RETURN NULL;
        END IF;
        v_has_column := TRUE;
      END IF;
    END LOOP;

    RETURN CASE WHEN v_has_column THEN v_predicate ELSE NULL END;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END;

  PROCEDURE append_permission(p_effect IN VARCHAR2, p_clause IN VARCHAR2) IS
  BEGIN
    IF p_clause IS NULL THEN
      RETURN;
    END IF;

    IF p_effect = 'DENY' THEN
      IF v_deny_predicate IS NULL THEN
        v_deny_predicate := p_clause;
      ELSE
        v_deny_predicate := '(' || v_deny_predicate || ') OR (' || p_clause || ')';
      END IF;
    ELSIF v_allow_predicate IS NULL THEN
      v_allow_predicate := p_clause;
    ELSE
      v_allow_predicate := '(' || v_allow_predicate || ') OR (' || p_clause || ')';
    END IF;
  END;

  PROCEDURE append_rule(p_clause IN VARCHAR2) IS
  BEGIN
    IF p_clause IS NULL THEN
      v_permission_invalid := TRUE;
      RETURN;
    END IF;

    IF v_permission_predicate IS NULL THEN
      v_permission_predicate := p_clause;
    ELSE
      v_permission_predicate := '(' || v_permission_predicate || ') AND (' || p_clause || ')';
    END IF;
  END;

  PROCEDURE flush_permission IS
  BEGIN
    IF NOT v_permission_invalid THEN
      append_permission(v_active_permission_effect, v_permission_predicate);
    END IF;
    v_permission_predicate := NULL;
    v_permission_invalid := FALSE;
  END;
BEGIN
  BEGIN
    v_user_id := TO_NUMBER(SYS_CONTEXT('CB_AGENT_CTX', 'USER_ID'));
  EXCEPTION
    WHEN OTHERS THEN
      RETURN '1 = 0';
  END;

  IF v_user_id IS NULL OR p_schema IS NULL OR p_object IS NULL THEN
    RETURN '1 = 0';
  END IF;

  v_target := UPPER(TRIM(p_object));

  FOR r IN (
    WITH effective_role AS (
      SELECT ur.role_id
      FROM   cb_user_role ur
      WHERE  ur.user_id = v_user_id
      UNION
      SELECT gr.role_id
      FROM   cb_user_group ug
      JOIN   cb_app_group g
      ON     g.group_id = ug.group_id
      AND    g.active_yn = 'Y'
      JOIN   cb_group_role gr
      ON     gr.group_id = ug.group_id
      WHERE  ug.user_id = v_user_id
    )
    SELECT p.perm_id,
           UPPER(TRIM(r.rule_type)) AS rule_type,
           UPPER(TRIM(r.rule_column)) AS rule_column,
           NVL(UPPER(TRIM(p.permission_effect)), 'ALLOW') AS permission_effect,
           r.rule_value
    FROM   effective_role er
    JOIN   cb_permission p
    ON     p.role_id = er.role_id
    JOIN   cb_permission_rule r
    ON     r.perm_id = p.perm_id
    WHERE  p.target_name = v_target
    AND    p.action_name = 'SELECT'
    ORDER BY p.perm_id, r.rule_id
  ) LOOP
    IF v_active_permission_id IS NULL THEN
      v_active_permission_id := r.perm_id;
      v_active_permission_effect := r.permission_effect;
    ELSIF v_active_permission_id <> r.perm_id THEN
      flush_permission;
      v_active_permission_id := r.perm_id;
      v_active_permission_effect := r.permission_effect;
    END IF;

    DECLARE
      v_column           VARCHAR2(128);
      v_static_predicate VARCHAR2(32767);
    BEGIN
      IF r.rule_type = 'ALL' THEN
        append_rule('1 = 1');
      ELSIF r.rule_type = 'MY_DEPT' THEN
        -- NULL rule_column keeps backward compatibility with the demo seed.
        v_column := safe_column(p_schema, p_object, NVL(r.rule_column, 'DEPT_CODE'));
        IF v_column IS NOT NULL THEN
          append_rule(v_column || ' = SYS_CONTEXT(''CB_AGENT_CTX'', ''DEPT_CODE'')');
        END IF;
      ELSIF r.rule_type = 'SELF' THEN
        -- NULL rule_column keeps backward compatibility with the demo seed.
        v_column := safe_column(p_schema, p_object, NVL(r.rule_column, 'OWNER_EMP_NO'));
        IF v_column IS NOT NULL THEN
          append_rule(v_column || ' = SYS_CONTEXT(''CB_AGENT_CTX'', ''EMP_NO'')');
        END IF;
      ELSIF r.rule_type = 'DEPT' THEN
        v_column := safe_column(p_schema, p_object, NVL(r.rule_column, 'DEPT_CODE'));
        IF v_column IS NOT NULL THEN
          append_rule(v_column || ' = ' || quote_literal(r.rule_value));
        END IF;
      ELSIF r.rule_type = 'EMP_NO' THEN
        v_column := safe_column(p_schema, p_object, NVL(r.rule_column, 'OWNER_EMP_NO'));
        IF v_column IS NOT NULL THEN
          append_rule(v_column || ' = ' || quote_literal(r.rule_value));
        END IF;
      ELSIF r.rule_type = 'TAG' THEN
        -- A TAG rule defaults to the ingestion metadata column TECH_TAG.
        v_column := safe_column(p_schema, p_object, NVL(r.rule_column, 'TECH_TAG'));
        IF v_column IS NOT NULL
           AND r.rule_value IS NOT NULL
           AND REGEXP_LIKE(TRIM(r.rule_value), '^[A-Za-z0-9_-]+$') THEN
          append_rule(
            'REGEXP_LIKE(UPPER(' || v_column || '), ' ||
            quote_literal('(^|,)' || UPPER(TRIM(r.rule_value)) || '(,|$)') || ')'
          );
        END IF;
      ELSIF r.rule_type IN (
          'TOKEN_SUBJECT', 'OWN_CONTRACT', 'CHANNEL_CONTRACT', 'OWN_CUSTOMER', 'CHANNEL_CUSTOMER'
      ) THEN
        -- 백오피스 권한 설정의 조건 코드를 안전한 WHERE 조각으로 확장한다.
        -- token -> stakeholder 해석은 context package가 한 번만 수행하며,
        -- VPD filter는 context와 대상/계약원장만 쓴다.
        v_column := safe_column(p_schema, p_object, r.rule_column);
        IF v_column IS NOT NULL
           AND UPPER(p_schema) = 'POC_2' THEN
          IF r.rule_type = 'TOKEN_SUBJECT'
             AND v_target = 'KB_STAKEHOLDERS'
             AND v_column = 'USER_ID' THEN
            append_rule(
              'USER_ID = SYS_CONTEXT(''CB_AGENT_CTX'', ''STAKEHOLDER_USER_ID'')'
            );
          ELSIF r.rule_type = 'OWN_CONTRACT'
                AND v_target = 'KB_CONTRACTS'
                AND v_column = 'FC_ID' THEN
            append_rule(
              'FC_ID = SYS_CONTEXT(''CB_AGENT_CTX'', ''STAKEHOLDER_USER_ID'')'
            );
          ELSIF r.rule_type = 'CHANNEL_CONTRACT'
                AND v_target = 'KB_CONTRACTS'
                AND v_column = 'FC_CHANNEL' THEN
            append_rule(
              'FC_CHANNEL = SYS_CONTEXT(''CB_AGENT_CTX'', ''STAKEHOLDER_CHANNEL'')'
            );
          ELSIF r.rule_type IN ('OWN_CUSTOMER', 'CHANNEL_CUSTOMER')
                AND v_target IN ('KB_CUSTOMERS', 'KB_COVERAGES', 'KB_CLAIMS', 'KB_EXTERNAL_HOLDINGS')
                AND v_column IN ('CUST_ID', 'CONTRACT_NO') THEN
            DECLARE
              v_contract_key VARCHAR2(30);
              v_subject_filter VARCHAR2(500);
            BEGIN
              v_contract_key := CASE v_column
                WHEN 'CUST_ID' THEN 'CUST_ID'
                WHEN 'CONTRACT_NO' THEN 'CONTRACT_NO'
              END;
              v_subject_filter := CASE r.rule_type
                WHEN 'OWN_CUSTOMER' THEN
                  'c.FC_ID = SYS_CONTEXT(''CB_AGENT_CTX'', ''STAKEHOLDER_USER_ID'')'
                ELSE
                  'c.FC_CHANNEL = SYS_CONTEXT(''CB_AGENT_CTX'', ''STAKEHOLDER_CHANNEL'')'
              END;
              append_rule(
                'EXISTS (SELECT 1 FROM (SELECT c.' || v_contract_key || ' AS access_key ' ||
                'FROM POC_2.KB_CONTRACTS c WHERE ' || v_subject_filter ||
                ') allowed_contract WHERE allowed_contract.access_key = ' || v_column || ')'
              );
            END;
          END IF;
        END IF;
      ELSIF r.rule_type = 'STATIC_SQL' THEN
        v_static_predicate := safe_static_predicate(p_schema, p_object, r.rule_value);
        append_rule(v_static_predicate);
      ELSIF r.rule_type = '=' THEN
        v_column := safe_column(p_schema, p_object, r.rule_column);
        IF v_column IS NOT NULL THEN
          append_rule('TO_CHAR(' || v_column || ') = ' || quote_literal(r.rule_value));
        END IF;
      ELSIF r.rule_type IN ('!=', '<>') THEN
        v_column := safe_column(p_schema, p_object, r.rule_column);
        IF v_column IS NOT NULL THEN
          append_rule('TO_CHAR(' || v_column || ') <> ' || quote_literal(r.rule_value));
        END IF;
      ELSE
        append_rule(NULL);
      END IF;
    END;
  END LOOP;

  flush_permission;

  IF v_allow_predicate IS NULL THEN
    RETURN '1 = 0';
  END IF;

  IF v_deny_predicate IS NULL THEN
    IF v_allow_predicate = '1 = 1' THEN
      RETURN '1 = 1';
    END IF;
    RETURN '(' || v_allow_predicate || ')';
  END IF;

  RETURN '((' || v_allow_predicate || ') AND NOT (' || v_deny_predicate || '))';
END;
/

SHOW ERRORS FUNCTION cb_agent_doc_vpd_filter

-- ORDS SQL tracing calls the same policy function after set_vpd_context so
-- the response can expose the token-specific VPD predicate.  This is
-- diagnostic metadata only; the SELECT remains protected by DBMS_RLS.
GRANT EXECUTE ON cb_agent_doc_vpd_filter TO cb_ords;

PROMPT === Dynamic whitelist VPD filter ready ===
