mysql data 权限_【MySQL Database】数据库管理：用户与权限

创建用户

mysql> create user 'soe'@'%' identified by 'soe';

Query OK, 0 rows affected (0.02 sec)

修改用户密码

mysql> set password for 'soe'@'%'=password('abcd.1234');

Query OK, 0 rows affected (0.03 sec)

查询用户权限

mysql> show grants for 'soe'@'%';

+-----------------------------------------------------------------------------------------------------+

| Grants for soe@% |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'soe'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |

+-----------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

删除用户

mysql> drop user 'soe'@'%';

Query OK, 0 rows affected (0.07 sec)

权限级别分为全局，库，表，列四个层次，在指定级别授予的权限也必须在指定的级别撤销权限

授予列的权限

mysql> grant all (w_id) on soe.warehouse to 'soe'@'%';

Query OK, 0 rows affected (0.02 sec

mysql> grant select (w_name) on soe.warehouse to 'soe'@'%';

Query OK, 0 rows affected (0.03 sec)

mysql> grant insert (w_street_1) on soe.warehouse to 'soe'@'%';

Query OK, 0 rows affected (0.04 sec)

mysql> show grants for 'soe'@'%';

+-----------------------------------------------------------------------------------------------------+

| Grants for soe@% |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'soe'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |

| GRANT SELECT (w_id, w_name), INSERT (w_street_1) ON `soe`.`warehouse` TO 'soe'@'%' |

+-----------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

撤销授予的列权限

mysql> revoke select (w_id, w_name), insert (w_street_1) on soe1000.warehouse from 'soe'@'%';

Query OK, 0 rows affected (0.03 sec)

mysql> show grants for 'soe'@'%';

+-----------------------------------------------------------------------------------------------------+

| Grants for soe@% |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'soe'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |

+-----------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

授予表的权限

mysql> grant all on soe.warehouse to 'soe'@'%';

Query OK, 0 rows affected (0.03 sec)

mysql> show grants for 'soe'@'%';

+-----------------------------------------------------------------------------------------------------+

| Grants for soe@% |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'soe'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |

| GRANT ALL PRIVILEGES ON `soe`.`warehouse` TO 'soe'@'%' |

+-----------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

撤销授予的表权限

mysql> revoke all on soe.warehouse from 'soe'@'%';

Query OK, 0 rows affected (0.02 sec)

mysql> show grants for 'soe'@'%';

+-----------------------------------------------------------------------------------------------------+

| Grants for soe@% |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'soe'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |

+-----------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

授予库的权限

mysql> grant all on soe.* to 'soe'@'%';

Query OK, 0 rows affected (0.02 sec)

mysql> show grants for 'soe'@'%';

+-----------------------------------------------------------------------------------------------------+

| Grants for soe@% |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'soe'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |

| GRANT ALL PRIVILEGES ON `soe`.* TO 'soe'@'%' |

+-----------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

撤销授予的库权限

mysql> revoke all on soe.* from 'soe'@'%';

Query OK, 0 rows affected (0.02 sec)

mysql> show grants for 'soe'@'%';

+-----------------------------------------------------------------------------------------------------+

| Grants for soe@% |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'soe'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |

+-----------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

授予全局权限

mysql> grant all on *.* to 'soe'@'%';

Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'soe'@'%';

+--------------------------------------------------------------------------------------------------------------+

| Grants for soe@% |

+--------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'soe'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |

+--------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

撤销授予的全局权限

mysql> revoke all on *.* from 'soe'@'%';

Query OK, 0 rows affected (0.03 sec)

mysql> show grants for 'soe'@'%';

+-----------------------------------------------------------------------------------------------------+

| Grants for soe@% |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'soe'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |

+-----------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)