开发者社区> 苍雪明南> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

使用PASSWORD_VERIFY_FUNCTION设置用户密码复杂度

简介: 依据PASSWORD_VERIFY_FUNCTION可以设置oracle用户的密码复杂度,比如密码长度>=10,必须包含字母/数字等首先需要创建一个密码验证的function,然后设置profile的PASSWORD_VERIFY_FUNCTION即可 SQL> s...
+关注继续查看

依据PASSWORD_VERIFY_FUNCTION可以设置oracle用户的密码复杂度,比如密码长度>=10,必须包含字母/数字等
首先需要创建一个密码验证的function,然后设置profile的PASSWORD_VERIFY_FUNCTION即可

SQL> select TEXT from dba_source where NAME='VERIFY_JUSTIN_USER';

TEXT
------------------------------------------------------------------------------------------------------------------------------------
FUNCTION verify_JUSTIN_user (  username VARCHAR2,
                                          password VARCHAR2,
                                          old_password varchar2 )
    RETURN boolean
    IS

        passwordMinLength   INTEGER;
        passwordLength      INTEGER;
        differ              INTEGER;
        differMinLength     INTEGER;
        isDigit             BOOLEAN;
        isChar              BOOLEAN;
        isPunct             BOOLEAN;
        digitArray          VARCHAR2(20);
        punctArray          VARCHAR2(25);
        charArray           VARCHAR2(52);

    BEGIN

        digitArray         := '0123456789';
        charArray          := 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
        punctArray         := '!"#$%&()``*+,-/:;<=>?_';
        passwordMinLength  := 10;
        differMinLength    := 2;--HAD-1
        passwordLength     := LENGTH(password);
        isDigit            := FALSE;
        isChar             := FALSE;
        isPunct            := FALSE;

        -- +------------------------------------------------+
        -- | Check if the password is same as the username  |
        -- +------------------------------------------------+
        IF NLS_LOWER(password) = NLS_LOWER(username) THEN
            raise_application_error( -20001, 'Password same as or similar to user' );
        END IF;


        -- +-------------------------------------------------+
        -- | Check that password is more than [x] characters |
        -- | in length.                                      |
        -- +-------------------------------------------------+
        IF (LENGTH(password) < passwordMinLength) THEN
            raise_application_error( -20002, 'Password must be greater than '
                                            ||
                                            passwordMinLength
                                            ||
                                            ' characters.' );
        END IF;


        -- +----------------------------------------------------+
        -- | Check if the password is too simple. A dictionary  |
        -- | of words may be maintained and a check may be made |
        -- | so as not to allow the words that are too simple   |
        -- | for the password.                                  |
        -- +----------------------------------------------------+
        IF NLS_LOWER(password) IN (   'welcome'
                                    , 'database'
                                    , 'account'
                                    , 'user'
                                    , 'password'
                                    , 'oracle'
                                    , 'computer'
                                    , 'abcd') THEN
            raise_application_error(-20003, 'Password too simple');
        END IF;

        -- +-----------------------------------------------------+
        -- | Check if the password contains at least one letter, |
        -- | one digit and one punctuation mark.                 |
        -- +-----------------------------------------------------+

        -- +-----------------------------------------------------+
        -- | (1.) Check for the digit                            |
        -- +-----------------------------------------------------+
        FOR i IN 1..10 LOOP
            FOR j IN 1..passwordLength LOOP
                IF SUBSTR(password,j,1) = SUBSTR(digitArray,i,1) THEN
                    isDigit := TRUE;
                    GOTO findchar;
                END IF;
            END LOOP;
        END LOOP;

        IF isDigit = FALSE THEN
            raise_application_error(-20004, 'Password should contain at least '
                                             ||
                                             ' one digit,'
                                             ||
                                             ' one character and'
                                             ||
                                             ' one punctuation');
        END IF;

        -- +-----------------------------------------------------+
        -- | (2.) Check for the character                        |
        -- +-----------------------------------------------------+
        <<findchar>>
        FOR i IN 1..LENGTH(charArray) LOOP
            FOR j IN 1..passwordLength LOOP
                IF SUBSTR(password,j,1) = SUBSTR(charArray,i,1) THEN
                    isChar := TRUE;
                    GOTO findpunct;
                END IF;
            END LOOP;
        END LOOP;

        IF isChar = FALSE THEN
            raise_application_error(-20004, 'Password should contain at least '
                                             ||
                                             ' one digit,'
                                             ||
                                             ' one character and'
                                             ||
                                             ' one punctuation');
        END IF;

        -- +-----------------------------------------------------+
        -- | (3.) Check for the punctuation                      |
        -- +-----------------------------------------------------+
        <<findpunct>>
        FOR i IN 1..LENGTH(punctArray) LOOP
            FOR j IN 1..passwordLength LOOP
                IF SUBSTR(password,j,1) = SUBSTR(punctArray,i,1) THEN
                    isPunct := TRUE;
                    GOTO endsearch;
                END IF;
            END LOOP;
        END LOOP;

        IF isPunct = FALSE THEN
            raise_application_error(-20004, 'Password should contain at least '
                                             ||
                                             ' one digit,'
                                             ||
                                             ' one character and'
                                             ||
                                             ' one punctuation');
        END IF;

        <<endsearch>>

        -- +-----------------------------------------------------+
        -- | Check that the new password is not null.            |
        -- +-----------------------------------------------------+
        IF old_password = '' THEN
            raise_application_error(-20005, 'Old password is null');
        END IF;


        -- +-----------------------------------------------------+
        -- | Check if the password differs from the previous     |
        -- | password by at least [x] letters.                   |
        -- +-----------------------------------------------------+
        differ := ABS(LENGTH(old_password) - LENGTH(password));

        IF differ < differMinLength THEN

            IF LENGTH(password) < LENGTH(old_password) THEN
                passwordLength := LENGTH(password);
            ELSE
                passwordLength := LENGTH(old_password);
            END IF;

            FOR i IN 1..passwordLength LOOP

                IF SUBSTR(password,i,1) != SUBSTR(old_password,i,1) THEN
                    differ := differ + 1;
                END IF;
            END LOOP;

            IF differ < differMinLength THEN
                raise_application_error(-20006, 'Password should differ by at least '
                                                ||
                                                differMinLength
                                                ||
                                                ' characters.');
            END IF;

        END IF;

        -- +-----------------------------------------------------+
        -- | Well, looks like we passed all of the requirements. |
        -- | Simple return 'true'.                               |
        -- +-----------------------------------------------------+
        RETURN(true);

    END;

