[OracleDB] ユーザー定義変更の「alter user」文とポイントを紹介

こんにちは！シミダイ(@shimidai2100)です。

Oracle Databaseのユーザーの定義変更SQL「alter user」とそのポイントを紹介します。

「alter user」はパスワードの変更やユーザーのロック/アンロックなど管理業務には必須の作業になります。

この記事では初心者向けに「alter user」を実施する時の重要なポイントも合わせて紹介します。

この記事の内容

パスワードの変更・ユーザのロック/アンロック
デフォルトの表領域・一時表領域・プロファイルの指定方法
表領域の使用容量の指定

Contents

1 ユーザー定義変更SQL：alter user
2 パスワード変更：identified by ~
3 ユーザーをロック/アンロック：account unlock / lock
4 デフォルト表領域を指定：default tablespace
5 デフォルト一時表領域を指定：temporary tablespace
6 デフォルトプロファイルを指定：profile
7 表領域の使用容量の変更：quota ~ on

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

「alter user」の基本構文は↓になります。

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

alter user <ユーザ名> identified by <パスワード>

[default tablespace <デフォルト表領域>]

[temporary tablespace <デフォルト一時表領域>]

[profile <デフォルトプロファイル>];

パスワード変更：identified by ~

パスワード変更の「alter user」文は↓になります。

alter user <ユーザー名> identified by <パスワード>;

1	alter user <ユーザー名> identified by <パスワード>;

下がサンプルのSQL文になります。

alter user test001 identified by test001;

1	alter user test001 identified by test001;

OracleDatabase 11gからは大文字と小文字を区別するので、バージョンアップ移行時には注意が必要です。

