Oracle Database 12c(12.1.0.2.0)でSQL Developerを使い、PDBデータベースの作成とユーザー、テーブルの作成など

通勤時間を利用して『アキラとあきら(著:池井戸潤)』、『検察側の罪人(著:雫井脩介)』、『サラバ!(著:西加奈子)』 読了しました。小説は面白いですね。

本当は技術書を読まないといけないんですが...。技術書はお値段も結構しますしね...。

 

今回は、Oracle Database 12cにデータベース(PDB)を作成してみたいと思います。

結論から申し上げますと、どうもデータベース管理者の設定など上手くいってないようなので、そのへんをご了承いただける方のみご照覧ください。

⇩  SQL Developerの導入については下記記事をご参考ください。 

ts0818.hatenablog.com

 

⇩  参考にさせていただくサイトはこちら。 

第1回 プラガブル・データベースの作成

第2回 表領域やユーザー、表の作成

SQL Developerで接続

検索で、「SQL Developer」を探して、クリックします。

f:id:ts0818:20171209135542j:plain

「接続」を選択した状態で右クリックし、「接続の作成(A)...」をクリック。

f:id:ts0818:20171209135724j:plain

接続情報を入力し、「接続(O)」をクリック。

f:id:ts0818:20171209135948j:plain

データベースサーバに接続されました。

f:id:ts0818:20171209140248j:plain

マルチテナント・コンテナ・データベース(CDB)への接続

「表示(V)」>「DBA」を選択。

f:id:ts0818:20171209143802j:plain

「DBA」のビューが表示されるので、「接続」を選択した状態で右クリックし、「接続の追加(A)...」を選択します。

f:id:ts0818:20171209144121j:plain

先ほど接続したデータベースサーバを選択し、「OK」。

f:id:ts0818:20171209144337j:plain

「コンテナ・データベース」内にあるのがCDB内のPDBということらしいです。

f:id:ts0818:20171209144814j:plain

 

プラガブル・データベース(PDB)の作成

データベースを作成していきたいと思います。 「コンテナ・データベース」を選択した状態で右クリックし、「プラカブル・データベースの作成(B)...」を選択。

f:id:ts0818:20171209151313j:plain

「データベース名」、「管理名」、「管理パスワード」を入力します(名前は自分で任意のものを決めていいみたいです。)。「ファイル名の変換」で、「カスタム名」を選択。

f:id:ts0818:20171209152603j:plain

「ターゲット・ファイル」の名前を変更していきます。

f:id:ts0818:20171209152619j:plain

変更前

C:\APP\ORACLE12C\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF.clone
C:\APP\ORACLE12C\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF.clone
C:\APP\ORACLE12C\ORADATA\ORCL\PDBSEED\PDBSEED_TEMP012017-11-10_11-16-32-PM.DBF.clone

変更後

C:\APP\ORACLE12C\ORADATA\ORCL\CRMTEST\SYSTEM01.DBF
C:\APP\ORACLE12C\ORADATA\ORCL\CRMTEST\SYSAUX01.DBF
C:\APP\ORACLE12C\ORADATA\ORCL\CRMTEST\CRMTEST_TEMP012017-11-10_11-16-32-PM.DBF

編集したら、隣の「SQL」タブをクリックします。データベースの作成なんですが、

f:id:ts0818:20171209155519j:plain

 

ROLES句を追加することにより、PDBの管理ユーザーにDBAロールを付与します。
PDBの管理ユーザーにはデフォルトでPDB_DBAロールが割当られます。しかし、PDB_DBAロールには、最初は権限が何も含まれていないためDBAロールをPDB_DBAロールに含めてPDBの管理を行えるように設定します。

第1回 プラガブル・データベースの作成

ということで、SQL文を編集していきます。

変更前

CREATE PLUGGABLE DATABASE CRM_TEST ADMIN USER Admin IDENTIFIED BY password
 FILE_NAME_CONVERT=(
  'C:\APP\ORACLE12C\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF', 'C:\APP\ORACLE12C\ORADATA\ORCL\CRMTEST\SYSTEM01.DBF',
  'C:\APP\ORACLE12C\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF', 'C:\APP\ORACLE12C\ORADATA\ORCL\CRMTEST\SYSAUX01.DBF',
  'C:\APP\ORACLE12C\ORADATA\ORCL\PDBSEED\PDBSEED_TEMP012017-11-10_11-16-32-PM.DBF', 'C:\APP\ORACLE12C\ORADATA\ORCL\CRMTEST\CRMTEST_TEMP012017-11-10_11-16-32-PM.DBF'
  )
 STORAGE UNLIMITED

