MySQL permission details

I. Permission table
Three permission tables in the MySQL database: User, DB, host
The access process of the permission table is:
1) check whether the connected IP address, user name, and password exist in the table from the host, user, and password fields in the User table. If the connection exists, authentication is performed;
2) After permission verification, when assigning permissions, follow user? DB? Tables_priv? Columns_priv. Check the global permission table user first. If the user has the corresponding permission y, the user has the permission y for all databases and does not check dB, tables_priv, columns_priv; if the value is N, check the specific database corresponding to this user in the DB table and obtain the permission Y in the DB. If the value is N in the DB, check the specific table corresponding to the database in tables_priv, and obtain the permission Y in the table, and so on.
Ii. Various MySQL permissions (27 in total)
(The following operations are grant authorization for Login as root, and run various commands as P1 @ localhost .)

1. Usage
Connection (LOGIN) Permission. When a user is created, the usage permission is automatically granted (default ).
Mysql> grant usage on *. * To 'p1' @ 'localhost' identified by '123 ′;
This permission can only be used for database login and cannot perform any operations. The usage permission cannot be revoked, that is, the revoke user cannot delete the user.
2. Select
You must have the select permission to use the Select Table
Mysql> grant select on PYT. * To 'p1' @ 'localhost ';
Mysql> select * from shop;
3. Create
You must have the create permission to use the CREATE TABLE
Mysql> grant create on PYT. * To 'p1' @ 'localhost ';
4. Create routine
You must have the create routine permission to use {create | alter | drop} {procedure | function}
Mysql> grant create routine on PYT. * To 'p1' @ 'localhost ';
When the create routine is granted, the execute and alter routine permissions are automatically granted to its creator:
Mysql> show grants for 'p1' @ 'localhost ';
+ ------------------------- +
Grants for P1 @ localhost
+ ------------------------- +
| Grant usage on *. * To 'p1' @ 'localhost' identified by password' * 23ae809ddacaf96af0fd78ed04b6a265e05aa257' |
| Grant select, create, create routine on 'pyt'. * To 'p1' @ 'localhost' |
| Grant execute, alter routine on procedure 'pyt'. 'Pro _ shop1 'to 'p1' @ 'localhost' |
+ ----------------------------- +
5. create temporary tables (note that this is tables, not table)
You must have the create temporary tables permission to use the create temporary tables.
Mysql> grant create temporary tables on PYT. * To 'p1' @ 'localhost ';
[MySQL @ mydev ~] $ Mysql-H localhost-u P1-P PYT
Mysql> create temporary table TT1 (id int );
6. Create View
You must have the create view permission to use the create View
Mysql> grant create view on PYT. * To 'p1' @ 'localhost ';
Mysql> Create view v_shop as select price from shop;
7. create user
To use create user, you must have the global create user permission for the MySQL database or the insert permission.
Mysql> grant create user on *. * To 'p1' @ 'localhost ';
Or: mysql> grant insert on *. * to P1 @ localhost;
8. insert
You must have the insert permission before using insert ..... Values ....
9. Alter
You must have the alter permission to use alter table.
Alter table shop modify dealer char (15 );
10. Alter routine
You must have the alter routine permission to use {alter | drop} {procedure | function}
Mysql> grant alter routine on PYT. * To 'p1' @ 'localhost ';
Mysql> drop procedure pro_shop;
Query OK, 0 rows affected (0.00 Sec)

