mysql新增用户，并赋予用户权限

在稍微大一些的公司里面都是进行着明显的权限管理，特别是进行数据库操作。如果赋予用户过多权限，用户一不小心进行删库操作，就会造成巨大影响。因此公司进行项目都会创建专门的用户指定其权限只能控制数据库进行有限的操作，不会赋予root权限进行操作。

 

MySQL 在安装时会自动创建一个名为 mysql 的数据库，mysql 数据库中存储的都是用户权限表。用户登录以后，MySQL 会根据这些权限表的内容为每个用户赋予相应的权限。user 表是 MySQL 中最重要的一个权限表，用来记录允许连接到服务器的账号信息。在 user 表里启用的所有权限都是全局级的，适用于所有数据库。user 表中的字段大致可以分为 4 类，分别是用户列、权限列、安全列和资源控制列。

SHOW columns FROM mysql.user;

 

 

 

在 MySQL 数据库中，权限表除了user表外，还有db表、tables_priv表、columns_priv表和procs_priv表。

tables_priv表用来对单个表进行权限设置，columns_priv表用来对单个数据列进行权限设置，procs_priv表可以对存储过程和存储函数进行权限设置。

 

 创建用户

# 语法格式为 [@'host']  host 为 'localhost' 表示本地登录用户，host 为 IP地址或 IP 地址区间，表示指定IP地址的主机可登录，host 为 "%"，表示所有主机都可登录，省略代表所有主机
CREATE USER 'username'[@'host'] IDENTIFIED BY 'password';
# eg. 常见 local_user 用户可以在所有主机登录，密码为 123456
CREATE USER 'local_user'@‘%’ IDENTIFIED BY '123456';
# eg. 创建 local_user 只允许在本地登录
CREATE USER 'local_user'@'localhost' IDENTIFIED BY '123456';

 删除用户

# 语法格式
DROP USER 'username'@'host';
# eg. 删除本地用户 local_user
DROP USER 'local_user'@'localhost';

修改用户

RENAME USER <'old username'@'host'> TO <'new username'@'host'>

查看用户权限

# 可以通过查询 user 表获取 语法格式为
SELECT  privileges|* FROM user WHERE `user` = 'username';
# eg. 查看 local_user 的权限
SELECT * FROM user WHERE `user` = 'local_user';
# 也可以用 SHOW GRANTS 查看
SHOW GRANTS FOR 'username' [@host];
# eg.
SHOW GRANTS FOR local_user;

 赋予用户权限

# 语法格式
GRANT privileges ON database.table TO 'username'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];        # [IDENTIFIED BY 'password']在MySQL8.0+无法使用

# eg. 赋予 local_user 在所有主机的所有权限，但不包含给其他账号赋予权限的权限 
GRANT all ON *.* TO 'local_user'@'%'; 
# 刷新权限 权限更新后刷新才会起作用 
FLUSH PRIVILEGES;

• GRANT命令说明：
  • priveleges (权限列表),可以是all, 表示所有权限，也可以是select,update等权限，多个权限的名词,相互之间用逗号分开。
  • ON 用来指定权限针对哪些库和表。格式为数据库 .表名 ，点号前面用来指定数据库名，点号后面用来指定表名，*.* 表示所有数据库所有表。
  • TO 表示将权限赋予某个用户, 格式为username@host，@前面为用户名，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。注意：这里%有的版本不包括本地，以前碰到过给某个用户设置了%允许任何地方登录，但是在本地登录不了，这个和版本有关系，遇到这个问题再加一个localhost的用户就可以了。
  • IDENTIFIED BY 指定用户的登录密码,该项可以省略(某些版本下回报错，必须省略)。
  • WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。
    备注：可以使用GRANT重复给用户添加权限，权限叠加，比如你先给用户添加一个select权限，然后又给用户添加一个insert权限，那么该用户就同时拥有了select和insert权限。
• 授权原则说明：
  • 只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。
  • 创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。
  • 初始化数据库的时候删除没有密码的用户。安装完数据库的时候会自动创建一些用户，这些用户默认没有密码。
  • 为每个用户设置满足密码复杂度的密码。
  • 定期清理不需要的用户。回收权限或者删除用户。

权限列表

grant 权限 on 数据库.表 to '用户'@'IP地址' [WITH GRANT OPTION]; 

//all privileges 除grant外的所有权限 
select 　　　　 仅查权限 
select,insert 查和插入权限 ...  
usage  无访问权限  
alter  使用alter table 
alter routine  使用alter procedure和drop procedure  
create  使用create table  
create routine 使用create procedure  
create temporary tables 使用create temporary tables  
create user 使用create user、drop user、rename user和revoke all privileges  
create view  使用create view  
delete   使用delete  
drop 使用drop table  
execute   使用call和存储过程 
file 使用select into outfile 和 load data infile  
grant option  使用grant 和 revoke  
index   使用index  
insert   使用insert  
lock tables  使用lock table  
process   使用show full processlist 
select 使用select 
show databases  使用show databases  
show view   使用show view  
update   使用update  
reload  使用flush  
shutdown  使用mysqladmin shutdown(关闭MySQL)  
super  ??使用change master、
kill、logs、purge、master和set global。还允许mysqladmin????调试登陆 
replication client 服务器位置的访问 
replication slave  由复制从属使用 
// 数据库名.* 数据库中的所有  
数据库名.表 　　     指定数据库中的某张表 
数据库名.存储过程 　　指定数据库中的存储过程 
*.* 　　           所有数据库

删除用户权限

revoke 权限 on 数据库.表 from '用户'@'IP地址';