mysql免密码登录下无法创建用户(分析mysql创建用户的方法)

一、用户与权限管理

(一)grant

1、help grant

和权限相关的命令关键字grant可通过help查看其用法:

mysql> help grant;
Name: 'GRANT'
Description:
Syntax:
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]

...
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
...

2、grant授权

对于上述:

GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

它实际包含了两条命令,先是创建用户jeffrey,然后才是对这个用户进行授权。如下:

mysql> create user 'jeffrey'@'localhost' identified by 'mypass';
mysql> grant all on db1.* to 'jeffrey'@'localhost' identified by 'mypass';

对于授权语句的一些关键字解释如下:

grantall privilegeson dbname.*to username@localhostIdentified by ‘mypass’
授权命令对应权限目标:库和表用户名和客户端主机用户密码

3、实战练习

  • 查看当前数据库用户情况
mysql> select user,host from mysql.user;
+--------+---------------+
| user   | host          |
+--------+---------------+
| root   | 127.0.0.1     |
| root   | ::1           |
| root   | hadoop-slave1 |
| root   | localhost     |
| system | localhost     |
+--------+---------------+
5 rows in set (0.00 sec)
  • 创建用户并授权
mysql> grant all privileges on test.* to 'admin'@'localhost' identified by 'admin123';
Query OK, 0 rows affected (0.01 sec)
  • 查看授权情况
mysql> select user,host from mysql.user;
+--------+---------------+
| user   | host          |
+--------+---------------+
| root   | 127.0.0.1     |
| root   | ::1           |
| root   | hadoop-slave1 |
| admin  | localhost     |
| root   | localhost     |
| system | localhost     |
+--------+---------------+
6 rows in set (0.00 sec)
  • 查看admin具体权限
mysql> show grants for 'admin'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for admin@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'admin'@'localhost'                                                      |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

(二)create和grant配合使用

1、使用步骤

  • 创建用户username、主机localhost、密码passwd
mysql> create user 'username'@'localhost' identified by 'passwd';
  • 授权创建的用户管理dbname数据库权限(无需密码)
mysql> grant all privileges to dbname.* to 'username'@'localhost';

2、实战练习

  • 查看当前数据库用户情况
mysql> select user,host from mysql.user;
+--------+---------------+
| user   | host          |
+--------+---------------+
| root   | 127.0.0.1     |
| root   | ::1           |
| root   | hadoop-slave1 |
| admin  | localhost     |
| root   | localhost     |
| system | localhost     |
+--------+---------------+
6 rows in set (0.02 sec)
  • 创建用户
mysql> create user 'admin1'@'localhost' identified by 'admin123456';
Query OK, 0 rows affected (0.02 sec)

注意的是这一步并没有授权,仅仅是创建一个普通用户。

  • 查看用户情况
mysql> select user,host from mysql.user;
+--------+---------------+
| user   | host          |
+--------+---------------+
| root   | 127.0.0.1     |
| root   | ::1           |
| root   | hadoop-slave1 |
| admin  | localhost     |
| admin1 | localhost     |
| root   | localhost     |
| system | localhost     |
+--------+---------------+
7 rows in set (0.00 sec)

如果对admin1进行授权就参照步使用步骤的第二步完成。

(三)用户授权的权限有什么

1、查看用户权限

在上面授权过程中可以看出来,使用的基本都是全部权限:

grant all privileges to dbname.* to 'username'@'localhost';

然后查看用户的权限后是这样的:

mysql> show grants for 'admin'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for admin@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'admin'@'localhost'                                                      |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

有时候并不需要给用户给这么多权限,那么怎么收回呢?

2、回收用户权限(revoke)

mysql> revoke insert on test.* from 'admin'@'localhost';  #一定要指定在那个数据库上的权限
Query OK, 0 rows affected (0.00 sec)

可以再次查看该用户的权限:

mysql> show grants for 'admin'@'localhos
+---------------------------------------------------------------------------------------------------------+
| Grants for admin@localhost                                                                         |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' |
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES,LOCK TABLES, EXECUTE,
 CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'admin'@'localhost' |
+----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

可以看到用户在test数据库上除了insert权限外的权限它都有了。

也就是说数据库的all priveleges包含下面的权限:

INSERT, 
SELECT, 
UPDATE, 
DELETE, 
CREATE, 
DROP,
REFERENCES, 
INDEX, 
ALTER, 
CREATE TEMPORARY TABLES,
LOCK TABLES, 
EXECUTE,
CREATE VIEW, 
SHOW VIEW,
CREATE ROUTINE,
ALTER ROUTINE,
EVENT, 
TRIGGER

所以我们在授权时尽量采用最小化的授权原则,比如:

mysql> grant select,insert,update,delete,create,drop on crm.* to 'admin'@'10.0.0.%' identified by '123456';

当admin用户创建表后记得收回create权限:

mysql> revoke create on crm.* from 'admin'@'10.0.0.0.%';

注意:可通过help revoke查看用法

二、远程连接

通过上面的授权,比如:…’admin1’@’localhost’..中的localhost是授权的主机,也就是说什么样的机器有权限连接MySQL服务器。 localhost可以用域名、IP地址、IP端来代替。

(一)匹配方式

1、百分号匹配法

mysql> grant all 0n dbname.* to 'admin1'@'10.0.0.%' identified by '123456';
mysql> flush privileges;

2、子网掩码配置法

mysql> grant all 0n dbname.* to 'admin1'@'10.0.0.0、255.255.255.0' identified by '123456';
mysql>flush privileges;

(二)客户端连接

客户端本地连接与远程连接是不一样的,如果远程连接首先应该赋予远程连接的权限:

mysql> grant all 0n dbname.* to 'admin1'@'10.0.0.%' identified by '123456';

其次,再进行远程连接:

mysql> mysql -uadmin1 -p123456 -h 10.0.0.0.3

版权声明:本站部分文章来源或改编自互联网及其他公众平台,主要目的在于分享信息,版权归原作者所有,内容仅供读者参考,如有侵权请联系我们,如若转载,请注明出处:https://www.seozhh.com/12793.html

发表评论

登录后才能评论