はじめに

Oracleの仕組みがよく分からなかったので調べつつ実際に触ってみれば分かるようになるだろうと思い、試してみました。
Dockerとsqlplusはインストールされていること前提で進めます。

何を触ったか

まず、Mac上でOracle Databaseをどうにかして触りたいなあ、あわよくばDockerとかでイメージが配布されていれば環境汚さずに簡単に触れるなあと思い調べてみました。
するとなんとドンピシャでありました。↓
https://github.com/oracle/docker-images

プロダクトがいろいろとありますが、今回はOracle Databaseを使います。
Dockerhubでイメージが配布されているような形ではないですが、Dockerfileをビルドすればイメージを生成できそうです。
なので今回はこのリポジトリを用いてイメージを生成し、Oracle DatabaseをローカルMac上に立ててみることにしました。

目標

今回はOracle Databaseを立ててユーザを作成し、ログインすることを目標としたいと思います。

前提知識

今回ユーザを作成する上で必要となるOracleの仕組みについて触れておきたいと思います。

マルチテナント

Oracle Database12cからデータベースレイヤーでマルチテナントを実現する機能が追加されました。
マルチテナントとは1データベースに複数データベースを構築するアーキテクチャのことで、これによりデータベースの管理を集約しやすくなり、管理コストの削減、効率的なリソースの使用が可能になります。
この機能の要になるのがCDBとPDBです。

(徹底解説！Oracle Database 12cのすべて Vol.1 | アシストより)

CDB

CDB(Container Database)はデータファイル以外のREDOログファイルや制御ファイルなどを管理する、いわゆる従来のデータベースを表すコンポーネントです。
CDBはデータベースに1つだけ存在します。

PDB

PDB(Pluggable Database)はデータファイルを管理するコンポーネントです。
アプリケーションから見るとデータベースの数だけPDBが存在します。

マルチテナントのメリット

以下のメリットが挙げられます。

CDBは1つだけなのでアップグレードやパッチ適用が1回で済む→運用面のコストが削減される
PDB同士が独立しているでスキーマ名の競合がなくなる
割り当てるリソースはCDBだけでよく、各PDBが共用で使用できるため、効率的にリソースを使える
PDBはCDBから簡単に取り外してコピーできるため、データ移行が簡単

ユーザ

マルチテナント機能により、ユーザの種類は2種類存在します。

共通ユーザ

CDB上に作成されるユーザです。後述するSYSやSYSTEMは共通ユーザに該当します。
CDBや全PDBにログインすることができます。
データベース管理者はこの共通ユーザでデータベースの起動停止、PDBの取り外し・取り付けなどのCDBに対する操作を行います。

ローカルユーザ

PDB上に作成されるユーザです。後述するPDBADMINはローカルユーザに該当します。
ローカルユーザはPDB上にスキーマを所有し、他PDBやルートにはログインできません。
アプリケーションのユーザはこのローカルユーザで作成します。

デフォルトで作成されるユーザ

SYSユーザ

SYSスキーマを持つ管理者ユーザです。
SYSスキーマにはデータディクショナリの実テーブルとビューが格納されています。
SYSユーザは全権限を持っています。また、SYSDBA権限が付与されており、この権限によりバックアップやリカバリを行うことができます。

SYSTEMユーザ

SYSTEMスキーマを持つ管理者ユーザです。
SYSTEMスキーマには管理者情報を表示するテーブルとビュー、及びOracle Databaseがオプションやツールで使う内部のテーブルとビューが格納されています。
SYSTEMユーザはSYSDBA権限は付与されておらず、バックアップとリカバリ、データベースアップグレードを除く全ての管理権限を持ちます。

権限

Oracle Databaseの権限にはシステム権限とオブジェクト権限の2種類が存在します。

システム権限

ユーザが特定のアクションや、特定のスキーマに対してアクションできるようにする権限のことです。
CREATE USERやCREATE TABLEなどのSQLが該当します。

オブジェクト権限

