権限付与の特徴

Oracleの権限付与には、次の特徴があります。

・システム権限とオブジェクト権限の2種類ある
・ロールにロールを付与することもできる
・権限付与だけでなくcreate user等で表領域割り当て（quota）が必要
・UNLIMITED TABLESPACEはシステム表領域を操作できてしまう
・grant文はロールと権限を混在して記述できない
・ストアド内部ではロールの権限は無視される

まず1つ目。

・システム権限とオブジェクト権限の2種類ある

MySQLでいう、グローバルレベルとテーブルレベルのようなものです。
インスタンス全体の権限と、オブジェクト（テーブル、ビュー、パッケージ、ストアド等）の権限です。
テーブルもパッケージもすべて「オブジェクトの1種」という、Oracleの特徴的な考え方がよく表れていると思います。

次、ロールの再帰的付与が可能です。

・ロールにロールを付与することもできる

例を挙げると、IMP_FULL_DATABASEロールはSELECT_CATALOG_ROLEロールを付与しています。

次はquota。

・ロールにロールを付与することもできる

スキーマの表領域へのアクセス制限を確認するにあるように、権限とその対象は別々の設定なので注意が必要です。

次は特徴というより注意です。

・UNLIMITED TABLESPACEはシステム表領域を操作できてしまう

UNLIMITED TABLESPACEはquota設定を無視するのでできるだけ付与しないほうが安全かと思います。

その次もGRANT設定上の注意です。

・grant文はロールと権限を混在して記述できない

理由はわからないのですが、カンマ区切りで同時に付与しようとするとエラーになるようです。

最後に、一番ハマりやすい特徴です。

・ストアド内部ではロールの権限は無視される

この仕様のせいで、せっかくロールで管理したい権限をスキーマに対していちいち付与しなくてはならないです。
みなさんはどういう管理してるんでしょうかね・・・。
Webフロント用のスキーマ、バッチ用スキーマと使い分けてるんでしょうか。

システム権限とオブジェクト権限を確認する

Oracleにはシステム権限とオブジェクト権限の2種類あります。
権限をロールとしてひとまとめにすることもでき、ロールにロールを付与することもできます。
注意として、権限だけではデフォルト表領域以外は、スキーマの表領域へのアクセス制限を確認する設定が必要です。
また、grantはロールと権限を混在して記述できません。
もっとも注意しなければならないのは、ストアド内部ではロールの権限は無視されることです。

スキーマやロールに付与したシステム権限を確認します。

set pages 10000
set lines 100
col grantee for a20
col privilege for a30
select grantee,privilege from dba_sys_privs
where grantee='SSS01DBUSER'
order by grantee,privilege;

GRANTEE              PRIVILEGE
-------------------- ------------------------------
SSS01DBUSER          ALTER ANY TABLE
SSS01DBUSER          CREATE ANY INDEX
SSS01DBUSER          CREATE ANY MATERIALIZED VIEW
SSS01DBUSER          CREATE ANY PROCEDURE
SSS01DBUSER          CREATE ANY SEQUENCE
SSS01DBUSER          CREATE ANY TABLE
SSS01DBUSER          CREATE ANY TRIGGER
SSS01DBUSER          CREATE ANY VIEW
SSS01DBUSER          CREATE PROCEDURE
SSS01DBUSER          CREATE SEQUENCE
SSS01DBUSER          CREATE SESSION
SSS01DBUSER          CREATE TABLE
SSS01DBUSER          CREATE TRIGGER
SSS01DBUSER          DELETE ANY TABLE
SSS01DBUSER          DROP ANY INDEX
SSS01DBUSER          DROP ANY MATERIALIZED VIEW
SSS01DBUSER          DROP ANY PROCEDURE
SSS01DBUSER          DROP ANY SEQUENCE
SSS01DBUSER          DROP ANY TABLE
SSS01DBUSER          DROP ANY TRIGGER
SSS01DBUSER          DROP ANY VIEW
SSS01DBUSER          EXECUTE ANY PROCEDURE
SSS01DBUSER          GLOBAL QUERY REWRITE
SSS01DBUSER          INSERT ANY TABLE
SSS01DBUSER          RESUMABLE
SSS01DBUSER          SELECT ANY SEQUENCE
SSS01DBUSER          SELECT ANY TABLE
SSS01DBUSER          UPDATE ANY TABLE

スキーマやロールに付与したロールを確認します。

set pages 10000
set lines 100
col grantee for a20
col granted_role for a20
select grantee,granted_role from dba_role_privs
where grantee='SSS01DBUSER'
order by grantee,granted_role;

GRANTEE              GRANTED_ROLE
-------------------- --------------------
SSS01DBUSER          SSS01APP
SSS01DBUSER          CONNECT

スキーマやロールに付与したオブジェクト権限を確認します。

set pages 10000
set lines 100
col grantee for a20
col owner for a10
col table_name for a30
col privilege for a20
select grantee,owner,table_name,privilege from dba_tab_privs
where grantee like 'SSS01DBUSER'
order by grantee,table_name,privilege;

GRANTEE      OWNER      TABLE_NAME      PRIVILEGE
------------ ---------- --------------- -----------
SSS01DBUSER  SYS        DBMS_LOCK       EXECUTE

ログインしているスキーマのシステム権限を確認します。

set pages 10000
set lines 100
col privilege for a30
select privilege from user_sys_privs order by privilege;

スキーマの表領域へのアクセス制限を確認する

スキーマ作成および権限付与(grant)をしても、特定の表領域を参照できないときは確認してみるといいでしょう。
表領域への割り当て制限設定がされていないと、権限があっても参照できません。

SQL> SELECT * FROM user_ts_quotas;
MAX_BYTES　　・・・　　利用できる上限。負の値は無制限

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
SSS01_INDEP3_DATA                       0         -1          0         -1 NO
MVLOG_I_DATA                    161480704         -1      19712         -1 NO
SSS01_MASTER_IDX                        0         -1          0         -1 NO
SSS01_HISTORY_IDX                       0         -1          0         -1 NO
SSS01_MASTER_DATA                       0         -1          0         -1 NO
SSS01_HISTORY_DATA                      0         -1          0         -1 NO
SSS01_I_DATA                   4600102912         -1     561536         -1 NO
CCCC01TOWN_DATA                         0         -1          0         -1 NO
MVLOG_I2_DATA                           0         -1          0         -1 YES
SSS01_I_IDX                    3890216960         -1     474880         -1 NO
SSS01_INDEP1_DATA                       0         -1          0         -1 NO
CCCC01TOWN_IDX                          0         -1          0         -1 NO
SSS01_INDEP1_IDX                        0         -1          0         -1 NO
SSS01_INDEP2_IDX                        0         -1          0         -1 NO
SSS01_INDEP2_DATA                       0         -1          0         -1 NO
SSS01_INDEP3_IDX                        0         -1          0         -1 NO