Password Validation Plugin in MySQL5.6

Table of Contents

1. What is Password Validation Plugin
2. Why we need Password Validation Plugin
3. Pre–checks for Implementation
4. Installation and UN-installation steps
4.1 Run-time password plugin installation
4.2 Permanent password plugin installation
5. Password validation plugin options and variables
6. Test scenarios

1.What is Password Validation Plugin

The Password Validation Plugin is an add on which is supporting from MySQL5.6.6 version. Its make sure that the end user provided password meet certain minimal security criteria.

The password validate plugin check user password as they are set through SET PASSWORD and GRANT statements, it will allow if its meet the password criteria else it will reject.

2. Why we need Password Validation Plugin
As of now you can set MySQL password as your wish, even you can set empty password or single charter password also. Have you think about if some one/hacker crack your password what happen your valuable data ?
To avoid such situation strongly recommend to implement the password validate plugin which emphasis to you set more secure password.

3. Pre–checks for Implementation
Before install password validation plugin make sure below points:
3.1. Password validation plugin file “validate_password.so” under MySQL liblery plugin directory.
Login into MySQL and check plugin directory path:
mysql> select @@plugin_dir;
+——————————————+
|         @@plugin_dir                      |
+——————————————+
|        /var/lib/mysql/plugin/             |
+——————————————+
Check it now on OS level
-bash-4.1$ ls -l /var/lib/mysql/plugin/validate_password.so
Expected Output:
-rwxr-xr-x 1 mysql mysql 136K Aug 27 2016 /var/lib/mysql/plugin/validate_password.so

3.2. Login into respective MySQL instance and check whether already the password validate plugin installed or not.
If password validation plugin is not installed:

Expected Output:
mysql> show plugins;
+—————————-+—————+—————————–+——————+————————-+
|               Name         |   Status   |          Type                  |       Library  |     License            |
+—————————-+————–+——————————+——————+————————-+
| binlog                      |  ACTIVE | STORAGE ENGINE  | NULL           | PROPRIETARY  |
| sha256_password | ACTIVE  | AUTHENTICATION | NULL           | PROPRIETARY  |

| audit_log                | ACTIVE  | AUDIT                         | audit_log.so | PROPRIETARY |
+—————————+—————+——————————-+——————–+———————–+

If password validation plugin is installed:
Expected Output:
mysql> show plugins;
+—————————-+—————+—————————+—————————–+————————-+
|               Name         |   Status   |               Type          |       Library               |     License            |
+—————————-+————–+—————————-+——————————+————————-+
| binlog                      | ACTIVE |STORAGE ENGINE |NULL                           | PROPRIETARY  |
| sha256_password | ACTIVE |AUTHENTICATION|NULL                           | PROPRIETARY  |

| audit_log                 | ACTIVE |AUDIT                       |audit_log.so                 |PROPRIETARY |
|validate_password| ACTIVE| validate password   |validate_password.so|PROPRIETARY |
+—————————+—————+—————————-+———————————+———————–+

other way to cross verify on information_schema using below query.
If password validation plugin is not installed:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE ‘validate%’;
Empty set (0.03 sec)

If password validation plugin is installed:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE ‘validate%’;
+——————————-+—————————-+
| PLUGIN_NAME         | PLUGIN_STATUS   |
+——————————-+—————————-+
| validate_password  | ACTIVE                   |
+——————————-+—————————-+

4. Installation and UN-installation steps

If the password validate plugin not installed then proceed with below steps.

4.1 Login to respective MySQL instance and install the password plugin during the run time.
mysql> INSTALL PLUGIN validate_password SONAME ‘validate_password.so’;
Query OK, 0 rows affected (0.13 sec)

Once installation get completed you can see below expected output.
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE ‘validate%’;
+——————————+—————————+
| PLUGIN_NAME       | PLUGIN_STATUS |
+——————————+—————————+
| validate_password | ACTIVE                   |
+—————————–+—————————-+

