Oracle DB：ユーザ作成と関連設定について

hassy

Oracle DBが基本からしてろくに分からず苦労しているので、調べたことなどをまとめました。
仕事で使う環境は既に構築済みだから何とかなっていただけで、一から作ることになると全然だめでした・・・


Oracle DBのユーザについて

CDB/PDB構成の環境では、CDB全体の管理に用いる「共通ユーザ」、PDBごとに独立して存在する「ローカルユーザ」が登場します。

共通ユーザ

CDB全体の管理用途で使用するユーザです。

SYS、SYSTEMの２つの共通ユーザがデフォルトで存在し、特にSYSはSYSDBAという非常に強力な権限を持っています。
SYSを使っていれば権限不足のエラーが発生することはなくなるかもしれませんが、管理用途で必要な場合以外での乱用は、セキュリティ的に好ましくありません。

共通ユーザを新たに作成するときは、ユーザ名の先頭にC##を付ける必要があります。
とはいえ、必要がない限り、あえて追加で作成するものではなさそうです。

ローカルユーザ

PDBごとに独立したユーザで、同名のユーザであっても、別々のPDBに存在するのであれば、別のユーザとなります。

ローカルユーザはPDBでテーブルやデータを扱うために作成するもので、デフォルトでは存在しません。


ローカルユーザの作成方法と関連設定

ただユーザを作成するだけでは、ほとんど何もすることができません。
以下のような設定を行う必要があり、それらを考慮せずにとりあえずで作ってしまうと、後々、設定が煩雑になることがあります。

  • 権限、ロールの付与
    Oracle DBで何らかの操作を行うには、それに応じた権限が付与されている必要があります。作成されたばかりのユーザでは、PDBに接続する権限すら有していません。
    ロールは権限をまとめたもので、権限を一つずつ付けるより、ロールを使ってまとめて付与することが推奨されているようです。

  • テーブルスペースの設定
    テーブルスペースはOracle DBでテーブルなどのオブジェクトを作成する際に使用する保存領域のようなものです。
    ユーザ作成時に指定しなかった場合、DBのデフォルトのものが使用されますが、きちんと運用するときには、用途に合わせて用意した方がいいと思われます。

  • クオータの割り当て
    クオータは、そのユーザがテーブルスペースをどれだけの容量まで使用することができるか設定するものです。
    デフォルトでは設定が行われないため、ユーザに設定されたテーブルスペースであっても一切使うことができず、容量不足や領域確保失敗といったエラーの原因になります。

テーブルスペースの作成

Oracle DBを試しに触ってみるだけなら、テーブルスペースはデフォルトの設定のままでも問題なさそうですが、ちゃんとした運用を見越しているときは、用途に応じて、ユーザごと、DBを使うアプリごと、などの形で専用のものを用意した方がよさそうです。

そう考えると、ユーザ作成の前にテーブルスペースを用意しておくべきで、ユーザ作成時には、先に作っておいたテーブルスペースを使うように設定するのがいいと思われます。

なお、テーブルスペースはOracle DB上の設定ですが、実態としてはOracle DBがインストールされたサーバ上のファイル（データファイルといいます）にデータを書き込む形になります。
そのため、まずはデータファイルについて考える必要があります。

データファイルはテーブルスペース作成時に一緒に作成されるので、事前に用意する必要はありません。
データファイルのパスはテーブルスペース作成時に指定する必要がありますが、初期化パラメータ「db_create_file_dest」が設定されていれば、パスを指定しなくても、Oracle Managedとして作成されます。

データファイルの容量がテーブルスペースの容量となるため、十分なサイズを設定する必要があります。
サーバのストレージに余裕があれば、データファイルの容量は自動拡張にするのが無難と思われますが、データファイルの容量には上限があります。
上限に達した場合は、テーブルスペースで使うデータファイルを追加する必要があります。
なお、デフォルトでは自動拡張はオフになっているようなので、必要に応じて有効化を行います。

以上を踏まえて、テーブルスペースの作成は以下のようなSQLコマンドで実施します。

  • 初期化パラメータ「db_create_file_dest」設定済みの場合

CREATE TABLESPACE <テーブルスペース名> SIZE <データファイルのサイズ> AUTOEXTEND ON;

  • データファイルのパスを明示的に指定する場合

CREATE TABLESPACE <テーブルスペース名> DATAFILE '<データファイルのパス>' SIZE <データファイルのサイズ> AUTOEXTEND ON;

ユーザの作成

ユーザの作成は以下のようなSQLコマンドで実施します。
このとき、ユーザに設定するテーブルスペースに対するクオータを割り当てますが、その容量は無制限にすることもできます。

  • クオータを制限する場合

CREATE USER <ユーザ名> IDENTIFIED BY <パスワード> DEFAULT TABLESPACE <テーブルスペース名> QUOTA <割り当てるサイズ> ON <テーブルスペース名>;

  • クオータを無制限にする場合

CREATE USER <ユーザ名> IDENTIFIED BY <パスワード> DEFAULT TABLESPACE <テーブルスペース名> QUOTA UNLIMITED ON <テーブルスペース名>;

権限・ロールの付与

ユーザを作成できたら、権限・ロールの付与を行います。
ひとまず、CONNECT、RESOURCEの２つのロールを付与しておけば、通り一遍の操作は可能になるようです。

以下のようなSQLコマンドで実施します。

GRANT CONNECT TO <ユーザ名>;
GRANT RESOURCE TO <ユーザ名>;

設定が上手くいっていないとき

上述の設定が上手くいっていないユーザでOracle DBの操作を行おうとすると、以下のようなエラーが発生します。

  • 権限・ロールが付いていない・足りていない場合
    特定の権限が必要な操作を実施することができず、権限不足のエラーが発生します。

  • テーブルスペース・クオータに不備がある場合
    テーブル作成など、保存領域の必要な操作が実施できず、容量が足りない、領域を確保できない、といったエラーが発生します。


Oracle DBへの接続について

ユーザの用意ができたら、sqlplusなどでDBに接続しますが、接続先がCDBかPDBか、使うユーザが共通ユーザかローカルユーザかなどによって、つなぎ方に違いがあります。

主な使い分けは、以下のようになると思われます。

ローカル接続

Oracle DBをインストールしたサーバ上でsqlplusを実行します。
CDBへの接続はローカル接続でしか実施できないようです。
Oracle DBをインストールしたユーザでsqlplusを実行する場合、OS認証という、ユーザ名・パスワードの記載を省略した形での接続が可能です。

# CDBにOS認証、SYSDBA権限でローカル接続
sqlplus / as sysdba

リモート接続

PDBへの接続はリモート接続で実施します。
リモートとはいいますが、Oracle DBをインストールしたサーバ上でも、同じ記法でPDBに接続することができます。

sqlplus <ユーザ名>/<パスワード>@<ホスト名orホストIP>:<ポート番号>/<サービス名>


いいなと思ったら応援しよう！