特定のスキーマに対して実行できる権限のことです。
SELECTやINSERTなどのSQLが該当します。

ロール

権限をグループ化したもので、ロールと権限は1:Nの関係です。
ロールはユーザやロールに付与することができ、複数単位での権限の付与を効率的に行うことができます。
Oracle Databaseはあらかじめ以下の3つのロールがデフォルトで用意されており、ユーザ作成後すぐに付与することができます。

ロール	内容
CONNECT	データベースに接続できるロール接続するユーザに付与する
RESOURCE	特定スキーマのスキーマオブジェクトの作成や削除ができるロールスキーマオブジェクトの作成が必要なユーザや開発者のみに付与するためのロール例えばCREATE TABLEは許可するがCREATE VIEWは許可しない
DBA	ユーザの作成・権限付与、ロールの作成・付与、スキーマでのスキーマオブジェクトの作成・変更・削除などあらゆるシステム権限（ただしデータベースインスタンスの起動・停止権限は含まれない）を持つロール SYSとSYSTEMユーザにデフォルトで付与されている

SQL

今回登場するSQLです。

-- 自分の所有する権限を確認
select * from user_sys_privs;

-- 自分の所有するロールを確認
select * from user_role_privs;

-- ロールに紐づくシステム権限を確認
select * from role_sys_privs where role = '<ロール名>';

-- ユーザに権限を付与
grant <権限> to <ユーザ>;

-- 権限をロールに付与
grant <権限> to <ロール>;

-- 接続先の確認
show con_name

-- 接続先の切り替え
alter session set container = <接続先SID>;

-- ユーザ作成
create user <ユーザ名> identified by <パスワード> default tablespace <デフォルト表領域> temporary tablespace <一時領域>;

Oracle Databaseイメージの生成

基本的にはREADMEの手順に従っていけばイメージを生成することができます。
今回自分はOracle Database 18c EEのイメージを生成しました。

リポジトリをクローンする

ローカルにリポジトリをクローンしましょう

$ git clone https://github.com/oracle/docker-images.git

Oracle Databaseのイメージをビルドするディレクトリに移動

$ cd OracleDatabase/SingleInstance

イメージを生成するシェルスクリプトを実行

移動した先のディレクトリ配下にイメージ生成シェルスクリプトがあるので実行します。
オプションについては以下のように-hを指定するとヘルプが表示されます。

$ ./buildDockerImage.sh -h

Usage: buildDockerImage.sh -v [version] [-e | -s | -x] [-i] [-o] [Docker build option]
Builds a Docker Image for Oracle Database.

Parameters:
   -v: version to build
       Choose one of: 11.2.0.2  12.1.0.2  12.2.0.1  18.3.0
   -e: creates image based on 'Enterprise Edition'
   -s: creates image based on 'Standard Edition 2'
   -x: creates image based on 'Express Edition'
   -i: ignores the MD5 checksums
   -o: passes on Docker build option

* select one edition only: -e, -s, or -x

LICENSE UPL 1.0

Copyright (c) 2014-2018 Oracle and/or its affiliates. All rights reserved.

今回バージョンは18.3.0のEnterprise Editionを使用するので以下のオプションを指定してイメージを生成します。
-iはMD5チェックサムを確認しないようにしないとビルドがうまくいかないようなので付けます。

$ ./buildDockerImage.sh -v 18.3.0 -e -i

実行して10分くらい待つと以下のメッセージがコンソールに表示されるはずです。
表示されればイメージが生成されています。

#########################
DATABASE IS READY TO USE!
#########################

確認（サイズでかいな。。）

$ docker images
REPOSITORY                        TAG                 IMAGE ID            CREATED             SIZE
oracle/database                   18.3.0-ee           137fe5c31621        5 days ago          8.53GB

Oracle Databaseの起動

イメージが生成できたら以下のフォーマットに従って実際にOracle Databaseを起動します。

