mysql为啥user表好几个root mysql user

转载

码海航行侠 2024-05-12 22:08:11

文章标签 mysql为啥user表好几个root mysql 数据库创建用户 文章分类 MySQL 数据库

2.用户管理和权限设置

用户管理分为几个方面，查看用户、创建用户、对用户授权、修改和删除用户、设置与更改用户密码、撤销用户权限等。

2.1.查看用户

查看用户并没有直接的SQL语句，而是进入 mysql数据库的user表（这个mysql库和user表都是一开始就有的），直接用 select * from user；来查看有什么用户

由于在linux下比较难看，因为格式比较乱，所以我用了 SQLyog这个软件登陆数据库来操作。

代码如下：

[sql] view plain copy

use mysql；
select * from user;

mysql为啥user表好几个root mysql user_mysql为啥user表好几个root

效果如上图，最后用数据库软件，因为在linux下界面看不清效果，太乱了。

从上图可以看到，会列出数据库所有的用户及权限

2.2.创建用户

创建账号有几种方法。

1.使用grant直接对账号授权，账号不存在则会创建；（MySQL8，已无法进行此操作）
2.向mysql.user表中插入记录；
3.使用create user命令。（最推荐的方法：先创建，然后grant授权）

后两种方法创建的用户初始时没有任何权限(只有usage登录数据库的权限)，并且修改权限后要使用 FLUSH PRIVILEGES 语句或执行 mysqladmin flush-privileges 或 mysqladmin reload 命令刷新权限表到内存中，而第一种方法简便的多，创建用户后会自动刷新权限表。

grant和revoke语法：

