[2025-07-05] PROFILE에서 INACTIVE_ACCOUNT_TIME사용 (12cR2 New features) (12cR2에서 테스트)
[실습
환경]
OS
: Oracle Linux Server 7.9 (Linux 5.4.17-2102.201.3.el7uek.x86_64)
DB : Oracle
Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit
Production
(Database Jan 2022 Release Update :
12.2.0.1.220118 (33587128))
RAC1 : rdb01p(192.168.0.11) : PRDB1
RAC2 :
rdb02p(192.168.0.12) : PRDB2
Oracle 12.2 릴리스에서는 프로필에 있는 INACTIVE_ACCOUNT_TIME 리소스 매개변수를 사용하여
지정된 일수 동안 데이터베이스 인스턴스에 로그인하지 않은 데이터베이스 사용자의 계정을 자동으로 잠글 수 있음
1. 기본적으로
무제한으로 설정
2. 기간 설정 시 최소 설정은 15이고 최대 설정은 24855
[PRDB1:oracle@rdb01p][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[PRDB1:oracle@rdb01p][/home/oracle]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jul 6 23:10:26 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production
col
RESOURCE_NAME for a43
col limit for a23
[2025-07-06:23:10:26][rdb01p]<SYS@PRDB1> set lines 299
[2025-07-06:23:10:26][rdb01p]<SYS@PRDB1> select RESOURCE_NAME,limit from dba_profiles where
profile='DEFAULT';
RESOURCE_NAME
LIMIT
-------------------------------------------
-----------------------
COMPOSITE_LIMIT
UNLIMITED
SESSIONS_PER_USER
UNLIMITED
CPU_PER_SESSION
UNLIMITED
CPU_PER_CALL
UNLIMITED
LOGICAL_READS_PER_SESSION
UNLIMITED
LOGICAL_READS_PER_CALL
UNLIMITED
IDLE_TIME
UNLIMITED
CONNECT_TIME
UNLIMITED
PRIVATE_SGA
UNLIMITED
FAILED_LOGIN_ATTEMPTS
10
PASSWORD_LIFE_TIME
180
PASSWORD_REUSE_TIME
UNLIMITED
PASSWORD_REUSE_MAX
UNLIMITED
PASSWORD_VERIFY_FUNCTION
NULL
PASSWORD_LOCK_TIME
1
PASSWORD_GRACE_TIME
7
INACTIVE_ACCOUNT_TIME
UNLIMITED
17 rows selected.
Elapsed: 00:00:00.01
30일 동안 사용하지 않으면 자동으로 계정 잠금을 설정하려면 INACTIVE_ACCOUNT_TIME을 30으로 설정하여 프로필을 생성하고 해당 사용자에게 프로필을 설정함
[2025-07-06:23:10:26][rdb01p]<SYS@PRDB1>
CREATE
PROFILE "END_PROFILE4"
LIMIT
COMPOSITE_LIMIT
UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION
UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION
UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME
UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA
UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME
180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX
UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME
1
PASSWORD_GRACE_TIME 7
INACTIVE_ACCOUNT_TIME
30
;
Profile created.
Elapsed: 00:00:00.07
[2025-07-06:23:10:26][rdb01p]<SYS@PRDB1> select RESOURCE_NAME,limit from dba_profiles where profile='END_PROFILE4' and resource_name='INACTIVE_ACCOUNT_TIME';
RESOURCE_NAME
LIMIT
-------------------------------------------
-----------------------
INACTIVE_ACCOUNT_TIME
30
1 row selected.
[2025-07-06:23:10:26][rdb01p]<SYS@PRDB1> CREATE USER testuser identified by testuser profile END_PROFILE4;
User created.
--만약 INACTIVE_ACCOUNT_TIME을
15보다 작게 설정하면
CREATE PROFILE "END_PROFILE5"
LIMIT
COMPOSITE_LIMIT
UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION
UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION
UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME
UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA
UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME
15552000/86400
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX
UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME
86400/86400
PASSWORD_GRACE_TIME 604800/86400
INACTIVE_ACCOUNT_TIME
10
;
ERROR at line
1:
ORA-02377: invalid profile limit INACTIVE_ACCOUNT_TIME
--> 위와 같은 에러가
발생함