$ docker run --name <container name> \
-p <host port>:1521 -p <host port>:5500 \
-e ORACLE_SID=<your SID> \
-e ORACLE_PDB=<your PDB name> \
-e ORACLE_PWD=<your database passwords> \
-e ORACLE_CHARACTERSET=<your character set> \
-v [<host mount point>:]/opt/oracle/oradata \ #データを永続化させるためにボリュームをマウントする
oracle/database:18.3.0-ee

環境変数は指定したい場合に設定します。デフォルト値は以下の通りです。

環境変数名	内容	デフォルト値
ORACLE_SID	SID	ORCLCDB
ORACLE_PDB	PDBの名前	ORCLPDB1
ORACLE_PWD	SYS, SYSTEM, PDBADMINユーザのパスワード	自動生成され、起動時にコンソールに表示されます
ORACLE_CHARACTERSET	データベース作成時の文字コード	AL32UTF8

今回以下のようにして起動します。
ちなみに、ボリュームのマウントは絶対パスで指定しないとうまくいかないです。(Dockerの基本かもしれない。。)

$ docker run --name study \
-p 1521:1521 -p 5500:5500 \
-e ORACLE_PWD=pass \
-v /Users/myname/oracle/study:/opt/oracle/oradata \
oracle/database:18.3.0-ee

実行すると起動ログが表示されます。
一番最初に環境変数で指定したSYS, SYSTEM, PDBADMINユーザのパスワードが表示されます。

ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN: pass

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 05-MAR-2019 13:26:35

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Starting /opt/oracle/product/18c/dbhome_1/bin/tnslsnr: please wait...

10分ほど待つと以下のメッセージが表示されます。
表示されればOracle Databaseは起動完了となります。

#########################
DATABASE IS READY TO USE!
#########################

ユーザを作成する

それではユーザを作成していきます。
今回はPDB上にローカルユーザを作成します。

PDBADMINユーザでログインする

まず、Oracle Database起動時に指定したパスワードを使ってPDBADMINユーザにログインしてみます。

$ sqlplus pdbadmin/pass@//localhost:1521/ORCLPDB1

ログイン後自分の接続先を確認します。

SQL> show con_name

CON_NAME
------------------------------
ORCLPDB1

PDBに接続していることが分かります。

ユーザ作成コマンドを打つ

以下SQLでユーザを作成します。

SQL> create user hoge identified by fuga default tablespace users temporary tablespace temp;

すると以下のように表示されました。

create user hoge identified by fuga default tablespace users temporary tablespace temp
*
ERROR at line 1:
ORA-01031: insufficient privileges

どうやら権限がないようです。

権限を確認してみる

PDBADMINユーザの権限を確認してみます。
自分の持っているシステム権限を確認します。

SQL> select * from user_sys_privs;

no rows selected

ロールを確認してみる

次にロールを確認してみます。
自分の持っているロールを確認します。

SQL> select * from user_role_privs;

USERNAME
--------------------------------------------------------------------------------
GRANTED_ROLE
--------------------------------------------------------------------------------
ADM DEL DEF OS_ COM INH
--- --- --- --- --- ---
PDBADMIN
PDB_DBA
YES NO  YES NO  NO  NO

PDBADMINはPDB_DBAというロールを持っていることが分かります。
そこでPDB_DBAロールに紐づくシステム権限を確認します。

SQL> select * from role_sys_privs where role = 'PDB_DBA';

ROLE
--------------------------------------------------------------------------------
PRIVILEGE                ADM COM INH
---------------------------------------- --- --- ---
PDB_DBA
SET CONTAINER                NO  NO  NO

PDB_DBA
CREATE PLUGGABLE DATABASE        NO  NO  NO

PDB_DBA
CREATE SESSION               NO  NO  NO

以上からCREATE USERのシステム権限は持っていないことが分かります。
PDBADMINユーザにCREATE USER権限を付与してみましょう。

SYSユーザでログインし直す

一旦ログアウトして再度SYSユーザでログインします。

$ sqlplus sys/pass@//localhost:1521/ORCLCDB as sysdba

接続先を確認します。

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

