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

1) Grant and recall of permissions

Method 1: the permission system-related permission information of MySQL is mainly stored in several system tables called grant tables, namely: mysql.User,mysql.db,mysql.Host,mysql.table_priv and mysql.column_priv. After manually modifying the tables related to permissions, you need to execute the "FLUSH PRIVILEGES" command to reload the permission information of MySQL.

Method 2: if you modify the relevant permissions through the GRANT,REVOKE or DROP USER command, you do not need to execute the FLUSH PRIVILEGES command manually, because the permission modification made through the GRANT,REVOKE or DROP USER command will update the permission information in the memory structure while modifying the system table. In MySQL5.0.2 or later, MySQL also adds the CREATE USER command to create users without any special permissions (only the initial USAGE permissions), and when a new user is created with the CREATE USER command, the new user's information is automatically updated to the memory structure. Therefore, it is recommended that readers generally try to use GRANT,REVOKE,CREATE USER and DROP USER commands to change users and permissions, so as to minimize the direct modification of grant tables to achieve the operation of changing users and permissions.

2) View granted permissions

To view the permissions that a user currently has, this can be done in two ways:

The first is to obtain all previous authorizations on the user by executing the "SHOW GRANTS FOR 'username'@'hostname'" command.

Another method is to query the permission information in grant tables.

3) permission level

Permissions in MySQL are divided into five levels, which are as follows:

Authority

Global

Database

Table

Column

Routine

INSERT

Y

Y

Y

Y

SELECT

Y

Y

Y

Y

UPDATE

Y

Y

Y

Y

ALTER

Y

Y

Y

CREATE

Y

Y

Y

DELETE

Y

Y

Y

DROP

Y

Y

Y

INDEX

Y

Y

Y

ALTER ROUTINE

Y

Y

Y

EXECUTE

Y

Y

Y

ALL [PRIVILEGES]

Y

Y

CREATE ROUTINE

Y

Y

CREATE TABLESPACE

Y

Y

CREATE TEMPORARY TABLES

Y

Y

CREATE VIEW

Y

Y

EVENT

Y

Y

GRANT OPTION

Y

Y

LOCK TABLES

Y

Y

PROXY

Y

Y

REFERENCES

Y

Y

SHOW DATABASES

Y

Y

SHOW VIEW

Y

Y

TRIGGER

Y

Y

CREATE USER

Y

FILE

Y

PROCESS

Y

RELOAD

Y

REPLICATION CLIENT

Y

REPLICATION SLAVE

Y

SHUTDOWN

Y

SUPER

Y

USAGE

Y

(1) Global Level

To grant the permission to Global Level, you only need to use "*. *" to specify that the scope of application is Global when executing the GRANT command. When there are multiple permissions to be granted, you do not need to execute the GRANT command repeatedly. You only need to separate all the required permission names by commas (",") at once, as follows:

Mysql > GRANT SELECT,UPDATE,DELETE,INSERT ON *. * TO 'def'@'localhost'

(2) Database Level

If you want to grant permissions to Database Level, you can implement it in two ways:

1. When executing the GRANT command, use "database.*" to limit the scope of permissions to the entire database database, as follows:

Root@localhost: mysql 06:06:26 > GRANT ALTER ON test.* TO 'def'@'localhost'

2. First, select the database that needs to be authorized by the USE command, and then limit the scope by "*", so that the scope of authorization is actually the entire database currently selected.

Root@localhost: mysql 06:14:05 > USE test

Root@localhost: test 06:13:10 > GRANT DROP ON * TO 'def'@'localhost'

When granting permissions, if you need to grant multiple users with the same permissions, we can also write more than one user information in the authorization statement, separated by a comma (,), as follows:

Root@localhost: mysql 05:22:32 > grant create on perf.* to'abc'@'localhost','def'@'localhost'

(3) Table Level

The scope of the permission of Table Level is the specified table of the database specified in the authorization statement. For example, you can authorize the T1 table of the test database with the following statement:

Root@localhost: test 12:02:15 > GRANT INDEX ON test.t1 TO

The authorization statement above tests the permissions granted to Table Level in the T1 table of the test database, as well as to all ".jianzhaoyang.com" hosts with the wildcard "%". The USAGE permission is the most basic right that every user has.

(4) Column Level

The syntax of the permission authorization statement of Column Level is basically similar to that of Table Level, except that you need to enclose the list of column names that need to be authorized in parentheses after the permission name, as follows:

Root@localhost: test 12:14:46 > GRANT SELECT (id,value) ON test.t2 TO

Note: when a user is INSERT data into a table, if the user does not have INSERT permission on a column in the table, the data for that column will be populated with the default value. This is a little different from many other databases, which is an extension made by MySQL itself on top of SQL.

(5) Routine Level

There are only two kinds of permissions for Routine Level: EXECUTE and ALTER ROUTINE, and the main objects are procedure and function. When granting Routine Level permission, you need to specify the database and related objects, such as:

Root@localhost: test 04:03:26 > GRANT EXECUTE ON test.p1 to

In addition to the above permissions, there is a very special permission GRANT. Users with GRANT permission can grant any permissions they have to any other user, so GRANT permission is a very special and very important right. The GRANT permission is also granted in a different way than any other permission, usually by adding the WITH GRANT OPTION clause at the end of the GRANT authorization statement to grant the GRANT permission. In addition, we can grant all available permissions of a Level to a user through the GRANT ALL statement, such as:

Root@localhost: test 04:15:48 > grant all on test.t5 to 'abc'

Root@localhost: test 04:27:39 > grant all on perf.* to 'abc'

Among the above five Level permissions, the objects that Table, Column and Routine rely on (or refer to) in authorization must already exist, unlike the permission granted by Database Level, which can be granted when the database does not currently exist.

[reference]

[1] MySQL performance tuning and architecture design PDF Chinese version full volume .pdf, http://www.linuxidc.com/

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.