September 12 2015

The Password Validation Plugin

安装办法:
在配置文件中打开
[mysqld]
plugin-load=validate_password.so
validate-password=FORCE_PLUS_PERMANENT
validate_password_policy=2

并装载plugin:
mysql>>INSTALL PLUGIN validate_password SONAME ‘validate_password.so’;
即可。

The Password Validation Plugin

The validate_password plugin (available as of MySQL 5.6.6) can be used to test passwords and improve security. This plugin implements two capabilities:

  • In statements that assign a password supplied as a cleartext value, the value is checked against the current password policy and rejected if it is weak (the statement returns an ER_NOT_VALID_PASSWORD error). This affects the CREATE USERGRANT, and SET PASSWORD statements. Passwords given as arguments to the PASSWORD()and OLD_PASSWORD() functions are checked as well.
  • The strength of potential passwords can be assessed using the VALIDATE_PASSWORD_STRENGTH() SQL function, which takes a password argument and returns an integer from 0 (weak) to 100 (strong).
  • For example, the cleartext password in the following statement is checked. Under the default password policy, which requires passwords to be at least 8 characters long, the password is weak and the statement produces an error:

    mysql> SET PASSWORD = PASSWORD('abc');
    ERROR 1819 (HY000): Your password does not satisfy the current policy
    requirements
    

    Passwords specified as already hashed values are not checked because the original password value is not available:

    mysql> SET PASSWORD = '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E';
    Query OK, 0 rows affected (0.01 sec)
    

    The parameters that control password checking are available as the values of the system variables having names of the form validate_password_xxx. These variables can be modified to configure password checking; see Section 6.1.2.6.2, “Password Validation Plugin Options and Variables”.

    The three levels of password checking are LOWMEDIUM, and STRONG. The default is MEDIUM; to change this, modify the value of validate_password_policy. The policies implement increasingly strict password tests. The following descriptions refer to default parameter values; these can be modified by changing the appropriate system variables.

    • LOW policy tests password length only. Passwords must be at least 8 characters long.
    • MEDIUM policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase and uppercase character, and 1 special (nonalphanumeric) character.
    • STRONG policy adds the condition that password substrings of length 4 or longer must not match words in the dictionary file, if one has been specified.

    If the validate_password plugin is not installed, the validate_password_xxx system variables are not available, passwords in statements are not checked, and VALIDATE_PASSWORD_STRENGTH() always returns 0. For example, accounts can be assigned passwords shorter than 8 characters.

    6.1.2.6.1 Password Validation Plugin Installation

    The password-validation plugin is named validate_password. To be usable by the server, the plugin library object file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, set the value of plugin_dir at server startup to tell the server the location of the plugin directory.

    To load the plugin at server startup, use the --plugin-load option to name the object file that contains the plugin. With this plugin-loading method, the option must be given each time the server starts. For example, put these lines in your my.cnf file:

    [mysqld]
    plugin-load=validate_password.so
    

    If object files have a suffix different from .so on your system, substitute the correct suffix (for example, .dll on Windows).

    Alternatively, to register the plugin at runtime, use this statement (changing the extension as necessary):

    mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
    

    INSTALL PLUGIN loads the plugin, and also registers it in the mysql.plugins table to cause the plugin to be loaded for each subsequent normal server startup.

    If the plugin has been previously registered with INSTALL PLUGIN or is loaded with --plugin-load, you can use the --validate-password option at server startup to control plugin activation. For example, to load the plugin at startup and prevent it from being removed at runtime, use these options:

    [mysqld]
    plugin-load=validate_password.so
    validate-password=FORCE_PLUS_PERMANENT
    

    If it is desired to prevent the server from running without the password-validation plugin, use --validate-password with a value of FORCE or FORCE_PLUS_PERMANENT to force server startup to fail if the plugin does not initialize successfully.

    For general information about installing or uninstalling plugins, see Section 5.1.8.1, “Installing and Uninstalling Plugins”. To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINSstatement. See Section 5.1.8.2, “Obtaining Server Plugin Information”.

    6.1.2.6.2 Password Validation Plugin Options and Variables

    To control the activation of the validate_password plugin, use this option:

    •  --validate-password[=value]
      Introduced 5.6.6
      Command-Line Format --validate-password[=value]
      Permitted Values Type enumeration
      Default ON
      Valid Values ON
      OFF
      FORCE
      FORCE_PLUS_PERMANENT

      This option controls how the server loads the validate_password plugin at startup. The value should be one of those available for plugin-loading options, as described in Section 5.1.8.1, “Installing and Uninstalling Plugins”. For example, --validate-password=FORCE_PLUS_PERMANENT tells the server to load the plugin at startup and prevents it from being removed while the server is running.

      This option is available only if the validate_password plugin has been previously registered with INSTALL PLUGINor is loaded with --plugin-load. See Section 6.1.2.6.1, “Password Validation Plugin Installation”.

    If the validate_password plugin is installed, it exposes several system variables that indicate the parameters that control password checking:

    mysql> SHOW VARIABLES LIKE 'validate_password%';
    +--------------------------------------+--------+
    | Variable_name                        | Value  |
    +--------------------------------------+--------+
    | validate_password_dictionary_file    |        |
    | validate_password_length             | 8      |
    | validate_password_mixed_case_count   | 1      |
    | validate_password_number_count       | 1      |
    | validate_password_policy             | MEDIUM |
    | validate_password_special_char_count | 1      |
    +--------------------------------------+--------+
    

    To change how passwords are checked, you can set any of these variables at server startup, and most of them at runtime. The following list describes the meaning of each variable.

    •  validate_password_dictionary_file
      Introduced 5.6.6
      System Variable (<= 5.6.25) Name validate_password_dictionary_file
      Variable Scope Global
      Dynamic Variable No
      System Variable Name validate_password_dictionary_file
      Variable Scope Global
      Dynamic Variable No
      System Variable (>= 5.6.26) Name validate_password_dictionary_file
      Variable Scope Global
      Dynamic Variable Yes
      Permitted Values Type file name

      The path name of the dictionary file used by the validate_password plugin for checking passwords. This variable is unavailable unless that plugin is installed.

      By default, this variable has an empty value and dictionary checks are not performed. To enable dictionary checks, you must set this variable to a nonempty value. If the file is named as a relative path, it is interpreted relative to the server data directory. Its contents should be lowercase, one word per line. Contents are treated as having a character set of utf8. The maximum permitted file size is 1MB.

      For the dictionary file to be used during password checking, the password policy must be set to 2 (STRONG); see the description of the validate_password_policy system variable. Assuming that is true, each substring of the password of length 4 up to 100 is compared to the words in the dictionary file. Any match causes the password to be rejected. Comparisons are not case sensitive.

      For VALIDATE_PASSWORD_STRENGTH() the password is checked against all policies, including STRONG, so the strength assessment includes the dictionary check regardless of the validate_password_policy value.

      Before MySQL 5.6.26, changes to the dictionary file while the server is running require a restart for the server to recognize the changes. As of MySQL 5.6.26, validate_password_dictionary_file can be set at runtime and assigning a value causes the named file to be read without a restart.

    •  validate_password_length
      Introduced 5.6.6
      System Variable Name validate_password_length
      Variable Scope Global
      Dynamic Variable Yes
      Permitted Values Type integer
      Default 8
      Min Value 0

      The minimum number of characters that passwords checked by the validate_password plugin must have. This variable is unavailable unless that plugin is installed.

      The validate_password_length minimum value is a function of several other related system variables. As of MySQL 5.6.10, the server will not set the value less than the value of this expression:

      validate_password_number_count
      + validate_password_special_char_count
      + (2 * validate_password_mixed_case_count)
      

      If the validate_password plugin adjusts the value of validate_password_length due to the preceding constraint, it writes a message to the error log.

    •  validate_password_mixed_case_count
      Introduced 5.6.6
      System Variable Name validate_password_mixed_case_count
      Variable Scope Global
      Dynamic Variable Yes
      Permitted Values Type integer
      Default 1
      Min Value 0

      The minimum number of lowercase and uppercase characters that passwords checked by the validate_passwordplugin must have if the password policy is MEDIUM or stronger. This variable is unavailable unless that plugin is installed.

    •  validate_password_number_count
      Introduced 5.6.6
      System Variable Name validate_password_number_count
      Variable Scope Global
      Dynamic Variable Yes
      Permitted Values Type integer
      Default 1
      Min Value 0

      The minimum number of numeric (digit) characters that passwords checked by the validate_password plugin must have if the password policy is MEDIUM or stronger. This variable is unavailable unless that plugin is installed.

    •  validate_password_policy
      Introduced 5.6.6
      System Variable Name validate_password_policy
      Variable Scope Global
      Dynamic Variable Yes
      Permitted Values Type enumeration
      Default 1
      Valid Values 0
      1
      2

      The password policy enforced by the validate_password plugin. This variable is unavailable unless that plugin is installed.

      The validate_password_policy value can be specified using numeric values 0, 1, 2, or the corresponding symbolic values LOWMEDIUMSTRONG. The following table describes the tests performed for each policy. For the length test, the required length is the value of the validate_password_length system variable. Similarly, the required values for the other tests are given by other validate_password_xxx variables.

      Policy Tests Performed
      0 or LOW Length
      1 or MEDIUM Length; numeric, lowercase/uppercase, and special characters
      2 or STRONG Length; numeric, lowercase/uppercase, and special characters; dictionary file
      Note

      Before MySQL 5.6.10, validate_password_policy was named validate_password_policy_number.

    •  validate_password_special_char_count
      Introduced 5.6.6
      System Variable Name validate_password_special_char_count
      Variable Scope Global
      Dynamic Variable Yes
      Permitted Values Type integer
      Default 1
      Min Value 0

      The minimum number of nonalphanumeric characters that passwords checked by the validate_password plugin must have if the password policy is MEDIUM or stronger. This variable is unavailable unless that plugin is installed.

    If the validate_password plugin is installed, it exposes several status variables that provide operational information:

    mysql> SHOW STATUS LIKE 'validate_password%';
    +-----------------------------------------------+---------------------+
    | Variable_name                                 | Value               |
    +-----------------------------------------------+---------------------+
    | validate_password_dictionary_file_last_parsed | 2015-06-29 11:08:51 |
    | validate_password_dictionary_file_words_count | 1902                |
    +-----------------------------------------------+---------------------+
    

    The following list describes the meaning of each variable.

    •  validate_password_dictionary_file_last_parsed

      When the dictionary file was last parsed.

      This variable was added in MySQL 5.6.26.

    •  validate_password_dictionary_file_words_count

      The number of words read from the dictionary file.

      This variable was added in MySQL 5.6.26.



Copyright 2019. All rights reserved.

Posted 2015年9月12日 by ucjmh in category "mysql

Leave a Reply

Your email address will not be published. Required fields are marked *