Oracleのユーザーとスキーマは何が違う？ CDB・PDBのユーザー一覧を確認するSQL

こんにちは、おうどんです🍜

Oracleの「ユーザー一覧」と「スキーマ一覧」。

名前が違うんだから、別々の一覧なんだよね？
でも、出てくる名前は似ている。というか、同じだったりする。

……そのへん、もう少し分かりやすくしてくれてもいいのでは。。。

今回は、このモヤモヤを整理して、確認用のSQLまで置いておきます。対象はOracle Database 19c。未来のおうどん用のメモも兼ねています。忘れる自信はあるので。。。

ユーザーはアカウント、スキーマはそのユーザーが所有するオブジェクトのまとまり

Oracleでは、ユーザーに同名のスキーマが対応します。ユーザーは権限や認証を考えるときの単位。スキーマは、そのユーザーが所有する表やビューなどを考えるときの単位です。

例えば、架空のユーザー APP_SAMPLE が ORDERS 表を所有していれば、表は APP_SAMPLE.ORDERS と表せます。

ただし、「ユーザーがある」と「そのユーザーが表を持っている」は別です。オブジェクトを持たないユーザーもいます。また、認証を持たず直接ログインできないスキーマ専用アカウントもあります。「ユーザー＝必ずログインできる」とは限りません。Oracle公式：CREATE USER

なので、設計書の「スキーマ一覧」が何を載せる資料なのか、最初に確認しておくと比較しやすくなります。

• アカウントを全部載せるのか。
• 業務用として管理しているものだけなのか。
• オブジェクトを所有しているものだけなのか。

ここが違うと、件数も変わります。

片方は全員集合、もう片方は選抜メンバー。それを比べて「人数が合わない！」となっても、そりゃそうなんですよね。SQLを書く前に、名簿の参加条件をそろえましょう。

まず接続先を確認する

SQL*Plusで、承認された参照権限を持つアカウントから実行します。以下のSQLは確認用で、ユーザー作成や設定変更は行いません。実DBでの動作検証は未実施です。

show con_name

一覧を採ったあとに「別のPDBだった」となると、比較作業がおかわりになります。うどんなら嬉しいけど、これは遠慮したい。。。

なので最初に接続先。地味ですが、ここは飛ばさずに。

接続中のコンテナのユーザーを確認する

set linesize 240
set pagesize 100
set trimspool on
column username format a40
column account_status format a24
column common format a6
column oracle_maintained format a17

select username,
       account_status,
       common,
       oracle_maintained
from dba_users
order by username;

ACCOUNT_STATUS はアカウントの状態、COMMON は共通ユーザーかどうかを示します。共通ユーザーの情報は、CDBルート側でも確認します。Oracle公式：DBA_USERS

ORACLE_MAINTAINED = ‘N’ は「業務ユーザーだけ」ではない

ORACLE_MAINTAINED は、Oracle提供のスクリプトによって作成・維持されるユーザーかを示す列です。

N に絞っても、それだけで業務用と確定するわけではありません。手動作成した管理用や検証用のユーザーも対象になり得ます。

棚卸しなら、まず絞り込まずに採って、設計書の管理対象と照らし合わせるのが分かりやすいと思います。件数が減ると仕事が進んだ気になりますが、必要なユーザーまで消えていたら困るんですよね。。。Oracle公式：DBA_USERS

CDBルートからコンテナ別に確認する

次は CDB$ROOT 接続で実行する例です。CDB_USERS と V$CONTAINERS の参照権限、および必要なコンテナへの可視性が前提です。

show con_name

column container_name format a30
column open_mode format a12

select con_id, name as container_name, open_mode
from v$containers
order by con_id;

spool oracle_user_inventory.txt

select u.con_id,
       c.name as container_name,
       u.username,
       u.account_status,
       u.common,
       u.oracle_maintained
from cdb_users u
left join v$containers c on c.con_id = u.con_id
order by u.con_id, u.username;

spool off

出力先はSQL*Plusを実行している環境のカレントディレクトリです。同名ファイルがある場合は上書きされるため、必要に応じてファイル名を変えてください。

CDB_USERS の結果は、権限と CONTAINER_DATA、PDBのオープン状態などに依存します。閉じたPDBや制限モードのPDBまで、このSQLだけで取得できたとは判断しないでください。RACでは接続インスタンスによる違いにも注意します。Oracle公式：CDB_* Views

取得後は、想定するPDBが結果に含まれているかを確認しましょう。足りない場合、一覧取得のためにいきなり設定を変えず、接続先・権限・PDBの状態を管理者と確認します。

一覧は「採れた」だけでは、まだ完成ではない

ユーザー一覧を採取したら、次は設計書が何を管理しているかと照合します。

名前だけを比較するのではなく、どのコンテナのユーザーかもセットにする。Oracle管理ユーザーを含めるかも揃える。

SQLが正常終了すると、ちょっと安心するんですよね。
よし、終わった。閉店！

……といきたいところですが、「必要な対象が全部入っているか」は、もうひと確認。SQLは、こちらの確認漏れまで察してはくれません。そこまで気が利いたら、もはや同僚です。

未来のおうどんが迷わないように、採取日時・接続先・抽出条件も一緒に残しておきます🍜