DB

Oracleにユーザーを追加しテーブルを作成してみた

 

Oracleデータベース上に、ユーザーを作成しテーブル作成等が行えるようにするには、ユーザーの作成・権限の付与等一定の手順をふむ必要がある。個々のコマンドについて説明しているサイトは多いものの、一連の手順を記載しているサイトが少なかったので、今回、その手順について共有することにした。

前提条件

Windows端末上に、Oracleをインストール済で、SQL Developerをダウンロード済であること。OracleのインストールとSQL Developerのダウンロードの手順は下記記事を参照のこと。

Windows端末にMySQL、PostgreSQL、Oracleをインストールしてみた今回はいろいろな種類のデータベースを使ってみたかったので、Windows端末に、MySQL、PostgreSQL、Oracleそれぞれの...



やってみたこと

  1. Oracleユーザーの作成と権限の付与
  2. 作成したユーザーによるテーブル作成確認

 

Oracleユーザーの作成と権限の付与

ユーザー作成は、Oracleインストール時に作成済である、SYSDBA権限をもつsysユーザーで作成する。その手順は以下の通り。

1) SQL Developerで「接続(+)」ボタンを押下
Oracleユーザーの作成1

2) 以下のように、sysユーザーでの接続設定を行い、「接続」ボタンを押下
Oracleユーザーの作成2
なお、このときのロールは、上図のように「SYSDBA」を選択する。

3) 以下のコマンドを実行し、ユーザーの所属する表領域を作成

CREATE TABLESPACE USER02 
  DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\USER02.dbf' 
  SIZE 100M AUTOEXTEND ON NEXT 500K MAXSIZE UNLIMITED;
Oracleユーザーの作成3_1

なお、表領域とはテーブル等を格納するものをいう。今回作成した表領域「USER02」は、下図赤枠のデータファイル「USER02.dbf」に含まれている。
Oracleユーザーの作成3_2

4) 以下のコマンドを実行し、「USER02」ユーザーを作成

CREATE USER USER02 
  IDENTIFIED BY "USER02" 
  DEFAULT TABLESPACE USER02 
  TEMPORARY TABLESPACE TEMP;

Oracleユーザーの作成4
上記コマンドでは、「USER02」ユーザーを、パスワード：USER02、デフォルト表領域：USER02、一時表領域：TEMPで作成している。なお、一時表領域「TEMP」は、Oracleインストール時に作成済の表領域である。

5) 以下のコマンドを実行し、「USER02」ユーザーに権限を付与

GRANT CONNECT, RESOURCE TO USER02;
GRANT UNLIMITED TABLESPACE TO USER02;

Oracleユーザーの作成5_1
上記コマンドの意味は、以下の通り。
・最初のGRANT文で、作成したUSER02ユーザーに、DBにアクセスできるCONNECTロールと、テーブル等のDBオブジェクトを作成できるRESOURCEロールを付与している。
・次のGRANT文で、作成したUSER02ユーザーが、任意の表領域を無制限に使うことができるようにしている。
ちなみに、「ロール」とは、複数の権限を1つにまとめたものをいう。

以上の手順により、作成した「USER02」ユーザーでDBにログインすると、テーブルの作成等が行えるようになる。

CONNECTロールの所属する権限を確認した結果は、以下の通りで、データベースアクセスするためのCREATE SESSION権限が含まれている。

select * from dba_sys_privs where grantee = 'CONNECT';
Oracleユーザーの作成5_2

RESOURCEロールの所属する権限を確認した結果は、以下の通りで、テーブルを作成するCREATE TABLE権限等が含まれている。

select * from dba_sys_privs where grantee = 'RESOURCE';
Oracleユーザーの作成5_3

USER02ユーザーの権限を確認した結果は、以下の通りで、任意の表領域を無制限に使うことができるUNLIMITED TABLESPACE権限が含まれている。

select * from dba_sys_privs where grantee = 'USER02';
Oracleユーザーの作成5_4

なお、表領域割当制限は、任意の表領域を無制限に使うことができるUNLIMITED TABLESPACE権限の他に、QUOTA句による割当制限を付与することができる。そのコマンドについては、下記サイトを参照のこと。
https://cosol.jp/knowledge/knowledge_post/ob0003_new_user/



作成したユーザーによるテーブル作成確認

作成した「USER02」ユーザーでDBにログインし、テーブル作成等が行えることを確認した。その手順は以下の通り。

1) SQL Developerで「接続(+)」ボタンを押下
Oracleユーザーの確認1

2) 以下のように、user02ユーザーでの接続設定を行い、「接続」ボタンを押下
Oracleユーザーの確認2
なお、このときのロールは、上図のように「デフォルト」を選択する。

3) 以下のコマンドを実行し、「test_table」テーブルを作成できることを確認

create table test_table (
    id NUMBER(6) PRIMARY KEY NOT NULL,
    name VARCHAR(40) NOT NULL
);
Oracleユーザーの確認3

4) 以下のコマンドを実行し、「test_table」テーブルにデータを追加できることを確認

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;
Oracleユーザーの確認4

5) 以下のコマンドを実行し、「test_table」テーブルのデータが参照できることを確認

select * from test_table;
Oracleユーザーの確認5

6) 以下のコマンドを実行し、「test_table」テーブルのデータを更新/削除できることを確認

update test_table set NAME = 'テスト２更新' where ID = 2;
delete from test_table where ID = 3;
commit;
Oracleユーザーの確認6

7) 以下のコマンドを実行し、「test_table」テーブルのデータが更新/削除されたことを確認

select * from test_table;
Oracleユーザーの確認7

以上より、作成した「USER02」ユーザーで、テーブルの作成・データ追加/参照/更新/削除ができることが確認できた。

要点まとめ

  • 作成したOracleユーザーでDBログイン・DBオブジェクトの生成を行えるようにするには、作成したユーザーに、CONNECT, RESOURCEの各ロールと、表領域割当権限であるUNLIMITED TABLESPACE等を付与する必要がある。
RELATED POST