mysql用户权限管理

在不同的项目中给不同的角色（mysql客户端用户，通常为开发者）不同的权限，为了保证数据库的数据安全。

查看user表数据

select * from mysql.user\G
# or
select * from mysql.user;

查询结果

*************************** 4. row ***************************
                    Host: localhost     # 主机地址
                    User: root             # 用户名
             Select_priv: Y             # 允许从表中查看数据，某些不查询表数据的select执行则不需要此权限，如Select 1+1，Select PI()+2;而且select权限在执行update/delete语句中含有where条件的情况下也是需要的
             Insert_priv: Y       # 允许在表里插入数据，同时在执行analyze table,optimize table,repair table语句的时候也需要insert权限
             Update_priv: Y       # 允许修改表中的数据的权限
             Delete_priv: Y       # 允许删除行数据的权限
             Create_priv: Y             # 允许创建新的数据库和表的权限
               Drop_priv: Y       # 允许删除数据库、表、视图的权限，包括truncatetable命令
             Reload_priv: Y       # 允许执行flush命令，指明重新加载权限表到系统内存中， refresh命令代表关闭和重新开启日志文件并刷新所有的表
           Shutdown_priv: Y       # 允许关闭数据库实例，执行语句包括mysqladmin shutdown
            Process_priv: Y       # 允许查看MySQL中的进程信息，比如执行showprocesslist,
               File_priv: Y       # 允许在MySQL可以访问的目录进行读写磁盘文件操作，可使用的命令包括load data infile,select ... into outfile,load file()函数
              Grant_priv: Y       # 允许此用户授权或者收回给其他用户你给予的权限
         References_priv: Y       # 允许创建外键
              Index_priv: Y       # 允许创建和删除索引
              Alter_priv: Y             # 允许修改表结构的权限，但必须要求有create和insert权限配合。
            Show_db_priv: Y       # 通过执行show databases命令查看所有的数据库名
              Super_priv: Y       # 允许执行一系列数据库管理命令，包括kill强制关闭某个连接 命令，change master to创建复制关系命令，以及create/alter/drop server等命令
   Create_tmp_table_priv: Y       # 允许创建临时表的权限
        Lock_tables_priv: Y       # 允许对拥有select权限的表进行锁定，以防止其他链接对此表的读或写
            Execute_priv: Y       # 允许执行存储过程和函数的权限
         Repl_slave_priv: Y       # 允许slave主机通过此用户连接master以便建立主从复制关系
        Repl_client_priv: Y       # 允许执行show master status,show slave status,show binary logs命令
        Create_view_priv: Y       # 允许创建视图的权限
          Show_view_priv: Y       # 通过执行show create view命令查看视图创建的语句mysqladmin processlist, show engine等命令
     Create_routine_priv: Y       # 允许创建存储过程、函数的权限
      Alter_routine_priv: Y       # 允许修改或者删除存储过程、函数的权限
        Create_user_priv: Y       # 允许创建、修改、删除、重命名user的权限
              Event_priv: Y       # 允许查询，创建，修改，删除MySQL事件
            Trigger_priv: Y       # 允许创建，删除，执行，显示触发器的权限
  Create_tablespace_priv: Y       # 代表允许创建、修改、删除表空间和日志组的权限
                ssl_type: 
              ssl_cipher: 
             x509_issuer: 
            x509_subject: 
           max_questions: 0
             max_updates: 0
         max_connections: 0
    max_user_connections: 0
                  plugin: caching_sha2_password
   authentication_string: $A$005$^RGz_WQEN5(    P]juZYFQYK5UP.gNA2s819lMW.kMd16.hzKJp48dSpsPB
        password_expired: N
   password_last_changed: 2019-10-21 10:33:52
       password_lifetime: NULL
          account_locked: N
        Create_role_priv: Y
          Drop_role_priv: Y
  Password_reuse_history: NULL
     Password_reuse_time: NULL
Password_require_current: NULL
         User_attributes: NULL

1. 创建用户

# create user '用户名'@'主机名' identified by '明文密码';
create user 'root'@'localhost' identified by '123456';

# 创建无需密码用户 guest001
create user guest001;

主机名 可以是localhost(本机)、具体ip地址/某网段的所有主机(192.168.0.%)，也可以是 % 无限制。

1. 删除用户

drop user '用户名'@'主机名';

# 删除地址为'' 或者 %  的用户
drop user 用户名;

1. 修改用户密码

set password for 用户名 = password('新的明文密码);

或者

update mysql.user set password = password('新的明文密码') where user = '用户名' and host='主机地址';

权限管理

1. 权限分类

   • 数据权限
     • 增 insert
     • 删 delete
     • 改 update
     • 查 select
   • 结构权限
     • 创建 create
     • 删除 drop
   • 管理权限
     • 创建用户 create user
     • 授权 grant
     • 权限回收 revoke

2. 查看权限

   show grants\G
   # 查看别人权限
   show grants for admin1@'%'\G
   

3. 授予权限

   grant 权限 on 数据库.表名 to 用户;
   # 权限列表: 
   #     多个权限(使用逗号分隔) : create,drop,insert,delete,update,select
   #     全部权限 : all privileges
   # 数据库.表名
   #     单表 : `数据库.表名`
   #     整个数据库 : `数据库.*`
   #     整个库 : `*.*`
   

4. 权限回收

   revoke 权限 on 数据库.表名 from 用户;
   

5. 刷新权限

   flush privileges;
   

密码丢失解决方案

1. 停止服务

   # mac
   mysql.server stop
   # ubuntu
   service mysql stop
   

2. 跳过权限启动mysql

   mysqld --skip-grant-tables
   

3. 登录mysql，并修改密码

   # 登录
   mysql
   # 修改密码
   mysql> set password for root = password('123456');