mysql> show plugins;
+—————————-+—————+—————————+—————————–+————————-+
|               Name         |   Status   |               Type          |       Library               |     License            |
+—————————-+————–+—————————-+——————————+————————-+
| binlog                      | ACTIVE |STORAGE ENGINE |NULL                           | PROPRIETARY  |
| sha256_password | ACTIVE |AUTHENTICATION|NULL                           | PROPRIETARY  |

| audit_log                 | ACTIVE |AUDIT                       |audit_log.so                 |PROPRIETARY |
|validate_password| ACTIVE| validate password|validate_password.so|PROPRIETARY|
+—————————+—————+—————————-+———————————+———————–+

[same way we can uninstall the password validate plugin during run time]
And you can see password validation variables, if its not installed these variables not exist.

mysql> SHOW GLOBAL 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          |
+————————————————————+—————-+

Above variables values are default, you can change variable’s values by dynamically except “validate_password_dictionary_file” variable.
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.

4.2 Permanent password plugin installation.
It will help to prevent removing the password plugin at run time and not require to load the plugin every time after start-up server.

Make it below entry in [mysqld] session on my.cnf file

[mysqld]
plugin-load = validate_password.so
validate-password = FORCE_PLUS_PERMANENT
validate_password_policy = 1
validate_password_dictionary_file = /var/lib/mysql/plugin/password_dictionary.txt

Once make it above entries restart the MySQL for change get reflect.
Login into MySQL and verify.
Expected output:
mysql> SHOW VARIABLES LIKE ‘validate_password%’;
+———————————————————+————————————————————————–+
| Variable_name                                         |                               Value                                                    | +——————————————————–+————————————————————————–+
| validate_password_dictionary_file         |/var/lib/mysqlplugin/password_dictionary.txt       |
| validate_password_length                       | 8                                                                                       | | validate_password_mixed_case_count  | 1                                                                                      | | validate_password_number_count         | 1                                                                                     |
| validate_password_policy                         | STRONG                                                                       |
| validate_password_special_char_count| 1                                                                                      |
+————————————–+——————————————————————————————-+

5. Password validation plugin options and variables
Below variables are controlling activation of password validation plugin.
Plugin_dir
Plugin_load
Validate_password
Validate_password_dictionary_file
Validate_password_length
Validate_password_mixed_case_count
Validate_password_number_count
Validate_password_policy
Validate_password_special_char_count

Plugin_dir:
The path name of the plugin directory, if we not mentioned then I will check in default path : BASEDIR/lib/plugin.

Plugin_load:
This option tells the server to load the named plugins at start-up.

Validate_password:
This option controls how the server loads the validate_password plugin at start-up, If its enabled then only we could see its variables.
Options:
ON – If the plugin fails to initialize the server runs with the plugin disabled.
OFF – Its tell the server to disable the plugin.
FORCE – If plugin initialization fails, the server does not start.
FORCE_PLUS_PERMANENT – Like FORCE, but in addition prevents the plugin
from being unloaded at run-time.

Validate_password_dictionary_file:
The path name of the dictionary file used by validate_password plugin for checking passwords. 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.

Validate_password_length:
Server will not set the value less than the value of below expression

validate_password_number_count
+ validate_password_special_char_count
+ (2 * validate_password_mixed_case_count)

If you changed above variables value according to re-set the ‘Validate_password_length’ values and it writes a message to the error log.
Validate_password_mixed_case_count:
It check minimum number of lowercase and uppercase characters in that passwords.
Validate_password_number_count:
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.

Validate_password_policy:
It value can be specified using numeric values 0, 1, 2, or the corresponding symbolic values LOW, MEDIUM, STRONG

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

Validate_password_special_char_count :
The minimum number of non-alphanumeric characters that passwords checked by the validate_password plugin must have if the password policy is MEDIUM or stronger.

Test scenarios:
After Installation verify whether password validate plugin working as expected or not.
Test #1.
Create a test user and try to set password with not satisfying condition.
mysql> GRANT SELECT ON *.* TO ‘testpass’@’172.16.%.%’ IDENTIFIED BY ‘Cal5spl8’;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Now try create a user with satisfying password condition.
mysql> GRANT SELECT ON *.* TO ‘testpass’@’172.16.%.%’ IDENTIFIED BY ‘Cal5$pl8’;
Query OK, 0 rows affected (0.01 sec)

