【Oracle】ユーザーの情報を変更・削除する方法（ALTER USER / DROP USER / GRANT / REVOKE）

ALTER USER ユーザー名
  IDENTIFIED BY 新パスワード
  DEFAULT TABLESPACE 表領域名
  TEMPORARY TABLESPACE 一時表領域名
  QUOTA サイズ ON 表領域名
  PROFILE プロファイル名
  ACCOUNT LOCK | UNLOCK
  PASSWORD EXPIRE;

ポイント：ALTER USER を実行するには ALTER USER システム権限が必要です。自分自身のパスワード変更のみ、権限がなくても実行できます。

-- ユーザー SCOTT のパスワードを変更
ALTER USER SCOTT IDENTIFIED BY NewPassword123;

-- 次回ログイン時にパスワード変更を強制
ALTER USER SCOTT PASSWORD EXPIRE;

注意：PASSWORD EXPIRE を実行すると、ユーザーは次回ログイン時に必ずパスワードを変更する必要があります。バッチ処理やアプリケーション接続で使用しているユーザーに実行すると、接続エラーが発生します。

-- OS認証に変更
ALTER USER SCOTT IDENTIFIED EXTERNALLY;

-- グローバル認証（LDAP等）に変更
ALTER USER SCOTT IDENTIFIED GLOBALLY AS 'CN=scott,OU=Users,DC=example,DC=com';

-- アカウントをロック（ログイン不可にする）
ALTER USER SCOTT ACCOUNT LOCK;

-- アカウントをアンロック（ログイン可能にする）
ALTER USER SCOTT ACCOUNT UNLOCK;

ロック時のエラーメッセージ

ORA-28000: The account is locked.

SELECT username, account_status, lock_date
FROM dba_users
WHERE username = 'SCOTT';

-- デフォルト表領域を変更
ALTER USER SCOTT DEFAULT TABLESPACE APP_DATA;

-- 一時表領域を変更
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP2;

デフォルト表領域の変更について

• 既に作成済みのオブジェクトは移動されません
• 変更後に新規作成するオブジェクトから適用されます
• 既存オブジェクトを移動する場合は ALTER TABLE ... MOVE TABLESPACE を使用します

-- 100MBのクォータを設定
ALTER USER SCOTT QUOTA 100M ON APP_DATA;

-- 無制限に設定
ALTER USER SCOTT QUOTA UNLIMITED ON APP_DATA;

-- クォータを0にする（新規オブジェクト作成不可）
ALTER USER SCOTT QUOTA 0 ON APP_DATA;

SELECT tablespace_name,
       bytes / 1024 / 1024 AS used_mb,
       max_bytes / 1024 / 1024 AS max_mb
FROM dba_ts_quotas
WHERE username = 'SCOTT';

-- ユーザーのプロファイルを変更
ALTER USER SCOTT PROFILE APP_PROFILE;

CREATE PROFILE APP_PROFILE LIMIT
  -- パスワード関連
  PASSWORD_LIFE_TIME     90          -- パスワード有効期限（日）
  PASSWORD_GRACE_TIME    7           -- 猶予期間（日）
  PASSWORD_REUSE_TIME    365         -- 同じパスワードを再使用できない期間
  PASSWORD_REUSE_MAX     10          -- パスワード履歴の保持数
  FAILED_LOGIN_ATTEMPTS  5           -- ログイン失敗許容回数
  PASSWORD_LOCK_TIME     1           -- ロック期間（日）
  -- リソース関連
  SESSIONS_PER_USER      5           -- 同時セッション数
  IDLE_TIME              30          -- アイドルタイムアウト（分）
  CONNECT_TIME           480         -- 最大接続時間（分）
;

SELECT resource_name, limit
FROM dba_profiles
WHERE profile = 'APP_PROFILE'
ORDER BY resource_name;

-- 基本的な権限を付与
GRANT CREATE SESSION TO SCOTT;
GRANT CREATE TABLE TO SCOTT;
GRANT CREATE VIEW TO SCOTT;

-- 複数権限を一度に付与
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO SCOTT;

-- ADMIN OPTION付き（他ユーザーにも付与可能）
GRANT CREATE TABLE TO SCOTT WITH ADMIN OPTION;

-- 定義済みロールを付与
GRANT CONNECT, RESOURCE TO SCOTT;

-- DBAロールを付与（管理者権限）
GRANT DBA TO SCOTT;

-- 特定テーブルへのSELECT権限
GRANT SELECT ON HR.EMPLOYEES TO SCOTT;

-- 複数の権限を一度に
GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO SCOTT;

-- 特定カラムのみUPDATE権限を付与
GRANT UPDATE (salary, commission_pct) ON HR.EMPLOYEES TO SCOTT;

-- GRANT OPTION付き（他ユーザーにも付与可能）
GRANT SELECT ON HR.EMPLOYEES TO SCOTT WITH GRANT OPTION;

-- システム権限を取消
REVOKE CREATE TABLE FROM SCOTT;

-- ロールを取消
REVOKE RESOURCE FROM SCOTT;

-- オブジェクト権限を取消
REVOKE SELECT ON HR.EMPLOYEES FROM SCOTT;

-- すべてのオブジェクト権限を取消
REVOKE ALL ON HR.EMPLOYEES FROM SCOTT;

