Oracle 限制业务用户自身修改密码防止应用连接故障

原创皇甫晓飞云和恩墨技术服务团队 2022-09-19

2904

防止业务自身通过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

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

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-

墨力计划故障处理故障案例 oracle

最后修改时间：2022-09-20 14:32:05

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 限制业务用户自身修改密码防止应用连接故障

评论