188 rows selected.

SQL> select * from dba_profiles;

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
JUSTIN_PROFILE                 PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_JUSTIN_USER

此后创建user可以指定该profile,密码若验证无法通过泽用户创建会失败
SQL> create user sagda identified by"asd245(" profile JUSTIN_PROFILE;
create user sagda identified by"asd245(" profile JUSTIN_PROFILE
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password must be greater than 10 characters.


SQL> create user sagda identified by"asd245234155" profile JUSTIN_PROFILE;
create user sagda identified by"asd245234155" profile JUSTIN_PROFILE
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20004: Password should contain at least  one digit, one character and one punctuation

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
苹果自研 5G 芯片或已失败;腾讯 QQ 回应大规模账号被盗;Vim 9.0 发布 | 思否周刊
苹果自研 5G 芯片或已失败;腾讯 QQ 回应大规模账号被盗;Vim 9.0 发布 | 思否周刊
9 0
微软行星云计算Planetary Computer——定了用python作为主要语言,告别GEE的JavaScript痛苦!
微软行星云计算Planetary Computer——定了用python作为主要语言,告别GEE的JavaScript痛苦!
65 0
使用Source Monitor检测Java代码的环复杂度
使用Source Monitor检测Java代码的环复杂度
18 0
使用Source Monitor检测Java代码的环复杂度
使用Source Monitor检测Java代码的环复杂度
38 0
使用Source Monitor检测Java代码的环复杂度
使用Source Monitor检测Java代码的环复杂度
43 0
【WPF】使用Popup控件做浮窗/提示框
原文:【WPF】使用Popup控件做浮窗/提示框 需求:当鼠标移入某个区域时,弹出一个浮窗,以便用户进行下一步操作。 效果如下图: 当鼠标移入左上角的【多选显示】框内,出现下面的浮窗(悬浮在原UI之上)。
2651 0
+关注
苍雪明南
主流数据库:Oracle、MySQL、DB2 编程语言:Java、Python、shell 大数据工具:ZooKeeper、kafka
412
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载