変更後

CREATE PLUGGABLE DATABASE CRM_TEST ADMIN USER Admin IDENTIFIED BY password ROLES=(DBA)
 FILE_NAME_CONVERT=(
  'C:\APP\ORACLE12C\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF', 'C:\APP\ORACLE12C\ORADATA\ORCL\CRMTEST\SYSTEM01.DBF',
  'C:\APP\ORACLE12C\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF', 'C:\APP\ORACLE12C\ORADATA\ORCL\CRMTEST\SYSAUX01.DBF',
  'C:\APP\ORACLE12C\ORADATA\ORCL\PDBSEED\PDBSEED_TEMP012017-11-10_11-16-32-PM.DBF', 'C:\APP\ORACLE12C\ORADATA\ORCL\CRMTEST\CRMTEST_TEMP012017-11-10_11-16-32-PM.DBF'
  )
 STORAGE UNLIMITED

SQLの編集後、「適用(A)」ボタンをクリックして作成を実行します。

f:id:ts0818:20171209155537j:plain

しばらく処理中の表示が出た後、「成功」というダイアログが表示されればOK。「OK」をクリックします。

f:id:ts0818:20171209155653j:plain

CRM_TEST」データベースが作成されました。

f:id:ts0818:20171209155952j:plain

 

プラガブル・データベース(PDB)への接続

PDB2を作成しましたが、作成した段階ではまだPDB2はオープンされて無く、接続ができません。まずは、PDB2に接続できるようにPDB2をオープンします。

第1回 プラガブル・データベースの作成

ということで、設定していきます。

作成したPDB(自分の場合ですと、『CRM_TEST』のことになります)を選択した状態で右クリックし、「状態の変更(D)...」を選択。

f:id:ts0818:20171209160645j:plain

⇧  「OPEN_MODE」が「MOUNTED」ってなっていると接続できないみたいです。

 

「状態のオプション」が「READ WRITE」になっていればOK。「適用(A)」を選択。

f:id:ts0818:20171209160904j:plain

「OPEN_MODE」が「READ WRITE」に変わりました。

「接続」ビューの「接続」を選択した状態で右クリックし、「接続の作成(A)...」を選択。

f:id:ts0818:20171209161307j:plain

「ユーザー名」「パスワード」はPDBのデータベース作成時のものを指定。「接続タイプ(Y)」は「基本」、「ロール」は「デフォルト」、「ホスト名(A)」はデータベースサーバ接続と同じものでいけました。「サービス名(E)」は「作成したPDB(プラガブル・データベース)名の小文字」でOKみたいです。 (自分の場合ですと、『crm_test』になります。)

「接続(Q)」をクリック。

f:id:ts0818:20171209161834j:plain

PDB(『CRM_TEST』)が、接続名『crmDBTest』として、「接続」に追加されました。

f:id:ts0818:20171209162514j:plain

 

プラガブル・データベース(PDB)へ表領域の作成

新規に作成したPDBは、system表領域や、temp表領域など基本的な表領域しか作成されません。そこで通常はユーザーが使用する表領域を作成する必要があります。

第2回 表領域やユーザー、表の作成 

ということで、USERS表領域を、先ほど作成したPDB(自分の場合ですとCRM_TEST)に作成します。

SQL ワークシート」を閉じてる場合は、「接続」ビューのデータベースを選択した状態で右クリックし、「SQLワークシートを開く(R)」を選択。

f:id:ts0818:20171209163509j:plain

ワークシートに、USERS表領域を作成するためのSQLを作成していくわけですが、表領域は、Oracle Dtabase 12cをインストールした場所によって変わると思いますが、自分の場合ですと『C:¥app¥oracle12c¥oradata¥orcl¥CRMTEST』フォルダ内に作られるようです。

f:id:ts0818:20171209193850j:plain

SQLを実行します。DATAFILEのパスはご自分の環境に合わせてください。