CDBのルートに接続されていることが分かります。
ただし、PDBADMINはPDB上に存在するため、接続先をPDBに切り替えます。

SQL> alter session set container = ORCLPDB1;

Session altered.

再度接続先を確認します。

SQL> show con_name

CON_NAME
------------------------------
ORCLPDB1

PDBに接続されていることが分かります。

PDBADMINユーザにGRANT権限を付与する

今回はPDBADMINユーザが持つPDB_DBAロールにCREATE USER権限を付与することで実現してみたいと思います。
その前にPDBADMINユーザ自体が権限付与をできるようにPDBADMINユーザに権限とロールのGRANT権限を付与します。

SQL> grant grant any privilege to PDBADMIN;

Grant succeeded.

SQL> grant grant any role to PDBADMIN;

Grant succeeded.

再度PDBADMINユーザでログインし直す

ログアウトして再度PDBADMINユーザでログインします。

$ sqlplus pdbadmin/pass@//localhost:1521/ORCLPDB1

まず自分に先程の権限が付与されいてることを確認します。

SQL> select * from user_sys_privs;

USERNAME
--------------------------------------------------------------------------------
PRIVILEGE                ADM COM INH
---------------------------------------- --- --- ---
PDBADMIN
GRANT ANY ROLE               NO  NO  NO

PDBADMIN
GRANT ANY PRIVILEGE          NO  NO  NO

GRANT権限が付与されていることが分かります。

PDB_DBAロールにCREATE USER権限を付与する

SQL> grant create user to PDB_DBA;

Grant succeeded.

実際に付与されたか確認します。

SQL> select * from role_sys_privs where role = 'PDB_DBA';

ROLE
--------------------------------------------------------------------------------
PRIVILEGE                ADM COM INH
---------------------------------------- --- --- ---
PDB_DBA
SET CONTAINER                NO  NO  NO

PDB_DBA
CREATE USER              NO  NO  NO

PDB_DBA
CREATE PLUGGABLE DATABASE        NO  NO  NO


ROLE
--------------------------------------------------------------------------------
PRIVILEGE                ADM COM INH
---------------------------------------- --- --- ---
PDB_DBA
CREATE SESSION               NO  NO  NO

CREATE USER権限が追加されたことが分かります。

CREATE USERでユーザを作成する

ここまででやっとユーザを作成できる準備が整いました。
再度ユーザ作成SQLを流します。

SQL> create user hoge identified by fuga default tablespace users temporary tablespace temp;

User created.

成功しました。

作成したユーザにロールを付与する

ユーザ作成後、そのユーザはまだログインする権限やテーブル作成権限などを持ち合わせていません。
そのためのロール(CONNECT, RESOURCE)を作成したユーザに付与します。

SQL> grant connect, resource to hoge;

Grant succeeded.

作成したユーザでログインする

作成したhogeユーザでログインしてみます。

$ sqlplus hoge/fuga@//localhost:1521/ORCLPDB1

成功しました。
先程付与したロールを持っているか確認してみます。

SQL> select * from user_role_privs;

USERNAME
--------------------------------------------------------------------------------
GRANTED_ROLE
--------------------------------------------------------------------------------
ADM DEL DEF OS_ COM INH
--- --- --- --- --- ---
HOGE
CONNECT
NO  NO  YES NO  NO  NO

HOGE
RESOURCE
NO  NO  YES NO  NO  NO

USERNAME
--------------------------------------------------------------------------------
GRANTED_ROLE
--------------------------------------------------------------------------------
ADM DEL DEF OS_ COM INH
--- --- --- --- --- ---

CONNECT, RESOURCEロールを持っていることが分かります。

まとめ

今回Dockerを使ってOracle Databaseを起動しました。
やはり手で触ってみて分かる部分が多いなと思いました。
また、環境を一切汚さずに試すことができるのでOracle Databaseを試してみたい・触ってみたい場合はおすすめです。

Dockerを使って環境を汚さずOracle Databaseを触ってみる