[호기심] 비번 재사용 제한 PROFILE 설정 상태에서, HASH 값을 통한 비번 변경 가능할까 ?

[호기심] 비번 재사용 제한 PROFILE 설정 상태에서, HASH 값을 통한 비번 변경 가능할까 ?
[확인 결과] 어라 되네... 최종 값 형태가 아닌, INPUT 되는 STRING 차이로 동일 비번 체크하는 듯 싶다.

 

1. 일반 HASH 값으로 비번 변경 하기 

SYS@ORACLE19>

SYS@ORACLE19> SELECT USERNAME, CREATED,EXPIRY_DATE,PROFILE FROM DBA_USERS WHERE USERNAME ='SCOTT';

USERNAME                       CREATED             EXPIRY_DATE         PROFILE
------------------------------ ------------------- ------------------- --------------------
SCOTT                          2022.04.07 15:21:15 2022.10.11 17:47:09 DEFAULT

SYS@ORACLE19>select name , CTIME, PTIME, EXPTIME from  SYS.USER$ WHERE NAME ='SCOTT';

NAME                           CTIME               PTIME               EXPTIME
------------------------------ ------------------- ------------------- -------------------
SCOTT                          2022.04.07 15:21:15 2022.04.14 17:47:09 2022.10.11 17:47:09

select 'alter user '||s.name||' identified by values '||''''||spare4||';'||s.password||''';'
from  sys.user$ s, dba_users d
where 1=1
-- and   d.account_status ='EXPIRED(GRACE)'
and   s.name = d.username 
and   d.username ='SCOTT' ; 



ALTER USER SCOTT identified by values 'S:4790C45087E041C179602D469CCFDBB5137051EB98769C3C7CF3C1325208;T:3FCC96B8048017FA7FA24DF79792005027A2EC602DB55AED08D4040989DCC7BE20F660D21251E7469B7FAA7061D768F5BB70540A335BCBFF7B0AFD9A557A55AEA716C2E70DAE63282313AEADB497EB5D;';


SYS@ORACLE19>SELECT USERNAME, CREATED,EXPIRY_DATE,PROFILE FROM DBA_USERS WHERE USERNAME ='SCOTT';

USERNAME                       CREATED             EXPIRY_DATE         PROFILE
------------------------------ ------------------- ------------------- --------------------
SCOTT                          2022.04.07 15:21:15 2022.10.21 13:48:55 DEFAULT

SYS@ORACLE19>select name , CTIME, PTIME, EXPTIME from  SYS.USER$ WHERE NAME ='SCOTT';

NAME                           CTIME               PTIME               EXPTIME
------------------------------ ------------------- ------------------- -------------------
SCOTT                          2022.04.07 15:21:15 2022.04.24 13:48:55 2022.10.21 13:48:55

SYS@ORACLE19>SELECT SYSDATE FROM DUAL ;

SYSDATE
-------------------
2022.04.24 13:49:43

SYS@ORACLE19>

SYS@ORACLE19>ALTER USER SCOTT identified by values 'S:4790C45087E041C179602D469CCFDBB5137051EB98769C3C7CF3C1325208;T:3FCC96B8048017FA7FA24DF79792005027A2EC602DB55AED08D4040989DCC7BE20F660D21251E7469B7FAA7061D768F5BB70540A335BCBFF7B0AFD9A557A55AEA716C2E70DAE63282313AEADB497EB5D;';

User altered.


2. 비번 재사용 설정 후, 일반 HASH 값으로 비번 변경 하기 

SYS@ORACLE19>SELECT RESOURCE_NAME, LIMIT
FROM  DBA_PROFILES
WHERE PROFILE ='DEFAULT'
AND   RESOURCE_NAME IN ('PASSWORD_LIFE_TIME','PASSWORD_REUSE_TIME','PASSWORD_REUSE_MAX');  

RESOURCE_NAME                    LIMIT
-------------------------------- -----------------
PASSWORD_LIFE_TIME               180
PASSWORD_REUSE_TIME              UNLIMITED
PASSWORD_REUSE_MAX               UNLIMITED

-- https://docs.oracle.com/database/121/SQLRF/statements_6012.htm
-- PASSWORD_REUSE_TIME = 30, PASSWORD_REUSE_MAX = 10, PASSWORD 10 변경 후, 30일 후에 암호재 사용 가능  


SYS@ORACLE19>ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX 3;
Profile altered.

SYS@ORACLE19>ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME 1 ;
Profile altered.

-- PASSWORD_REUSE_MAX  3  >>> 최소 3번 이상 변경 후, 동일 패스워드 사용 가능 
-- PASSWORD_REUSE_TIME 1  >>> 3번 변경 후, 1일 경과후 , PASSWORD_LIFE_TIME = 180 일이라, 4번째부터 첫번째 비번 사용가능 


SELECT RESOURCE_NAME, LIMIT
FROM  DBA_PROFILES
WHERE PROFILE ='DEFAULT'
AND   RESOURCE_NAME IN ('PASSWORD_LIFE_TIME','PASSWORD_REUSE_TIME','PASSWORD_REUSE_MAX');  

RESOURCE_NAME                    LIMIT
-------------------------------- --------------------------------------------------------------------------------------------------------------------------------
PASSWORD_LIFE_TIME               180
PASSWORD_REUSE_TIME              1
PASSWORD_REUSE_MAX               3

-- SCOTT 유저로 동일 비번으로 변경 시도 --> 실패 
SCOTT@ORACLE19>alter user scott identified by oracle123;
alter user scott identified by oracle123
*
ERROR at line 1:
ORA-28007: the password cannot be reused

-- SYS 유저로 동일 비번으로 변경 시도 --> 실패 
SYS@ORACLE19>alter user scott identified by oracle123;
alter user scott identified by oracle123
*
ERROR at line 1:
ORA-28007: the password cannot be reused


-- SYS 유저로 HASH 값으로 동일 비번으로 변경 시도 --> 성공 

SYS@ORACLE19>ALTER USER SCOTT identified by values 'S:4790C45087E041C179602D469CCFDBB5137051EB98769C3C7CF3C1325208;T:3FCC96B8048017FA7FA24DF79792005027A2EC602DB55AED08D4040989DCC7BE20F660D21251E7469B7FAA7061D768F5BB70540A335BCBFF7B0AFD9A557A55AEA716C2E70DAE63282313AEADB497EB5D;';

User altered.


SYS@ORACLE19>SELECT USERNAME, CREATED,EXPIRY_DATE,PROFILE FROM DBA_USERS WHERE USERNAME ='SCOTT';

USERNAME                       CREATED             EXPIRY_DATE         PROFILE
------------------------------ ------------------- ------------------- --------------------
SCOTT                          2022.04.07 15:21:15 2022.10.21 14:00:10 DEFAULT

SYS@ORACLE19>select name , CTIME, PTIME, EXPTIME from  SYS.USER$ WHERE NAME ='SCOTT';

NAME                           CTIME               PTIME               EXPTIME
------------------------------ ------------------- ------------------- -------------------
SCOTT                          2022.04.07 15:21:15 2022.04.24 14:00:10 2022.10.21 14:00:10
-- 1번 테스트 결과로 SYS.USER$,PTIME(비번 변경 시간) 이
-- "2022.04.24 13:48:55" 에서 "2022.04.24 14:00:10" 로 변경 확인

SYS@ORACLE19>SELECT SYSDATE FROM DUAL ;

SYSDATE
-------------------
2022.04.24 14:00:37

SYS@ORACLE19>