CREATE TABLESPACE USERS
    DATAFILE 'C:\app\oracle12c\oradata\orcl\CRMTEST¥users01.dbf'
    SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

f:id:ts0818:20171209195008j:plain

⇧  スクリプトの出力にUSERS表領域が作成されたと表示されました。

⇩  エクスプローラーで確認すると、USERS表領域が作成されています。

f:id:ts0818:20171209195139j:plain

 

作成したプラガブル・データベース(PDB)にユーザーを作成

SQLで直接、ユーザーを作成することはできますが、DBAビューから、作成したPDBデータベースに接続すると、GUIでユーザーの作成を行えるようです。

DBAビューの「接続」を選択した状態で右クリックし、「接続の追加(A)...」を選択。

f:id:ts0818:20171209200121j:plain

作成したPDBデータベースを選択します。「OK」。

f:id:ts0818:20171209201820j:plain

DBAビューに、作成したPDBデータベースが追加されました。

f:id:ts0818:20171209201906j:plain

作成したPDBデータベースの左横の「+」ボタンをクリックし、「セキュリティ」>「ユーザー」を選択した状態で右クリックし、「新規作成(B)...」を選択します。

f:id:ts0818:20171209201954j:plain

「ユーザーの作成」が表示されるので、『ユーザー』タブでは、「ユーザー名」「新規パスワード」「パスワード確認」に任意のものを入力し、「デフォルト表領域」に「USERS」、「一時表領域」に「TEMP」を指定します。

次に「付与されたロール」タブをクリックします。

f:id:ts0818:20171209202217j:plain

「付与されたロール」タブでは、「CONNECT」「RESOURCE」の2つにチェックします。

CONNECT 

CREATE SESSIONシステム権限を提供します。

このロールは、Oracle Databaseの以前のリリースとの互換性を考慮して用意されています。このロールに組み込まれている権限は、DBA_SYS_PRIVSデータ・ディクショナリ・ビューを問い合せることで判別できます。

注意: このロールに依存せずに、データベース・セキュリティ用に独自のロールを設計することをお薦めします。このロールは、将来のリリースのOracle Databaseでは自動生成されない可能性があります。

関連項目: DBA_SYS_PRIVSビューの詳細は、Oracle Databaseリファレンス』を参照してください。

権限とロール認可の構成

RESOURCE 

CREATE CLUSTERCREATE INDEXTYPECREATE OPERATORCREATE PROCEDURECREATE SEQUENCECREATE TABLECREATE TRIGGERCREATE TYPEの各システム権限を提供します。

RESOURCEUNLIMITED TABLESPACEシステム権限を提供しないことに注意してください。

このロールは、Oracle Databaseの以前のリリースとの互換性を考慮して用意されています。このロールに組み込まれている権限は、DBA_SYS_PRIVSデータ・ディクショナリ・ビューを問い合せることで判別できます。

注意: このロールに依存せずに、データベース・セキュリティ用に独自のロールを設計することをお薦めします。このロールは、将来のリリースのOracle Databaseでは自動生成されない可能性があります。

関連項目: DBA_SYS_PRIVSビューの詳細は、Oracle Databaseリファレンス』を参照してください。

権限とロール認可の構成

 

次に「割当て容量」タブを選択します。

f:id:ts0818:20171209202620j:plain

f:id:ts0818:20171209202730j:plain

「USERS」にチェックを入れ、「適応(A)」をクリック。

f:id:ts0818:20171209202813j:plain

SQLが実行されるので、「OK」をクリックします。

f:id:ts0818:20171209202917j:plain

作成したPDBデータベースに新しくユーザー(自分の場合ですと「TS0818」)が追加されました。

f:id:ts0818:20171209203000j:plain

 

作成したプラガブル・データベース(PDB)に作成したUSERS領域に作成したユーザー(スキーマ)にテーブルを作成...できない

Oracle Databaseでは、ユーザーのことをスキーマと呼んでるみたいですね。スキーマにテーブルを作成していくのが一般的なんですかね。

接続ビューで「表(フィルタ対象)」を選択した状態で右クリックし、「新規の表(I)...」を選択。

f:id:ts0818:20171209225823j:plain

表の作成で「スキーマ(S):」の欄でスキーマ(追加したユーザー)が選択候補に出てこないという...毎度毎度すんなりいきませんな。

