この記事では特定スキーマのオブジェクト権限を一括で付与する方法について解説します。
オブジェクト権限の付与方法
オブジェクト権限の付与は以下のようなSQLで実施します。
GRANT <オブジェクト権限名> ON <オブジェクト名> TO <ユーザ名>;オブジェクト権限はこのようにオブジェクトごとに権限を付与する必要があります。
そのため、大量のオブジェクト権限をまとめて付与したい場合は手間がかかります。
SELECT ANY TABLEなどのシステム権限を付与することもできますが、この権限ではすべてのスキーマの表に対して権限が付与されてしまいます。
特定のスキーマのオブジェクト権限を一括付与する方法
特定のスキーマオブジェクトの権限のみを一括で付与するにはPL/SQLを使用して行うことができます。
SQL*PLusで下記のPL/SQLを実行すると特定のスキーマオブジェクト(表、ビュー)のSELECT権限の付与を一括で行うことができます。
SET SERVEROUTPUT ON
DECLARE
object_owner VARCHAR2(30) := '&owner_name';
target_user VARCHAR2(30) := '&user_name';
w_sql VARCHAR2(1024);
BEGIN
FOR c_tab IN (SELECT table_name FROM all_tables WHERE owner = object_owner UNION ALL
SELECT view_name FROM all_views WHERE owner = object_owner) LOOP
w_sql := 'GRANT SELECT ON ' || object_owner || '.' || c_tab.table_name || ' TO ' || target_user;
DBMS_OUTPUT.PUT_LINE( w_sql );
EXECUTE IMMEDIATE w_sql;
END LOOP;
END;
/使用方法
今回は「TEST」スキーマのすべてのオブジェクトのSELECT権限を「RDTEST」ユーザに対して行います
SQL*PlusでこのPL/SQLを実行すると「owner_nameに値を入力してください:」と表示されます。
オブジェクトのオーナーである「TEST」入力し、ENTERを押します。
ユーザ名はすべて大文字で入力する必要があります。
続いて同様に「user_nameに値を入力してください:」と表示されるので権限を付与するユーザである「RDTEST」と入力しENTERを押します。
するとTESTスキーマのすべての表、ビューに対して自動で権限を付与するSQLが生成され実行されます。
実行例は下記のとおりです。
SQL> DECLARE
2 object_owner VARCHAR2(30) := '&owner_name';
3 target_user VARCHAR2(30) := '&user_name';
4 w_sql VARCHAR2(1024);
5
6 BEGIN
7 FOR c_tab IN (SELECT table_name FROM all_tables WHERE owner = object_owner UNION ALL
8 SELECT view_name FROM all_views WHERE owner = object_owner) LOOP
9 w_sql := 'GRANT SELECT ON ' || object_owner || '.' || c_tab.table_name || ' TO ' || target_user;
10 DBMS_OUTPUT.PUT_LINE( w_sql );
11 EXECUTE IMMEDIATE w_sql;
12 END LOOP;
13 END;
14 /
owner_nameに値を入力してください: TEST
旧 2: object_owner VARCHAR2(30) := '&owner_name';
新 2: object_owner VARCHAR2(30) := 'TEST';
user_nameに値を入力してください: RDTEST
旧 3: target_user VARCHAR2(30) := '&user_name';
新 3: target_user VARCHAR2(30) := 'RDTEST';
GRANT SELECT ON TEST.SALES TO RDTEST
GRANT SELECT ON TEST.CARD TO RDTEST
GRANT SELECT ON TEST.EMP1 TO RDTEST
GRANT SELECT ON TEST.EMP2 TO RDTEST
GRANT SELECT ON TEST.DEPT TO RDTEST
GRANT SELECT ON TEST.EMP TO RDTEST
GRANT SELECT ON TEST.ID TO RDTEST
PL/SQLプロシージャが正常に完了しました。PL/SQLのSELECTの部分をUPDATEなど他のオブジェクト権限に変更してしようすることも可能です。