【Oracleの便利SQL】ユーザ情報・状態を確認するSQL

本記事では、ユーザの一覧やアカウントの状態を確認するSQLコマンドを、目的別に3つ紹介します。

3つのビューの違い

Oracleでユーザ情報を確認するビューは、dba_users、user_users、all_usersの3種類があります。

用途と必要な権限が異なるため、まず違いを押さえておきましょう。

全ユーザの一覧・アカウント状態の詳細情報を確認

全てのユーザの詳細情報を確認するSELECT文です。

set pages 300
set lines 300
col user_id for 999999999999999
col username for a30
col account_status for a32
col lock_date for a20
col expiry_date for a20
col default_tablespace for a20
col temporary_tablespace for a20
col profile for a20
SELECT
	user_id,
	username,
	account_status,
	lock_date,
	expiry_date,
	default_tablespace,
	temporary_tablespace,
	profile
FROM
	dba_users
ORDER BY
	user_id
;

dba_users を参照するにはDBAロール、またはSELECT ANY DICTIONARY / SELECT_CATALOG_ROLE権限が必要です。

出力されるカラムは以下のとおりです。

• user_id・・・ユーザに割り当てられた一意のID
• username・・・ユーザ名
• account_status・・・アカウントの状態（後述の値一覧を参照）
• lock_date・・・アカウントがロックされた日時（ロックされていない場合は値が入らない）
• expiry_date・・・パスワードの有効期限
• default_tablespace・・・デフォルト表領域
• temporary_tablespace・・・一時表領域
• profile・・・適用されているプロファイル名

account_status列の値の一覧は、以下の通りです。

• OPEN・・・アカウントが正常に使用可能な状態
• EXPIRED・・・パスワードの有効期限切れの状態（PASSWORD_LIFE_TIME の経過、または ALTER USER … PASSWORD EXPIRE コマンドの実行による）
• EXPIRED(GRACE)・・・パスワード有効期限を過ぎたが、猶予期間（PASSWORD_GRACE_TIME）内の状態（ログイン時に ORA-28002 警告が表示される）
• LOCKED・・・ALTER USER … ACCOUNT LOCK コマンドによる手動ロック、または FAILED_LOGIN_ATTEMPTS 超過によるロック状態（PASSWORD_LOCK_TIME が UNLIMITED の場合は自動解除されない）
• LOCKED(TIMED)・・・ログイン失敗回数超過による一時的なロック状態（PASSWORD_LOCK_TIME の経過後に自動解除される）
• EXPIRED & LOCKED・・・パスワード期限切れかつロックされた状態
• EXPIRED(GRACE) & LOCKED・・・猶予期間中のパスワード期限切れかつロックされた状態
• EXPIRED & LOCKED(TIMED)・・・パスワード期限切れかつログイン失敗超過による一時ロック状態
• EXPIRED(GRACE) & LOCKED(TIMED)・・・猶予期間中のパスワード期限切れかつログイン失敗超過による一時ロック状態
• OPEN & IN ROLLOVER・・・パスワード・ロールオーバー期間中で、旧パスワードと新パスワードのどちらでもログイン可能な状態
• EXPIRED & IN ROLLOVER・・・パスワード・ロールオーバー期間中かつパスワード期限切れの状態
• LOCKED & IN ROLLOVER・・・パスワード・ロールオーバー期間中かつロックされた状態
• EXPIRED & LOCKED & IN ROLLOVER・・・パスワード・ロールオーバー期間中かつパスワード期限切れかつロックされた状態
• LOCKED(TIMED) & IN ROLLOVER・・・パスワード・ロールオーバー期間中かつログイン失敗超過による一時ロック状態
• EXPIRED & LOCKED(TIMED) & IN ROL・・・パスワード・ロールオーバー期間中かつパスワード期限切れかつログイン失敗超過による一時ロック状態

IN ROLLOVERを含む状態は、Oracle Database 19.12以降で導入されたパスワード・ロールオーバー機能（PASSWORD_ROLLOVER_TIME）が有効な場合に表示されます。

現行ユーザ(自分自身)の情報を確認

現在の接続しているユーザ自身の情報を確認するSELECT文です。

特別な権限なしで実行できますので、自分自身のアカウント状態のみを確認したいときに便利です。

set pages 300
set lines 300
col user_id for 999999999999999
col username for a30
col account_status for a32
col expiry_date for a20
col password_change_date for a20
col default_tablespace for a20
col temporary_tablespace for a20
col common for a5
col oracle_maintained for a3
SELECT
	user_id,
	username,
	account_status,
	expiry_date,
	password_change_date,
	default_tablespace,
	temporary_tablespace,
	common,
	oracle_maintained
FROM
	user_users
ORDER BY
	user_id
;

出力されるカラムは以下のとおりです。

• user_id・・・ユーザに割り当てられた一意のID
• username・・・ユーザ名
• account_status・・・アカウントの状態
• expiry_date・・・パスワードの有効期限
• password_change_date・・・最後にパスワードを変更した日時
• default_tablespace・・・デフォルト表領域
• temporary_tablespace・・・一時表領域
• common・・・CDB環境で全PDB共通のユーザかどうか
• oracle_maintained・・・Oracleが管理する組み込みユーザかどうか

全ユーザの簡易情報を確認

全てのユーザの簡易情報を確認するSELECT文です。

特別な権限なしで実行できますが、account_status列などの運用情報が一部含まれていません。

set pages 300
set lines 300
col user_id for 999999999999999
col username for a30
col created for a20
col common for a5
col oracle_maintained for a3
SELECT
	user_id,
	username,
	created,
	common,
	oracle_maintained
FROM
	all_users
ORDER BY
	user_id
;

出力されるカラムは以下のとおりです。

• user_id・・・ユーザに割り当てられた一意のID
• username・・・ユーザ名
• created・・・ユーザが作成された日時
• common・・・CDB環境で全PDB共通のユーザかどうか
• oracle_maintained・・・Oracleが管理する組み込みユーザかどうか

【Oracleの便利SQL】シリーズ記事