f:id:ts0818:20171209225846j:plain

ロールの各説明の中の一文で、「このロールに組み込まれている権限は、DBA_SYS_PRIVSデータ・ディクショナリ・ビューを問い合せることで判別できます。」とあったのが気になりました。

というわけで、「DBA_SYS_PRIVSデータ・ディクショナリ・ビュー」って何なのさ?と思ったので調べてみました。

 

データ・ディクショナリ・ビュー(静的ディクショナリ)とは?

www.shift-the-oracle.com

⇧  上記サイト様によると、 

ユーザー、権限、領域、制約などの 根幹的な情報を保持する読み取り専用の表とビュー 
データディクショナリ表は、DDLなどが実行されスキーマ構造が変更されるなどの行為によって自動的に更新される。 
静的ディクショナリともよばれる ⇔ 動的パフォーマンス・ビュー 
ディクショナリ表へは、直接アクセスできない。以下のビューを経由してアクセスを行う。

ALL_〜
ユーザーがアクセスできる情報(他のスキーマ含む)
USER_〜
ユーザーのスキーマの情報
DBA_〜
データベース全体に関連する情報(SELECT ANY TABLE、SELECT ANY DICTIONARY権限が必要:DBAロール)

実際に、「DBA_SYS_PRIVS」の説明を見ると、

データ型 NULL許容 説明
GRANTEE VARCHAR2(128)   権限受領者名、つまり、権限を受け取るユーザーまたはロール
PRIVILEGE VARCHAR2(40)   システム権限
ADMIN_OPTION VARCHAR2(3)   ADMIN OPTION付きで付与されたかどうか(YES | NO)
COMMON VARCHAR2(3)   権限がどのように付与されたかを示します。可能な値は次のとおり。
  • YES: 権限が共通して付与された場合(CONTAINER=ALLが使用された場合)
  • NO: 権限がローカルで付与された場合(CONTAINER=ALLが使用されなかった場合)

⇧   「CONNECT」「RESOURCE」などのロールにどような権限が含まれてるか確認できるということなんですかね。

 

そして、「CONNECT」「RESOURCE」ロールは非推奨ってなってますね... 

CONNECTロール、RESOURCEロールはOracle7の時に考えられた権限セットで、Oracle8ではこのロールを使うことを推奨していない。Oracle8以降でデータベースを構築する場合、CONNECTロール、RESOURCEロールを使わずに、用途別のロールを作成するべきである。
(CONNECTロール、RESOURCEロールは下位互換性のために提供されている)

 Oracle入門 第5章補足

⇧  肝心の代替案の具体例を載せて欲しいですね。

オラクルDB担当エンジニア直伝のユージさん&ギョータさん は普通に「CONNECT」「RESOURCE」ロール使っていたんですが...。

どちらにしろ、「表の作成」で新しく作成したユーザー(スキーマ)が選択できないということは、「CONNECT」「RESOURCE」ロールが有効になってない感じですかね。

ということで、一旦、スキーマとかについて調べ直し。

 

データベース・オブジェクト = スキーマ・オブジェクト、つまり?

スキーマ・オブジェクトの管理 によりますと、 

スキーマ・オブジェクトについて

スキーマ

スキーマはデータベース・オブジェクトの集合です。スキーマはデータベース・ユーザーによって所有され、ユーザー名と同じ名前を共有します。スキーマ・オブジェクトは、ユーザーによって作成される論理構造です。表や索引などのいくつかのオブジェクトはデータを保持します。ビューやシノニムなどのその他のオブジェクトは定義のみで構成されます。

⇧  よく分かりませんな... 

スキーマ・オブジェクトの命名

データベースのすべてのオブジェクトは1つのスキーマに属し、スキーマ内に一意の名前を持っています。異なるスキーマにある場合、複数のデータベース・オブジェクトは同じ名前を共有できます。スキーマ名を使用して、確実にオブジェクトを参照できます。たとえば、hr.employeesは、hrスキーマemployeesという名前の表を参照します。(employees表はhrによって所有されます。)データベース・オブジェクトおよびスキーマ・オブジェクトという用語は同じ意味で使用されます。

データベース・オブジェクトを作成するときには、必ず任意のスキーマ内に作成する必要があります。その1つの方法として、スキーマを所有するユーザーとしてデータベースにログインしてからオブジェクトを作成します。

