-- ============================================================
-- 67_drop_legacy_kb_contracts_premium_vpd_cls_policy.sql
--
-- 목적:
--   과거 VPD Column-Level Security(CLS) PoC에서 생성된
--   POC_2.KB_CONTRACTS.PREMIUM 대상 VPD 컬럼 정책을 제거한다.
--
-- 배경:
--   현재 운영 설계는 다음처럼 역할을 분리한다.
--
--   * VPD/DBMS_RLS : 행 수준 필터링만 담당
--   * ASO/Data Redaction : 컬럼 마스킹만 담당
--
--   따라서 DBMS_RLS.ADD_POLICY의 sec_relevant_cols 기반 컬럼 제어 정책은
--   운영 정책에서 제거한다. 이 스크립트는 named policy 하나만 삭제하며,
--   ASO/Data Redaction 정책은 건드리지 않는다.
--
-- 삭제 대상:
--   object_schema : POC_2
--   object_name   : KB_CONTRACTS
--   policy_name   : KB_PREMIUM_CLS_POLICY
--   column        : PREMIUM
--
-- 실행 권한:
--   ADMIN 또는 DBMS_RLS.DROP_POLICY 수행 권한이 있는 계정.
-- ============================================================
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET DEFINE OFF
SET FEEDBACK ON

PROMPT === Before: legacy VPD CLS policy ===
COLUMN object_owner FORMAT A18
COLUMN object_name FORMAT A32
COLUMN policy_name FORMAT A36
COLUMN function FORMAT A40
COLUMN enable FORMAT A10
COLUMN policy_type FORMAT A20

SELECT object_owner,
       object_name,
       policy_name,
       pf_owner,
       package,
       function,
       enable,
       policy_type,
       sel
FROM   dba_policies
WHERE  object_owner = 'POC_2'
AND    object_name = 'KB_CONTRACTS'
AND    UPPER(policy_name) = 'KB_PREMIUM_CLS_POLICY';

PROMPT === Before: security relevant columns ===
COLUMN sec_rel_column FORMAT A32

SELECT object_owner,
       object_name,
       policy_name,
       sec_rel_column
FROM   dba_sec_relevant_cols
WHERE  object_owner = 'POC_2'
AND    object_name = 'KB_CONTRACTS'
AND    UPPER(policy_name) = 'KB_PREMIUM_CLS_POLICY';

PROMPT === Dropping only the legacy VPD CLS policy ===
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO   v_count
  FROM   dba_policies
  WHERE  object_owner = 'POC_2'
  AND    object_name = 'KB_CONTRACTS'
  AND    UPPER(policy_name) = 'KB_PREMIUM_CLS_POLICY';

  IF v_count > 0 THEN
    DBMS_RLS.DROP_POLICY(
      object_schema => 'POC_2',
      object_name   => 'KB_CONTRACTS',
      policy_name   => 'KB_PREMIUM_CLS_POLICY'
    );
  END IF;
END;
/

PROMPT === After: legacy VPD CLS policy ===
SELECT object_owner,
       object_name,
       policy_name,
       pf_owner,
       package,
       function,
       enable,
       policy_type,
       sel
FROM   dba_policies
WHERE  object_owner = 'POC_2'
AND    object_name = 'KB_CONTRACTS'
AND    UPPER(policy_name) = 'KB_PREMIUM_CLS_POLICY';

PROMPT === After: security relevant columns ===
SELECT object_owner,
       object_name,
       policy_name,
       sec_rel_column
FROM   dba_sec_relevant_cols
WHERE  object_owner = 'POC_2'
AND    object_name = 'KB_CONTRACTS'
AND    UPPER(policy_name) = 'KB_PREMIUM_CLS_POLICY';

PROMPT === ASO/Data Redaction on PREMIUM is intentionally not changed ===
COLUMN column_name FORMAT A24
COLUMN function_type FORMAT A28

SELECT object_owner,
       object_name,
       column_name,
       function_type,
       function_parameters
FROM   redaction_columns
WHERE  object_owner = 'POC_2'
AND    object_name = 'KB_CONTRACTS'
AND    UPPER(column_name) = 'PREMIUM';

PROMPT === Legacy KB_CONTRACTS premium VPD CLS cleanup complete ===