パスワードは、アルファベット以外の文字で始まるか、英数字、アンダースコア(_)、ドル記号($)および番号記号(#)以外の文字を含む場合は

ダブルクォーテーションで括る必要あります。

また使える記号文字は↓になります。

読み方	記号
アットマーク	@
パーセント記号	%
プラス記号	+
円記号	\
スラッシュ	/
一重引用符	'
感嘆符	!
番号記号	#
ドル記号	$
カレット	^
疑問符	?
コロン	:
カンマ	.
左カッコ	(
右カッコ	)
左中カッコ	{
右中カッコ	}
左大カッコ	[
右大カッコ	]
チルダ	~
ハイフン	-
アンダースコア	_

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

下がユーザーのロックの「alter user」文になります。

alter user <ユーザー名> account lock;

1	alter user <ユーザー名> account lock;

ユーザーのロックはデータは使用するがアプリから接続してこないユーザーを作成する時に使います。

下がサンプルのSQLになります。

alter user test001 account lock;

1	alter user test001 account lock;

またパスワード期限切れにするには「password expire」を使用します。

alter user test001 account lock password expire;

1	alter user test001 account lock password expire;

下がユーザーのアンロック（ロック解除）の「alter user」文になります。

alter user <ユーザー名> account unlock;

1	alter user <ユーザー名> account unlock;

パスワード期限切れ時や規定回数ログインに失敗した時にロックしますので、その際に使用しましょう。

下がサンプルのSQLになります。

alter user TEST001 account unlock;

1	alter user TEST001 account unlock;

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

デフォルト表領域を指定する時には、「default tablespace」句を指定します。

alter user <ユーザー名> default tablespace <表領域名>;

1	alter user <ユーザー名> default tablespace <表領域名>;

ユーザー作成時にデフォルト表領域を指定しない場合、データベースのデフォルト表領域が指定されます。

またユーザーオブジェクト作成後にデフォルト表領域を変更する場合には、オブジェクトの移動も忘れずに行いましょう。

下のSQLで確認できます。

col PROPERTY_NAME for a30
col PROPERTY_VALUE for a100
select PROPERTY_VALUE from DATABASE_PROPERTIES
where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';
PROPERTY_VALUE
--------------------------------------------------------------------------------
USERS

col PROPERTY_NAME for a30

col PROPERTY_VALUE for a100

select PROPERTY_VALUE from DATABASE_PROPERTIES

where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE

--------------------------------------------------------------------------------

USERS

下がサンプルのSQL文になります。

alter user TEST001 default tablespace TESTTBS;

1	alter user TEST001 default tablespace TESTTBS;

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

デフォルト一時表領域を指定する時には、「temporary tablespace」句を指定します。

alter user <ユーザー名> temporary tablespace <一時表領域名>;

1	alter user <ユーザー名> temporary tablespace <一時表領域名>;

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

下のSQLで確認できます。

col PROPERTY_NAME for a30
col PROPERTY_VALUE for a100
select PROPERTY_VALUE from DATABASE_PROPERTIES
where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
----------------------------------------------------------------------------------------------------
TEMP

col PROPERTY_NAME for a30

col PROPERTY_VALUE for a100

select PROPERTY_VALUE from DATABASE_PROPERTIES

where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE

----------------------------------------------------------------------------------------------------

TEMP

下がサンプルのSQL文になります。

alter user TEST001 default tablespace TEMPTBS;

1	alter user TEST001 default tablespace TEMPTBS;

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

デフォルトプロファイルを指定する時には、「profile」句を指定します。

alter user <ユーザー名> profile <プロファイル名>;

1	alter user <ユーザー名> profile <プロファイル名>;

デフォルトプロファイルを指定しない場合、データベースのデフォルトプロファイル「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';
RESOURCE_NAME                  RESOURCE_TYPE                  LIMIT
------------------------------ ------------------------------ ------------------------------
COMPOSITE_LIMIT                KERNEL                         UNLIMITED
SESSIONS_PER_USER              KERNEL                         UNLIMITED
CPU_PER_SESSION                KERNEL                         UNLIMITED
CPU_PER_CALL                   KERNEL                         UNLIMITED
LOGICAL_READS_PER_SESSION      KERNEL                         UNLIMITED
LOGICAL_READS_PER_CALL         KERNEL                         UNLIMITED
IDLE_TIME                      KERNEL                         UNLIMITED
CONNECT_TIME                   KERNEL                         UNLIMITED
PRIVATE_SGA                    KERNEL                         UNLIMITED
FAILED_LOGIN_ATTEMPTS          PASSWORD                       10
PASSWORD_LIFE_TIME             PASSWORD                       180
RESOURCE_NAME                  RESOURCE_TYPE                  LIMIT
------------------------------ ------------------------------ ------------------------------
PASSWORD_REUSE_TIME            PASSWORD                       UNLIMITED
PASSWORD_REUSE_MAX             PASSWORD                       UNLIMITED
PASSWORD_VERIFY_FUNCTION       PASSWORD                       NULL
PASSWORD_LOCK_TIME             PASSWORD                       1
PASSWORD_GRACE_TIME            PASSWORD                       7
INACTIVE_ACCOUNT_TIME          PASSWORD                       UNLIMITED

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';

RESOURCE_NAME RESOURCE_TYPE LIMIT

------------------------------ ------------------------------ ------------------------------

COMPOSITE_LIMIT KERNEL UNLIMITED

SESSIONS_PER_USER KERNEL UNLIMITED

CPU_PER_SESSION KERNEL UNLIMITED

CPU_PER_CALL KERNEL UNLIMITED

LOGICAL_READS_PER_SESSION KERNEL UNLIMITED

LOGICAL_READS_PER_CALL KERNEL UNLIMITED

IDLE_TIME KERNEL UNLIMITED

CONNECT_TIME KERNEL UNLIMITED

PRIVATE_SGA KERNEL UNLIMITED

FAILED_LOGIN_ATTEMPTS PASSWORD 10

PASSWORD_LIFE_TIME PASSWORD 180

RESOURCE_NAME RESOURCE_TYPE LIMIT

------------------------------ ------------------------------ ------------------------------

PASSWORD_REUSE_TIME PASSWORD UNLIMITED

PASSWORD_REUSE_MAX PASSWORD UNLIMITED

PASSWORD_VERIFY_FUNCTION PASSWORD NULL

PASSWORD_LOCK_TIME PASSWORD 1

PASSWORD_GRACE_TIME PASSWORD 7

INACTIVE_ACCOUNT_TIME PASSWORD UNLIMITED

下がサンプルのSQL文になります。

alter user TEST001 profile TESTPROFILE;

1	alter user TEST001 profile TESTPROFILE;

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

表領域の使用容量を変更する時には、「quota ~ on」句を指定します。

サイズを指定するのは下になります。

alter user <ユーザー名> quota <サイズ> on <表領域名>;

1	alter user <ユーザー名> quota <サイズ> on <表領域名>;

サイズを無制限にするのは下になります。

alter user <ユーザー名> quota unlimited on <表領域名>;

1	alter user <ユーザー名> quota unlimited on <表領域名>;

下が1GB使用容量を指定する場合と無制限に使用する場合のサンプルのSQL文になります。

alter user TEST001 quota 1G on TESTTBS;
または
alter user TEST001 quota unlimited on TESTTBS;

alter user TEST001 quota 1G on TESTTBS;

または

alter user TEST001 quota unlimited on TESTTBS;