【PL/SQL】AUTHIDと権限管理の設計｜DEFINER・CURRENT_USER・直接GRANTの実務チェック

-- TABLE_OWNER が表を所有している想定
CREATE TABLE table_owner.orders (
  order_id     NUMBER PRIMARY KEY,
  customer_id  NUMBER NOT NULL,
  amount       NUMBER NOT NULL,
  updated_at   DATE DEFAULT SYSDATE
);

-- API_OWNER に必要最小限の直接権限を付与
GRANT SELECT, INSERT, UPDATE ON table_owner.orders TO api_owner;

-- API_OWNER 側で業務APIを作成
CREATE OR REPLACE PACKAGE api_owner.order_api AUTHID DEFINER AS
  PROCEDURE upsert_order(
    p_order_id    IN NUMBER,
    p_customer_id IN NUMBER,
    p_amount      IN NUMBER
  );
END order_api;
/

CREATE OR REPLACE PACKAGE BODY api_owner.order_api AS
  PROCEDURE upsert_order(
    p_order_id    IN NUMBER,
    p_customer_id IN NUMBER,
    p_amount      IN NUMBER
  ) IS
  BEGIN
    MERGE INTO table_owner.orders d
    USING (
      SELECT p_order_id order_id,
             p_customer_id customer_id,
             p_amount amount
      FROM dual
    ) s
    ON (d.order_id = s.order_id)
    WHEN MATCHED THEN
      UPDATE SET d.customer_id = s.customer_id,
                 d.amount      = s.amount,
                 d.updated_at  = SYSDATE
    WHEN NOT MATCHED THEN
      INSERT (order_id, customer_id, amount)
      VALUES (s.order_id, s.customer_id, s.amount);
  END upsert_order;
END order_api;
/

-- APP_USER には表権限ではなくAPI実行権限だけを付与
GRANT EXECUTE ON api_owner.order_api TO app_user;

-- NG: ロール経由だけではPL/SQL内で不足することがある
GRANT SELECT ON table_owner.orders TO app_read_role;
GRANT app_read_role TO api_owner;

-- OK: PL/SQL所有者へ必要なオブジェクト権限を直接付与
GRANT SELECT, INSERT, UPDATE ON table_owner.orders TO api_owner;

-- 呼び出し側へはAPIのEXECUTEを直接付与
GRANT EXECUTE ON api_owner.order_api TO app_user;

CREATE OR REPLACE PACKAGE report_util AUTHID CURRENT_USER AS
  FUNCTION total_amount(p_from IN DATE, p_to IN DATE) RETURN NUMBER;
END report_util;
/

CREATE OR REPLACE PACKAGE BODY report_util AS
  FUNCTION total_amount(p_from IN DATE, p_to IN DATE) RETURN NUMBER IS
    v_total NUMBER;
  BEGIN
    SELECT NVL(SUM(amount), 0)
    INTO v_total
    FROM orders
    WHERE updated_at >= p_from
      AND updated_at <  p_to;

    RETURN v_total;
  END total_amount;
END report_util;
/

-- APP_USER の権限を UTIL_OWNER が継承できるようにする例
GRANT INHERIT PRIVILEGES ON USER app_user TO util_owner;

-- より広い権限。付与先は慎重に限定する
GRANT INHERIT ANY PRIVILEGES TO util_owner;

CREATE OR REPLACE PROCEDURE purge_audit_log(
  p_table_key IN VARCHAR2,
  p_days      IN NUMBER
) AUTHID DEFINER
IS
  v_table_name VARCHAR2(30);
BEGIN
  v_table_name :=
    CASE p_table_key
      WHEN 'LOGIN' THEN 'LOGIN_AUDIT_LOG'
      WHEN 'API'   THEN 'API_AUDIT_LOG'
      ELSE RAISE_APPLICATION_ERROR(-20010, 'Invalid table key')
    END;

  EXECUTE IMMEDIATE
    'DELETE FROM audit_owner.' || v_table_name ||
    ' WHERE created_at < SYSDATE - :days'
    USING p_days;
END;
/

-- TABLE_OWNER -> API_OWNER
GRANT SELECT, INSERT, UPDATE, DELETE ON table_owner.orders TO api_owner;

-- API_OWNER -> APP_USER / BATCH_USER
GRANT EXECUTE ON api_owner.order_api TO app_user;
GRANT EXECUTE ON api_owner.order_admin_api TO batch_user;

-- APP_USER に表権限を直接渡さない
-- GRANT SELECT ON table_owner.orders TO app_user;  -- 原則NG

CREATE OR REPLACE PACKAGE BODY api_owner.order_admin_api AS
  PROCEDURE assert_allowed IS
  BEGIN
    IF SYS_CONTEXT('USERENV', 'SESSION_USER') NOT IN ('BATCH_USER', 'OPS_USER') THEN
      RAISE_APPLICATION_ERROR(-20020, 'not allowed');
    END IF;

    IF SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') IS NULL THEN
      RAISE_APPLICATION_ERROR(-20021, 'client identifier is required');
    END IF;
  END;

  PROCEDURE close_month(p_yyyymm IN CHAR) IS
  BEGIN
    assert_allowed;
    -- monthly close logic
    NULL;
  END;
END;
/

CREATE OR REPLACE PACKAGE api_owner.order_internal
  AUTHID DEFINER
  ACCESSIBLE BY (api_owner.order_api)
AS
  PROCEDURE validate_amount(p_amount IN NUMBER);
END order_internal;
/

-- 自スキーマが持つオブジェクト権限
SELECT owner, table_name, privilege, grantor
FROM user_tab_privs
ORDER BY owner, table_name, privilege;

-- 自スキーマのオブジェクトへ付与している権限
SELECT grantee, table_name, privilege
FROM user_tab_privs_made
ORDER BY grantee, table_name, privilege;

-- PL/SQLユニットのAUTHID確認
SELECT object_name, procedure_name, authid
FROM user_procedures
WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE')
ORDER BY object_name, procedure_name;

-- 依存オブジェクト確認
SELECT name, type, referenced_owner, referenced_name, referenced_type
FROM user_dependencies
WHERE type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY')
ORDER BY name, referenced_owner, referenced_name;