CREATEやら、UPDATEやら、SELECTやらの権限を付与する

Oracleデータベースをユーザーとして利用するには権限が必要です。いわば、データベースを触れる権利みたいなもの。
そして、権限はデータベースに接続できる権限を持つAユーザー、データベースに接続でき、かつテーブル作成できる権限を持つBユーザーなどユーザーごとに違います。
権限には2種類あります。1つ目はシステム権限です。これはOracleデータベースシステムに対する操作の権限です。CREATE SESSION(=Oracleデータベースシステムにログインする)、CREATE TABLE(=Oracleデータベースシステムにテーブルを作る)、DROP ANY CACHE GROUP(=Oracleデータベースシステムの任意のキャッシュグループを削除する)です。データベースシステムに対する操作なので、データベースに詳しい方、いわゆるDBAレベルが主にこのような操作を行います。
2つ目は、オブジェクト権限です。これは、Oracleのオブジェクト(テーブルや索引、ビューなど)に対する操作の権限です。SELECT (テーブルを検索する)、INDEX(ビューを作成する)などです。主にデータを操作するために使う権限です。

覚えるべきキーワード

・GRANT システム権限 TO ユーザー名;
・GRANT システム権限 TO PUBLIC;
・GRANT システム権限TO ロール名;
・GRANT システム権限TO ユーザー名(PUBLIC、ロール名) WITH ADMIN OPTION;
・REVOKE システム権限FROM ユーザー名(PUBLIC、ロール名);
・GRANT オブジェクト権限 ON オブジェクト名 TO ユーザー名;
・GRANT オブジェクト権限 ON オブジェクト名 TO PUBLIC;
・GRANT オブジェクト権限 ON オブジェクト名 TO ロール名;
・GRANTオブジェクト権限 ON オブジェクト名 TO ユーザー名(PUBLIC、ロール名)WITH GRANT OPTION;
・REVOKE オブジェクト権限 ON オブジェクト名 FROM ユーザー名(PUBLIC、ロール名);

権限はどうやって付与するのか

では、権限はどのように付与するのか。それは、圧倒的な管理権限であるSYSDBAシステム権限を持つユーザーが付与する、です。接続時に最後にas sysdbaを付けます。接続にはOS認証とパスワードファイル認証の2パターンありますが、どちらともas sysdbaを付けてログインします。ユーザーがoracle、パスワードがoracle_passだった場合のそれぞれのログイン方法です。

(OS認証)
connect / as sysdba

(パスワードファイル認証で接続)
connect oracle/oracle_pass as sysdba

どちらかの方法でログインします。OS認証はOracleがインストールされている端末だとログインできます。パスワードファイル認証はリモートでログインできます。

システム権限の付与と削除

システム権限の付与はGRANT文を使用します。oracleユーザーにCREATE TABLE権限を付与するSQLです。

GRANT システム権限 TO ユーザー名
GRANT CREATE TABLE TO oracle;

これでoracleユーザーはCREATE TABLEが実行できるようになりました。(権限付与前に実行するとエラーになります。)
ユーザーごとに付与するのが面倒で、すべてのユーザーに一括で付与したい場合はpublicに権限を付与します。
CREATE TABLE権限をユーザーすべてに付与します。

GRANT システム権限 TO PUBLIC
GRANT CREATE TABLE TO PUBLIC;

これで、すべてのユーザーにCREATE TABLE権限が付与されます。

Oracleには、管理者がユーザーを1まとまりのグループにして、そのグループに権限を付与すれば、そのグループに属するユーザーすべてに権限が付与される仕組みも用意されています。この1まとまりのグループをロールと呼びます。例えば、CREATE ROLE文でmakersというroleを作成した後にgrant文でoracleユーザーをroleに追加します。
その後に次のSQLを実行すると、oracleユーザー含めて、makersロールに所属しているユーザーすべてにcreate table権限が付与されます。

GRANT システム権限 TO ロール名
GRANT CREATE TABLE TO makers;

このようにシステム権限を付与する対象は、ユーザー、PUBLIC、ロールです。

ちなみに、システム権限を付与したユーザー(ロール、PUBLIC)にそのシステム権限を第三者ユーザーに付与する権限を与えたい時はWITH ADMIN OPTIONを付けてGRANT文を実行します。
oracleユーザーにCREATE TABLE権限を付与し、さらにoracleユーザーにCREATE TABLE権限を付与するための権限も与えたい時は次のSQLを実行します。