⇧  この説明を自分なりに解釈してみた結果、

『データベース・オブジェクト = スキーマ・オブジェクト 』

    ↓

スキーマ・オブジェクト = スキーマ.表 』 

    ↓

スキーマ.表 = スキーマ.テーブル』 

    ↓

スキーマ.テーブル = データベース.テーブル』 

つまり、「スキーマ(ユーザー)= データベース」が、PDB(プラカブル・データベース)の中に、スキーマの数だけできるってことですかね?

スキーマ・オブジェクト管理権限について

データベース管理者(DBA)として、所有するスキーマおよびその他のスキーマスキーマ・オブジェクトを作成、変更および削除できます。この目的のために、データベース管理者はDBAロールを付与されているユーザーとして定義されます。これにはSYSおよびSYSTEMユーザーがデフォルトで含まれます。

DBAロールは、管理タイプ・アクセスを必要とするユーザーのみに付与することをお薦めします。

他のユーザーに、DBA権限を付与することなくスキーマ・オブジェクトを管理させることができます。たとえば、通常は、アプリケーション開発者がスキーマ・オブジェクトを作成、変更、削除できます。そのため、RESOURCEロールをアプリケーション開発者に付与します。

⇧   この説明を自分なりに解釈すると、『データベース管理者(DBA)』には、所有するスキーマおよびその他のスキーマで、オブジェクトの作成、変更および削除ができるとありますね。

f:id:ts0818:20171223114435j:plain

PDB(プラカブル・データベース)を作成した際に、管理者として「ADMIN」を作っていて、『DBA』ロールを追加した気がするんですが、追加されてないっぽいですね。

ただ、追加してみたものの、

f:id:ts0818:20171223134650j:plain

 

f:id:ts0818:20171223134712j:plain

f:id:ts0818:20171223134749j:plain

状況は変わらず、「表の作成」のところで、作成したスキーマの選択ができない。

f:id:ts0818:20171223135125j:plain

 

作成したスキーマで、作成したPDBにログイン

致し方ないので、作成したスキーマで 、作成したPDBにログイン。一旦、作成したPDBへの接続を「切断(M)」します。

f:id:ts0818:20171223141538j:plain

そしたら、「接続(L)」で作成したPDBに再接続していきます。

f:id:ts0818:20171223141631j:plain

作成したスキーマで、ログインします。

f:id:ts0818:20171223141809j:plain

「新規の表(T)」を選択。

f:id:ts0818:20171223141904j:plain

作成したスキーマが選択できるので、「詳細(V)」のチェックボックスにチェックを入れます。

f:id:ts0818:20171223143145j:plain

「名前(M):」に「テーブル名」、『列プロパティ』の部分でテーブルのカラムの情報を定義し、 f:id:ts0818:20171223143637j:plain で追加していけます。データ型のタイプは全部『VARCHAR2』にしました。「OK」でテーブルが作成できます。

f:id:ts0818:20171223143255j:plain

作成されたので、 

f:id:ts0818:20171223144054j:plain

再度、PDBへの接続を「切断(M)」して、「接続(L)」でデータベース管理者でログイン。

f:id:ts0818:20171223145404j:plain

「他のユーザー」で、

f:id:ts0818:20171223145527j:plain

作成したスキーマにテーブルが作成されているのが確認できました。

f:id:ts0818:20171223145602j:plain

 

データベース管理者で、他のスキーマへテーブル作成できないと不便ですね。

とりあえず、今回はこのへんで。次は、作成したPDBのテーブルにJava側から接続していければいいですね。

 

 

番外編:新しく作成したスキーマPDBに接続できないとき

もし、新しく作成したスキーマPDBに接続できないときは、「ユーザーのロック解除(F)...」を選択し、

f:id:ts0818:20171223153525j:plain

「適用(A)」を選択。

f:id:ts0818:20171223153906j:plain

「OK」。

f:id:ts0818:20171209202917j:plain

 

それでもダメなときは、「編集(A)...」から、

f:id:ts0818:20171223154321j:plain

パスワードを設定しなおしたりすればいけるかもしれません。

f:id:ts0818:20171223154354j:plain

 

参考

Oracle Databaseへの接続および検索