Test #2:
Create a user and try to set dictionary password.
Dictionary file:
$ cat /var//lib/mysql/plugin/password_dictionary_assic.txt
P@ssw0rd
wa@!2go0

Try to set dictionary password.
mysql> GRANT SELECT ON *.* TO ‘testpass’@’172.16.%.%’ IDENTIFIED BY ‘P@ssw0rd’;ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Try to set non dictionary password.
mysql> GRANT SELECT ON *.* TO ‘testpass’@’172.16.%.%’ IDENTIFIED BY ‘P@ssw1rd’;Query OK, 0 rows affected (0.01 sec)

 

References : https://dev.mysql.com/doc/refman/5.6/en/validate-password-plugin.html

================================== END ===================================

Advertisements

MySQL Config Editer

MySQL Config Editor

 

Contents
1. Why we need mysql_config_editor
2. How to configure mysql_config_editor
3. How to connect MySQL using mysql_config_editor
4. How to modify/Remove mysql_config_editor
5. Pros and Cons of mysql_config_editor

The MySQL config editor (secure login) intention is to make sure your valuable data should be in more secure and your login credentials should not visible to others.

Why we need mysql_config_editor :

There are some block holes to theft your data by using your credentials.
Example:
If you stored your password in option file like “.my.cnf” to login the MySQL, since its stored password as plain text so whoever has access the file they can easily read it.
[client]
user = user_name
password = your_password

On some systems, your password becomes visible to system status programs such as system process, that may be invoked by other users to display command lines. To overcome the security issue you can the mysql_config_editor utility (available from MySQL 5.6.6) which enables you to store authentication credentials in an encrypted login file named .mylogin.cnf.

The file location is the %APPDATA%\MySQL directory on Windows and the current user’s home directory on Red-hat / fedora / Ubuntu / other Linux-flavor. The file can be read later by MySQL clientprograms to obtain authentication credentials for connecting to MySQL Server.

The encryption used by mysql_config_editor prevents passwords from appearing in .mylogin.cnf as clear text and provides a measure of security by preventing accidental password exposure.

How to configure mysql_config_editor

Using mysql_config_editor utility we can create N number of login path for different instances on the server or for the remote server.

shell> mysql_config_editor [program_options] command [command_options]

program_options consists of general mysql_config_editor options, ‘command’ indicates what command to perform, and ‘command_options’ indicates any additional options needed by the command.
The command indicates what action to perform on the .mylogin.cnf login file.

For example:
set writes a login path to the file.
remove removes a login path.
print displays login path contents.
Any options given provide information to the command, such as the login path name and the values to use in the login path.
The position of the command name within the set of program arguments is significant.

For example, these command lines have the same arguments, but produce different results:
mysql_config_editor –help set                # It Display the information except ‘set’
mysql_config_editor set –help                 # It Display only ‘set’ information

Suppose that you want to establish two login paths named local and remote for connecting to the local MySQL server and a server on the host remote.mysql.com.

shell> mysql_config_editor set –login-path=local –host=localhost –user=localuser –port=3366 –password
Enter password: enter password “localpass” here

shell> mysql_config_editor set –login-path=remote –host=remote.mysql.com –user=remoteuser
–port=3377 –password

Enter password: enter password “remotepass” here

To see what mysql_config_editor wrote to the .mylogin.cnf file, use the print command:
shell>ls -la
# you can see the “.mylogin.cnf” under user home directory as a hidden file
-rw——-. 1 user group 392 Dec 1 16:08 .mylogin.cnf
shell> mysql_config_editor print –all
[local]
user = localuser
password = *****
host = localhost
port = 3366
[remote]
user = remoteuser
password = *****
host = remote.mysql.com
port = 3377

If you try to see . mylogin.cnf file, its should be encrypted as below:
shell>cat .mylogin.cnf
5 � >S3%# � 62 ���� # � ^ � J\ � &#8y �� Q �� r ��� #3J: � ,?
#k2 �Ɇ @# �� #ab(D � #Y � M#JK# � q

