Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "introduction to the authority of MySQL5.7". In the course of the operation of the actual case, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The permissions supported by MySQL are as follows:

ALL or ALL PRIVILEGES represents all permissions of the specified permission level.

ALTER allows the use of ALTER TABLE to change the structure of the table, and ALTER TABLE also requires CREATE and INSERT permissions. Renaming a table requires ALTER and DROP permissions on the old table and CREATE and INSERT permissions on the new version.

ALTER ROUTINE allows you to change and delete stored procedures and functions

CREATE allows you to create new databases and tables

CREATE ROUTINE allows you to create stored procedures and packages

CREATE TABLESPACE allows you to create, change, and delete tablespaces and log filegroups

CREATE TEMPORARY TABLES allows you to create temporary tables

CREATE USER allows you to change, create, delete, rename users and reclaim all permissions

CREATE VIEW allows you to create views

DELETE allows rows to be deleted from the table in the database

DROP allows you to delete databases, tables, and views

EVENT allows you to create, change, delete and view events in event scheduling

EXECUETE allows execution of stored procedures and packages

FILE allows you to use LOAD DATA INFILE, SELECT on the host of the server. INTO OUTFILE and LOAD_FILE () functions read and write files

GRANT OPTION allows permissions to be granted or removed to other users

INDEX allows you to create and delete indexes

INSERT allows rows to be inserted into tables in the database

LOCK TABLE allows you to execute LOCK TABLES statements to lock the table

PROCESS allows you to display the thread information executed on the server, that is, the statement information executed by the session. This permission allows you to execute SHOW PROCESSLIST and mysqladmin processlist commands to view threads, and this permission also allows you to execute SHOW ENGINE commands

PROXY allows a user to pretend to be another user

REFERENCES allows you to create foreign keys

RELOAD allows the use of flush statements

REPLICATION CLIENT allows the execution of SHOW MASTER STATUS,SHOW SLAVE STATUS and SHOW BINARY LOGS commands

REPLICATION SLAVE allows SLAVE servers to connect to the current server as their primary server

SELECT allows you to query tables from a database

SHOW DATABASES allows the account to execute SHOW DATABASE statements to view the database. Accounts that do not have this permission can only see the database for which they have permission.

SHOW VIEW allows execution of SHOW CREATE VIEW statements

SHUTDOWN allows execution of SHUTDOWN statements and mysqladmin shutdown has mysql_shutdown () C API function

SUPER allows users to execute CHANGE MASTER TO,KILL or mysqladmin kill commands to kill other users' threads, to execute PURGE BINARY LOGS commands, to set system parameters through SET GLOBAL, to execute mysqladmin debug commands, to open and close logs, to execute update statements even if the read_only parameter is on, to turn on and off replication from the server, and to connect to the server when the number of connections reaches max_connections.

TRIGGER allows operation of triggers

UPDATE allows you to update tables in the database

The USAGE representative does not have any permissions.

Grant global permissions:

*. * represents the permissions of all databases

Mysql > grant all on *. * to 'test'@'%'

Query OK, 0 rows affected (0.00 sec)

Mysql > grant select, insert on *. * to 'test'@'%'

Query OK, 0 rows affected (0.00 sec)

Grant permissions to the specified database:

Mysql > grant all on test.* to 'test'@'localhost'

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Mysql > grant select, insert on *. * to 'test'@'%'

Query OK, 0 rows affected (0.00 sec)

Mysql > grant select, insert on test.* to 'test'@'%'

Query OK, 0 rows affected (0.00 sec)

Grant permissions to the specified table:

Mysql > grant all on test.orders to 'jeffrey'@'localhost'

Query OK, 0 rows affected (0.13 sec)

Mysql > grant select, insert on test.orders to 'jeffrey'@'localhost'

Query OK, 0 rows affected (0.07 sec)

Grant permissions to the specified field:

Mysql > desc test.orders_1

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | order_date | date | YES | | NULL |

| | order_id | int (11) | YES | | NULL |

| | customer_name | varchar (15) | YES | | NULL |

| | product_id | int (11) | YES | | NULL |

+-+ +

4 rows in set (0.00 sec)

Mysql > grant select (order_date), insert (order_id,customer_name) on test.orders_1 to 'jeffrey'@'localhost'

Query OK, 0 rows affected (0.01 sec)

[root@T400-kelong] # mysql-ujeffrey-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 6

Server version: 5.7.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > use test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > select * from orders_1

ERROR 1142 (42000): SELECT command denied to user 'jeffrey'@'localhost' for table' orders_1'

Mysql > select order_date from orders_1

+-+

| | order_date |

+-+

| | 2016-03-26 |

+-+

1 row in set (0.00 sec)

Permissions granted to stored procedures:

Mysql > grant create routine on test.* to 'jeffrey'@'localhost'

Query OK, 0 rows affected (0.08 sec)

Mysql > grant execute on procedure test.myproc to 'jeffrey'@'localhost'

Query OK, 0 rows affected (0.04 sec)

Grant proxy user permissions:

PROX permissions enable one user to become an agent for another user

Mysql > grant proxy on 'jeffrey'@'localhost' to' test'@'%'

Query OK, 0 rows affected (0.09 sec)

This is the end of the introduction to the permissions of MySQL5.7. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.