Shulou(Shulou.com)06/01 Report--

The following content mainly brings you a detailed explanation of the authority management of the mysql database. The knowledge mentioned here, which is slightly different from books, is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

There is no need for me to repeat the importance of database security. In addition to doing a good security backup, the first thing we need to do is to control the permissions of our database and try our best to protect our database from being *.

This paper is mainly about the authority management of mysql database. In the current network environment, most of the relational databases in Internet companies use mysql.

First of all, let's say that the database with administrative authority for the mysql database is the mysql library. Let's take a look at what tables are in the 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 mysql database

The columns_ private table records the authorized user's access to the fields in the table

The db table records the access permissions of the user's library on the CVM.

The tables_priv table records the access rights of authorized users to the tables in the library

User records which authorized users

Fields in the user table

User is the user name root is the administrator user of mysql

Host is the client from which the user is allowed to access the mysql server can be the ip address hostname network segment (hostbit is replaced by%) use% for all network addresses except the local machine

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 users can create new databases and tables.

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

Reload_priv . Determines whether users can perform specific commands to refresh and reload various internal caches used by MySQL, including logs, permissions, hosts, queries, and tables.

Shutdown_priv . Determine if the user can shut down the MySQL server. Great care should be taken when granting this permission to any user outside the root account.

Process_priv . Determine whether users can view the processes of other users through the SHOW PROCESSLIST command.

File_priv . Determine whether the user can execute the SELECT INTO OUTFILE and LOAD DATA INFILE commands.

Grant_priv . Determines whether a user can grant permissions that have been granted to the user himself to other users. For example, if a user can insert, select, and delete information from an foo database and grant GRANT permissions, the user can grant any or all of his or her privileges to any other user in the system.

References_priv . Currently it's just a placeholder for some future functionality; it doesn't work now.

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 those to which the user has sufficient access. Consider disabling this permission for all users, unless there is a particularly irresistible reason.

Super_priv . Determine whether the user can perform some powerful administrative functions, such as deleting the user process through the KILL command and using SET

GLOBAL modifies the global MySQL variable to execute various commands about replication and logging.

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 only makes sense in MySQL 5.0 and later.

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 main system and facilitates communication between the host and the client.

Repl_client_priv . Determines whether the user can determine the location of the replication slave server and the master server.

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

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

Create_routine_priv . Determines whether the 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 storage 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 to create a new MySQL account.

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

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

Generally speaking, 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 administrator users can only log in locally in order to facilitate management, we will add a root user in the management machine address, all permissions, can remotely manage the mysql database

Log in to mysql with local root users

Mysql-hlocalhost-uroot-p administrator password

If it is a newly installed mysql database server (take version 5.7 as an example), the default root password is generated immediately upon installation. You can find root@localhost in the log file: followed by random passwords, most random passwords have special symbols, so when logging in, the password should be enclosed in single quotation marks.

[root@A5 ~] # grep 'password' / var/log/mysqld.log

2018-02-23T00:42:30.776575Z 1 [Note] A temporary password is generated for root@localhost: aRaOssg_*774

After logging in to mysql

Mysql > grant all on *. * client of to root@' remote Administration ip' identified by 'password for remote Administration' with grant option; # with grant option is an authorization for the newly added root user

In this way, you can log in to the MySQL server on the management computer you specify.

User authorization

Grant permission list on library name. Table name to "user name" @ "client address" identified by "password" with grant option; newly authorized permissions can also authorize with grant option on the specified client

The client address can be the specified ip

The host bit of the specified network segment is represented by% for 192.168.4.%.

So ip uses%

Permissions all all permissions

Libraries and tables can use * to express all the meaning

View the authorized users and permissions already on the database server

Select user,host from mysql.user

Show garts for "user name" @ "client address"

Show currently logged in users

Select user ()

Show current hostname

Select @ @ hostname

View the access rights of the login user

Show grants

Revocation of authority

Revoke permission list on library name. Table name from "user name" @ "client address"

Delete authorized user

Drop user "user name" @ "client address"; after the authorized user is deleted, all permissions of that user will be removed

Modify the login password of an authorized user

Set password for "user name" @ "client address" = password (new password)

Authorized users to reset their user passwords

Set password=password ("New password")

If you want to give a certain database all the permissions, including the permissions that can be granted.

You need to add that all tables of the user in the mysql database have insert permissions (if there is no insert permission for mysql, the with grant option granted to this user cannot authorize other users) and the permissions that the user can authorize to other users cannot be greater than the permissions he or she has.

Recover the database administrator's local administrative password (forgotten root user's administrative password on the mysql server operating system)

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

Under [mysqld]

Add a parameter that skips the authorization table (if you have a parameter setting for an authentication policy, you need to comment on the parameters that have already been added) skip-grant-tables

two。 Restart the mysql service

3. Enter mysql without password

4. Modify mysql root user password

Update mysql.user set authentication_string=password (new password) where user= "root" host= "localhost"

5. Exit and change back to the original mysql configuration

6. Restart the mysqld service

Here are some examples

1. Change the password for the database administrator to log in from this machine to 123456

The database administrator connects to the database server locally using the new password

Mysqladmin-uroot-p654321 password123456

2. Who is the user who currently logs in to the database server?

Mysql > select user ()

3. Check the permissions of the user who currently logs in to the database server?

Mysql > show grants for root@localhost

4. What authorized users are there in the current database server?

Mysql > select user,host from mysql.user

5. Authorized administrator users can log in on the 192.168.4.254 host in the network, with full permissions and authorized permissions for all libraries and tables; login password abc123

Mysql > grant all on *. * to root@'192.168.4.254' identified by 'abc123' with grant option

6. Database administrators are not allowed to log in locally on the database server. (be sure to add a user who can be managed remotely before doing this, otherwise you won't be able to access the mysql server.)

Drop user root@ "localhost"

7. Authorized userweb users can access the database server from any host in the network, have the right to view and update the name field and age field of the user table under the db3 library, and log in to userweb888.

Grant select,update (name,age) on db3.user to userweb@'%' identified by 'userweb888'

8. Verify whether the above authorization is successful

# mysql-h292.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 changes its login password to 123456 and verifies whether it can log in with the new password.

Mysql > set password=password ("123456")

Query OK, 0 rows affected, 1 warning (0.00 sec)

# mysql-h292.168.4.51-uuserweb-p123456

10. The database administrator changes the login password of the authorized user userweb to 654321 to allow the authorized user userweb to log in to the database server with the new password.

Set password for userweb@ "%" = password ("654321")

Revoke all authorization from the authorized user userweb and make it no longer able to use this user to connect to the database server.

Mysql > drop user user@ "%"

12. Authorized webadmin users can log in from all hosts in the network and have full permissions and authorization rights to the bbsdb library. The login password is 123456.

Mysql > grant all on bbsdb.* to webadmin@'%' identified by '123456' with grant option

Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql > grant insert on mysql.* to webadmin@ "%"

Query OK, 0 rows affected (0.00 sec)

13. Log in with the authorized user webadmin on the client side, and authorize your permissions to the userone user. The login password is 123456.

# mysql-h292.168.4.51-uwebadmin-p123456

Mysql > grant all on bbsdb.* to userone@ "" identified by "123456" with grant option

15. Revoke the authorization of webadmin users. Mysql > revoke grant option on bbsdb.* from webadmin@ "%"

Query OK, 0 rows affected (0.00 sec)

For the above detailed explanation of the permission management of mysql database, if you have more information, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact the pre-sale and after-sale ones on the official website. I hope this article can bring you some knowledge updates.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.