Oracleデータベース上に、ユーザーを作成しテーブル作成等が行えるようにするには、ユーザーの作成・権限の付与等一定の手順をふむ必要がある。個々のコマンドについて説明しているサイトは多いものの、一連の手順を記載しているサイトが少なかったので、今回、その手順について共有することにした。
前提条件
Windows端末上に、Oracleをインストール済で、SQL Developerをダウンロード済であること。OracleのインストールとSQL Developerのダウンロードの手順は下記記事を参照のこと。
やってみたこと
Oracleユーザーの作成と権限の付与
ユーザー作成は、Oracleインストール時に作成済である、SYSDBA権限をもつsysユーザーで作成する。その手順は以下の通り。
1) SQL Developerで「接続(+)」ボタンを押下
2) 以下のように、sysユーザーでの接続設定を行い、「接続」ボタンを押下
なお、このときのロールは、上図のように「SYSDBA」を選択する。
3) 以下のコマンドを実行し、ユーザーの所属する表領域を作成
1 2 3 | CREATE TABLESPACE USER02 DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\USER02.dbf' SIZE 100M AUTOEXTEND ON NEXT 500K MAXSIZE UNLIMITED; |
なお、表領域とはテーブル等を格納するものをいう。今回作成した表領域「USER02」は、下図赤枠のデータファイル「USER02.dbf」に含まれている。
4) 以下のコマンドを実行し、「USER02」ユーザーを作成
1 2 3 4 | CREATE USER USER02 IDENTIFIED BY "USER02" DEFAULT TABLESPACE USER02 TEMPORARY TABLESPACE TEMP; |
上記コマンドでは、「USER02」ユーザーを、パスワード：USER02、デフォルト表領域：USER02、一時表領域：TEMPで作成している。なお、一時表領域「TEMP」は、Oracleインストール時に作成済の表領域である。
5) 以下のコマンドを実行し、「USER02」ユーザーに権限を付与
1 2 | GRANT CONNECT, RESOURCE TO USER02; GRANT UNLIMITED TABLESPACE TO USER02; |
上記コマンドの意味は、以下の通り。
・最初のGRANT文で、作成したUSER02ユーザーに、DBにアクセスできるCONNECTロールと、テーブル等のDBオブジェクトを作成できるRESOURCEロールを付与している。
・次のGRANT文で、作成したUSER02ユーザーが、任意の表領域を無制限に使うことができるようにしている。
ちなみに、「ロール」とは、複数の権限を1つにまとめたものをいう。
以上の手順により、作成した「USER02」ユーザーでDBにログインすると、テーブルの作成等が行えるようになる。
CONNECTロールの所属する権限を確認した結果は、以下の通りで、データベースアクセスするためのCREATE SESSION権限が含まれている。
1 | select * from dba_sys_privs where grantee = 'CONNECT'; |
RESOURCEロールの所属する権限を確認した結果は、以下の通りで、テーブルを作成するCREATE TABLE権限等が含まれている。
1 | select * from dba_sys_privs where grantee = 'RESOURCE'; |
USER02ユーザーの権限を確認した結果は、以下の通りで、任意の表領域を無制限に使うことができるUNLIMITED TABLESPACE権限が含まれている。
1 | select * from dba_sys_privs where grantee = 'USER02'; |
なお、表領域割当制限は、任意の表領域を無制限に使うことができるUNLIMITED TABLESPACE権限の他に、QUOTA句による割当制限を付与することができる。そのコマンドについては、下記サイトを参照のこと。
https://cosol.jp/knowledge/knowledge_post/ob0003_new_user/
作成したユーザーによるテーブル作成確認
作成した「USER02」ユーザーでDBにログインし、テーブル作成等が行えることを確認した。その手順は以下の通り。
1) SQL Developerで「接続(+)」ボタンを押下
2) 以下のように、user02ユーザーでの接続設定を行い、「接続」ボタンを押下
なお、このときのロールは、上図のように「デフォルト」を選択する。
3) 以下のコマンドを実行し、「test_table」テーブルを作成できることを確認
1 2 3 4 | create table test_table ( id NUMBER(6) PRIMARY KEY NOT NULL, name VARCHAR(40) NOT NULL ); |
4) 以下のコマンドを実行し、「test_table」テーブルにデータを追加できることを確認
1 2 3 4 | insert into test_table (id, name) values (1, 'テスト'); insert into test_table (id, name) values (2, 'テスト２'); insert into test_table (id, name) values (3, 'テスト３'); commit; |
5) 以下のコマンドを実行し、「test_table」テーブルのデータが参照できることを確認
1 | select * from test_table; |
6) 以下のコマンドを実行し、「test_table」テーブルのデータを更新/削除できることを確認
1 2 3 | update test_table set NAME = 'テスト２更新' where ID = 2; delete from test_table where ID = 3; commit; |
7) 以下のコマンドを実行し、「test_table」テーブルのデータが更新/削除されたことを確認
1 | select * from test_table; |
以上より、作成した「USER02」ユーザーで、テーブルの作成・データ追加/参照/更新/削除ができることが確認できた。
要点まとめ
- 作成したOracleユーザーでDBログイン・DBオブジェクトの生成を行えるようにするには、作成したユーザーに、CONNECT, RESOURCEの各ロールと、表領域割当権限であるUNLIMITED TABLESPACE等を付与する必要がある。
