概要文件profile使用verify function定义用户密码格式

于 2019-01-29 11:35:55 发布
阅读量1.9k 收藏
点赞数
分类专栏： oracle学习笔记 文章标签： verify function profile 定义用户密码格式 概要文件
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m18994118189/article/details/86687631
版权

参考mos文档：

 

Password Verify Function Not Enforcing Difference Between Old and New Passwords (Doc ID 816932.1

根据oracle官方文档ID 816932.1的解释，在使用ora_string_distance(old_password, password)验证新旧密码差异时：

如果为当前用户修改当前密码，则由于提供了旧密码和新密码，所以可以实现新旧密码差异对比并对不满足要求的新密码抛出exception

如果为特权用户修改其他用户密码时，则由于有特权而无需提供旧密码，导致无法实现新旧密码差异的比较，而不抛出exception

 

问题：应用说自己无法使用alert user orddata identified by格式修改自己的密码

 

使用sys用户登录

SQL> conn / as sysdba

Connected.

 

查看verify_function的创建语句，我们看到创建和修改密码的要求是大于15个字符、必须要有1个大写、有1个小写、有1个 数字，且old password与new password必须存在连续的4个字符不一致才可以满足要求

SQL> SELECT DBMS_METADATA.GET_DDL('FUNCTION','VERIFY_FUNCTION') FROM DUAL;

 

CREATE OR REPLACE NONEDITIONABLE FUNCTION "SYS"."VERIFY_FUNCTION"

(username varchar2,

password varchar2,

old_password varchar2)

return boolean IS

differ integer;

begin

if not ora_complexity_check(password, chars => 15, upper => 1, lower => 1, digit

=> 1)

then

return(false);

end if;

 

-- Check if the password differs from the previous password by at least

-- 8 characters

if old_password is not null then

differ := ora_string_distance(old_password, password);

if differ < 4 then

raise_application_error(-20032, 'Password should differ from previous '

|| 'password by at least 4 characters');

end if;

end if;

 

return(true);

end;

 

查看password_verfiy_function指定的是verify_function

SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';

COMPOSITE_LIMIT UNLIMITED

SESSIONS_PER_USER UNLIMITED

CPU_PER_SESSION UNLIMITED

CPU_PER_CALL UNLIMITED

LOGICAL_READS_PER_SESSION UNLIMITED

LOGICAL_READS_PER_CALL UNLIMITED

IDLE_TIME UNLIMITED

CONNECT_TIME UNLIMITED

PRIVATE_SGA UNLIMITED

FAILED_LOGIN_ATTEMPTS 10

PASSWORD_LIFE_TIME 180

PASSWORD_REUSE_TIME UNLIMITED

PASSWORD_REUSE_MAX UNLIMITED

PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION

PASSWORD_LOCK_TIME 1

PASSWORD_GRACE_TIME 7

 

16 rows selected.

 

查看用户orddata使用的概要文件

SQL> select username,profile from dba_users where username='ORDDATA';

ORDDATA

DEFAULT

 

使用特权sys用户修改oraddata的密码

1.密码14位

SQL> alter user ORDDATA identified by qwertyuiopasdf;

alter user system identified by qwertyuiopasdf

*

ERROR at line 1:

ORA-28003: password verification for the specified password failed

ORA-20001: Password length less than 15

 

2.密码中没有大写字符

SQL> alter user ORDDATA identified by qwertyuiopasdfg;

alter user system identified by qwertyuiopasdfg

*

ERROR at line 1:

ORA-28003: password verification for the specified password failed

ORA-20023: Password must contain at least 1 uppercase character(s)

 

3.密码中没有数字

SQL> alter user ORDDATA identified by qwertyuiopasdfG;

alter user system identified by qwertyuiopasdfG

*

ERROR at line 1:

ORA-28003: password verification for the specified password failed

ORA-20025: Password must contain at least 1 digit(s)

 

4.密码大于等于15位、有大写字母、有数字

SQL> alter user ORDDATA identified by qwertyuiopasd1G;

 

User altered.

 

登录orddata用户修改自己的密码

SQL> conn ORDDATA/qwertyuiopasd1G;

Connected.

 

修改自己的密码，发现要想修改自己的密码，必须要知道老的密码

SQL> alter user ORDDATA identified by qwertyuiopasdhG;

alter user ORDDATA identified by qwertyuiopasdhG

*

ERROR at line 1:

ORA-28221: REPLACE not specified

 

第一个字母不同

SQL> alter user orddata identified by awertyuiopasd1G replace qwertyuiopasd1G;

alter user orddata identified by awertyuiopasd1G replace qwertyuiopasd1G

*

ERROR at line 1:

ORA-28003: password verification for the specified password failed

ORA-20032: Password should differ from previous password by at least 4 characters

 

前面两个字母不同

SQL> alter user orddata identified by asertyuiopasd1G replace qwertyuiopasd1G;

alter user orddata identified by asertyuiopasd1G replace qwertyuiopasd1G

*

ERROR at line 1:

ORA-28003: password verification for the specified password failed

ORA-20032: Password should differ from previous password by at least 4 characters

 

前面三个字母不同

SQL> alter user orddata identified by asdrtyuiopasd1G replace qwertyuiopasd1G;

alter user orddata identified by asdrtyuiopasd1G replace qwertyuiopasd1G

*

ERROR at line 1:

ORA-28003: password verification for the specified password failed

ORA-20032: Password should differ from previous password by at least 4 characters

 

前面4个字母不同

SQL> alter user orddata identified by asdftyuiopasd1G replace qwertyuiopasd1G;

 

User altered.

 

但是，拥有“ALTER user”权限的用户可以选择使用该权限修改自己的密码，而不需要提供旧密码，方法如下:

SQL> alter user system identified by "oracle123;";

User altered.

SQL> alter user sys identified by Qwertyuiop12345;

User altered.

在这里，用户可以在不知道旧密码的情况下使用特权更改任何用户的密码，旧密码也可以在用户自己的帐户上使用。

 

========================杨阳的测试=====================================

新总，经过测试，使用特权用户，无论修改自己的密码或者其他用户的密码，在需要和旧密码比较这部分，都没有报错

如果是当前用户修改当前用户的密码，则在和旧密码比较时，会出现新密码不符合要求的报错。

SQL> alter user sys identified by Qwertyuiop12345;

 

User altered.

 

SQL> alter user sys identified by Qwertyuiop16789;

 

User altered.

 

SQL> alter user sys identified by Qwertyuiop16780;

 

User altered.

 

SQL> create user passwdtest identified by Qwertyuiop12345 account unlock;

 

User created.

 

SQL> alter user passwdtest identified by Qwertyuiop16789;

 

User altered.

 

SQL> alter user passwdtest identified by Qwertyuiop16780;

 

User altered.

 

SQL> grant connect to passwdtest;

 

Grant succeeded.

 

SQL> conn passwdtest/Qwertyuiop16780;

Connected.

SQL> alter user passwdtest identified by Qwertyuiop16790 replace  Qwertyuiop16780;

alter user passwdtest identified by Qwertyuiop16790 replace  Qwertyuiop16780

*

ERROR at line 1:

ORA-28003: password verification for the specified password failed

ORA-20032: Password should differ from previous password by at least 4

characters

 

 

SQL> alter user passwdtest identified by Qwertyuiop12345 replace  Qwertyuiop16780;

alter user passwdtest identified by Qwertyuiop12345 replace  Qwertyuiop16780

*

ERROR at line 1:

ORA-28007: the password cannot be reused

 

 

SQL> alter user passwdtest identified by Qwertyuiop54321 replace  Qwertyuiop16780;

 

User altered.

 

 

使用的verify_function如下（这也是当前生产环境使用的verify_function）

create or replace function sys.verify_function

(username varchar2,

password varchar2,

old_password varchar2)

return boolean IS

   differ integer;

begin

   if length(password) > 15 then

      raise_application_error(-20020, 'Password length more than 15 characters');

   end if;

   IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN

      raise_application_error(-20002, 'Password too simple');

   END IF;

   if not ora_complexity_check(password, chars => 10, upper => 1, lower => 1,

                           digit => 1) then

      return(false);

   end if;

 

   -- Check if the password differs from the previous password by at least

   -- 4 characters

   if old_password is not null then

      differ := ora_string_distance(old_password, password);

      if differ < 4 then

         raise_application_error(-20032, 'Password should differ from previous '

                                 || 'password by at least 4 characters');

      end if;

   end if;

 

   return(true);

end;

 

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值