How to use GRANT to authorize a user to browse and modify table operation by MySQL
Editor to share with you how MySQL uses GRANT to authorize a user to browse and modify the table. I hope you will get something after reading this article. Let's discuss it together.
The following example is an example of authorizing logger users to query, insert and modify test database table records, but cannot delete:
C:\ Users\ duansf > mysql-u root-p
Enter password: *
Welcome to the MySQL monitor. Commands end with; or\ g.
Your MySQL connection id is 2
Server version: 5.5.13 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, 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 > show databases
+-+
| | Database |
+-+
| | information_schema |
| | db_shop |
| | frag_test |
| | mysql |
| | performance_schema |
| | test |
+-+
Mysql > use test
Database changed
Mysql > show tables
+-+
| | Tables_in_test |
+-+
| | workinfo |
+-+
1 row in set (0.03 sec)
Mysql > select * from workinfo
Empty set (0.12 sec)-the table record is empty
Mysql > grant select,insert,update on test.workinfo to logger@localhost identified by 'timber';-authorizes logger users to access and modify the workinfo table of the test database
Query OK, 0 rows affected (0.14 sec)
Mysql > quit
Bye
C:\ Users\ duansf > mysql-h localhost-u logger-p-- Log in to the database with the logger user
Enter password: *
Welcome to the MySQL monitor. Commands end with; or\ g.
Your MySQL connection id is 3
Server version: 5.5.13 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, 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 >
Mysql > select * from workinfo;-- can be queried
Empty set (0.12 sec)
Mysql > desc workinfo
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (10) | NO | PRI | NULL | auto_increment |
| | name | varchar (20) | NO | | NULL |
| | type | varchar (10) | YES | | NULL |
| | address | varchar (50) | YES | | NULL |
| | tel | varchar (20) | YES | | NULL |
| | wages | int (11) | YES | | NULL |
| | contents | tinytext | YES | | NULL |
| | extra | text | YES | | NULL |
+-+ +
8 rows in set (0.02 sec)
Mysql > insert into workinfo values;-- have the right to insert
Query OK, 1 row affected (0.13 sec)
Mysql > select * from workinfo;-- have query permission
+-+ +
| | id | name | type | address | tel | wages | contents | extra |
+-+ +
| | 3 | chenfeng | type | zhengzhou | 123456 | 22 | 1 | 1 |
+-+ +
1 row in set (0.00 sec)
Mysql > update workinfo set name='duansf' where id=3;-- permission to modify
Query OK, 1 row affected (0.13 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Mysql > select * from workinfo
+-+ +
| | id | name | type | address | tel | wages | contents | extra |
+-+ +
| | 3 | duansf | type | zhengzhou | 123456 | 22 | 1 | 1 |
+-+ +
1 row in set (0.00 sec)
Mysql >
Mysql > delete from workinfo where id=3;-- No permission to delete. The following is the error message
ERROR 1142 (42000): DELETE command denied to user 'logger'@'localhost' for table
'workinfo'
After reading this article, I believe you have a certain understanding of "how MySQL uses GRANT to authorize a user to browse and modify tables". If you want to know more about it, please follow the industry information channel and thank you for your reading!
