版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/bisal/article/details/103257711
我们负责维护的一套系统,需要通过Pro*C连接客户的远程Oracle数据库,最近因客户要求,需要修改这套数据库的登录密码,使用alter user ... identified by,提示错误ORA-28221,
SQL> alter user bisal identified by bisal01;
alter user bisal identified by bisal01
*
ERROR at line 1:
ORA-28221: REPLACE not specified
ORA-28221解释如下,指出当前打开了password verification函数,因此未提供原始密码,并且当前用户没有alter user系统权限,就会提示这个错,
SQL> !oerr ora 28221
28221, 00000, "REPLACE not specified"
// *Cause: User is changing password but password verification function is
// turned on and the original password is not specified and the
// user does not have the alter user system privilege.
// *Action: Supply the original password.
这是什么问题?
确实首次碰见,所以稍微研究了一下。其实,这个和Oracle自身提供的用户密码安全机制相关。password verification function,翻译过来,就叫做密码复杂度验证函数,顾名思义,启用这个函数,会让Oracle使用复杂密码验证函数,保护用户,如上面的提示,要求改密码的时候提供原始密码,避免非法修改,11g默认情况下,未启用该函数,通过dba_profiles视图,可以看到PASSWORD_VERIFY_FUNCTION的值是NULL,表示未设置,
PROFILE RESOURCE_NAME LIMIT
---------- -------------------------------- ---------------
DEFAULT COMPOSITE_LIMIT UNLIMITED
DEFAULT SESSIONS_PER_USER UNLIMITED
DEFAULT CPU_PER_SESSION UNLIMITED
DEFAULT CPU_PER_CALL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL UNLIMITED
DEFAULT IDLE_TIME UNLIMITED
DEFAULT CONNECT_TIME UNLIMITED
DEFAULT PRIVATE_SGA UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS 10
DEFAULT PASSWORD_LIFE_TIME 180
DEFAULT PASSWORD_REUSE_TIME UNLIMITED
DEFAULT PASSWORD_REUSE_MAX UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION NULL
DEFAULT PASSWORD_LOCK_TIME 1
DEFAULT PASSWORD_GRACE_TIME 7
那么如何启用?其实很简单,sys账户执行utlpwdmg.sql脚本,即可启用,
SQL> @?/rdbms/admin/utlpwdmg.sql
Function created.
Grant succeeded.
Profile altered.
Function created.
Grant succeeded.
此时,dba_profile视图的PASSWORD_VERIFY_FUNCTION值,已经设置为VERIFY_FUNCTION_11G,
PROFILE RESOURCE_NAME LIMIT
---------- ---------------------------- -------------------------
DEFAULT COMPOSITE_LIMIT UNLIMITED
DEFAULT SESSIONS_PER_USER UNLIMITED
DEFAULT CPU_PER_SESSION UNLIMITED
DEFAULT CPU_PER_CALL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL UNLIMITED
DEFAULT IDLE_TIME UNLIMITED
DEFAULT CONNECT_TIME UNLIMITED
DEFAULT PRIVATE_SGA UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS 10
DEFAULT PASSWORD_LIFE_TIME 180
DEFAULT PASSWORD_REUSE_TIME UNLIMITED
DEFAULT PASSWORD_REUSE_MAX UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G
DEFAULT PASSWORD_LOCK_TIME 1
DEFAULT PASSWORD_GRACE_TIME 7
VERIFY_FUNCTION_11G,这是什么?
我们回过头来,再看下脚本utlpwdmg.sql,可以看到,描述信息就说明了这个脚本的作用,他是通过设置default的密码资源限制来启用密码管理特性,其实就是设置default的profile的PASSWORD_VERIFY_FUNCTION值,并指出这个函数是密码复杂度的最小检查集合,更像是个例子,用户可以基于这个函数开发出他们需要的更复杂的密码验证函数,追溯这个脚本的历史,可以看到,在1996年就已经开发,从这点足以看出Oracle软件设计的缜密,以及前瞻性,
Rem $Header: rdbms/admin/utlpwdmg.sql /st_rdbms_11.2.0/1 2013/01/31 01:34:11 skayoor Exp $
Rem
Rem utlpwdmg.sql
Rem
Rem Copyright (c) 2006, 2013, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem utlpwdmg.sql - script for Default Password Resource Limits
Rem
Rem DESCRIPTION
Rem This is a script for enabling the password management features
Rem by setting the default password resource limits.
Rem
Rem NOTES
Rem This file contains a function for minimum checking of password
Rem complexity. This is more of a sample function that the customer
Rem can use to develop the function for actual complexity checks that the
Rem customer wants to make on the new password.
Rem
Rem MODIFIED (MM/DD/YY)
Rem skayoor 01/17/13 - Backport skayoor_bug-14671375 from main
Rem asurpur 05/30/06 - fix - 5246666 beef up password complexity check
Rem nireland 08/31/00 - Improve check for username=password. #1390553
Rem nireland 06/28/00 - Fix null old password test. #1341892
Rem asurpur 04/17/97 - Fix for bug479763
Rem asurpur 12/12/96 - Changing the name of password_verify_function
Rem asurpur 05/30/96 - New script for default password management
Rem asurpur 05/30/96 - Created
接着,脚本中定义了VERIFY_FUNCTION_11G,以及具体的逻辑,包括设置密码的长度、包含的字符和数字的个数、前后两次修改是否相同等,
-- This script sets the default password resource parameters
-- This script needs to be run to enable the password features.
-- However the default resource parameters can be changed based
-- on the need.
-- A default password complexity function is also provided.
-- This function makes the minimum complexity checks like
-- the minimum length of the password, password not same as the
-- username, etc. The user may enhance this function according to
-- the need.
-- This function must be created in SYS schema.
-- connect sys/<password> as sysdba before running the script
CREATE OR REPLACE FUNCTION verify_function_11G
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
db_name varchar2(40);
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);
i_char varchar2(10);
simple_password varchar2(10);
reverse_user varchar2(32);
...
注意到,其中有段逻辑,就是修改default这个profile,PASSWORD_VERIFY_FUNCTION设置为了VERIFY_FUNCTION_11G,
-- This script alters the default parameters for Password Management
-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION verify_function_11G;
此时,登录账户bisal,执行alter user ... identified,提示ORA-28221,
SQL> alter user bisal identified by bisal01;
alter user bisal identified by bisal01
*
ERROR at line 1:
ORA-28221: REPLACE not specified
使用简单密码,还会禁止更改,
SQL> alter user bisal identified by bisal01 replace bisal;
alter user bisal identified by bisal01 replace bisal
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8
按照提示,使用REPLACE,加上强密码,才可以执行,
SQL> alter user bisal identified by bisal00000 replace bisal;
User altered.
但是,如果使用sys,不用REPLACE,就可以执行,因为账户sys具有alter user的系统权限,
SQL> alter user bisal identified by bisal111111;
User altered.
这个功能,是可以禁用的,就是将PASSWORD_VERIFY_FUNCTION置空,
SQL> alter profile default limit password_verify_function null;
Profile altered.
此时即使bisal账号,不用REPLACE,都可以改了,而且不用强密码,
SQL> alter user bisal identified by bisal;
User altered.
虽然只是个密码复杂度验证函数,但以小见大,Oracle在设计层面,确实很多值得我们借鉴和学习的,无论现在各种开源数据库,炒的多么热,如果打算踏实学点技术,Oracle还是不二选择。
参考:
The Conditions In password_verify_function Not Applied When ALTER USER Privilege Granted (Doc ID 2363109.1)
OERR: ORA-28221 "REPLACE not specified" Reference Note (Doc ID 194726.1)
https://blog.csdn.net/cuiqu7295/article/details/100282895
https://www.2cto.com/database/201305/215210.html
https://blog.csdn.net/xqf222/article/details/50263181/