ワダです。各種DBMSの権限設定方法について記事を書こうと思います。
今回は、Oracle 12cについてです。
■環境
・OS
Windows10 Pro ビルド番号 1803
・DBMS
| DBMS | バージョン |
|---|---|
| Oracle | 12.2.0 |
・DB情報
| DB名 | スキーマ | テーブル | 項目名 | 型 | 桁数 |
|---|---|---|---|---|---|
| PDB | TESTUSER01 | TEST0001 | VARCHAR2 | 100 | |
| NAME | VARCHAR2 | 30 | |||
| AGE | NUMBER | 3 |
今回は以下のように、権限が異なる３通りのユーザーを作成するケースを考えてみます。
- 「USER01」参照が可能なユーザー
- 「USER02」参照、更新が可能なユーザー
- 「USER03」参照、更新、構成変更（ALTER）が可能なユーザー
※自分のスキーマにあるオブジェクトからはもともとALTERが可能なので、別スキーマ（TESTUSER01）のテーブルを使用します。
■手順
1. ユーザーを作成。
2. ロールを作成。
3. ２で作成したロールに権限を付与。
4. ３のロールをユーザーに付与。
5. 結果確認。
1. ユーザー作成（接続権限も付与）
|
1
2
3
4
5
6
7
8
9
|
--コマンドプロンプトから、SYSDBA（ conn sys /as sysdba）で接続して実施。
CREATE USER USER01 IDENTIFIED BY "USER01" ;
GRANT CONNECT TO USER01;
CREATE USER USER02 IDENTIFIED BY "USER02" ;
GRANT CONNECT TO USER02;
CREATE USER USER03 IDENTIFIED BY "USER03" ;
GRANT CONNECT TO USER03;
|
※Oracle12c以降アーキテクチャが変わり、プラガブルデータベースに対しユーザーを作成しましたが、ここで思った以上にハマりました。
ユーザーをDebelopment Studio上で作成できないはずはないと思うのですが、今回は方法を調査できずコマンドで作成しました。
このあたりの内容は次回以降に（汗）・・・。
2. ロール作成（Debelopment Studio上で作成可能）
|
1
2
3
|
CREATE ROLE C##ROLE01 NOT IDENTIFIED;
CREATE ROLE C##ROLE02 NOT IDENTIFIED;
CREATE ROLE C##ROLE03 NOT IDENTIFIED;
|
※ロール名について、頭に「C##」を付けた「共通ロール」で作成しています。
プラガブルDB側のロールとして作成してしまうとコンテナDB側から見えず、手順３で権限付与ができませんでした
この理由については長文になってしまいそうなため、今回は先へ進みます・・。
3. ロールに権限付与
|
1
2
3
4
5
6
7
8
9
|
GRANT SELECT ON TESTUSER01.TEST0001 TO C##ROLE01;
GRANT SELECT ON TESTUSER01.TEST0001 TO C##ROLE02
GRANT INSERT,UPDATE,DELETE ON TESTUSER01.TEST0001 TO C##ROLE02;
GRANT SELECT ON TESTUSER01.TEST0001 TO C##ROLE03;
GRANT INSERT,UPDATE,DELETE ON TESTUSER01.TEST0001 TO C##ROLE03;
GRANT ALTER ON TESTUSER01.TEST0001 TO C##ROLE03;
|
4. ユーザーにロールを付与
|
1
2
3
|
GRANT C##ROLE01 TO USER01;
GRANT C##ROLE02 TO USER02;
GRANT C##ROLE03 TO USER03;
|
5. 結果確認
想定通りにできているか、
USER01、USER02、USER03それぞれで下記「test.sql」を試します。
|
1
2
3
4
5
6
|
--(1)
SELECT * FROM TEST0001;
--(2)
INSERT INTO TESTUSER01.TEST0001 VALUES('aaa@dcom.jp','yamada',40);
--(3)
ALTER TABLE TESTUSER01.TEST0001 ADD (DEL_FLG NUMBER);
|
「test.sql」をバッチプログラムから試してみます。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
echo off
rem ######################################################################
rem # test.bat
rem # 概要：
rem # rem # 権限確認用バッチ
rem ######################################################################
setlocal
set BATDIR=%~dp0
set LOGFILE=%BATDIR%oracletest.log
rem -------------------------
rem ORACLE接続情報
rem -------------------------
set ORA_USER_NAME=USER01
set ORA_PASSWORD=USER01
set ORA_IP_ADDR=localhost
set ORA_PORT_NUMBER=1521
set ORA_SERVICE_NAME=PDB
rem ---------------------------------------------------------------
rem 開始
rem ---------------------------------------------------------------
echo 処理開始 1> %LOGFILE% 2>&1
rem -------------------------
rem sqlplus開始
rem -------------------------
sqlplus %ORA_USER_NAME%/%ORA_PASSWORD%@%ORA_IP_ADDR%:%ORA_PORT_NUMBER%/%ORA_SERVICE_NAME% @oracle.sql 1>> %LOGFILE% 2>&1
echo 処理終了 1>> %LOGFILE% 2>&1
|
結果（USER01）→SELECTのみ可能。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL*Plus: Release 12.2.0.1.0 Production on 日 8月 4 16:46:25 2019
Copyright (c) 1982, 2016, Oracle.All rights reserved.
最終正常ログイン時間: 日 8月04 2019 15:40:00 +09:00
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
に接続されました。
レコードが選択されませんでした。
INSERT INTO TESTUSER01.TEST0001 VALUES('aaa@dcom.jp','yamada',40)
*
行1でエラーが発生しました。:
ORA-01031: 権限が不足しています
ALTER TABLE TESTUSER01.TEST0001 ADD (DEL_FLG NUMBER)
*
行1でエラーが発生しました。:
ORA-01031: 権限が不足しています
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Productionとの接続が切断されました。
処理終了
|
結果（USER02）→SELECT、INSERTが可能。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL*Plus: Release 12.2.0.1.0 Production on 日 8月 4 16:48:54 2019
Copyright (c) 1982, 2016, Oracle.All rights reserved.
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
に接続されました。
レコードが選択されませんでした。
1行が作成されました。
ALTER TABLE TESTUSER01.TEST0001 ADD (DEL_FLG NUMBER)
*
行1でエラーが発生しました。:
ORA-01031: 権限が不足しています
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Productionとの接続が切断されました。
処理終了
|
結果（USER03）→SELECT、INSERT、ALTER TABLEが可能。
|
1
2
3
4
5
6
7
|
EMAILNAME AGE
------------------------------------------------------------
aaa@dcom.jpyamada 40
1行が作成されました。
表が変更されました。
|
ここまでで、権限の異なる３通りのユーザーを作成できました。
今回は以上です。ありがとうございました。
参考サイト
Oracle12cでユーザを作成する方法
スキーマ（ユーザ）作成時に「ORA-65096」～Oracle 12cのアーキテクチャはここが違う～:DBMoto