注意：システム権限の REVOKE は連鎖しません（ADMIN OPTION で付与された権限を取り消しても、そのユーザーが他に付与した権限はそのまま残ります）。一方、オブジェクト権限の REVOKE は連鎖します（GRANT OPTION で付与された権限を取り消すと、その先の付与もすべて取り消されます）。

-- ユーザーを削除（オブジェクトを持っていない場合）
DROP USER SCOTT;

-- ユーザーと所有オブジェクトをすべて削除
DROP USER SCOTT CASCADE;

注意：CASCADE は元に戻せません。本番環境では必ず事前にバックアップを取得してから実行してください。

SELECT object_type, COUNT(*) AS cnt
FROM dba_objects
WHERE owner = 'SCOTT'
GROUP BY object_type
ORDER BY cnt DESC;

エラーメッセージ

ORA-01940: cannot drop a user that is currently connected

-- 1. 対象ユーザーのセッションを確認
SELECT sid, serial#, status
FROM v$session
WHERE username = 'SCOTT';

-- 2. セッションを強制切断（SID=123, SERIAL#=456 の例）
ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;

-- 3. ユーザーを削除
DROP USER SCOTT CASCADE;

SELECT username,
       account_status,
       default_tablespace,
       temporary_tablespace,
       profile,
       created,
       expiry_date,
       lock_date
FROM dba_users
WHERE username = 'SCOTT';

-- システム権限
SELECT privilege, admin_option
FROM dba_sys_privs
WHERE grantee = 'SCOTT';

-- 付与されたロール
SELECT granted_role, admin_option, default_role
FROM dba_role_privs
WHERE grantee = 'SCOTT';

-- オブジェクト権限
SELECT owner, table_name, privilege, grantable
FROM dba_tab_privs
WHERE grantee = 'SCOTT';

ALTER USER SCOTT
  IDENTIFIED BY NewSecurePass123
  DEFAULT TABLESPACE APP_DATA
  TEMPORARY TABLESPACE TEMP
  QUOTA 500M ON APP_DATA
  PROFILE APP_PROFILE
  ACCOUNT UNLOCK;

-- MySQL: パスワード変更
ALTER USER 'scott'@'localhost' IDENTIFIED BY 'NewPassword123';

-- MySQL: ユーザー削除
DROP USER 'scott'@'localhost';

-- PostgreSQL: パスワード変更
ALTER USER scott WITH PASSWORD 'NewPassword123';

-- PostgreSQL: ユーザー削除（所有オブジェクトの移譲が必要）
REASSIGN OWNED BY scott TO postgres;
DROP OWNED BY scott;
DROP USER scott;

-- SQL Server: パスワード変更
ALTER LOGIN scott WITH PASSWORD = 'NewPassword123';

-- SQL Server: ユーザー削除（ログインとユーザーの両方を削除）
DROP USER scott;
DROP LOGIN scott;

-- エラーが発生するケース
DROP USER SCOTT;
-- ORA-01922: CASCADE must be specified to drop 'SCOTT'

-- 対処: CASCADEを付けて実行
DROP USER SCOTT CASCADE;

-- パスワード検証関数を確認
SELECT profile, resource_name, limit
FROM dba_profiles
WHERE resource_name = 'PASSWORD_VERIFY_FUNCTION'
  AND profile = (
    SELECT profile FROM dba_users
    WHERE username = 'SCOTT'
  );

ユーザー管理のベストプラクティス

• 本番環境では必ず手順書を作成 — ALTER USER / DROP USER の影響範囲を事前に文書化
• DROP USER 前にバックアップ — CASCADE で削除するオブジェクトをエクスポート（expdp）しておく
• 権限は最小限に — DBAロールの安易な付与を避け、必要な権限のみ付与する
• ロールを活用 — 個別のシステム権限よりもカスタムロールでまとめて管理する
• プロファイルでパスワードポリシーを統一 — アプリケーション用と管理者用でプロファイルを分ける
• 定期的な権限の棚卸し — 不要な権限やユーザーを定期的にレビューする
• 監査ログの有効化 — ALTER USER / DROP USER の実行を監査ログに記録する

-- 1. ユーザーの所有オブジェクトを確認
SELECT object_type, object_name
FROM dba_objects
WHERE owner = 'SCOTT'
ORDER BY object_type, object_name;

-- 2. ユーザーの権限を確認
SELECT privilege FROM dba_sys_privs WHERE grantee = 'SCOTT';
SELECT granted_role FROM dba_role_privs WHERE grantee = 'SCOTT';

-- 3. 他ユーザーが参照しているオブジェクトを確認
SELECT owner, name, type, referenced_name
FROM dba_dependencies
WHERE referenced_owner = 'SCOTT';

-- 4. バックアップを取得（Data Pump）
-- expdp system/password schemas=SCOTT directory=DATA_PUMP_DIR dumpfile=scott_backup.dmp

-- 5. 接続中のセッションを確認・切断
SELECT sid, serial# FROM v$session WHERE username = 'SCOTT';

-- 6. アカウントをまずロック（即時削除しない場合）
ALTER USER SCOTT ACCOUNT LOCK;

-- 7. 問題なければ削除を実行
DROP USER SCOTT CASCADE;