PROFILE 중 PASSWORD_ROLLOVER_TIME 추가됨 ( 19.12 부터 지원 )
원문 : https://oracle-base.com/articles/21c/gradual-database-password-rollover-time-21c
PASSWORD_ROLLOVER_TIME 2
2022.12.06 - [Oracle/Oh Oracle ] - PROFILE 중 PASSWORD_ROLLOVER_TIME 추가됨 ( 19.12 부터 지원 ) 2
PASSWORD_ROLLOVER_TIME : 쉽게 말해서, PASSWORD 변경 후, 한시적으로 이전 비번/현재 비번으로 로그인 가능하도록 지원 해주는 기능으로 19.12 부터 지원됨
보안 강화로, 주기적으로 비번 변경 시, 변경 후, Application(WAS) 재기동에 여유를 갖을 수 있다.
-------------------------------------------------------------------------------------------
1차 테스트 -- 원문 따라 하기
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
2차 테스트 -- PASSWORD_ROLLOVER_TIME 활성화 와 비번 변경 순서
-------------------------------------------------------------------------------------------
PASSWORD_ROLLOVER_TIME 을 할당 혹은 활성화 후에 비번 변경 시만, ROLLOVER가 적용 된다.
EX) PASSWORD_ROLLOVER_TIME 할당/활성화 -> 비번 변경 -> 전/후 비번으로 로그인 성공
EX) 비번 변경 -> PASSWORD_ROLLOVER_TIME 할당/활성화 -> 후(최근) 비번으로만 로그인 성공
Gradual Database Password Rollover Time (PASSWORD_ROLLOVER_TIME) in Oracle Database 19c and 21c
From Oracle 21c onward we can define a password rollover time, which allows us to connect using both the old and the new passwords during the gradual rollover time period. This feature was backported to Oracle 19c in the 19.12 release update.
oracle-base.com
-------------------------------------------------------------------------------------------
1차 테스트 -- 원문 따라 하기
-------------------------------------------------------------------------------------------
#1. PASSWORD ROLLOVER 활성화
SCOTT@ORACLE19>create user testuser1 identified by testuser1 quota unlimited on users ;
User created.
SCOTT@ORACLE19>grant connect, resource to testuser1 ;
Grant succeeded.
SCOTT@ORACLE19>create profile pw_rollover_time_prof limit password_rollover_time 1 ;
Profile created.
SCOTT@ORACLE19>alter user testuser1 profile pw_rollover_time_prof ;
User altered.
TESTUSER1@ORACLE19>alter user testuser1 identified by newpasswd1;
User altered.
-- password_rollover_time 설정 및 User 에 할당
-- User 비번 변경 이후, rollover 가 활성화 됨
-- 기존 비번 로그인 성공
TESTUSER1@ORACLE19>conn testuser1/testuser1
Connected.
Session altered.
-- 신규 비번 로그인 성공
TESTUSER1@ORACLE19>conn testuser1/newpasswd1
Connected.
Session altered.
-- DBA_USERS 의 account_status 를 통해서 open & IN ROLLOVER 상태 확인됨
-- PASSWORD_CAHGE_DATE (비번 변경 후 ) , ROLLOVER 상태 확인 가능
SCOTT@ORACLE19>select account_status,
to_char(password_change_date, 'yyyy-mm-dd hh24:mi:ss') as password_change_date
from dba_users
where username = 'TESTUSER1';
ACCOUNT_STATUS PASSWORD_CHANGE_DATE
-------------------------------- ------------------------------
OPEN & IN ROLLOVER 2022-04-27 21:00:01
SCOTT@ORACLE19>select resource_name, limit
from dba_profiles
where profile = 'PW_ROLLOVER_TIME_PROF'
and resource_name = 'PASSWORD_ROLLOVER_TIME';
RESOURCE_NAME LIMIT
------------------------- ----------
PASSWORD_ROLLOVER_TIME 1
#2. PASSWORD ROLLOVER 비활성화
SCOTT@ORACLE19>alter profile pw_rollover_time_prof limit password_rollover_time 0 ;
Profile altered.
SCOTT@ORACLE19>select resource_name, limit
from dba_profiles
where profile = 'PW_ROLLOVER_TIME_PROF'
and resource_name = 'PASSWORD_ROLLOVER_TIME';
RESOURCE_NAME LIMIT
------------------------- ----------
PASSWORD_ROLLOVER_TIME 0
SCOTT@ORACLE19>select account_status,
to_char(password_change_date, 'dd-mon-yyyy hh24:mi:ss') as password_change_date
from dba_users
where username = 'TESTUSER1';
ACCOUNT_STATUS PASSWORD_CHANGE_DATE
-------------------------------- ------------------------------
OPEN 27-apr-2022 21:00:01
-- ACCOUNT_STATUS 이 "OPEN & IN ROLLOVER" 에서 "OPEN" 으로 변경됨
-- 기본 비번으로 로그인 시도 -> 실패
TESTUSER1@ORACLE19>conn testuser1/testuser1
ERROR:
ORA-01017: invalid username/password; logon denied
-- 신규 비번으로 로그인 시도 -> 성공
TESTUSER1@ORACLE19>conn testuser1/newpasswd1
Connected.
Session altered.
#3. 주의
SCOTT@ORACLE19>alter profile pw_rollover_time_prof limit password_rollover_time 1.5;
Profile altered.
SCOTT@ORACLE19>select account_status,
to_char(password_change_date, 'dd-mon-yyyy hh24:mi:ss') as password_change_date
from dba_users
where username = 'TESTUSER1';
ACCOUNT_STATUS PASSWORD_CHANGE_DATE
-------------------------------- ------------------------------
OPEN & IN ROLLOVER 27-apr-2022 21:04:48
SCOTT@ORACLE19>select resource_name, limit
from dba_profiles
where profile = 'PW_ROLLOVER_TIME_PROF'
and resource_name = 'PASSWORD_ROLLOVER_TIME';
RESOURCE_NAME LIMIT
------------------------- ----------
PASSWORD_ROLLOVER_TIME 1.5
-- password_rollover_time PROFILE 설정 -> 비번 변경 -> 전/후 비번 로그인 성공 확인
TESTUSER1@ORACLE19>alter user testuser1 identified by newpasswd2;
User altered.
-- 기존 비번 로그인 성공
TESTUSER1@ORACLE19>conn testuser1/newpasswd1
Connected.
Session altered.
-- 신규 비번 로그인 성공
TESTUSER1@ORACLE19>conn testuser1/newpasswd2
Connected.
Session altered.
SCOTT@ORACLE19>alter profile pw_rollover_time_prof limit password_rollover_time 0;
Profile altered.
TESTUSER1@ORACLE19>conn testuser1/newpasswd2
Connected.
Session altered.
SCOTT@ORACLE19>select resource_name,limit
from dba_profiles
where profile = 'PW_ROLLOVER_TIME_PROF'
and resource_name = 'PASSWORD_ROLLOVER_TIME';
RESOURCE_NAME LIMIT
------------------------- ----------
PASSWORD_ROLLOVER_TIME 0
SCOTT@ORACLE19>select account_status,
to_char(password_change_date, 'dd-mon-yyyy hh24:mi:ss') as password_change_date
from dba_users
where username = 'TESTUSER1';
ACCOUNT_STATUS PASSWORD_CHANGE_DATE
-------------------------------- ------------------------------
OPEN 27-apr-2022 21:04:48
TESTUSER1@ORACLE19>conn testuser1/newpasswd2
Connected.
Session altered.
TESTUSER1@ORACLE19>conn testuser1/newpasswd1
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
TESTUSER1@ORACLE19>
-------------------------------------------------------------------------------------------
2차 테스트 -- PASSWORD_ROLLOVER_TIME 활성화 와 비번 변경 순서
-------------------------------------------------------------------------------------------
--PASSWORD_ROLLOVER_TIME 을 할당 혹은 활성화 후에 비번 변경 시만, ROLLOVER가 적용 된다.
--EX) PASSWORD_ROLLOVER_TIME 할당/활성화 -> 비번 변경 -> 전/후 비번으로 로그인 성공
--EX) 비번 변경 -> PASSWORD_ROLLOVER_TIME 할당/활성화 -> 후(최근) 비번으로만 로그인 성공
SQL> create user testuser101 identified by testuser101 quota unlimited on users ;
User created.
SQL> create user testuser102 identified by testuser102 quota unlimited on users ;
User created.
SQL>
SQL>
SQL> grant connect, resource to testuser101 ;
Grant succeeded.
SQL> grant connect, resource to testuser102 ;
Grant succeeded.
SQL> select account_status,
to_char(password_change_date, 'yyyy-mm-dd hh24:mi:ss') as password_change_date
from dba_users
where username in ( 'TESTUSER101','TESTUSER102' ); 2 3 4
ACCOUNT_STATUS PASSWORD_CHANGE_DAT
-------------------------------- -------------------
OPEN 2022-04-28 21:17:22
OPEN 2022-04-28 21:17:18
SYS@ORACLE19>conn testuser101/testuser101
Connected.
Session altered.
TESTUSER101@ORACLE19>alter user testuser101 identified by newpasswd1; ---- testuser01 을 PASSWORD_ROLLOVER_TIME 할당전 비번 변경
User altered.
SYS@ORACLE19>conn testuser102/testuser102
Connected.
Session altered.
SQL> alter user testuser101 profile pw_rollover_time_prof ;
User altered.
SQL> alter user testuser102 profile pw_rollover_time_prof ;
User altered.
SQL> select account_status,
to_char(password_change_date, 'yyyy-mm-dd hh24:mi:ss') as password_change_date
from dba_users
where username in ( 'TESTUSER101','TESTUSER102' ); 2 3 4
ACCOUNT_STATUS PASSWORD_CHANGE_DAT
-------------------------------- -------------------
OPEN 2022-04-28 21:17:22
OPEN 2022-04-28 21:18:23
TESTUSER102@ORACLE19>alter user testuser102 identified by newpasswd1; -- testuser102 비번 변경
User altered.
SQL> select username, account_status,
to_char(password_change_date, 'yyyy-mm-dd hh24:mi:ss') as password_change_date
from dba_users
where username in ( 'TESTUSER101','TESTUSER102' ) ;
USERNAME ACCOUNT_STATUS PASSWORD_CHANGE_DAT
------------------------------ -------------------------------- -------------------
TESTUSER102 OPEN & IN ROLLOVER 2022-04-28 21:19:11
TESTUSER101 OPEN 2022-04-28 21:18:23
-- TESTUSE102 만 ROLLOVER 활성화
-- 로그인 테스트
TESTUSER101@ORACLE19>conn testuser101/newpasswd1
Connected.
Session altered.
TESTUSER101@ORACLE19>conn testuser101/testuser101
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
TESTUSER101@ORACLE19>
-- TESTUSER101 는 최신 비번으로만 로그인 가능
TESTUSER102@ORACLE19>conn testuser102/newpasswd1
Connected.
Session altered.
TESTUSER102@ORACLE19>conn testuser102/testuser102
Connected.
Session altered.
TESTUSER102@ORACLE19>
-- TESTUSER102 는 전/후 비번 모두 로그인 성공