【Oracle】ユーザーごとに接続制限を設定する方法｜SESSIONS_PER_USER・CONNECT_TIME・ログオントリガー・アカウントロックまで解説

-- 現在の設定を確認
SHOW PARAMETER resource_limit;
-- デフォルト: FALSE（リソース制限は無効）

-- 有効化（即時反映、再起動不要）
ALTER SYSTEM SET resource_limit = TRUE SCOPE=BOTH;

-- 同時接続 3 セッションまでに制限するプロファイル
CREATE PROFILE limited_session LIMIT
    SESSIONS_PER_USER 3;

-- ユーザーに適用
ALTER USER dev_user PROFILE limited_session;

-- 4 つ目のセッションを開こうとすると:
-- ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

-- ユーザーごとの現在のセッション数
SELECT username, COUNT(*) AS session_count
FROM v$session
WHERE username IS NOT NULL
GROUP BY username
ORDER BY session_count DESC;

-- 接続時間 60 分、アイドル 15 分で自動切断するプロファイル
CREATE PROFILE time_limited LIMIT
    CONNECT_TIME 60      -- 最大接続時間: 60 分
    IDLE_TIME    15;     -- アイドル制限: 15 分

ALTER USER dev_user PROFILE time_limited;

-- CONNECT_TIME 超過: ORA-02399 で自動切断
-- IDLE_TIME 超過: ORA-02396 で自動切断

-- 開発者用: 接続制限あり、パスワード制限は緩め
CREATE PROFILE dev_profile LIMIT
    SESSIONS_PER_USER   5
    CONNECT_TIME        480    -- 8 時間
    IDLE_TIME           30     -- 30 分
    PASSWORD_LIFE_TIME  UNLIMITED
    FAILED_LOGIN_ATTEMPTS 10;

-- アプリ接続用: セッション数は多め、時間制限なし
CREATE PROFILE app_profile LIMIT
    SESSIONS_PER_USER   100
    CONNECT_TIME        UNLIMITED
    IDLE_TIME           UNLIMITED
    PASSWORD_LIFE_TIME  UNLIMITED
    FAILED_LOGIN_ATTEMPTS 5;

-- DBA 用: 制限なし
CREATE PROFILE dba_profile LIMIT
    SESSIONS_PER_USER   UNLIMITED
    CONNECT_TIME        UNLIMITED
    IDLE_TIME           UNLIMITED
    PASSWORD_LIFE_TIME  90
    FAILED_LOGIN_ATTEMPTS 3;

-- ユーザーに割り当て
ALTER USER dev_user PROFILE dev_profile;
ALTER USER app_user PROFILE app_profile;
ALTER USER admin_user PROFILE dba_profile;

-- 22:00〜7:00 の間はログインを禁止
CREATE OR REPLACE TRIGGER trg_restrict_logon_time
AFTER LOGON ON DATABASE
BEGIN
    IF USER NOT IN ('SYS', 'SYSTEM') THEN
        IF TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) >= 22
           OR TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) < 7 THEN
            RAISE_APPLICATION_ERROR(-20001,
                '22:00-7:00 の間はログインできません');
        END IF;
    END IF;
END;
/

-- 特定 IP アドレスからのみ接続を許可
CREATE OR REPLACE TRIGGER trg_restrict_logon_ip
AFTER LOGON ON DATABASE
DECLARE
    v_ip VARCHAR2(100);
BEGIN
    IF USER = 'ADMIN_USER' THEN
        v_ip := SYS_CONTEXT('USERENV', 'IP_ADDRESS');
        IF v_ip NOT IN ('10.0.1.100', '10.0.1.101', '192.168.1.50') THEN
            RAISE_APPLICATION_ERROR(-20002,
                'このIPアドレスからの接続は許可されていません: ' || v_ip);
        END IF;
    END IF;
END;
/

-- RESOURCE_LIMIT を TRUE にできない環境での代替手段
CREATE OR REPLACE TRIGGER trg_limit_sessions
AFTER LOGON ON DATABASE
DECLARE
    v_count NUMBER;
BEGIN
    IF USER = 'APP_USER' THEN
        SELECT COUNT(*) INTO v_count
        FROM v$session WHERE username = 'APP_USER';
        IF v_count > 50 THEN
            RAISE_APPLICATION_ERROR(-20003,
                'APP_USER のセッション数が上限(50)を超えています');
        END IF;
    END IF;
END;
/

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

-- アカウントをアンロック
ALTER USER dev_user ACCOUNT UNLOCK;

-- 状態の確認
SELECT username, account_status, lock_date
FROM dba_users
WHERE username = 'DEV_USER';

-- 5 回失敗で自動ロック、1 時間後に自動アンロック
CREATE PROFILE strict_login LIMIT
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LOCK_TIME    1/24;  -- 1/24 日 = 1 時間

ALTER USER target_user PROFILE strict_login;

-- PASSWORD_LOCK_TIME = UNLIMITED の場合: DBA が手動でアンロックするまで永久ロック
-- PASSWORD_LOCK_TIME = 1 の場合: 1 日後に自動アンロック

-- ユーザーのプロファイル割り当てを確認
SELECT username, profile, account_status
FROM dba_users
WHERE username NOT IN ('SYS','SYSTEM','ANONYMOUS','XDB')
ORDER BY profile, username;

-- プロファイルのリソース制限を確認
SELECT profile, resource_name, limit
FROM dba_profiles
WHERE resource_type = 'KERNEL'  -- リソース制限
ORDER BY profile, resource_name;

-- パスワード制限を確認
SELECT profile, resource_name, limit
FROM dba_profiles
WHERE resource_type = 'PASSWORD'
ORDER BY profile, resource_name;

ALTER SYSTEM SET resource_limit = TRUE SCOPE=BOTH;

CREATE PROFILE dev_limited LIMIT
    SESSIONS_PER_USER 3
    IDLE_TIME 30;

ALTER USER dev_user PROFILE dev_limited;

-- 退職者のアカウントを即時ロック
ALTER USER ex_employee ACCOUNT LOCK;

-- 既存セッションの強制切断（必要に応じて）
-- SID / SERIAL# を確認
SELECT sid, serial# FROM v$session WHERE username = 'EX_EMPLOYEE';
-- 切断
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 業務時間（9:00〜18:00）のみ接続を許可するトリガー
CREATE OR REPLACE TRIGGER trg_business_hours_only
AFTER LOGON ON DATABASE
BEGIN
    IF USER = 'BATCH_USER' THEN
        IF TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) BETWEEN 9 AND 17 THEN
            RAISE_APPLICATION_ERROR(-20010,
                'BATCH_USER は業務時間中（9:00-18:00）は接続できません');
        END IF;
    END IF;
END;
/

-- 30 分以上アイドルのユーザーセッションを確認
SELECT sid, serial#, username, status,
       last_call_et/60 AS idle_minutes
FROM v$session
WHERE status = 'INACTIVE'
  AND type = 'USER'
  AND last_call_et > 1800;  -- 1800 秒 = 30 分

-- 一括切断用 SQL を生成
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;'
FROM v$session
WHERE status = 'INACTIVE' AND type = 'USER' AND last_call_et > 1800;