MySQL Rights Management

I. Introduction to MySQL Privileges

The simple understanding of MySQL permissions is that MySQL allows you to do what you do best, and you can't cross the border. For example, if you are only allowed to perform a select operation, you cannot perform an update operation. Only allow you to connect to MySQL from a machine, then you cannot connect to MySQL from other machines except that one.

So how is MySQL's permissions implemented? This is about the two-phase verification of MySQL, detailed below: First stage: The server will first check whether you allow the connection. Because the user is created with a host limit, you can limit the cost, an IP, an IP segment, and any place, and only allow you to log in from the specified location of the configuration. Second stage: If you can connect, MySQL will check every request you make to see if you have sufficient permissions to implement it. For example, if you want to update a table, or query a table, MySQL will see if you have permissions on which table or column. For example, if you run a stored procedure, MySQL checks to see if you have execute permissions on the stored procedure.

What permissions does MySQL have? Copy a table from the website to see:

Permissions	Permission levels	Permission Description
CREATE	Database, table, or index	Create database, table, or index permissions
DROP	Database or table	Delete database or table permissions
GRANT OPTION	A database, table, or saved program	Granting permission Options
REFERENCES	Database or table
Alter	Table	Change tables, such as adding fields, indexes, etc.
DELETE	Table	Delete Data permissions
INDEX	Table	Index permissions
INSERT	Table	Insert Permissions
SELECT	Table	Query permissions
UPDATE	Table	Update permissions
CREATE VIEW	View	CREATE VIEW Permissions
SHOW VIEW	View	View View Permissions
ALTER ROUTINE	Stored Procedures	Change stored procedure permissions
CREATE ROUTINE	Stored Procedures	Create Stored Procedure permissions
EXECUTE	Stored Procedures	Execute Stored Procedure permissions
FILE	File access on the server host	File access Permissions
CREATE Temporary TABLES	Server Management	Create temporary table permissions
LOCK TABLES	Server Management	Lock table Permissions
CREATE USER	Server Management	Create User Rights
PROCESS	Server Management	View Process Permissions
RELOAD	Server Management	Perform flush-hosts, Flush-logs, Flush-privileges, Flush-status, Flush-tables, flush-threads, refresh, reload, and so on command permissions
REPLICATION CLIENT	Server Management	Copy Permissions
REPLICATION SLAVE	Server Management	Copy Permissions
SHOW DATABASES	Server Management	View Database Permissions
SHUTDOWN	Server Management	Turn off database permissions
SUPER	Server Management	Execute Kill Thread Permissions

How MySQL's permissions are distributed is what permissions are set on the table, what permissions are set on the column, and so on, which can be explained in a table in the official documentation:

Distribution of permissions	Possible settings for permissions
Table Permissions	' Select ', ' Insert ', ' Update ', ' Delete ', ' Create ', ' Drop ', ' Grant ', ' References ', ' Index ', ' Alter '
Column permissions	' Select ', ' Insert ', ' Update ', ' References '
Process permissions	' Execute ', ' Alter Routine ', ' Grant '

second, the MySQL authority experience principle:

Permissions control is primarily for security reasons, so you need to follow a few rules of thumb:

1, only to meet the needs of the minimum permissions to prevent users from doing bad things. For example, users just need to query, then only give select permission on it, do not give the user update, insert or delete permissions.

2, when the user is created to restrict the user's login host, is generally limited to the designated IP or intranet IP segment.

3. When initializing the database, delete the user without password. Some users are automatically created when the database is installed, and these users do not have a password by default.

4. Set a password that satisfies the complexity of the password for each user.

5, regularly clean up the unwanted users. Reclaim permissions or delete users.

Third, MySQL rights combat:

1. Instructions for use of the grant command:

Let's take a look at an example, create a super-user jack that only lets you log on locally, and allow permissions to be given to other users with the password: Jack.

Mysql> Grant all privileges on * * to [e-mail protected] ' localhost ' identified by ' Jack ' with GRANT option; Query OK, 0 rows affected (0.01 sec)

Description of the GRANT command:
All privileges is a representation of all permissions, and you can also use the Select, Update, and other permissions.

On to specify which libraries and tables the permissions are for.

The * in front of * * is used to specify the database name, followed by the * number to specify the table name.

To indicates that the permission is assigned to a user.

[email protected]' localhost ' indicates that the jack user, at the end of the limited host, can be IP, IP segment, domain name, and%,% to represent anywhere. Note: There are some versions of this is not included in the local, previously encountered to a user set the% allowed to log anywhere, but not on the local login, this and the version has a relationship with this problem plus a localhost user can be.

Identified by specifies the user's login password.

With GRANT option This option means that the user can delegate the permissions they own to others. Note: It is often not specified with the WITH GRANT option when creating an operation user that the user cannot use the grant command to create a user or authorize another user.

