【PL/SQL】マルチスキーマ環境での権限管理とInvoker Rights設計

-- 例示用。実運用ではパスワード、表領域、プロファイルを別途設計する。
CREATE USER data IDENTIFIED BY "******" QUOTA UNLIMITED ON users;
CREATE USER app IDENTIFIED BY "******" QUOTA UNLIMITED ON users;
CREATE USER app_user IDENTIFIED BY "******";

GRANT CREATE SESSION TO data, app, app_user;
GRANT CREATE TABLE TO data;
GRANT CREATE PROCEDURE, CREATE SYNONYM TO app;

-- DATAスキーマ
CREATE TABLE data.orders (
  order_id    NUMBER PRIMARY KEY,
  customer_id NUMBER NOT NULL,
  amount      NUMBER(12,2) NOT NULL,
  created_at  TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);

-- Definer RightsのAPPパッケージが使う権限は直接GRANTする
GRANT SELECT, INSERT, UPDATE ON data.orders TO app;

-- API実行権限はAPPから接続ユーザーへ付与する
-- 実体パッケージ作成後に実行する
-- GRANT EXECUTE ON app.pkg_order_write TO app_user;

CREATE OR REPLACE PACKAGE app.pkg_order_write AUTHID DEFINER AS
  PROCEDURE upsert_order(
    p_order_id    IN NUMBER,
    p_customer_id IN NUMBER,
    p_amount      IN NUMBER
  );
END pkg_order_write;
/

CREATE OR REPLACE PACKAGE BODY app.pkg_order_write AS
  PROCEDURE upsert_order(
    p_order_id    IN NUMBER,
    p_customer_id IN NUMBER,
    p_amount      IN NUMBER
  ) IS
  BEGIN
    MERGE INTO data.orders d
    USING (
      SELECT p_order_id AS order_id,
             p_customer_id AS customer_id,
             p_amount AS 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
    WHEN NOT MATCHED THEN
      INSERT (order_id, customer_id, amount)
      VALUES (s.order_id, s.customer_id, s.amount);
  END;
END pkg_order_write;
/

GRANT EXECUTE ON app.pkg_order_write TO app_user;

CREATE OR REPLACE PACKAGE app.pkg_order_read AUTHID CURRENT_USER AS
  FUNCTION get_amount(p_order_id IN NUMBER) RETURN NUMBER;
END pkg_order_read;
/

CREATE OR REPLACE PACKAGE BODY app.pkg_order_read AS
  FUNCTION get_amount(p_order_id IN NUMBER) RETURN NUMBER IS
    v_amount NUMBER;
  BEGIN
    -- Invoker Rightsでは名前解決が呼び出し元に寄るため、完全修飾を基本にする。
    SELECT amount
    INTO v_amount
    FROM data.orders
    WHERE order_id = p_order_id;

    RETURN v_amount;
  END;
END pkg_order_read;
/

GRANT EXECUTE ON app.pkg_order_read TO app_user;

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

-- より広い権限。安易に付けず、管理者ロールや運用方針に合わせて判断する。
-- GRANT INHERIT ANY PRIVILEGES TO app;

-- 現在の継承権限を確認
SELECT grantee, owner, table_name, privilege
FROM dba_tab_privs
WHERE privilege = 'INHERIT PRIVILEGES'
  AND grantee = 'APP'
ORDER BY owner, table_name;

-- 参照ロールを作り、APP_USERにだけ付与する例
CREATE ROLE r_read_orders;
GRANT SELECT ON data.orders TO r_read_orders;
GRANT r_read_orders TO app_user;

-- APP_USERで実行できることを確認
CONNECT app_user/******
SELECT app.pkg_order_read.get_amount(1001) FROM dual;

-- ロールを無効化して差分を見る
SET ROLE NONE;
SELECT app.pkg_order_read.get_amount(1001) FROM dual;

-- Definer Rightsの更新APIは、APP_USERがDATA.ORDERSを直接更新できなくても実行可能
EXEC app.pkg_order_write.upsert_order(1001, 10, 500);

CREATE OR REPLACE PACKAGE app.pkg_safe_exec AUTHID DEFINER AS
  PROCEDURE drop_temp_idx(p_table_name IN VARCHAR2);
END pkg_safe_exec;
/

CREATE OR REPLACE PACKAGE BODY app.pkg_safe_exec AS
  PROCEDURE drop_temp_idx(p_table_name IN VARCHAR2) IS
    v_index_name VARCHAR2(261);
  BEGIN
    -- 動的DDLはホワイトリストで対象を限定する
    IF UPPER(p_table_name) NOT IN ('ORDERS') THEN
      RAISE_APPLICATION_ERROR(-20000, 'not allowed target');
    END IF;

    v_index_name := 'DATA.' || UPPER(p_table_name) || '_TMP_IDX';

    EXECUTE IMMEDIATE
      'DROP INDEX ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(v_index_name);
  END;
END pkg_safe_exec;
/

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'ORDER_API',
    action_name => 'READ'
  );

  DBMS_SESSION.SET_IDENTIFIER('CID-20260606-0001');

  -- APP_USERからAPPのAPIを呼ぶ
  :amount := app.pkg_order_read.get_amount(1001);
END;
/

-- APPがDATAへ直接持つ権限
SELECT owner, table_name, privilege
FROM all_tab_privs
WHERE grantee = 'APP'
  AND owner = 'DATA'
ORDER BY table_name, privilege;

-- APP_USERが実行できるAPP API
SELECT owner, table_name AS object_name, privilege
FROM all_tab_privs
WHERE grantee = 'APP_USER'
  AND owner = 'APP'
  AND privilege = 'EXECUTE'
ORDER BY object_name;

-- APPスキーマのINVALID
SELECT object_type, object_name, status
FROM all_objects
WHERE owner = 'APP'
  AND status = 'INVALID'
ORDER BY object_type, object_name;

-- APPからDATAへの依存
SELECT owner, name, type, referenced_owner, referenced_name, referenced_type
FROM all_dependencies
WHERE owner = 'APP'
  AND referenced_owner = 'DATA'
ORDER BY name, referenced_type, referenced_name;