MySQL permission system Introduction

1. Permission System Introduction: The permission system of MySQL is simple to implement, and the relevant permission information is mainly stored in mysql. user, mysql. db, mysql. host, mysql_table_priv, and mysql. column_priv tables

1. Permission System Introduction: The permission system of MySQL is simple to implement, and the relevant permission information is mainly stored in mysql. user, mysql. db, mysql. host, mysql_table_priv, and mysql. column_priv tables

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, website space, and no permissions are available here. 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, the server space will overwrite all other levels of the same permission settings. Global Lovel has the following permissions:

Name version restriction Information

ALTERALL table structure change permission

ALTER ROUTINE5.0.3procedure, function, trigger, and other change permissions

CREATEALL database, table, and index creation Permissions

CREATE ROUTINE5.0.3 + procedure, function, trigger, and other change permissions

Create temporary TABLES4.0.2 + zero-hour table creation permission

CREATE USER5.0.3 + CREATE User Permissions

CREATE VIEW5.0.1 + permission for creating views

DELETEALL: Permission to delete table data

EXECUTE5.0.3 + procedure, function, trigger, and other execution Permissions

FILE
ALL: Execute the load data infile and SELECT... into file permissions.

INDEXALL 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 execute 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 + replication permissions required for SLAVE connection users in master-SLAVE Replication

REPLICATION CLIENT
4.0.2 + permission for executing the show master status and show slave stststus commands

SELECT
ALL data query permission

SHOW DATABASES
4.0.2 + execute show databases permission

SHUTDOWN
Shut down permission of ALLMySQL Server

SHOW VIEW
5.0.1 + execute 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 has the 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 @ 100'; Query OK, 0 rows affected (123 sec) mysql> show grants for wolf @ '% '; + Grants + | Grants for wolf @ % | + Grants + | grant usage on *. * TO 'Wolf '@' % 'identified by password' * f693761138516215c4ac1a7c23a8b8f5b94704d1' | grant all privileges on 'test '. 'test1' TO 'Wolf '@' % '| + rows + 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