【PL/SQL】権限分離とセキュリティパッケージ設計｜Invoker Rights・直接GRANT・RLS

-- DATAスキーマ: 実データを保持
CREATE TABLE data.orders (
  order_id    NUMBER PRIMARY KEY,
  tenant_id   VARCHAR2(30) NOT NULL,
  cust_id     NUMBER NOT NULL,
  amount      NUMBER(12,2) NOT NULL,
  status      VARCHAR2(20) NOT NULL,
  created_at  TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- APIスキーマへ必要最小限のオブジェクト権限を直接付与
GRANT SELECT, INSERT, UPDATE ON data.orders TO api;

-- アプリユーザーにはAPIだけ実行させる
GRANT EXECUTE ON api.order_api TO app_user;

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

CREATE OR REPLACE PACKAGE BODY api.report_api AS
  FUNCTION total_amount(
    p_from IN DATE,
    p_to   IN DATE
  ) RETURN NUMBER IS
    v_total NUMBER;
  BEGIN
    SELECT SUM(amount)
      INTO v_total
      FROM data.orders
     WHERE created_at >= p_from
       AND created_at <  p_to;

    RETURN NVL(v_total, 0);
  END;
END;
/

CREATE OR REPLACE PACKAGE api.order_api
  AUTHID DEFINER
AS
  PROCEDURE approve_order(
    p_order_id IN NUMBER,
    p_tenant_id IN VARCHAR2
  );
END;
/

CREATE OR REPLACE PACKAGE BODY api.order_api AS
  PROCEDURE assert_caller IS
  BEGIN
    IF SYS_CONTEXT('USERENV', 'SESSION_USER') NOT IN ('APP_USER', 'BATCH_USER') THEN
      RAISE_APPLICATION_ERROR(-20000, 'not allowed');
    END IF;
  END;

  PROCEDURE approve_order(
    p_order_id IN NUMBER,
    p_tenant_id IN VARCHAR2
  ) IS
  BEGIN
    assert_caller;

    UPDATE data.orders
       SET status = 'APPROVED'
     WHERE order_id = p_order_id
       AND tenant_id = p_tenant_id
       AND status = 'WAITING';

    IF SQL%ROWCOUNT = 0 THEN
      RAISE_APPLICATION_ERROR(-20001, 'order not found or not allowed');
    END IF;
  END;
END;
/

-- 自分に直接付与されたオブジェクト権限
SELECT owner,
       table_name,
       privilege,
       grantor
  FROM user_tab_privs
 ORDER BY owner, table_name, privilege;

-- APIスキーマに付与されている権限を確認
SELECT owner,
       table_name,
       privilege,
       grantee
  FROM all_tab_privs
 WHERE grantee = 'API'
 ORDER BY owner, table_name, privilege;

-- apiユーザーがapp_userの権限を継承してInvoker Rightsを実行できるようにする例
GRANT INHERIT PRIVILEGES ON USER app_user TO api;

-- 逆に不用意な継承を避けたい場合は取り消す
REVOKE INHERIT PRIVILEGES ON USER app_user FROM api;

CREATE CONTEXT app_ctx USING api.ctx_pkg;

CREATE OR REPLACE PACKAGE api.ctx_pkg
  AUTHID DEFINER
AS
  PROCEDURE set_tenant(p_tenant_id IN VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY api.ctx_pkg AS
  PROCEDURE set_tenant(p_tenant_id IN VARCHAR2) IS
  BEGIN
    DBMS_SESSION.SET_CONTEXT(
      namespace => 'APP_CTX',
      attribute => 'TENANT_ID',
      value     => p_tenant_id
    );
  END;
END;
/

SELECT SYS_CONTEXT('APP_CTX', 'TENANT_ID') AS tenant_id
  FROM dual;

CREATE OR REPLACE FUNCTION api.order_policy(
  p_schema IN VARCHAR2,
  p_object IN VARCHAR2
) RETURN VARCHAR2
AS
BEGIN
  RETURN 'tenant_id = SYS_CONTEXT(''APP_CTX'', ''TENANT_ID'')';
END;
/

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => 'DATA',
    object_name     => 'ORDERS',
    policy_name     => 'ORDERS_TENANT_POLICY',
    function_schema => 'API',
    policy_function => 'ORDER_POLICY',
    statement_types => 'SELECT,UPDATE,DELETE',
    update_check    => TRUE
  );
END;
/

CREATE OR REPLACE FUNCTION api.count_by_status(
  p_table_name IN VARCHAR2,
  p_status     IN VARCHAR2
) RETURN NUMBER
  AUTHID DEFINER
AS
  v_sql   VARCHAR2(1000);
  v_count NUMBER;
BEGIN
  IF p_table_name NOT IN ('DATA.ORDERS', 'DATA.ORDER_ARCHIVE') THEN
    RAISE_APPLICATION_ERROR(-20010, 'invalid table');
  END IF;

  v_sql := 'SELECT COUNT(*) FROM ' || p_table_name ||
           ' WHERE status = :status';

  EXECUTE IMMEDIATE v_sql INTO v_count USING p_status;
  RETURN v_count;
END;
/

CREATE OR REPLACE PACKAGE api.audit_pkg
  AUTHID DEFINER
AS
  PROCEDURE write_log(
    p_action IN VARCHAR2,
    p_key    IN VARCHAR2,
    p_result IN VARCHAR2
  );
END;
/

CREATE OR REPLACE PACKAGE BODY api.audit_pkg AS
  PROCEDURE write_log(
    p_action IN VARCHAR2,
    p_key    IN VARCHAR2,
    p_result IN VARCHAR2
  ) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO api.audit_log(
      action_name,
      key_value,
      result_code,
      session_user,
      current_user,
      client_identifier,
      logged_at
    ) VALUES (
      p_action,
      p_key,
      p_result,
      SYS_CONTEXT('USERENV', 'SESSION_USER'),
      SYS_CONTEXT('USERENV', 'CURRENT_USER'),
      SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER'),
      SYSTIMESTAMP
    );

    COMMIT;
  END;
END;
/

-- app_userでは表を直接読めないこと
CONNECT app_user/password
SELECT COUNT(*) FROM data.orders;
-- ORA-00942 または権限不足になることを確認

-- API経由なら許可範囲だけ処理できること
BEGIN
  api.order_api.approve_order(
    p_order_id  => 1001,
    p_tenant_id => 'TENANT_A'
  );
END;
/

-- 別テナントは拒否されること
BEGIN
  api.order_api.approve_order(
    p_order_id  => 2001,
    p_tenant_id => 'TENANT_B'
  );
END;
/