GRANT システム権限TO ユーザー名(PUBLIC、ロール名) WITH ADMIN OPTION
GRANT CREATE TABLE TO oracle WITH ADMIN OPTION;

これで、oracleユーザーは他のユーザーにCREATE TABLE権限を付与できるようになります。

システム権限の削除

システム権限を削除する時はREVOKE文を使います。oracleユーザーからCREATE TABLE権限を削除するには次のSQLを使います。

REVOKE システム権限FROM ユーザー名
REVOKE CREATE TABLE FROM oracle;

これでoracleユーザーはCREATE TABLEが実行不可になります。

ちなみに、oracleユーザーにWITH GRANT OPTION付きでCREATE TABLE権限が付与されていて、oracleユーザーがantという名のユーザーにCREATE TABLE権限を付与していた場合、oracleユーザーのCREATE TABLE権限が削除されても、antユーザーのCREATE TABLE権限は削除されません。

オブジェクト権限の付与

オブジェクト権限の付与もGRANT文を使用します。spユーザーが所有するdep表に対して、oracleユーザーにselect権限を付与するSQLです。

GRANT オブジェクト権限 ON オブジェクト名 TO ユーザー名
GRANT SELECT ON sp.dep TO oracle;

これでoracleユーザーはspユーザーが所有するdep表にSELECTが実行できるようになりました。(権限付与前に実行するとエラーになります。)
システム権限と同様、オブジェクト権限もすべてのユーザーに一括で付与したい場合はpublicに権限を付与します。
spユーザーのdept表に対するSELECT権限をすべてのユーザーに付与します。

GRANT オブジェクト権限 ON オブジェクト名 TO PUBLIC
GRANT SELECT ON sp.dep TO public;

これですべてのユーザーがspユーザーのdep表にSELECT実行できるようになりました。

オブジェクト権限もロールに権限付与ができます。例えば、CREATE ROLE文でmakers_objというroleを作成した後にgrant文でoracleユーザーをroleに追加します。
その後に次のSQLを実行すると、oracleユーザー含めて、makers_objロールに所属しているユーザーすべてにsp所有のdep表にSELECT権限が付与されます。

GRANT オブジェクト権限 ON オブジェクト名 TO ロール名
GRANT SELECT ON sp.dep TO makers_obj;

オブジェクト権限を付与する対象も、ユーザー、PUBLIC、ロールです。

ちなみに、オブジェクト権限を付与したユーザー(ロール、PUBLIC)にそのオブジェクト権限を第三者ユーザーに付与する権限を与えたい時はWITH GRANT OPTIONを付けてGRANT文を実行します。システム権限はWITH ADMIN OPTIONだったので少し違いますね。
oracleユーザーにsp所有のdep表にSELECT権限を付与し、さらにoracleユーザーにsp所有のdep表にSELECT権限を付与するための権限も与えたい時は次のSQLを実行します。

GRANT システム権限TO ユーザー名 WITH GRANT OPTION
GRANT SELECT ON sp.dep TO oracle WITH GRANT OPTION;

これで、oracleユーザーは他のユーザーにSELECT権限を付与できるようになります。

オブジェクト権限の取り消し

オブジェクト権限を削除する時もREVOKE文を使います。oracleユーザーからsp所有のdep表に 対するSELECT権限を削除するには次のSQLを使います。

REVOKE オブジェクト権限 ON オブジェクト名 FROM ユーザー名
REVOKE SELECT ON sp.dep FROM oracle

これでoracleユーザーはdep表にSELECT実行不可になります。

ちなみに、oracleユーザーにWITH GRANT OPTION付きでSELECT権限が付与されていて、oracleユーザーantという名のユーザーにSELECT権限を付与していた場合、oracleユーザーのSELECT権限が削除されたら、antユーザーのSELECT権限も削除されます。ここはシステム権限の第三者付与と違うところです。
システム権限が削除されたユーザーが、第三者に付与したシステム権限は削除されませんが、オブジェクト権限が削除されたユーザーが、第三者に付与したオブジェクト権限は同じタイミングで削除されます。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です