How to connect MySQL using mysql_config_editor :

Once you created secure login using mysql_config_editor you can connect the appropriate MySQL
instance using login path as below.
To connect local MySQL instance(3366):

shell> mysql –login-path=local

To connect to the remote MySQL instance(3377)

shell> mysql –login-path=remote

When you use the set command with mysql_config_editor to create a login path, you need not specify all the possible option values (host name, user name,port and password). Only those values given are written to the path. Any missing values required later can be specified when you invoke a client path to connect to the MySQL server.

For example, When you create remote login path if missed to give remote hostname option then you can explicitly provide host option to connect the remote MySQL server “remote2.mysql.net” as below:

shell> mysql –login-path=remote –host=remote2.mysql.net

How to modify/Remove mysql_config_editor :

You can modify/remove exist secure options / login path as below:
if you want to remove host option from remote login path.

shell> mysql_config_editor remove –login-path=remote –host
shell> mysql_config_editor print –login-path=remote
[remote]
user = remoteuser
password = *****
port = 3377

If you wants to remove complete remote/local login path, you can remove as below:

shell> mysql_config_editor remove –login-path=remote
shell> mysql_config_editor print –all
[local]
user = localuser
password = *****
host = localhost
port = 3366

Note: –port and –socket options are supported as of MySQL 5.6.11

Pros and Cons of mysql_config_editor :

Pros:
1. Easy way to hide the credentials from attacker.
2. Preventing accidental password exposure.
3. Easy to login in MySQL instance where have multiple instances (Local/Remote).
Cons:
1. We could not add additional option in existing login path, if you need add then you need to remove
full login path and create new login path with require options.
2. If the login path name already exists in .mylogin.cnf, then set command will replaces it. To ensure
mysql_config_editor prints a warning and prompts for confirmation.

——————————————————- END ——————————————————-

 

Reference: https://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html

 

FEDERATED Storage Engine

What is Federated Engine
Why we need Federated Engine
Overview of Federated Engine
How Enable the Federated Engine
How to create Federated Engine Table
Testing Federated Engine
Disadvantage of Federated Engine

What is Federated Engine:

It is one of the MySQL database storage engine.
We can access or store the data in remote end without replication or cluster technique.

Why we need Federated Engine:

We can easily access/store the data from multiple source.
End user not aware source information.
No need to bother about local storage space.

Overview of Federated Engine:

over_view_federated_engine

How Enable the Federated Engine:

Login into MySQL server and check whether Federated engine enabled or not.

show_engines

If its not enabled then enable it same via my.cnf as below
Vi /etc/my.cnf

enable_my.cnf

Verify the engines after bounced/re-start the MySQL.

show_engines_enabled

How to create Federated Engine Table Using Connection

On source server(MySQL 3306 Instance):
The format of the connection string is as follows:
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

source_create_Ftable

Federated table structure:

Ftable_in_server

Create a Federated Table using Create server:

Create server syntax:
CREATE SERVER
server_name
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option [, option] …)
9_1
Federated table creation using create server option:
9_2
9_3

On destination server:

10_1

The remote table physical structure:
10_2

Try to insert the data in source table:
11_1_edited
Give access on remote server:
11_2
Now try to insert the Data on source table:
12_1_edited
Retrieve the data from source tables(But actually it take from destination table “destination_table”)
12_2_edited

Changing table structure on destination server:
13_1
Table structure on Source:
13_2
Now try to SELECT or INERT on source table.
14_1_edited
Select the data from destination table:
15_1_edited

Security Issue:

Those who are all having basic SELECT access on Federated table they can easily find remote table credentials.

16_1
TO solve the security issue, Create a view from different schema.
17_1
switch to user DB:
17_2_edited

Notes and Tips about Federated Storage engine

If its slave server ensure the federated table able to use the user/password combination that is defined in the CONNECTION string.

The remote server must be a MySQL server.

The remote table must exist before you try to access the table through the FEDERATED table.