13、mysql用户权限

FeiYi

MySQL

发布于：2019年12月13日

次浏览

字数：2.3k字

时长：10分钟

MYSQL用户权限

在mysql中维护了一个存放用户信息的表，user，该表位于mysql数据库中，用户之所以能登录mysql是因为匹配了该表中的用户信息，才能登录成功，否则登录失败

SELECT * FROM mysql.user\G用来查看用户权限的表

一旦删除此表，数据库将会瘫痪

1、用户列

包含user，host，password，使用用户，用户有权限的主机名，和密码（5.7.20之后使用authentication_string保存密码），当用户登录mysql时，之后匹配用户列的内容才能成功登录mysql，user和host列属于联合主键，用来确定用户身份的唯一性

2、权限列

规定了用户只能在给定权限的范围内进行活动，如果该用户没有权限，将不能执行指定的命令，在user表中，如果用户拥有该权限则该权限为Y，如果没有则为N，在priv权限列中，所有的字段使用ENUM枚举进行控制，只有Y和N两个选项，不存在空值

           Select_priv: Y            查看权限
           Insert_priv: Y            写入权限
           Update_priv: Y            更新权限
           Delete_priv: Y            删除权限（数据）
           Create_priv: Y            创建权限
             Drop_priv: Y            删除权限（库表）
           Reload_priv: Y            重载权限（是否可以使用flush命令）
         Shutdown_priv: Y            关闭服务权限    mysqladmin -u root shutdown
          Process_priv: Y            查看进程和中断进程权限     show processlist;
             File_priv: Y            载入文件权限
            Grant_priv: Y            给其他用户授权权限        
       References_priv: Y            创建约束权限
            Index_priv: Y            创建索引权限
            Alter_priv: Y            修改表权限
          Show_db_priv: Y            查看库表权限
            Super_priv: Y            超级权限(change master(更改主服务器)/set global(设置全局变量)/purge master log(删除日志))
 Create_tmp_table_priv: Y            创建临时表权限
      Lock_tables_priv: Y            锁表权限
          Execute_priv: Y            执行函数或者存储过程的权限
       Repl_slave_priv: Y            slave的管理权限
      Repl_client_priv: Y            查看master和slave的权限
      Create_view_priv: Y            创建视图的权限
        Show_view_priv: Y            查看视图的权限
   Create_routine_priv: Y            创建函数或者存储过程的权限
    Alter_routine_priv: Y            修改函数或者存储过程的权限
      Create_user_priv: Y            创建用户的权限
            Event_priv: Y            管理事件的权限
          Trigger_priv: Y            管理触发器的权限
Create_tablespace_priv: Y            创建表空间权限

创建用户：

三种方式的区别：

1、create user仅用于创建用户，而grant可以在创建用户的同时赋予权限

2、create user创建的用户没有权限，且用户已经创建如果再一次创建该用户则创建失败，grant创建用户多次不会失败，相当于继续给该用户授权

3、create user一次只能创建一个用户，而grant可以创建多个，并且同时赋予权限

4、如果使用insert不安全，且用户同样没有权限，还要使用flush privileges识别用户

5、grant可以在创建用户赋予权限的同时还能修改密码

CREATE USER创建用户

语法：create user 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password']

mysql> CREATE USER 'cyj'@'localhost' IDENTIFIED BY '123.com';
/*创建完用户没有任何权限，因为权限列的默认值为N，不加PASSWORD使用明文密码*/
mysql> CREATE USER 'pjf'@'localhost' IDENTIFIED BY PASSWORD '*AC241830FFDDC8943AB31CBD47D758E79F7953EA'
/*创建完用户没有任何权限，因为权限列的默认值为N，加上PASSWORD使用密文密码*/

GRANT创建用户

GRANT privileges ON db.table TO 'user'@'host' [IDENTIFIED BY 'password'] [,user@host [IDENTIFIED BY 'password']] [with grant option]

with grant option：表示授权的这个用户是否有grant授权权限，加上则有，不加则没有

mysql> GRANT ALL ON *.* TO 'mp'@'localhost' IDENTIFIED BY '123.com';
/*创建用户并给予all权限设置密码*/
mysql> GRANT ALL ON *.* TO 'mu'@'localhost' IDENTIFIED BY '123.com',
'pei'@'localhost' IDENTIFIED BY '123.com';
/*授权两个用户*/

直接操作user表

mysql> INSERT INTO mysql.user(host,user,password) VALUES(‘localhost’,’mpei’,PASSWORD(123.com));

3、安全列

包含以下字段

两个SSL相关字段，用于加密，两个x509字段用户标识用户，plugin字段标识验证用户身份的插件，authentication_string：在5.7.20版本之后替代password存放密码

当前使用的mysql自带的身份验证机制（用户+主机+密码），没有其余插件

4、用户资源控制

         max_questions: 0            用户每小时被允许执行查询操作的次数
           max_updates: 0            用户每小时被允许执行更新操作的次数
       max_connections: 0            用户每小时被允许执行连接操作的次数
  max_user_connections: 0            用户被允许同时连接的次数

