PASSWORD_LIFE_TIME UNLIMITED -> LIMITED 변경 시, 주의 사항

-- run by sys user
select u.username, s.ctime, s.ptime
 ,'alter user '||u.username||' identified by values '''||s.spare4||''';' cmd
 from dba_users u
 join sys.user$ s
 on u.user_id = s.user#
 where u.username = upper('&username');

파트 회의 때 듣은 이야기 근거로 간단히 정리 

 

<< #3. 응용 결론 >> 
PASSWORD_LIFE_TIME UNLIMITED -> LIMITED 변경 시, 
동일 비번으로 패스워드 변경 ( SYS.USER$. PTIME 현재 시점으로 업데이트 )
이후 PASSWORD_LIFE_TIME 을 LIMITED 로 변경 해주는 센스 

 

#1. PASSWORD_LIFE_TIME UNLIMITED -> LIMITED 변경 시, 주의 사항 
: 가장 최근 패스워드 갱신일 기준 혹은 ( 패스워드 갱신이 없는 경우) 계정 생성일 기준으로 날짜 계산됨 

https://docs.oracle.com/database/121/DBSEG/authentication.htm#DBSEG567

The PASSWORD_LIFE_TIME limit of a profile is measured from the last time that an account's password

is changed, or the account creation time if the password has never been changed. 

These dates are recorded in the PTIME (password change time) and CTIME (account creation time) columns of the

SYS.USER$ system table.  

The PASSWORD_LIFE_TIME limit is not measured starting from the timestamp of the last change to the

PASSWORD_LIFE_TIME profile parameter, as may be initially thought.

Therefore, any accounts affected by the changed profile whose last password change time was more than

PASSWORD_LIFE_TIME days ago immediately expire and enter their grace period on their next

connection, issuing the ORA-28002: The password will expire within n days warning.

#2. 패스워드 모르는 상태에서 기존(해시값으로) 패스워드로 RESET 하기

https://ruepprich.wordpress.com/2018/01/09/reset-password-by-values-in-12c/

Reset Password By Values in 12c
January 9, 2018 at 2:21 pm | Posted in Oracle Developement | 3 Comments
In prior Oracle version you used to be able to hack a users password by using the identified by values syntax. 

This no longer works in 12c, but there is a hack for the hack as blogged by Brian Peasland.

Here is a quick query that will generate the alter user command:



#3. 응용 결론 
PASSWORD_LIFE_TIME UNLIMITED -> LIMITED 변경 시, 
동일 비번으로 패스워드 변경 ( SYS.USER$. PTIME 현재 시점으로 업데이트 )
이후 PASSWORD_LIFE_TIME 을 LIMITED 로 변경 해주는 센스 

SQL> select u.username, s.ctime, s.ptime
 ,'alter user '||u.username||' identified by values '''||s.spare4||''';' cmd
 from dba_users u
 join sys.user$ s
 on u.user_id = s.user#
 where u.username = upper('&username');  2    3    4    5    6
Enter value for username: scott
old   6:  where u.username = upper('&username')
new   6:  where u.username = upper('scott')


SCOTT 07-APR-22 07-APR-22 
alter user SCOTT identified by values 'S:4790C45087E041C179602D469CCFDBB5137051EB98769C3C7CF3C1325208;T:3FCC96B8048017FA7FA24DF79792005027A2EC602DB55AED08D4040989DCC7BE20F660D21251E7469B7FAA7061D768F5BB70540A335BCBFF7B0AFD9A557A55AEA716C2E70DAE63282313AEADB497EB5D';
-- ctime : 2022/04/07 계정 생성 
-- ptime : 2022/04/07 계정 비번 변경 ( 비번 변경이 없어, 계정 생성일로 확인됨 ) 

SQL> select sysdate from dual ;

SYSDATE
---------
14-APR-22

SQL>

SQL> conn scott/oracle123
Connected.
SQL> show user
USER is "SCOTT"

-- hash 값으로 비번 변경 
SQL> alter user SCOTT identified by values 'S:4790C45087E041C179602D469CCFDBB5137051EB98769C3C7CF3C1325208;T:3FCC96B8048017FA7FA24DF79792005027A2EC602DB55AED08D4040989DCC7BE20F660D21251E7469B7FAA7061D768F5BB70540A335BCBFF7B0AFD9A557A55AEA716C2E70DAE63282313AEADB497EB5D';
User altered.
-- 동일 비번으로 접속 가능 확인 
SQL> conn scott/oracle123
Connected.
SQL>