MySQL五个授权表的功能

MySQL五个授权表的功能

mysql.user

1. select host,user,password from mysql.user;  
2. grant all privileges on *.* to monty@localhost identified by   
3.   
4. '123456' with grant option;  
5. select host,user,password from mysql.user;  

display:

 

1. +-----------+------+-------------------------------------------+   
2. | host      | user | password                                  |  
3. +-----------+------+-------------------------------------------+   
4. | localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |  
5. +-----------+------+-------------------------------------------+   
6. 1 row in set (0.00 sec)  
7.   
8.   
9. Query OK, 0 rows affected (0.01 sec)  
10.   
11.   
12. +-----------+-------+-------------------------------------------+   
13. | host      | user  | password                                  |  
14. +-----------+-------+-------------------------------------------+   
15. | localhost | root  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |  
16. | localhost | monty | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |  
17. +-----------+-------+-------------------------------------------+   
18. 2 rows in set (0.00 sec)  

 

1. mysql.db

 

1. drop user monty@localhost;  
2. select host,db,user from mysql.db;  
3. grant all privileges on test.* to monty@localhost identified   
4.   
5. by '123456' with grant option;  
6. select host,user,db from mysql.db;  

display:

Query OK, 0 rows affected (0.00 sec)Empty set (0.00 sec)Query OK, 0 rows affected (0.00 sec)+-----------+-------+------+| host      | user  | db   |+-----------+-------+------+| localhost | monty | test |+-----------+-------+------+1 row in set (0.00 sec)

mysql.host\

#未测试

mysql.tables_priv

1. select * from tables_priv;  
2. grant all privileges on test.student to monty@localhost   
3.   
4. identified by '123456' with grant option;  
5. select host,db,table_name,user from tables_priv;  
6. drop user monty@localhost;  

display:

Empty set (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


+-----------+------+------------+-------+
| host      | db   | table_name | user  |
+-----------+------+------------+-------+
| localhost | test | student    | monty |
+-----------+------+------------+-------+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.00 sec)

mysql.columns_priv

1. select * from columns_priv;  
2. grant select(sno) on test.student to monty@localhost   
3.   
4. identified by '123456' with grant option;  
5. select host,db,user,table_name,column_name,column_priv from   
6.   
7. columns_priv;     
8. drop user monty@localhost;  

display：

Empty set (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


+-----------+------+-------+------------+-------------+-------------+
| host      | db   | user  | table_name | column_name | column_priv |
+-----------+------+-------+------------+-------------+-------------+
| localhost | test | monty | student    | sno         | Select      |
+-----------+------+-------+------------+-------------+-------------+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.00 sec)