[Oracle]セキュアアプリケーションロール

セキュアアプリケーションロールを使うと、ロール作成時に指定したPL/SQLからのみそのロールを有効化することができるようになる。
また、ロールを有効化する直前に、PL/SQLのコード内でセキュリティ要件を満たしているかのチェックができるメリットがある。

セキュアアプリケーションロールの作成

セキュアアプリケーションロールは、ロール作成時にidentified using を付加することで作成できる。
なお、このタイミングでidentified usingに指定したpl/sqlが存在していなくても問題ない。

SQL> create role sample_role identified using hoge.activation_sample_role;

Role created.

ロールを有効化するためのpl/sqlを作成する

このpl/sqlでは、要件を満たす場合にのみdbms_sessionパッケージを使って、先ほど作ったロールを有効化します。
サンプルとして、接続プログラムを表す値が「SQL*Plus」の場合にロールを有効化します。

create or REPLACE PROCEDURE activation_sample_role
authid CURRENT_USER
AS
BEGIN
  if (SYS_CONTEXT('USERENV','MODULE') = 'SQL*Plus') THEN
    DBMS_SESSION.set_role('sample_role');
  END IF;
END;

ロールの付与

hrスキーマのjobsテーブルへの参照権限をロールに付与し、ロールをhogeユーザに付与する。

SQL> grant select on hr.jobs to sample_role;

Grant succeeded.

SQL> grant sample_role to hoge;

Grant succeeded.

ログインしてロールの確認をする

SQL> sho user
USER is "HOGE"

-- ログイン直後のロールの確認
-- sample_roleは付与されていない
SQL> select * from session_roles;

ROLE
--------------------------------------------------------------------------------
RESOURCE

-- ロールに与えているオブジェクト権限で参照できるテーブルへアクセスしてみるがエラーとなる。
SQL> select* from hr.jobs;
select* from hr.jobs
                *
ERROR at line 1:
ORA-00942: table or view does not exist

-- ロール有効化のPL/SQLを実行
SQL> execute activation_sample_role;

PL/SQL procedure successfully completed.

-- ロールの確認
-- 有効になっていることがわかる
SQL> select * from session_roles;

ROLE
--------------------------------------------------------------------------------
SAMPLE_ROLE

-- ロールに値ている権限で参照できるテーブルも見れるようになる。
SQL> select* from hr.jobs;

JOB_ID    JOB_TITLE          MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President        20080      40000