Note: You can use grant to repeatedly add permissions to the user, permissions overlay, such as when you first add a SELECT permission to the user, and then add an INSERT permission to the user, then the user has both select and insert permissions.

2. Refresh Permissions

Use this command to make permissions effective, especially if you have updated or delete updates to the Permissions table user, DB, host, and so on. If you have previously encountered permissions that have not been updated with grant, use the flush privileges command to refresh the permissions whenever you make changes to the permissions.

mysql> flush Privileges; Query OK, 0 rows affected (0.01 sec)

3. View Permissions

View permissions for the current user:mysql> show grants;+---------------------------------------------------------------------+| Grants for [email protected] |+-------------------------------------------- -------------------------+| GRANT all privileges on * * to ' root ' @ ' localhost ' with GRANT OPTION | | Grant PROXY on "@" to "root" @ ' localhost ' with GRANT OPTION |+---------------------------------------------------- -----------------+2 rows in Set (0.00 sec) View a user's permissions:mysql> show grants for ' Jack ' @ '% '; +----------------------------- ------------------------------------------------------------------------+| Grants for [email protected]% |+--- --------------------------------------------------------------------------------------------------+| GRANT USAGE on * * to ' jack ' @ '% ' identified by PASSWORD ' *9bcdc990e611b8d852efaf1e3919ab6ac8c8a9f0 ' |+------------------ -----------------------------------------------------------------------------------+1 Row in Set (0.00 sec) 

4. Reclaim Permissions

mysql> REVOKE Delete on * * from ' jack ' @ ' localhost '; Query OK, 0 rows affected (0.01 sec)

5. Delete users

Mysql> Select Host,user,password from user;+-----------+------+-------------------------------------------+| Host      | user | password                                  |+-----------+------+-------------------------------------------+| localhost | root |                                           || rhel5.4 | root | | |                                           127.0.0.1 | root |                                           | |: 1       | root | |                                           | localhost |      | |                                           | | rhel5.4   |
   
    |                                           || localhost | Jack | *9BCDC990E611B8D852EFAF1E3919AB6AC8C8A9F0 |+-----------+------+-------------------------------------------+7 Rows in Set (0.00 sec) mysql> drop user ' jack ' @ ' localhost '; Query OK, 0 rows affected (0.01 sec)
   

6. Renaming the Account

mysql> rename user ' Jack ' @ '% ' to ' Jim ' at '% '; Query OK, 0 rows Affected (0.00 sec)

7. Change the password

  1, with Set PASSWORD command mysql> set PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' 123456 '); Query OK, 0 rows Affected (0.00 sec) 2, with mysqladmin [[email protected] ~]# mysqladmin-uroot-p123456 password 1234a BCD notes: Format: Mysqladmin-u User name---old password Password new password 3, edit user table directly with Update mysql> use mysqlreading table information for COM Pletion of table and column namesyou can turn off this feature to get a quicker startup with-adatabase changedmysql> u Pdate user Set PASSWORD = PASSWORD (' 1234ABCD ') where user = ' root '; Query OK, 1 row affected (0.01 sec) Rows matched:1 changed:1 warnings:0mysql> flush privileges; Query OK, 0 rows Affected (0.00 sec) 4, when the root password is lost: [[email protected] ~]# mysqld_safe--skip-grant-tables &[1 ] 15953[[email protected] ~]# 130911 09:35:33 mysqld_safe Logging to '/mysql/mysql5.5/data/rhel5.4.err '. 130911 09:35:33 Mysqld_safe starting mysqld daemon with databases from/mysql/mysql5.5/data[[email protected] ~]# mysql-u R Ootwelcome to the MYsql Monitor. Commands End With; or \g.your MySQL connection ID is 2Server version:5.5.22 Source distributioncopyright (c) #, Oracle and/or its a Ffiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help.  Type ' \c ' to clear the current input statement.mysql> \s--------------mysql Ver 14.14 distrib 5.5.22, for Linux (i686)            Using Editline wrapperconnection id:2current database:current User: [Email protected]ssl: Not in Usecurrent pager:stdoutusing outfile: ' Using delimiter:;  Server version:5.5.22 Source distributionprotocol version:10connection:localhost via UNIX socketserver  Characterset:utf8db characterset:utf8client Characterset:utf8conn. Characterset:utf8unix socket:/tmp/mysql.sockuptime:36 secthreads:1 questions:5 Slow queries:0 opens:23 Flush tables:1 Open tables:18 queries per second avg:0.138--------------mysql> use MySQL Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changedmysql> Update user Set password = password (' 123456 ') where user = ' root '; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0mysql> flush privileges; Query OK, 0 rows Affected (0.00 sec)

MySQL Rights Management