Oracle, SQL Server, PostgreSQL, MySQL, MariaDB などの主流なDBMSを、横断的に使用すると、スキーマやユーザーとロールの扱い方で、混乱しやすい。
SQLなどRDBの基本的な使い方は、だいたい共通しているのに、スキーマやユーザー・ロールの扱い方は、かなり違う。
DBMSのヘビーユーザーが、別のDBMSを使うときに最初に障害になるのが、この部分だ。
スキーマやユーザー・ロールの扱い方が、分からなくて仕事が進まない、生産性が上がらない、という影響は地味に大きいと思う。
しかし、DBMSを横断したとき、主な障害になるのはスキーマやユーザー・ロールの部分だけで、他の障害は細かいSQLの仕様の違いぐらいだ。
SQLの違いは、ネット検索で簡単に調べられることが多いので、それほど障害にはならない。
しかし、主流DBMSのデータベース・スキーマやユーザー・ロールの違いをまとめて、解説しているサイトは意外に少なく、断片的な情報しか見つかりにくい。
そこで、この記事では、Oracle, SQL Server, PostgreSQL, MySQL, SQLiteの、インスタンスとデータベースとスキーマとユーザーとロールの違いを簡潔に解説する。
MariaDBは、MySQLと同じと考えて欲しい。
Oracle ヘビーユーザーが、PostgreSQLを使う事になったとか、SQL ServerユーザーがMySQLを使うようになったというように、DBMSを横断したときに、最初に読んで欲しい。
早期にDBMSの違いを理解して、容易に新しいDBMSに慣れる事ができると思う。
まずはDBMSの基礎知識
まずは、簡単にインスタンスとデータベースとスキーマとユーザーとロールについて解説する。この概念は、どのDBMSでも大雑把には同じである。
この解説記事は、「大雑把には同じ」部分が、「細部はどのように違うのか」を説明する形式を取る。
インスタンス
インスタンスは、正確にはデータベース・インスタンスと呼ぶ。
DBMSのプログラムがメモリに読み込まれ、稼働しているプロセス・インスタンスでもある。
インスタンスを形成しているプログラムが、具体的なデータベースの管理を行うので、全てのデータ資源は、インスタンスの中にある。
インスタンスは一番外側の、一番大きな「入れ物」と考えても良い。
通常はDBMSをインストールしたときインスタンス(入れ物)も作成される。
データベース
インスタンスの中に入る二番目に大きな「入れ物」である。
基礎概念としては、この中に、スキーマやテーブルなど、他の全ての枠組みやデータ資源が入る。
通常はインスタンスを作ったとき初期データベースも作られる。
新規でデータベースを作るときは、標準ツールを使うことが多い。
SQLで作るときは、Create Database [データベース名] で作成する。
スキーマ
基礎概念としては、データベースの中に入る三番目に大きな「入れ物」である。
基礎概念としては、この中に、テーブルなどのオブジェクトが入る。
DBMSによって仕様が大きく違う。
SQLで作るときは、Create Schema [スキーマ名] で作成する。
テーブルなどオブジェクト
テーブルとは「表形式のデータ」である。
DBMSが扱うデータ資源は、テーブル以外にもインデックスやビューやプロシージャや関数などがある。
テーブルを含めて、これらは全てオブジェクトと呼ぶ。
具体的にDBMSが管理する対象の事である。
オブジェクトはスキーマの中に格納されて管理される。
Create Table [テーブル名], Create Index [インデックス名], Create View [ビュー名], Create Procedure [プロシージャ名] というようにオブジェクト事に用意された Create 文で作成する。
インスタンスとデータベースとスキーマの基本的関係
インスタンスとデータベースとスキーマとテーブル等オブジェクトは、基礎概念として以下の図のような関係にある。
インスタンスの中にデータベースが入り、
データーベースの中にスキーマが入り、
スキーマの中にテーブル等オブジェクトが入る。
「基礎概念として」という注釈が付くのは、個々のDBMSでは細部の仕様が異なるからだ。個々のDBMSと基礎概念の違いは後で解説する。
ユーザー
DBMSが管理するデータ資源を扱える「人」を制限する為に、「人」に紐付けるオブジェクト。
ITシステムの場合は、「人」はなく「アプリ」がユーザーを使用する。
個々のテーブルなどユーザー単位で、アクセス制限を変更できる。
SQLで作るときは、Create User [ユーザー名] で作成する。
ユーザーに権限を与えるときは、SQLの
GRANT SELECT ON [テーブル名] TO [ユーザー名]
などで権限を与えるが、通常はロールに権限を設定して、GRANT文でロールをユーザーに、紐付ける。
ロール
ユーザーのテーブル等オブジェクトへのアクセス権限を定義したオブジェクト。
一言で言えば「権限」のオブジェクト。
ロールをユーザーに関連付けることで、ユーザーにオブジェクトへのアクセス権限を与える。
一つのユーザーに複数のロールを与えることも、一つのロールを複数のユーザーに与えることもできる。
SQLで作るときは、Create Role [ロール名] で作成する。
また、ロールに権限を与えるときは、
GRANT SELECT ON [テーブル名] TO [ロール名]
のようにGRANT文でロールに権限を与える。
ユーザーとロールの基本的関係
ユーザーとロールの基本的関係は、基礎概念として以下の図のようになる。
例えば、ロールAには「テーブルAへのフルアクセス権」があるとする。
ロールBには「テーブルBへのフルアクセス権」が、ロールCには「テーブルCへの読み取り権」があるとする。
この場合、ユーザー1,ユーザー3,ユーザー4,ユーザー5は、テーブルAへフルアクセス可能な権限が与えられている。
ユーザー2,ユーザー3,ユーザー4は、テーブルBへのフルアクセス可能な権限が与えられている。
ユーザー4,ユーザー,5,ユーザー6は、テーブルCを読み取る事ができる。
ロールはユーザーに紐付けることで、ユーザーにアクセス権限を与える。
ロールとユーザーも、DBMSによる違いが多い。
通常は、SQLの
GRANT [ロール名] TO [ユーザー名]
で、ユーザーに権限を与えるが、
GRANT SELECT,INSERT,UPDATE,DELETE ON [テーブル名] TO [ユーザー名]
のように、ロールを使用しないで、直接ユーザーに権限を与える場合もある。
(SQLの文法はDBMSによって少し異なるので、注意)
Oracleの場合
インスタンスとデータベース
データベースごとにインスタンスを作る構造なので、インスタンスとデータベースは同じ枠組みと考え、区別しない。
新規にデータベースを作る時は、インスタンスも作るので、非常にデータベースの消費するリソースが大きい。
データベースの新規作成にも時間が掛かる。
インスタンスの規模が大きく、1サーバー1インスタンスでの運用が普通で、インスタンスとサーバーが同義にされる事が多い。
サーバー ＝ インスタンス ＝ データベース と考えても良い。
マルチテナント
Oracle12c以降から一つのサーバーに複数のデータベースを作成できるマルチテナント構成が導入されている。
これは一見「一つのインスタンスの中に複数のデータベースが格納できる」構成に見えるし、大雑把にそのように解釈しても使えてしまうと思うが、厳密には異なる。
マルチテナントは外部仕様上は、旧Oracleのインスタンスとデータベースが一体になった仕様と同様に扱うことができる。旧Oracleと互換性があるということだ。
sqlplusなどクライアントから接続するときも、インスタンスとデータベースが一体になったデータベースに対して直接接続する。
SQL Serverなどのようにインスタンスだけにログインして、中で複数のデータベースを使うということはできない。
マルチテナントは一見、一つのサーバーにインスタンスが複数インストールできているようにも見える構成だ。
しかし、ポート番号などは一つで良いので、厳密にはインスタンスは一つである。
マルチテナントとは「一つのインスタンスで、インスタンス&データベースが複数使用できる構成」と説明するのが妥当に思える。
マルチテナントに関してはOracle固有の仕様なので、他のDBMSと比較して考えない方が良い。
スキーマ
データベースの中に複数のスキーマを作成することが可能。
ユーザーとスキーマ
ユーザーとスキーマは同じ概念になっている。
ユーザーごとに自分のスキーマを保有する。
スキーマとユーザーを分ける事ができない。
スキーマ ＝ ユーザー と考えて良い。
ロール
ロールの仕様は基礎概念と同じ。
ロールはインスタンス配下であり、複数スキーマで使用可能だ。
SQL Server の場合
インスタンス
SQL Server は一つのサーバーに、複数のSQL Serverをインストールする事ができる。
異なるバージョンのSQL Server を共存させる事も可能。
SQL Server は一つのサーバーに、複数のインスタンスを作る事ができるので、バージョンの異なるSQL Server を異なるインスタンスにインストールする事で複数バージョンの共存を可能としている。もちろん同一バージョンのインスタンスを複数作る事もできる。
インスタンスの中にSQL Server のエンジン本体を保有している。
データベース
インスタンスの中に、複数のデータベースを作成出来る。
基礎概念と同じ。
スキーマ
一つのデータベースの中に、複数のスキーマを作成出来る。
基礎概念と同じ。
データベースを作成すると、自動で作成される固定ユーザーと固定スキーマがあり、大半はそれらを使って問題無い。
代表的なものが、dboである。
データベースを作ると、自動でdboスキーマとdboユーザーが作られる。
ログイン認証でログインしたまま、SSMSなどでSQL Serverを操作する場合は、dboユーザーでdboスキーマを操作している。
ロールも固定データベースロールが自動で付加されている。
dboを使用しているとスキーマやユーザーとロールを意識する必要がない。
ユーザー
ユーザーとロールの構成は複雑である。
まず、SQL Serverへのログイン用のユーザーと、データベースの中で権限を管理するユーザーは、異なるユーザー概念で管理されている。
ログインユーザー
昔は、SQL Serverへログインするときは、「sa」という固定アカウントでログインしていた。現在もLinux版SQL Serverでは、「sa」でログインする。
現在は、Windows認証(Windowsグループユーザー、Windows Active Directory アカウント、Azure AD ログイン)によってログインするのが標準仕様となる。
つまり、OSのユーザーでログインする仕様となっている。
インストール時の設定で「sa」でのログインも可能にする事ができる。
データーベースユーザー
基礎概念のユーザーと同じ。
SQL Serverではデータベース配下にユーザーが作られるので、一つのユーザーが複数データベースに跨いでアクセスできない。
ロール
ロールの構成は複雑であるが、インスタンスやデータベースを作成したときに、デフォルトでよく使う固定ロールが設定されていて、一からロールを作る必要が無い。
ロールはデフォルトで用意されたものが便利に使えて、あまり自作する必然性がない。
扱うユーザーにとっては、非常に楽な仕様でもある。
また、SQL Server のロールはサーバーロールを除いて、データベース配下に存在するので、複数データベースに渡って使うことはできない。
固定サーバーロール
インスタンス自体に最初から固定で付加されているロール。
固定データベースロール
データベース作成時に最初から固定で付加されているロール。
通常は、これをユーザーに紐付ける。
ユーザー定義ロール
これが基礎概念のロールに近い。
ユーザーが必要に応じて、アクセス権限を追加編集する事ができる。アプリケーションロールを含む。
PostgreSQLの場合
インスタンス
通常はサーバーにインスタンスは一つ作成する。
SQL Serverのように複数のインスタンスを作成することは可能である。
複数のバージョンのPostgreSQLをインスタンスを分けて共存させることもできる。
その場合、ポート番号はインスタンスごとに別の番号を振ることになる。
データベース
インスタンスの中に、複数のデータベースを作成することができる。
基礎概念と同じ。
スキーマ
データベースの中に、複数のスキーマを作成することができる。
基礎概念と同じ。
ユーザー
Oracleと同様にインスタンスの配下にユーザーが作成される。
一つのユーザーが複数データベースを跨いでアクセスすることも可能だ。
ユーザーには、PostgreSQLへのログイン権限がある。
基礎概念と同じ。
ロール
PostgreSQLでは、ユーザーとロールは同じものである。
ログイン権限のあるものをユーザーと呼び、ログイン権限の無いものをロールと呼んでいる。
ユーザーを作れば、同一名称のロールが作られていると考えても良い。
Oracleと同様にインスタンスの配下にロールが作成される。
一つのロールを複数データベースに渡って使用することも可能だ。
基礎概念と同じ。
MySQL(MariaDB)の場合
インスタンス
MySQLエンジン本体がインスタンスとも言える。
一つのサーバーに一つしか、MySQL本体がインストールできないので、インスタンスは一つだけと考える。
1サーバー1インスタンスの関係になる。
データベースとスキーマ
データベースとスキーマは同一の概念となる。
SQLにはDATABASEもSCHEMAも存在するが、CREATE DATABASE でデータベースを作成すると、同一名のスキーマも作成される。
CREATE SCHEMA でスキーマを作成しても、同一名のデータベースを作成する。
ユーザー
MySQLのユーザーはインスタンスごとに管理されており、複数データベースに跨がって使用できる。
ロール
MySQLのロールもインスタンスごとに管理されており、複数データベースに跨がって使用できる。
ユーザーとロールの違いは大きくない、ほとんど同じと言っても良い。
MySQLでロールが追加されたのはMySQL8.0からであり、それ以前はユーザーだけで事足りていたので、ユーザー自体がロールのような機能を持っている。
ユーザーの持つ権限を、別のユーザーへ付与することもできる。
8.0から作られたロールは、機能制限されたユーザーに過ぎないと言っても言い過ぎではないと思う。
SQLiteの場合
インスタンス
SQLiteはアプリに組み込む部品として機能するように設計されたDBMSである。
他のDBMSのように、サービスやデーモンプログラムを常時起動するような仕組みにはなっていない。
SQLiteライブラリ部品として、アプリに組み込まれるか、sqlite3コマンドでデータベースを操作する。
よって、常駐プログラムであるインスタンスは存在しない。
「入れ物」という意味でのインスタンスという概念も無い。
データベース
SQLiteのデータベースは、ファイルである。
sqlite3 コマンドの第１パラメーターにデータベース名を指定してやると、自身がデータベースであるファイルが作成される。
データベースへのアクセスも、sqlite3 コマンドの第１パラメーターにデータベース名を指定して行う。
このデータベースファイルの中に、テーブルなどのオブジェクトとデータが入る。
スキーマ
スキーマという概念は存在しない。
ユーザーとロール
SQLite は、先に説明したように「アプリに組み込む部品」であるため、SQLiteデータベースを複数人で扱うことは、想定していない。
機能としては、複数同時読み込みには対応しているが、複数同時更新には対応していない。
データベースを扱うユーザーの数も少人数(通常は一人)になることを想定しているので、ユーザーの管理機能自体が必要無い。
ロールも同様の理由で存在しない。
主流DBMSの違いのまとめ
Oracle
サーバーにインスタンスを複数作れるが、インスタンスが重いので普通は一つしか作らない。
インスタンスとデータベースが同義。
ユーザーとスキーマが同義。
ロールはインスタンス配下で、複数スキーマで使用可能。
SQL Server
サーバーに複数インスタンスを作成できる。
インスタンスの中に、複数データベースを作成できる。
データベースの中に、複数スキーマを作成できる。
ユーザーはログインユーザーとデータベースユーザーで異なる。
データベースユーザーはデータベース配下にあるので、複数データベースに跨がって使用できない。
ロールは、サーバーロールと固定データベースロールとユーザー定義ロールがある。
ユーザー定義ロールは自由に作成・変更できる。他は変更できない。
固定データベースロールとユーザー定義ロールはデータベース配下にあるので、複数データベースに跨がって使用できない。
PostgreSQL
サーバーに複数インスタンスを作成できるが、原則として一つしか作らない。ポート番号がインスタンスごとに別になる。
インスタンスの中に、複数データベースを作成できる。
データベースの中に、複数スキーマを作成できる。
ユーザーはインスタンス配下にあるので、複数データベースに跨がって使用できる。
ロールとユーザーは同じもの。ロールにはログイン権限がないだけ。
MySQL(MariaDB)
インスタンスは一つだけ。MySQL本体がインスタンスのようなもの。
データベースとスキーマが同義。
ユーザーはインスタンス配下にあるので、複数データベースに跨がって使用できる。
ロールの機能はユーザーとほとんど同じで、機能制限されたユーザーのようなもの。
逆にユーザーをロールのように使用できる。
SQLite
アプリに組み込む部品として作られたDBMSなので、インスタンスという概念が無い。
データベースはファイルである。
スキーマの概念はない。
複数人で使用するわけではないので、ユーザーやロールの概念は必要無いため、存在しない。
複数同時更新には対応していない。複数同時読取りは可能。
終わりに
あるDBMSのユーザーが別のDBMSを使用するとき、一番困るのが、インスタンスとデータベースとスキーマとユーザーとロールの仕様の違いが分からないことだ。
しかも、この解説資料は、一カ所にまとまったものが無いので、調べるのが意外に大変だ。
最近は、この辺の資料を纏めているサイトやブログも増えてきたが、まだ充分とは言えないと思っている。
自分も、SQL ServerからOracleやPostgreSQLへ移ったとき、かなり苦労したタイプだ。
この知識自体は大した情報量ではないので、上手く纏まったサイトがあれば、事足りる程度の知識なのだが、なぜか世間には不親切なマニュアルしか存在しない。
DBMSに限らず、IT製品全般に要点を簡潔にまとめた説明資料が少なすぎる印象だ。
締めが「愚痴」になってしまった。
今回は、自分自身の備忘録として、この記事をアップロードしておく。
しばらく、使っていないとすぐに忘れるから。
この記事が、お役に立てば幸いだ。
