[第17回] SELECT ANY TABLEシステム権限を使わないために

※ 本記事は2017年1月26日に公開されたものです。

最小権限の原則を実現するためには、全てのユーザーの表にアクセス可能になるSELECT ANY TABLE権限などのANYシステム権限の付与に注意が必要なこは何回か紹介してきました。

しかし、特定のデータベーススキーマのデータを管理するために、スキーマ内の全ての表に対して権限を与えたいこともあります。それが複数のスキーマだったり、アプリ改修で表が増えたりすると、権限の付与が大変で、結局利便性のためにSELECT ANY TABLE権限を利用してしまうケースもあるかもしれません。

では、大量の表に対して一度に権限を付与することはできないでしょうか？
方法のひとつとして以下のようなPL/SQLプログラムの利用が考えられます。
ここでは、PUKUというスキーマの全ての表のSELECT権限をPUKU_ROというロールに付与するSQL文を自動生成する例です。
(注：このSQL/SQLプログラムはデータベース管理者で実行することを想定しています。行が画面からはみ出してしまっている場合にはお手数ですが、テキストエディタなどにコピペしてください。)

set serveroutput on
declare
  objectOwner varchar2(64) := 'PUKU';
  grantee varchar2(64) := 'PUKU_RO';
begin
  for cur in ( select table_name from dba_tables where owner=objectOwner ) loop
    dbms_output.put_line('grant select on ' || objectOwner || '.' || cur.table_name || ' to ' || grantee || ';');
  end loop;
end;
/

SQL> set serveroutput on
SQL> declare
  2    objectOwner varchar2(64) := 'PUKU';
  3    grantee varchar2(64) := 'PUKU_RO';
  4  begin
  5    for cur in ( select table_name from dba_tables where owner=objectOwner ) loop
  6      dbms_output.put_line('grant select on ' || objectOwner || '.' || cur.table_name || ' to ' || grantee || ';');
  7    end loop;
  8  end;
  9  /
grant select on PUKU.TEST to PUKU_RO;
grant select on PUKU.CUSTOMER to PUKU_RO;
grant select on PUKU.ORDER to PUKU_RO;
grant select on PUKU.M_BOM to PUKU_RO;
grant select on PUKU.M_ZIPCODE to PUKU_RO;
grant select on PUKU.M_COMPANYCODE to PUKU_RO;
PL/SQLプロシージャが正常に完了しました。 

declare
  objectOwner varchar2(64) := 'PUKU';
  grantee varchar2(64) := 'PUKU_RO';
begin
  for cur in ( select table_name from dba_tables where owner=objectOwner ) loop
    execute immediate 'grant select on ' || objectOwner || '.' || cur.table_name || ' to ' || grantee;
  end loop;
end;
/ 

set serveroutput on
declare
  grantee varchar2(64) := 'PUKU_RO';
begin
  for cur in ( select table_name from user_tables where table_name like 'M\_%' ) loop
    dbms_output.put_line('grant select on ' || cur.table_name || ' to ' || grantee || ';');
  end loop;
end;
/

SQL> show user
ユーザーは"PUKU"です。
SQL> set serveroutput on
SQL> declare
  2    grantee varchar2(64) := 'PUKU_RO';
  3  begin
  4    for cur in ( select table_name from user_tables where table_name like 'M\_%' ) loop
  5      dbms_output.put_line('grant select on ' || cur.table_name || ' to ' || grantee || ';');
  6    end loop;
  7  end;
  8  /
grant select on M_BOM to PUKU_RO;
grant select on M_ZIPCODE to PUKU_RO;
grant select on M_COMPANYCODE to PUKU_RO;
PL/SQLプロシージャが正常に完了しました。