mysql> GRANT ALL ON *.* to 'user'@'localhost' WITH MAX_USER_CONNECTIONS 2;

设置同时连接次数是2次，开三个终端登录，发现第三个终端登录不了，报错：

ERROR 1226 (42000): User ‘user’ has exceeded the ‘max_user_connections’ resource (current value: 2)

删除用户

1）DROP USER ‘user’@’localhost’

DROP USER ‘user’：删除‘user’@’%’

2）直接使用delete操作表（不限版本）

mysql> delete from mysql.user where user='user6' and host='192.168.1.%';
/*必须指定user和host*/

修改用户密码

1、使用mysqladmin修改密码

mysqladmin -u user -p 旧密码 password 新密码

[root@localhost ~]# mysqladmin -u root password '123.com' /*本身没有密码*/

2、直接修改user表修改密码

mysql> UPDATE mysql.user SET PASSWORD=PASSWORD('123.com') WHERE user='root' AND host='localhost';

3、使用set修改密码(更改当前用户密码)

SET PASSWORD=PASSWORD('密码');

mysql> SET PASSWORD=PASSWORD('321.com');

4、修改指定用户的密码（root权限）

SET PASSWORD FOR 'user'@'host'=PASSWORD('密码');

mysql> CREATE USER 'user1'@'localhost' identified by '123.com';
创建用户user1，密码为123.com
mysql>  SET PASSWORD FOR 'user1'@'localhost'=PASSWORD('321.com');
修改user1用户的密码

5、使用grant修改密码

GRANT PRIVILEGES ON db.table TO 'user'@'host' IDENTIFIED BY '密码';

mysql> GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY '111.com';

忘记密码怎么办

[root@localhost ~]# /etc/init.d/mysqld stop                /*停止mysql服务*/
Shutting down MySQL.... SUCCESS! 
[root@localhost ~]# mysqld_safe --skip-grant-tables user=mysql        
/*使用mysql用户设置跳过授权表，不检测密码*/
[root@localhost ~]# /etc/init.d/mysqld start --skip-grant-tables
/*开启服务并跳过授权表*/
[root@localhost ~]# mysql -u root            /*不使用密码即可登录*/
mysql> UPDATE mysql.user SET PASSWORD=PASSWORD('123.com') WHERE user='root';
/*只能通过修改user表来修改密码
mysql> flush privileges;            /*刷新用户表*/
[root@localhost ~]# /etc/init.d/mysqld  restart         /*重启服务去掉跳过授权表*/
[root@localhost ~]# mysql -u root -p123.com        /*密码修改成功*/

收回用户权限

收回权限意味着该用户将不会在具有之前所赋予的某种或者所有权限，他在指定的范围将不能继续操作之前可以操作的命令，通过grant是不能收回权限的，只能通过revoke进行收回，收回权限分为全部收回和部分收回，收回权限可以在一定程度上保证数据库的安全

1、收回所有权限

REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'user'@'host','user'@'host';

mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'user2'@'localhost';
mysql> SELECT * FROM mysql.user WHERE user='user2'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: user2
              Password: *AC241830FFDDC8943AB31CBD47D758E79F7953EA
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N

2、收回指定权限

REVOKE priv,priv ON db.table FROM 'user'@'host';

mysql> REVOKE INSERT,UPDATE,DELETE,ALTER,SELECT ON *.* FROM  'user2'@'localhost';
mysql> SELECT * FROM mysql.user WHERE user='user2'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: user2
              Password: *AC241830FFDDC8943AB31CBD47D758E79F7953EA
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
            Grant_priv: N
            Alter_priv: N

查看用户权限的方式

mysql库中的user表所显示的权限为全局权限，意味着该用户只有在所有库所有表中都拥有该权限，则该权限才会显示Y，否则为N

1、查看mysql库中的user表

SELECT * FROM mysql.user\G

2、使用show grant进行查看

SHOW GRANTS FOR 'user'@'host'\G

Grants for user2@localhost: GRANT USAGE ON *.* TO 'user2'@'localhost' IDENTIFIED BY PASSWORD '*AC241830FFDDC8943AB31CBD47D758E79F7953EA'

上述语句中USAGE是重点，USAGE在英语中表示可以使用，在mysql中表示登陆了mysql权限，该权限随着用户的创建而诞生，随着用户的删除而消失，该权限不能通过revoke进行收回，只要用户一直存在，USAGE权限就会一直存在，直到该用户被删除为止。

mysql> use cyj;        /*使用cyj数据库*/
mysql> CREATE TABLE class(s_name VARCHAR(30),c_name VARCHAR(20),c_id INT);
/*创建测试表*/
mysql> INSERT INTO class VALUES('a','s25',10);
/*创建测试数据*/
mysql> GRANT UPDATE ON cyj.class TO 'user2'@'localhost';
/*授权user2用户有update权限*/

上述实验证明：

生产环境中，权限一般会成组赋予，如果赋予单个权限，有可能造成用户无法执行指定操作的问题，例如：update权限，如果没有select权限，不能查看数据，也就不能修改，即使update能够使用，但对于数据来说很不安全。不建议使用！！！