06-04.USERの作成と権限【権限とロール02--オブジェクト権限(Object Privileges)】

■オブジェクト権限（Object Privileges）
　オブジェクト権限はUserが所有する特定のオブジェクトを
　他のユーザーがアクセスまたは操作出来るようにするために作成する。

※オブジェクト権限（Object Privileges）とは？
　-テーブル、ビュー、シーケンス、プロシーザー、ファンクションまたはパッケージの中から
　　指定された１つのオブジェクトへ特別な作業を実施できるようにする。
　-オブジェクト所有者は他ユーザーへ特定のオブジェクト権限を付与できる。
　-PUBLICで権限を付与すると回収する時もPUBLICで回収しなければならない。
　-基本的に所有するオブジェクトに対してすべての権限が自動で与えられる。
　-WITH GRANT OPTIONはROLEへ権限を付与するとは使えない。

■オブジェクト権限付与文

GRANT object_privilege [  ] 
ON object
TO { user[,user] | role | PUBLIC }
[ WITH GRANT OPTION ]

-object_privilege:付与するオブジェクト権限名
-object:オブジェクト名
-user, role:付与するユーザー名と他DBの役割名
-PUBLIC:オブジェクト権限、DB役割をすべてのユーザーへ付与
-WITH GRANT OPTION:権限を付与されたユーザーもその権限を他ユーザー、役割へ付与できる

表の列（ALTER,DELETE,EXECUTE…）はobject_privilegeになり、
行（テーブル、ビュー、シーケンス、プロシーザー…）がONの次のobjectとなる。


■オブジェクト権限付与例文

---オブジェクト権限を付与するユーザーを作成（test1,test2）
---test1,test2へ基本権限を付与

SQL> show user
ユーザーは"SYS"です。
SQL> CREATE USER test1 IDENTIFIED BY test;

ユーザーが作成されました。

SQL> CREATE USER test2 IDENTIFIED BY test;

ユーザーが作成されました。

SQL> GRANT CONNECT, RESOURCE, CREATE TABLE TO test1, test2;

権限付与が成功しました。

SQL>

---scottで接続する
---test1へempテーブルをSELECT,INSERT出来る権限を付与
---test1も他ユーザーへその権限を付与できる。

SQL> CONN scott/tiger
接続されました。
SQL> show user
ユーザーは"SCOTT"です。
SQL> GRANT SELECT, INSERT
  2  ON emp
  3  TO test1
  4  WITH GRANT OPTION;

権限付与が成功しました。

---test1で接続してempテーブルを参照する。
SQL> CONN test1/test
接続されました。
SQL> show user
ユーザーは"TEST1"です。
SQL> SELECT * FROM scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 13-08-13        800                    20
      7499 ALLEN      SALESMAN        7698 13-08-13       1600        300         30
      7521 WARD       SALESMAN        7698 13-08-13       1250        500         30
      7566 JONES      MANAGER         7839 13-08-13       2975                    20
      7654 MARTIN     SALESMAN        7698 13-08-13       1250       1400         30
      7698 BLAKE      MANAGER         7839 13-08-13       2850                    30
      7782 CLARK      MANAGER         7839 13-08-13       2450                    10
      7788 SCOTT      ANALYST         7566 13-08-13       3000                    20
      7839 KING       PRESIDENT            13-08-13       5000                    10
      7844 TURNER     SALESMAN        7698 13-08-13       1500          0         30
      7876 ADAMS      CLERK           7788 13-08-13       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 13-08-13        950                    30
      7902 FORD       ANALYST         7566 13-08-13       3000                    20
      7934 MILLER     CLERK           7782 13-08-13       1300                    10

14行が選択されました。

SQL>

---test2で接続し、empテーブルを確認
---test1で接続し、SELECT権限をtest2へ付与する
---test2で接続し、empテーブルを再確認

SQL> CONN test2/test
接続されました。
SQL> show user
ユーザーは"TEST2"です。
SQL> SELECT * FROM scott.emp;
SELECT * FROM scott.emp
                    *
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。


SQL> CONN test1/test
接続されました。
SQL> show user
ユーザーは"TEST1"です。
SQL> GRANT SELECT on scott.emp TO test2;

権限付与が成功しました。

SQL> CONN test2/test
接続されました。
SQL> show user
ユーザーは"TEST2"です。
SQL> SELECT * FROM scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 13-08-13        800                    20
      7499 ALLEN      SALESMAN        7698 13-08-13       1600        300         30
      7521 WARD       SALESMAN        7698 13-08-13       1250        500         30
      7566 JONES      MANAGER         7839 13-08-13       2975                    20
      7654 MARTIN     SALESMAN        7698 13-08-13       1250       1400         30
      7698 BLAKE      MANAGER         7839 13-08-13       2850                    30
      7782 CLARK      MANAGER         7839 13-08-13       2450                    10
      7788 SCOTT      ANALYST         7566 13-08-13       3000                    20
      7839 KING       PRESIDENT            13-08-13       5000                    10
      7844 TURNER     SALESMAN        7698 13-08-13       1500          0         30
      7876 ADAMS      CLERK           7788 13-08-13       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 13-08-13        950                    30
      7902 FORD       ANALYST         7566 13-08-13       3000                    20
      7934 MILLER     CLERK           7782 13-08-13       1300                    10

14行が選択されました。

SQL>

■オブジェクト権限回収例文

---test1へ付与したempテーブルに対するSELECT, INSERT権限回収例文
---test1の権限が回収されるとtest1が権限付与したtest2の権限も回収される。

SQL> CONN scott/tiger
接続されました。
SQL> show user
ユーザーは"SCOTT"です。
SQL> REVOKE SELECT, INSERT
  2  ON emp
  3  FROM test1;

取消しが成功しました。

SQL> CONN test1/test
接続されました。
SQL> show user
ユーザーは"TEST1"です。
SQL> SELECT * FROM scott.emp;
SELECT * FROM scott.emp
                    *
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。


SQL> CONN test2/test
接続されました。
SQL> show user
ユーザーは"TEST2"です。
SQL> SELECT * FROM scott.emp;
SELECT * FROM scott.emp
                    *
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。


SQL>

■WITH GRANT OPTIONによるオブジェクト権限回収
　WITH GRANT OPTIONを使用して付与したオブジェクト権限を回収すると
　付与されたユーザーが付与したユーザーの権限も回収される。

 【解説】
１．SCOTTがTEST1にWITH GRANT OPTIONを使用してempテーブルのSELECT権限を付与
２．TEST1がempテーブルのSELECT権限をTEST2へ付与
３．SCOTTがTEST1に付与したempテーブルのSELECT権限を回収

【結果】
　-SCOTTがTEST1に付与したempテーブルのSELECT権限を回収するとTEST2のempテーブル
　 SELECT権限も自動的に回収される。