Oracleのユーザー管理まとめ。create user・alter user・drop userの構文とポイント

Oracle Databaseのユーザー管理に必要なSQLを、この記事1本にまとめて解説します。

作成: create user（パスワード認証・OS認証、表領域・プロファイルの指定）
変更: alter user（パスワード変更、ロック/アンロック、quota）
削除: drop user（cascadeの注意点）
確認: dba_users によるユーザー一覧

管理業務でそのまま使える実行例と、初心者がつまずきやすいポイントを合わせて紹介します。

ユーザー一覧の確認：dba_users

現在のユーザーとステータスは dba_users ビューで確認できます。

col USERNAME for a20
col ACCOUNT_STATUS for a20
select USERNAME, ACCOUNT_STATUS, DEFAULT_TABLESPACE
from dba_users order by USERNAME;

ACCOUNT_STATUS が LOCKED や EXPIRED になっているユーザーは、後述の alter user で解除できます。

ユーザー作成：create user

create user の基本構文は次のとおりです。

create user <ユーザー名> identified by <パスワード>
[default tablespace <デフォルト表領域>]
[temporary tablespace <デフォルト一時表領域>]
[profile <デフォルトプロファイル>];

パスワード認証でユーザー作成：identified by

create user test001 identified by test001;

OracleDatabase 11gからパスワードは大文字と小文字を区別します。 バージョンアップ移行時には注意が必要です。

また、パスワードがアルファベット以外の文字で始まる場合や、英数字・アンダースコア（_）・ドル記号（$）・番号記号（#）以外の文字を含む場合は、ダブルクォーテーションで括る必要があります。

OS認証でユーザー作成：identified externally

OS認証ユーザーは identified externally を指定して作成します（identified by externally ではないので注意してください）。

ユーザー名の先頭には初期化パラメータ OS_AUTHENT_PREFIX の値（デフォルトは ops$）を付けます。

Linux系OSの場合:

create user ops$test001 identified externally;

WindowsOSの場合は、ユーザー名をすべて大文字で記載し、Windowsのホスト名を付ける必要があります。

create user OPS$WINSERVER001\TEST001 identified externally;

デフォルト表領域を指定：default tablespace

create user test001 identified by test001
default tablespace TESTTBS;

指定しない場合はデータベースのデフォルト表領域が使われます。現在の設定は次のSQLで確認できます。

select PROPERTY_VALUE from DATABASE_PROPERTIES
where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE
--------------------------------------------------------------------------------
USERS

デフォルト一時表領域を指定：temporary tablespace

create user test001 identified by test001
temporary tablespace TEMPTBS;

指定しない場合はデータベースのデフォルト一時表領域が使われます。

select PROPERTY_VALUE from DATABASE_PROPERTIES
where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP

デフォルトプロファイルを指定：profile

create user test001 identified by test001
profile testprofile;

指定しない場合はデフォルトプロファイル DEFAULT が適用されます。DEFAULTプロファイルの内容は次のSQLで確認できます。

col RESOURCE_NAME for a30
col RESOURCE_TYPE for a30
col LIMIT for a30
select RESOURCE_NAME, RESOURCE_TYPE, LIMIT
from DBA_PROFILES where PROFILE = 'DEFAULT';

FAILED_LOGIN_ATTEMPTS（ログイン失敗の許容回数、デフォルト10回）や PASSWORD_LIFE_TIME（パスワード有効期間、デフォルト180日）など、運用に直結する項目が含まれます。プロファイルの変更方法はパスワードプロファイルを確認・変更する手順を参考にしてください。

ユーザー定義の変更：alter user

alter user はパスワードの変更やユーザーのロック/アンロックなど、管理業務には必須のSQLです。

alter user <ユーザー名> identified by <パスワード>
[default tablespace <デフォルト表領域>]
[temporary tablespace <デフォルト一時表領域>]
[profile <デフォルトプロファイル>];

パスワード変更：identified by

alter user test001 identified by newpassword;

パスワードの大文字小文字の区別・使用できる記号のルールは、create user と同じです。

ユーザーのロック/アンロック：account lock / unlock

ユーザーをロックするには account lock を指定します。データは保持するがアプリケーションから接続させたくないユーザーに使います。

alter user test001 account lock;

パスワードを期限切れにする場合は password expire を組み合わせます。

alter user test001 account lock password expire;

パスワード期限切れや規定回数のログイン失敗でロックされたユーザーは、account unlock で解除します。

alter user test001 account unlock;

デフォルト表領域・一時表領域・プロファイルの変更

create user と同じ句を alter user で指定します。

alter user test001 default tablespace TESTTBS;
alter user test001 temporary tablespace TEMPTBS;
alter user test001 profile TESTPROFILE;

ユーザーのオブジェクト作成後にデフォルト表領域を変更する場合、既存オブジェクトは自動では移動しません。 必要に応じてオブジェクトの移動も合わせて行いましょう。

表領域の使用容量の変更：quota

表領域ごとの使用容量の上限は quota ~ on 句で指定します。

-- 1GBに制限する
alter user test001 quota 1G on TESTTBS;

-- 無制限にする
alter user test001 quota unlimited on TESTTBS;

ユーザーの削除：drop user

drop user の基本構文は次のとおりです。

drop user <ユーザー名> [cascade];

ユーザーのみ削除

drop user test001;

ただし、所有するスキーマにオブジェクトが含まれているユーザーは、この形式では削除できません（ORA-01922が発生します）。先にオブジェクトを削除するか、次のcascadeを使います。

ユーザーとオブジェクトを合わせて削除：cascade

drop user test001 cascade;

ユーザーが所有するオブジェクトがすべて削除されます。 削除したオブジェクトを戻すにはバックアップからのリストアが必要になるため、実行前に対象ユーザーの所有オブジェクトを必ず確認しましょう。

select OBJECT_TYPE, count(*) from dba_objects
where OWNER = 'TEST001' group by OBJECT_TYPE;

まとめ

操作	SQL	注意点
一覧確認	`select ... from dba_users`	ACCOUNT_STATUSでロック状態を確認
作成	`create user ~ identified by ~`	11g以降パスワードは大文字小文字を区別
パスワード変更	`alter user ~ identified by ~`	記号を含む場合はダブルクォーテーション
ロック解除	`alter user ~ account unlock`	ログイン失敗の規定回数超過時に使用
削除	`drop user ~ [cascade]`	cascadeはオブジェクトごと削除される

ユーザー作成後の動作確認には、サンプルスキーマの作成手順（SCOTTスキーマの作成方法）も参考にしてください。