Mysql> revoke alter routine on PYT. * From 'p1' @ 'localhost ';
[MySQL @ mydev ~] $ Mysql-H localhost-u P1-P PYT
Mysql> drop procedure pro_shop;
Error 1370 (42000): Alter routine command denied to user 'p1' @ 'localhost' for routine 'pyt. pro_shop'
11. Update
You must have the update permission to use the update table.
Mysql> Update shop set price = 3.5 where article = 0001 and dealer = 'a ';
12. Delete
You must have the delete permission before using Delete from .... Where .... (Delete records in the table)
13. Drop
Drop database db_name; drop table tab_name;
Drop view vi_name; drop index in_name;
14. Show Database
You can only view databases with certain permissions through show database, unless you have the global show databases permission.
For the P1 @ localhost user, the user does not have permission for the MySQL database, so the MySQL database cannot be viewed during login query using this identity:
Mysql> show databases;
+ ------- +
| Database |
+ ------- +
| Information_schema |
| PYT |
| Test |
+ ------- +
15. Show View
You must have the show view permission to execute show create view.
Mysql> Grant Show view on PYT. * to P1 @ localhost;
Mysql> show create view v_shop;
16. Index
You must have the index permission to execute [Create | drop] Index
Mysql> grant index on PYT. * to P1 @ localhost;
Mysql> Create index ix_shop on shop (Article );
Mysql> drop index ix_shop on shop;
17. excute
Execute the existing functions, procedures
Mysql> call pro_shop1 (0001, @ );
+ --- +
| Article |
+ --- +
| 1, 0001 |
| 1, 0001 |
+ --- +
Mysql> select @;
+ -- +
| @ A |
+ -- +
| 2 |
+ -- +
18. Lock tables
You must have the lock tables permission to use the lock tables
Mysql> grant lock tables on PYT. * to P1 @ localhost;
Mysql> lock tables A1 read;
Mysql> unlock tables;
19. References
With the references permission, you can use a field of another table as a foreign key constraint of a table.
20. Reload
You must have the reload permission before you can execute flush [tables | logs | privileges]
Mysql> grant reload on PYT. * to P1 @ localhost;
Error 1221 (hy000): Incorrect usage of DB Grant and global privileges
Mysql> grant reload on *. * To 'p1' @ 'localhost ';
Query OK, 0 rows affected (0.00 Sec)
Mysql> flush tables;
21. Replication Client
With this permission, you can query the status of the master server and slave server.
Mysql> show Master status;
Error 1227 (42000): Access denied; you need the super, replication client privilege for this operation
Mysql> grant replication client on *. * to P1 @ localhost;
Or: mysql> grant super on *. * to P1 @ localhost;
Mysql> show Master status;
+ ------ + ---- + ----- + ------ +
| File | position | binlog_do_db | binlog_ignore_db |
+ ------ + ---- + ----- + ------ +
| Mysql-bin.000006 | 2111 |
+ ------ + ---- + ----- + ------ +
Mysql> show slave status;
22. Replication slave
With this permission, you can view the slave server and Read Binary logs from the master server.
Mysql> show slave hosts;
Error 1227 (42000): Access denied; you need the replication slave privilege for this operation
Mysql> show BINLOG events;
Error 1227 (42000): Access denied; you need the replication slave privilege for this operation
Mysql> grant replication slave on *. * to P1 @ localhost;
Mysql> show slave hosts;
Empty set (0.00 Sec)
Mysql> show BINLOG events;
+ ----- + --- + ------ + ---- + ----- +
| Log_name | POS | event_type | server_id | end_log_pos | info | + ----- + --- + ----- + ---- + ----- +
| Mysql-bin.000005 | 4 | format_desc | 1 | 98 | Server Ver: 5.0.77-log, BINLOG Ver: 4 | mysql-bin.000005 | 98 | query | 1 | 197 | use 'mysql '; create Table A1 (I INT) engine = MyISAM |
..........................................
23. Shutdown
Close MYSQL:
[MySQL @ mydev ~] $ Mysqladmin Shutdown
Reconnect:
[MySQL @ mydev ~] $ Mysql
Error 2002 (hy000): Can't connect to local MySQL server through socket '/tmp/MySQL. Sock' (2)
[MySQL @ mydev ~] $ CD/u01/MySQL/bin
[MySQL @ mydev bin] $./mysqld_safe &
[MySQL @ mydev bin] $ mysql
24. Grant Option
With grant option, you can grant your permissions to other users (only the permissions you have)
Mysql> Grant option on PYT. * to P1 @ localhost;
Mysql> grant select on PYT. * to P2 @ localhost;
25. File
If you have the file permission, you can execute select .. into OUTFILE and load data infile... But do not grant the file, process, and super permissions to accounts other than administrators. This poses a serious security risk.
Mysql> grant file on *. * to P1 @ localhost;
Mysql> load data infile '/home/MySQL/pet.txt' into Table PET;
26. Super
This permission allows the user to terminate any query, modify the set statement of the global variable, and use change master and purge master logs.
Mysql> grant super on *. * to P1 @ localhost;
Mysql> purge master logs before 'mysql-bin.000006 ′;
27. Process
With this permission, you can run the show processlist and kill commands. By default, each user can execute the show processlist command, but can only query the user's processes. Mysql> show processlist;
+ -- + ---- + -- + --- + ------ +
| ID | user | host | dB | command | time | state | info |
+ -- + ---- + -- + --- + ------ +
| 12 | P1 | localhost | PYT | query | 0 | null | show processlist |
+ -- + ---- + -- + --- + ------ +
In addition,
Management permissions (such as super, process, file, etc.) cannot be specified for a database. On must be followed *.*
Mysql> grant super on PYT. * to P1 @ localhost;
Error 1221 (hy000): Incorrect usage of DB Grant and global privileges
Mysql> grant super on *. * to P1 @ localhost;
Query OK, 0 rows affected (0.01 Sec)