GRANT
     priv_type [(column_list)]
       [, priv_type [(column_list)]] ...
     ON [object_type] priv_level
     TO user  [IDENTIFIED [BY [PASSWORD] 'password'][WITH with_option [with_option]
  
 object_type:
     TABLE
   | FUNCTION
   | PROCEDURE
  
 priv_level:
     *
   | *.*
   | db_name.*
   | db_name.tbl_name
   | tbl_name
   | db_name.routine_name
  
 with_option:  
     GRANT OPTION
   | MAX_QUERIES_PER_HOUR count
   | MAX_UPDATES_PER_HOUR count
   | MAX_CONNECTIONS_PER_HOUR count
   | MAX_USER_CONNECTIONS count
   | MAX_STATEMENT_TIME time

grant可以在库、表、函数、存储过程、特定列上授权，且一次性可以为多个用户授予多个对象的权限。其中 with grant option 表示拥有该权限后的用户可以给别的用户授予自身所拥有的权限。

revoke表示收回权限，注意revoke无法收回usage权限。

其中user的表示方法是 '用户名'@'主机名' ，主机名部分可以是主机名，可以是IP地址，可以是localhost，可以是通配符组成的主机名(空的host值也表示所有host，等价于'user_name'@'%')。如下示例：

mysql为啥user表好几个root mysql user_mysql为啥user表好几个root_02

对于网段地址，可以指定掩码来表示，如192.168.100.1/255.255.255.0，不能使用cidr格式的掩码记录方式，也不能指定非8、16、24、32位的掩码，如192.168.100.1/255.255.255.240是不允许的。

如果在user表中的用户有交叉部分，如root既可以从localhost登录，也可以从127.0.0.1登录，还可以从本机IP192.168.100.61登录，还可以从网段地址192.168.100.%登录，那么到底会从哪个登录？

在读取权限表user到内存中的时候，首先会根据host列的具体性进行排序，然后再根据user列进行具体性排序(即理解为order by host,user)，然后从上到下扫描，首次扫描到符合的记录就使用该记录登录。具体性的意思是越具体的host优先级越高，通配符范围越宽的host优先级越低。例如root@localhost的具体性比root@'%'的具体性高，后者又比'%'@'%'的具体性高。

CREATE USER 'user_name'@'host' IDENTIFIED BY 'password'；

user_name：要创建用户的名字。

host：表示要这个新创建的用户允许从哪台机登陆，如果只允许从本机登陆，则填　‘localhost’ ，如果允许从远程登陆，则填 ‘%’

password：新创建用户的登陆数据库密码，如果没密码可以不写。

例：

CREATE USER ‘aaa’@‘localhost’ IDENTIFED BY ‘123456’； //表示创建的新用户，名为aaa，这个新用户密码为123456，只允许本机登陆

CREATE USER 'bbb'@'%' IDENTIFED BY '123456'；//表示新创建的用户，名为bbb，这个用户密码为123456，可以从其他电脑远程登陆mysql所在服务器

CREATE USER ‘ccc’@‘%’ ；//表示新创建的用户ccc，没有密码，可以从其他电脑远程登陆mysql服务器

我用 CREATE USER 'aaa'@‘%’；创建新用户，再用 select * from user；查看用户列表：

mysql为啥user表好几个root mysql user_mysql_03

可以看出有 aaa用户了。但是可以看到 aaa 的权限很多都是 N，表示没有权限，因为还要为用户授予权限。

2.3.授权用户

命令：GRANT privileges ON databasename.tablename TO ‘username’@‘host’

privileges：表示要授予什么权力，例如可以有 select ， insert ，delete，update等，如果要授予全部权力，则填 ALL

databasename.tablename：表示用户的权限能用在哪个库的哪个表中，如果想要用户的权限很作用于所有的数据库所有的表，则填 *.*，*是一个通配符，表示全部。

’username‘@‘host’：表示授权给哪个用户。

例：

GRANT select，insert ON zje.zje TO ‘aaa’@‘%’； //表示给用户aaa授权，让aaa能给zje库中的zje表实行 insert 和 select。

GRANT ALL ON *.* TO ‘aaa’@‘%’；//表示给用户aaa授权，让aaa能给所有库所有表实行所有的权力。

用GRANT ALL ON *.* TO ‘aaa’@‘%’ ；再看用户列表，可以发现权限都变成 Y了。

mysql为啥user表好几个root mysql user_mysql_04

注意：

用以上命令授权的用户不能给其他用户授权，如果想这个用户能够给其他用户授权，就要在后面加上 WITH GRANT OPTION

如： GRANT ALL ON *.* TO ’aaa‘@'%' WITH GRANT OPTION；

2.4.删除用户

直接使用drop user命令或者从mysql.user表中删除对应记录。

命令：DROP USER ‘user_name’@‘host’

例：

DROP USER 'aaa'@‘%’；//表示删除用户aaa；

注意，删除表中用户记录的时候不会从现有用户中回收对该表的权限，当下次再创建同名表的时候，会自动为用户授予该表的权限造成权限外流。

因此，建议使用drop user语句来删除用户。

2.5.设置与更改用户密码

SET PASSWORD FOR ‘username’@‘host’ = PASSWORD(‘newpassword’)；

比如：

(1)grant all on *.* to 'root'@'localhost' identified by '123456' with grant option;
(2)grant usage on *.* to 'root'@'localhost' identified by '123456' with grant option;

使用usage权限表示在不影响现有权限的情况下使用grant来修改密码。

(3)set password [for 'root'@'localhost'] =password('123456');

password函数中必须加引号，不写user时是为当前用户修改。

如果是设置当前用户的密码：

SET PASSWORD = PASSWORD('newpassword')；

如： SET PASSWORD = PASSWORD(‘123456’)；

2.6.撤销用户权限：

命令：REVOKE privileges ON database.tablename FROM ‘username’@‘host’；

例如： REVOKE SELECT ON *.* FROM ‘zje’@‘%’；

但注意：

若授予权利是这样写： GRANT SELECT ON *.* TO ‘zje’@‘%’；

则用 REVOKE SELECT ON zje.aaa TO ‘zje’@‘%’；是不能撤销用户zje 对 zje.aaa 中的SELECT 权利的。

反过来 GRANT SELECT ON zje.aaa TO ‘zje’@‘%’；授予权力

用 REVOKE SELECT ON *.* FROM ‘zje’@‘%’；也是不能用来撤销用户zje 对zje库的aaa表的SELECT 权利的

（附）

A.记录创建用户的时间

MySQL中user的元数据信息都存放在mysql.user表中，但是在这个表中的信息分类很少，常用的就只有用户类列和权限类列，没有用户的创建时间。

可以通过新增一列来记录用户的创建时间。

alter table mysql.user add column create_time timestamp default current_timestamp;

这样以后新建用户都会记录创建时间。但是显然，对于已有的用户是没有记录时间的，它们的值都为'0000-00-00 00:00:00'。

MariaDB [mysql]> select host,user,create_time from mysql.user;
 +---------------------+-----------+---------------------+
 | host                | user      | create_time         |
 +---------------------+-----------+---------------------+
 | localhost           | root      | 2018-04-21 05:58:19 |
 | 127.0.0.1           | root      | 2018-04-21 05:58:19 |
 | ::1                 | root      | 2018-04-21 05:58:19 |
 | localhost           |           | 2018-04-21 05:58:19 |
 | 192.168.100.%       | root      | 2018-04-21 05:58:19 |
 | 192.168.100.1       | long      | 2018-04-21 05:58:19 |
 | 127.0.0.1           | longshuai | 2018-04-21 05:58:19 |
 | 192.168.100.1       | longshuai | 0000-00-00 00:00:00 |
 +---------------------+-----------+---------------------+

B.查看用户权限

可以使用show grants语句查看某个user的权限信息。

例如：

mysql> show grants for 'root'@'localhost';
  
 Grants for root@localhost                                                                                    
 -----------------------------------------------------------------------------------------------------------------
 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION 
 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

mysql>SHOW GRANTS FOR 'long'@'192.168.100.1';
  
 Grants for long@192.168.100.1   
 -----------------------------------------------------------------------------------------------------------
 GRANT USAGE ON *.* TO 'long'@'192.168.100.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' 
 GRANT SELECT ON `test`.* TO 'long'@'192.168.100.1'

C.revoke命令的严格性

revoke命令回收权限时必须要明确指定回收的数据库对象以及用户名，其中usage权限无法回收。特别要说明的是revoke all，当你以为它会回收所有权限的时候，它可能一点权限都没有回收。也就是说revoke命令的书写非常严格。

用户 'long'@'192.168.100.1' 在 *.* 上具有usage权限，在test.*上具有select权限。

MariaDB [mysql]> SHOW GRANTS FOR 'long'@'192.168.100.1';
 Grants for long@192.168.100.1 
 -----------------------------------------------------------------------------------------------------------
 GRANT USAGE ON *.* TO 'long'@'192.168.100.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' 
 GRANT SELECT ON `test`.* TO 'long'@'192.168.100.1'

对该用户在 *.* 上进行revoke all，再次查看权限，发现权限根本一点变化都没有。因为usage权限无法回收，而select权限是在test.*上而非*.*上。

REVOKE ALL ON *.* FROM 'long'@'192.168.100.1';

要回收test.*上的select权限，必须在revoke中指定test.*，而不能是 *.* 。以下两个语句都能回收。

revoke select on test.* from 'long'@'192.168.100.1';
revoke all on test.* from 'long'@'192.168.100.1';

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。