MySQL Authorization management

The importance of the security of the database does not need me to repeat it. In addition to doing a secure backup, the first thing we need to do is to do our database permissions control, as far as possible to protect our database against hackers.
This article is mainly about MySQL database rights Management, in the current network environment in the Internet company's relational database is mostly the use of MySQL.
first of all, the MySQL database management permissions database is MySQL library let's see what tables are in MySQL library .

Mysql> Show tables from mysql;+---------------------------+| Tables_in_mysql |+---------------------------+| Columns_priv | | db | | Engine_cost | | Event |

| Func |
| General_log |
| gtid_executed |
| Help_category |
| Help_keyword |
| help_relation |
| Help_topic |
| Innodb_index_stats |
| Innodb_table_stats |
| Ndb_binlog_index |
| Plugin |
| Proc |
| Procs_priv |
| Proxies_priv |
| Server_cost |
| Servers |
| Slave_master_info |
| Slave_relay_log_info |
| Slave_worker_info |
| Slow_log |
| Tables_priv |
| Time_zone |
| Time_zone_leap_second |
| Time_zone_name |
| time_zone_transition |
| Time_zone_transition_type |
| user |
+---------------------------+

Several important tables in the MySQL database

Columns_priv table records authorized user access to fields in a table

DB table records access permissions for libraries on user servers

Tables_priv table records authorized user access to tables in the library

What authorized users are logged by user

fields in the user table

User is the username root is the administrator user of MySQL

Host is the user who is allowed to access the MySQL server from which client can be IP address hostname network segment (host bit in% instead of) all network addresses except native use%

Select_priv. Determines whether the user can select data through the Select command.

Insert_priv. Determines whether the user can insert data through the Insert command.

Update_priv. Determines whether the user can modify existing data through the update command.

Delete_priv. Determines whether the user can delete existing data through the Delete command.

Create_priv. Determines whether the user can create a new database and table.

Drop_priv. Determines whether users can delete existing databases and tables.

Reload_priv. Determines whether a user can perform a specific command that refreshes and reloads the various internal caches used by MySQL, including logs, permissions, hosts, queries, and tables.

Shutdown_priv. Determines whether the user can shut down the MySQL server. You should be very cautious when giving this permission to any user other than the root account.

Process_priv. Determines whether users can view the processes of other users through the show Processlist command.

File_priv. Determines whether the user can execute the SELECT INTO outfile and load DATA infile commands.

Grant_priv. Determines whether a user can grant another user the permissions that have been granted to the user. For example, if a user can insert, select, and delete information from the Foo database and grant grant permissions, that user can grant any or all of their permissions to any other user on the system.

References_priv. Currently it is only a placeholder for some future features;

Index_priv. Determines whether the user can create and delete table indexes.

Alter_priv. Determines whether the user can rename and modify the table structure.

Show_db_priv. Determines whether the user can view the names of all databases on the server, including databases with sufficient access to the user. You may consider disabling this permission for all users, unless there is a particularly compelling reason.

Super_priv. Determines whether a user can perform some powerful administrative functions, such as deleting a user process with the KILL command, using the set

Global MySQL variable is modified to perform various commands on replication and logs.

Create_tmp_table_priv. Determines whether the user can create a temporary table.

Lock_tables_priv. Determines whether the user can use the Lock Tables command to block access/modification to the table.

Execute_priv. Determines whether the user can execute the stored procedure. This permission is only meaningful in MySQL 5.0 and later versions.

Repl_slave_priv. Determines whether the user can read the binary log files used to maintain the replicated database environment. This user is located in the primary system and facilitates communication between the host and the client.

Repl_client_priv. Determines whether the user can determine the location of replication from the server and the primary server.

Create_view_priv. Determines whether the user can create a view. This permission is only meaningful in MySQL 5.0 and later versions. For more information about views, see Chapter 34th.

Show_view_priv. Determines whether the user can view the view or understand how the view is performed. This permission is only meaningful in MySQL 5.0 and later versions. For more information about views, see Chapter 34th.

Create_routine_priv. Determines whether a user can change or discard stored procedures and functions. This permission was introduced in MySQL 5.0.

Alter_routine_priv. Determines whether the user can modify or delete stored functions and functions. This permission was introduced in MySQL 5.0.

Create_user_priv. Determines whether the user can execute the Create user command, which is used for creating a new MySQL account.

Event_priv. Determines whether the user can create, modify, and delete events. This permission is new for MySQL 5.1.6.

Trigger_priv. Determines whether the user can create and delete triggers, which is a new addition to MySQL 5.1.6.

In general, the permissions we use in the program are set just enough, do not set too high permissions to bring some security risks to the database;

MySQL database default root Admin user can only log in for easy administration we add a root user to the address of the management machine and have all permissions to remotely administer the MySQL database

Log in to MySQL with the root user on this machine

Mysql-hlocalhost-uroot-p Administrator Password

If it is a newly installed MySQL database server (in version 5.7), the default root password is generated at the time of installation and can be found in the log file [email protected]: followed by random passwords most random passwords have special symbols so when you log in, the password is quoted in single quotation marks.

[[email protected] ~]# grep ' password '/var/log/mysqld.log
2018-02-23t00:42:30.776575z 1 [Note] A temporary password is generated for [email protected]: araossg_*774
After landing into MySQL

