21 MySQL 用户权限

MySQL的user分类:

超级管理员root: 拥有所有权限;
普通用户: 限被授予的各种权限;

管理user创建/修改/删除

权限表:控制user对数据库的访问,存放在MySQL数据库中;
user创建:
create user 创建用户,仅连接权限;

create user'userName'@'host'
identified by [password]'password';

create user'rose'@'localhost' identified by 'rosepwd';
/* 创建rose 设置明文密码 rosepwd */
create user'rose2'@'localhost';
/* 创建rose2 无密码 */
create user'rose3'@'localhost' identified by password'rosepwd';
/* 创建rose 设置哈希密码 */

grant 创建/修改用户及权限;

grant privileges on db.table to 'userName'@'host' 
[identified by [password]'password'];
/* 为db.table 授予 privileges权限,给 userName 密码是'password' */

grant select,update on booksys.* to 'testuser'@'localhost' identified by'testpwd';

grant all privileges on booksys.* to 'testuser2'@'localhost';

user 删除:

drop user 'userName'@'host';
delete from db.table where user='userName' and host='localhost';

drop user 'rose'@'localhost';
delete from mysql.user where user='rose2' and host='localhost';

查询用户授权:

show grants for 'userName'@'localhost';

user管理:
权限管理: columns_priv/ db/ host/ procs_priv/ tables_priv/ user

mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| user                      |允许连接到服务器的user信息;限定host主机操作权限,全局级权限
| db                        |限定user对[数据库]操作权限
| tables_priv               |限定user对[数据表]操作权限
| columns_priv              |限定user对[数据表的某列]操作权限
| procs_priv                |限定对[存储过程][存储函数]操作权限
| component                 |
| default_roles             |
| engine_cost               |
| func                      |
| general_log               |
| global_grants             |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| password_history          |
| plugin                    |
| proxies_priv              |
| role_edges                |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
+---------------------------+
33 rows in set (0.12 sec)