[oracle]alter user ... replace 구문 (ORA-28221)

DBMS: oracle 19.3

 

- ALTER USER 권한이 없고 PASSWORD_VERIFY_FUNCTION 사용중인 경우 REPLACE 구문 필요
- ORA-28221 에러 발생
- 본인 계정 패스워드 변경 시 (alter user 권한이 없으면 다른계정 패스워드는 변경할 수 없음)

alter user usr1 identified by {new 패스워드} replace {old 패스워드} ;

 

[목차여기]

 

PASSWORD_VERIFY_FUNCTION 없음, ALTER USER 특권 없음

PASSWORD_VERIFY_FUNCTION 기능을 사용하지 않는 경우 alter user 권한이 없어도 본인 비밀번호는 변경가능 합니다

SQL> create user usr1 identified by "usr123" ;

User created.

SQL> grant connect to usr1 ;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[ora19ent@svr ~]$ sqlplus usr1

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 21 20:20:11 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

-- 패스워드 변경
SQL> alter user usr1 identified by "usr123!123" ;

User altered.

-- alter user 권한 확인
SQL> select * from session_privs 
where privilege like 'ALTER%'
;

no rows selected

SQL>

 

PASSWORD_VERIFY_FUNCTION 있음, ALTER USER 특권 없음

PASSWORD_VERIFY_FUNCTION 기능을 사용하지 않는 경우 alter user 권한 없이 본인 패스워드 변경 시 replace 구문이 필수 입니다. ORA-28221 에러 발생

-- PASSWORD_VERIFY_FUNCTION 사용 프로파일 생성
SQL> create profile pf_usr limit PASSWORD_VERIFY_FUNCTION verify_function ;

Profile created.

-- usr1 계정 프로파일 변경
SQL> alter user usr1 profile pf_usr ;

User altered.

-- PG_USR 프로파일 확인
SQL> select resource_name, limit
from dba_profiles 
where profile = 'PF_USR'
; 

RESOURCE_NAME			 LIMIT
-------------------------------- --------------------------------------------------------------------------------------------------------------------------------
COMPOSITE_LIMIT 		 DEFAULT
SESSIONS_PER_USER		 DEFAULT
CPU_PER_SESSION 		 DEFAULT
CPU_PER_CALL			 DEFAULT
LOGICAL_READS_PER_SESSION	 DEFAULT
LOGICAL_READS_PER_CALL		 DEFAULT
IDLE_TIME			 DEFAULT
CONNECT_TIME			 DEFAULT
PRIVATE_SGA			 DEFAULT
FAILED_LOGIN_ATTEMPTS		 DEFAULT
PASSWORD_LIFE_TIME		 DEFAULT
PASSWORD_REUSE_TIME		 DEFAULT
PASSWORD_REUSE_MAX		 DEFAULT
PASSWORD_VERIFY_FUNCTION	 VERIFY_FUNCTION
PASSWORD_LOCK_TIME		 DEFAULT
PASSWORD_GRACE_TIME		 DEFAULT
INACTIVE_ACCOUNT_TIME		 DEFAULT

17 rows selected.

-- usr1 계정의 프로파일 확인
SQL> select profile from dba_users 
where username = 'USR1'
;

PROFILE
--------------------------------------------------------------------------------------------------------------------------------
PF_USR

SQL> 
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

-- usr1 계정으로 접속
[ora19ent@svr ~]$ sqlplus usr1

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 21 21:21:16 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password: 
Last Successful login time: Wed Feb 21 2024 20:20:15 +09:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

-- 비번 변경 시 에러 발생
SQL> alter user usr1 identified by "usr123!123" ;
alter user usr1 identified by "usr123!123"
*
ERROR at line 1:
ORA-28221: REPLACE not specified

-- alter user 권한 확인
SQL> select * from session_privs 
where privilege like 'ALTER%'
;

no rows selected

-- replace 구문 추가하여 비번 변경
SQL> alter user usr1 identified by "usr456!456" replace "usr123!123" ;

User altered.

반응형

PASSWORD_VERIFY_FUNCTION 있음, ALTER USER 특권 있음

alter user 권한을 가지고 있으면 바로 변경가능합니다

-- usr1 계정에 alter user 권한 부여
SQL> grant alter user to usr1 ;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

-- usr1 계정 로그인
[ora19ent@svr ~]$ sqlplus usr1

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 21 21:24:36 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password: 
Last Successful login time: Wed Feb 21 2024 21:21:24 +09:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

-- alter user 권한 확인
SQL> select * from session_privs 
where privilege like 'ALTER%'
;

PRIVILEGE
----------------------------------------
ALTER USER

-- replace 구문없이 패스워드 변경
SQL> alter user usr1 identified by "usr123!123" ;

User altered.