Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (中文) » SQL Anywhere 服务器 - 数据库管理 » 配置数据库 » 数据库选项 » 数据库选项简介 » 按字母顺序排列的选项列表

 

verify_password_function 选项 [数据库]

使用 verify_password_function 选项来实现口令规则。

允许值

字符串

缺省值

空字符串(设置口令时不调用任何函数)。

作用域

需要 DBA 权限。

注释

创建或设置不为 NULL 的口令时,将自动调用由 verify_password_function 指定的函数。要防止用户替换函数,请将选项值设置为 owner.function-name。用户必须拥有口令才能连接到数据库。口令区分大小写且不可以:

创建或更改口令时,口令先被转换为 UTF-8,然后再散列并存储在数据库中。如果数据库被卸载和重装到带有不同字符集的数据库,则现有口令将继续有效。如果数据库服务器不能将客户端的字符集转换为 UTF-8,建议采用由 7 位 ASCII 字符组成的口令,因为使用其它字符可能会无法正常工作。

可以使用以下任一语句来设置口令:

对用于创建或设置口令的语句进行校验之后,将调用函数,使用指定的规则来验证口令。如果口令符合指定的规则,该函数必须返回 NULL 以表示成功并执行调用的语句。否则,通过设置错误或返回非 NULL 字符串来指示出错。如果返回非 NULL 字符串,则该字符串将作为失败原因包括在错误中,以提供给用户。

口令验证函数具有两个参数:user-name VARCHAR(128) 和 new-pwd VARCHAR(255)。它返回类型为 VARCHAR(255) 的值。建议您对口令验证函数执行 ALTER FUNCTION function-name SET HIDDEN 语句,以确保它不能通过使用调试程序而分步执行。如果设置了 verify_password_function 选项,则不允许使用 GRANT CONNECT 语句指定一个以上的用户 ID 和口令。

有关口令规则的详细信息,请参见使用口令验证

另请参见
示例

以下示例定义表和函数并设置一些登录策略选项。它们共同实现了高级口令规则,其中包括要求在口令中使用某些类型的字符、不允许口令重用和为口令设置有效期。当创建用户 ID 或更改口令时,数据库服务器会通过 verify_password_function 选项调用函数。应用程序可以调用 post_login_procedure 选项指定的过程,以报告口令应在到期前进行更改。

也可以从以下位置获得此示例的代码:samples-dir\SQLAnywhere\SQL\verify_password.sql。(有关 samples-dir 的信息,请参见示例目录。)

-- only DBA should have permissions on this table
CREATE TABLE DBA.t_pwd_history(
        pk          INT         DEFAULT AUTOINCREMENT PRIMARY KEY,
        user_name   CHAR(128),  -- the user whose password is set
        pwd_hash    CHAR(32) ); -- hash of password value to detect
                                -- duplicate passwords

-- called whenever a non-NULL password is set
-- to verify the password conforms to password rules
CREATE FUNCTION DBA.f_verify_pwd( uid     VARCHAR(128),
                                  new_pwd VARCHAR(255) )
RETURNS VARCHAR(255)
BEGIN
    -- a table with one row per character in new_pwd
    DECLARE local temporary table pwd_chars(
            pos INT PRIMARY KEY,    -- index of c in new_pwd
            c   CHAR( 1 CHAR ) );   -- character
    -- new_pwd with non-alpha characters removed
    DECLARE pwd_alpha_only      CHAR(255);
    DECLARE num_lower_chars     INT;

    -- enforce minimum length (can also be done with
    -- min_password_length option)
    IF length( new_pwd ) < 6 THEN
        RETURN 'password must be at least 6 characters long';
    END IF;

    -- break new_pwd into one row per character
    INSERT INTO pwd_chars SELECT row_num, substr( new_pwd, row_num, 1 )
                            FROM dbo.RowGenerator
                            WHERE row_num <= length( new_pwd );

    -- copy of new_pwd containing alpha-only characters
    SELECT list( c, '' ORDER BY pos ) INTO pwd_alpha_only
        FROM pwd_chars WHERE c BETWEEN 'a' AND 'z' OR c BETWEEN 'A' AND 'Z';

    -- number of lower case characters IN new_pwd
    SELECT count(*) INTO num_lower_chars
        FROM pwd_chars WHERE CAST( c AS BINARY ) BETWEEN 'a' AND 'z';

    -- enforce rules based on characters contained in new_pwd
    IF ( SELECT count(*) FROM pwd_chars WHERE c BETWEEN '0' AND '9' )
           < 1 THEN
        RETURN 'password must contain at least one numeric digit';
    ELSEIF length( pwd_alpha_only ) < 2 THEN
        RETURN 'password must contain at least two letters';
    ELSEIF num_lower_chars = 0
           OR length( pwd_alpha_only ) - num_lower_chars = 0 THEN
        RETURN 'password must contain both upper- and lowercase characters';
    END IF;

    -- not the same as any user name
    -- (this could be modified to check against a disallowed words table)
    IF EXISTS( SELECT * FROM SYS.SYSUSER
                    WHERE lower( user_name ) IN ( lower( pwd_alpha_only ),
                                                  lower( new_pwd ) ) ) THEN
        RETURN 'password or only alphabetic characters in password ' ||
               'must not match any user name';
    END IF;

    -- not the same as any previous password for this user
    IF EXISTS( SELECT * FROM t_pwd_history
                    WHERE user_name = uid
                      AND pwd_hash = hash( uid || new_pwd, 'md5' ) ) THEN
        RETURN 'previous passwords cannot be reused';
    END IF;

    -- save the new password
    INSERT INTO t_pwd_history( user_name, pwd_hash )
        VALUES( uid, hash( uid || new_pwd, 'md5' ) );

    RETURN( NULL );
END;

ALTER FUNCTION DBA.f_verify_pwd SET HIDDEN;
GRANT EXECUTE ON DBA.f_verify_pwd TO PUBLIC;
SET OPTION PUBLIC.verify_password_function = 'DBA.f_verify_pwd';


-- All passwords expire in 180 days. Expired passwords can be changed
-- by the user using the NewPassword connection parameter.
ALTER LOGIN POLICY DEFAULT password_life_time = 180;

-- If an application calls the procedure specified by the 
-- post_login_procedure option, then the procedure can be used to 
-- warn the user that their password is about to expire. In particular, 
-- Interactive SQL and Sybase Central call the post_login_procedure.
ALTER LOGIN POLICY DEFAULT password_grace_time = 30;

-- Five consecutive failed login attempts will result in a non-DBA 
-- user ID being locked.
ALTER LOGIN POLICY DEFAULT max_failed_login_attempts = 5;