Mysql> Grant All on * * to [email protected] ' Remote managed client IP ' identified by ' Remote managed password ' with GRANT option; #with Grant option is granted permissions to the newly added root user

This will allow you to log on to the MySQL server on your designated management machine.

User Authorization

Grant permission list on Library name. Table name to "user name" @ "client Address" identified by "password" with GRANT option; The newly granted permission can also be authorized with GRANT option on the specified client

The client address can be a specified IP

Specifies that the network segment host bit is represented by% 192.168.4.%

So IP with%

Permissions All Permissions

libraries and tables can use * to denote all meanings.

View authorized users and permissions that are already on the database server

Select User,host from Mysql.user;

Show Garts for "username" @ "client address";

Show currently logged in user

Select User ();

Show current host name

SELECT @ @hostname;

View access rights for logged-in users

Show grants;

Revoke Permissions

Revoke permissions list on the library name. Table name from "User name" @ "Client Address"

Remove an authorized user

Drop user "username" @ "client address"; The user's permissions are also removed after the authorized user is removed

Modify the login password of the authorized user

Set password for "user name" @ "Client Address" =password ("New password")

Authorizing users to reset their user passwords

Set Password=password ("New password");

If you want to give a library that has all permissions that contain permissions that can be authorized

You need to add the user to all tables in the MySQL database with insert permission (without the Insert permission of MySQL, grant option granted to the user is not authorized for other users) and the user can grant other users permissions that are not greater than the permissions they have

Restore the database administrator Native Admin password (forget the root user's admin password on the MySQL server operating system)

1. Modify the configuration file/etc/my.cnf file

Under [Mysqld]

Add parameters to the Bypass authorization table (if there are parameter settings for the validation policy you need to annotate the validation policy parameters that have already been added) Skip-grant-tables

2. Restart the MySQL service

3. No password to enter MySQL

4. Modify the MySQL root user password

Update Mysql.user Set Authentication_string=password ("New password") where user= "root" host= "localhost";

5. Exit, change back to the original MySQL configuration

6. Restart the MYSQLD service

Some examples are provided below

1. Modify the password for the database administrator to log on from the local computer to 123456
The database administrator uses the new password to connect to the database server from the computer
mysqladmin-uroot-p654321 password123456;

2. Who is the user who is currently logged in to the database server?
Mysql> Select User ();
3, check the current login database server user permissions?
Mysql> Show grants for [email protected];

4. See which authorized users are currently on the database server?
Mysql> select User,host from Mysql.user;

5, authorized administrator users can log in the network 192.168.4.254 host, all libraries and tables have full permissions and authorized permissions; login Password abc123
Mysql> Grant All on * * to [e-mail protected] ' 192.168.4.254 ' identified by ' abc123 ' with GRANT option;


6. The database administrator is not allowed to log on to the database server natively. (Be sure to add a remotely managed user before you do this, otherwise you won't be able to get into MySQL server)
drop user [email protected] "localhost";


7, authorized Userweb users can access the database server from any host in the network, the user table under the DB3 Library has the permission to view, update the Name field and the Age field, login password userweb888.
Grant Select,update (Name,age) on db3.user to [email protected] '% ' identified by ' userweb888 '

8. Verify the above authorization is successful
#mysql-h192.168.4.51-uuserweb-puserweb888
Mysql> select * from Db3.user;
mysql> Update db3.user set name= "xx", age=20 where name= "SS";
Query OK, 2 rows Affected (0.00 sec)
Rows matched:2 Changed:2 warnings:0
Mysql> Delete from Db3.user where name= "XX";
ERROR 1142 (42000): DELETE command denied to user ' userweb ' @ ' 192.168.4.254 ' for table ' user '

9, userweb modify their login password is 123456, and verify the use of the new password login
Mysql> set Password=password ("123456");
Query OK, 0 rows affected, 1 Warning (0.00 sec)
#mysql-h192.168.4.51-uuserweb-p123456

10, the database administrator modifies the authorized user Userweb the login password is 654321, lets the authorized user Userweb uses the new password to log on the database server.
Set password for [email protected] "%" =password ("654321");


11. Revoke all authorizations granted by the authorized user Userweb and make it no longer able to use this user to connect to the database server.
mysql> drop user [email protected] "%";


12, authorized WebAdmin users can log in from all the hosts on the network, have full access to the BBSDB library, and have authorization rights, login password is 123456
Mysql> Grant all on bbsdb.* to [e-mail protected] '% ' identified by ' 123456 ' with GRANT option;
Query OK, 0 rows affected, 1 Warning (0.00 sec)

Mysql> Grant Insert on mysql.* to [email protected] "%";
Query OK, 0 rows Affected (0.00 sec)


13, in the client using authorized user WebAdmin Login, to give their own rights to userone users, login password is 123456
# mysql-h192.168.4.51-uwebadmin-p123456
Mysql> Grant all on bbsdb.* to [email protected] '% ' identified by ' 123456 ' with GRANT option;


15, revoke the WebAdmin user's authorization permission. Mysql> revoke grant option on the bbsdb.* from [email protected] "%";
Query OK, 0 rows Affected (0.00 sec)

MySQL Authorization management