MySQL permission system Introduction

1. Permission System introduction:

The implementation of the MySQL permission system is relatively simple. The relevant permission information is mainly stored in mysql. User, mysql. db, mysql. Host, mysql_table_priv, and mysql. column_priv tables. Because the volume of permission information is small and frequently accessed, MySQL will Load all permission information to the memory at startup to ensure that there are several specific structures, therefore, after you manually modify the permission-related table, you must run the "flush privileges" command to reload the permission information of MySQL. You can also use the GRANT, REVOKE, or drop user command to update the permission information to the memory structure at the same time.

Ii. Permission assignment and Removal

To authorize a user, you can use the GRANT command. To remove the existing permissions of a user, you can use the REVKOE command. to authorize the user, you must not only provide the user name, but also specify the host through which to access the user, the following is a simple column:

# Create a user test1 to log on from the local machine and grant this user the query permission for the test database. mysql> grant select on test. * to test1 @ 'localhost' identified by 'test123'; Query OK, 0 rows affected (0.03 sec) # create a user test2 to log on from any host on the Internet and grant this user the query permission for the test database mysql> grant select on test. * to test2 @ '%' identified by 'test234'; Query OK, 0 rows affected (0.02 sec) Refresh permission, and Query the permission of user test1 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show grants for te St1 @ 'localhost'; + hosts + | Grants for test1 @ localhost | + hosts + | grant usage on *. * TO 'test1' @ 'localhost' identified by password' * 676243218923905cf94cb52a3c9d3eb30ce8e20d' | GRANT SELECT ON 'Test '. * TO 'test1' @ 'localhost' | + rule + 2 rows in set (0.00 sec) delete user test1 permission mysql> revoke select on test. * from 'test1' @ 'localhost' identified by 'test123'; Query OK, 0 rows affected (0.00 sec) check user test1 here, and you have no permission. Mysql> show grants for test1 @ 'localhost'; + hosts + | Grants for test1 @ localhost | + grants + | grant usage on *. * TO 'test1' @ 'localhost' identified by password' * 676243218923905cf94cb52a3c9d3eb30ce8e20d' | + signature +

Iii. Permission level

Mysql has five levels of permissions:

1. Global Lovel:

The permission control of Global Lovel is also called the Global Control permission. All permission information u is stored in mysql. in the User table, all permissions of Global Lovel are for the entire mysqld and are valid for all tables and fields in all mysql databases. If a permission is granted by Global Lovel, it will overwrite all other levels of the same permission settings. Global Lovel has the following permissions:

Name	Supported versions	Restriction Information
ALTER	ALL	Table Structure Modification permission
ALTER ROUTINE	5.0.3	Change permissions for procedure, function, and trigger
CREATE	ALL	Create permissions for databases, tables, and indexes
CREATE ROUTINE	5.0.3 +	Change permissions for procedure, function, and trigger
CREATE TEMPORARY TABLES	4.0.2 +	Create a zero-time table
CREATE USER	5.0.3 +	Create User Permissions
CREATE VIEW	5.0.1 +	View creation permission
DELETE	ALL	Permission to delete table data
EXECUTE	5.0.3 +	Execution permissions for procedure, function, and trigger
FILE	ALL	Execute the load data infile and SELECT... into file Permissions
INDEX	ALL	Permission to create an index on an existing table
INSERT	ALL	Data insertion permission
LOCK TABLES	4.0.2 +	Execute the lock tables command to display the table LOCK permission
PROCESS	ALL	Permission to run the show processlist command
RELOAD	ALL	Execute FLUSH and so on to allow the database to LOAD certain objects or data commands
REPLCATION SLAVE	4.0.2 +	Copy permissions required for connecting SLAVE users in master-SLAVE Replication
REPLICATION CLIENT	4.0.2 +	Permission to run the show master status and show slave ststus commands
SELECT	ALL	Data Query permission
SHOW DATABASES	4.0.2 +	Execute show databases permission
SHUTDOWN	ALL	MySQL Server's shut down permission
SHOW VIEW	5.0.1 +	Run the show create view command to VIEW the permission of the VIEW creation statement.
SUPER	4.0.2 +	Permission for executing commands such as kill thread, change master, purge master logs, and SET GLOBAL
UPDATE	ALL	Update database Permissions
USAGE	ALL	Minimum permissions required when a new user is not authorized

To GRANT the Global Lovel permission, you only need to use *. * to specify the Global range when executing the GRANT command. If there are multiple users, use commas to separate them, as shown below:

mysql> grant all on *.* to test3,test4@'localhost' identified by 'test123';Query OK, 0 rows affected (0.00 sec)

2. Database Level

The Database Level is the permission Level under the Global Level and above the other three levels. Its scope is all objects in the specified Database, compared with Database Level, the following permissions are missing: create user, FILE, PROCESS, RELOAD, replication client, replication slave, show databases, and SHUTDOWN,

To grant the Database Level permission, follow these steps:

1) when executing the GRANT command, use database. * to specify the scope as the whole database: or create a user without permissions to GRANT permissions by using the GRANT command.

mysql> grant all on test.* to test3,test4@'localhost' identified by 'test123';Query OK, 0 rows affected (0.00 sec)

3. Table Level

The Table Level permission can be overwritten by the Global Level and Database Level permissions. The scope of the Table Level permission is the Table specified for authorization. You can use the following statements to authorize the Table:

mysql> grant all on test.test1 to wolf@'%' identified by 'wolf@123';Query OK, 0 rows affected (0.01 sec)mysql> show grants for wolf@'%';+-----------------------------------------------------------------------------------------------------+| Grants for wolf@%                                                                                   |+-----------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'wolf'@'%' IDENTIFIED BY PASSWORD '*F693761139616215C4AC1A7C23A8B8F5B94704D1' || GRANT ALL PRIVILEGES ON `test`.`test1` TO 'wolf'@'%'                                                |+-----------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

Table Level permissions are limited to each Table, so the permission types are small. There are only eight permissions, namely ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT, and UODATE.

4. Column Level

The scope of the Column Level permission is limited to a certain Column of a Table. The Column Level can also be overwritten by the same permissions of Database Level, Database Level, and Table Level, the Column Level permission and the Routine Level permission scope do not overlap, so they are not overwritten. The Column Level permission only has SELECT, UPDATE, and INSERT permissions, the names of the columns that need to be granted permissions are enclosed in brackets using the following method ):

mysql> grant select(id,name) on test.test to kelly@'%' identified by 'wolf123';Query OK, 0 rows affected (0.01 sec)mysql> show grants for kelly@'%';+------------------------------------------------------------------------------------------------------+| Grants for kelly@%                                                                                   |+------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'kelly'@'%' IDENTIFIED BY PASSWORD '*30F17FEB599168D8F1BC498525B27B83A13F54E3' || GRANT SELECT (name, id) ON `test`.`test` TO 'kelly'@'%'                                              |+------------------------------------------------------------------------------------------------------+

5. Routine Level

Routine Level has only two types of permissions: EXECUTE and alter routine. The main objects are procedure and function. To grant Routine Level permissions, you must specify the database and related objects as follows:

mysql> grant execute on test.pl to kelly@'%';Query OK, 0 rows affected (0.01 sec)

This article from the "ordinary days" blog, please be sure to keep this source http://wolfchen.blog.51cto.com/2211749/1243990