限制业务用户自身修改密码

防止业务自身通过Oracle客户端或第三方客户端工具修改密码，造成其他应用连接错误，防止数据库因密码延时验证，出现Library cache lock等待事件。

适用版本：Oracle 11.2.0.4及以后版本

创建用户hfxf并授予权限。

SYS@cdb19c >create user hfxf identified by Oracle_123;User created.

SYS@cdb19c >grant connect,resource to hfxf;Grant succeeded.

使用业务用户hfxf登录，尝试修改自身密码

[oracle@db19do01 ~]$ sqlplus hfxf/Oracle_123@hrpdb

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 19 21:57:08 2022

Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  

All rights reserved.

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

HFXF@hrpdb >show user

USER is "HFXF"

HFXF@hrpdb >alter user hfxf identified by Oracle_456;

User altered.

HFXF@hrpdb >

HFXF@hrpdb >password

Changing password for HFXF

Old password:

New password:

Retype new password:

Password changed

HFXF@hrpdb >

业务用户hfxf可以修改自身密码

创建密码验证的函数

SYS@cdb19c >CREATE OR REPLACE FUNCTION verify_function_false

(username varchar2,password varchar2,

 old_password varchar2)  

  RETURN boolean IS  

  BEGIN 

 if user not in ('SYS','SYSTEM') 

then  -- customize this to allow any particular user by adding those users in this list of users.   RETURN(FALSE);  

 elsereturn true;

  end if; 

 END;

/ 

 

Function created.

 

创建profile并使用函数verify_function_false

SYS@cdb19c >CREATE PROFILE NO_CHANGE_PWD LIMIT PASSWORD_VERIFY_FUNCTION verify_function_false;

Profile created.

SYS@cdb19c >

 

修改业务用户profile

SYS@cdb19c >alter user hfxf profile NO_CHANGE_PWD;

User altered.

SYS@cdb19c >

 

进行验证，业务用户hfxf已经不能修改自己密码

[oracle@db19do01 ~]$ sqlplus hfxf/Oracle_789@hrpdb

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 19 22:06:54 2022

Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  A

ll rights reserved.

Last Successful login time: 

Mon Sep 19 2022 22:06:19 +08:00

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

HFXF@hrpdb >alter user hfxf identified by Oracle_123;

alter user hfxf identified by Oracle_123

*ERROR at line 1:

ORA-28221: REPLACE not specified

HFXF@hrpdb >passwordChanging password for 

HFXFOld password:

New password:

Retype new password:

ERROR:ORA-28003: password verification for the specified password failedPassword unchanged

HFXF@hrpdb >

 

-the end-