糖心Vlog:学妹视角互动,解压密码链接提取码,手机电脑本地解压
在糖心Vlog系列中...
2024-09-20
和权限相关的命令关键字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;...
对于上述:
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';
对于授权语句的一些关键字解释如下:
grant | all privileges | on dbname.* | to username@localhost | Identified by ‘mypass’ |
授权命令 | 对应权限 | 目标:库和表 | 用户名和客户端主机 | 用户密码 |
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)
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)
mysql> create user 'username'@'localhost' identified by 'passwd';
mysql> grant all privileges to dbname.* to 'username'@'localhost';
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进行授权就参照步使用步骤的第二步完成。
在上面授权过程中可以看出来,使用的基本都是全部权限:
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)
有时候并不需要给用户给这么多权限,那么怎么收回呢?
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端来代替。
mysql> grant all 0n dbname.* to 'admin1'@'10.0.0.%' identified by '123456';mysql> flush privileges;
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
以上内容就是为大家推荐的mysql免密码登录下无法创建用户(分析mysql创建用户的方法)最佳回答,如果还想搜索其他问题,请收藏本网站或点击搜索更多问题
内容来源于网络仅供参考版权声明:所有来源标注为小樱知识网www.cnfyy.com的内容版权均为本站所有,若您需要引用、转载,只需要注明来源及原文链接即可。
本文标题:mysql免密码登录下无法创建用户(分析mysql创建用户的方法)
本文地址:https://www.cnfyy.com/shcs/122740.html
相关文章
热点文章
2021年独生子女补贴新政策是真的吗(独生子女证有有效期吗)
2021年国庆节阅兵仪式几点开始几点结束(2021年国庆节还有阅兵吗)
鼠目寸光一点红是什么生肖动物(鼠目寸光一点红)指什么生肖,紧密
k0到k9的玩法大全(强制gc的玩法和注意事项)
入土为安是什么生肖《入土为安》打一个生肖动物,词语解释
浙江12月底全面停工是真的吗(浙江什么时候放假停工)
如何做t(t怎么把p做哭)
北京口碑最差的三甲医院(北京301医院最擅长什么)