mysql用户创建、删除、权限修改

一. 创建用户:

mysql root@localhost:(none)> create user 'wxj'@'localhost' identified by '123456';
Query OK, 0 rows affected
Time: 0.003s

PS：

username ----wxj为创建的用户名

localhost ----代表只能从本机访问，%-----可以允许使用远程连接工具访问，也可以去指定只能允许某台机器访问

123456 - ---该用户的登陆密码，如果密码为空则可以不需要密码登陆数据库。

二.授权:

mysql root@localhost:(none)> grant all privileges on wxj.* to 'wxj'@'localhost';                                      
 Query OK, 0 rows affected
 Time: 0.002s

PS: all ----用户的操作权限,如SELECT , INSERT , UPDATE 等，如果要授予所的权限则使用ALL.，wxj.*---- 数据库名，*----表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示, 如*.*。

用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:

mysql root@localhost:(none)> grant all privileges on wxj.* to 'wxj'@'localhost' with grant option;                    
 Query OK, 0 rows affected
 Time: 0.002s

刷新权限使权限生效：

mysql root@localhost:(none)> flush privileges;                                                                        
 Query OK, 0 rows affected
 Time: 0.002s

创建用户并同时授权

mysql root@localhost:(none)> grant all privileges on wxj.* to test@localhost identified by '123456';                  
 Query OK, 0 rows affected
 Time: 0.002s
 mysql root@localhost:(none)> flush privileges;                                                                        
 Query OK, 0 rows affected
 Time: 0.001s

三、设置与更改用户密码

mysql root@localhost:(none)> set password for 'wxj'@'localhost' = PASSWORD('Aa123456');                               
 Query OK, 0 rows affected
 Time: 0.002s
 mysql root@localhost:(none)> flush privileges;                                                                        
 Query OK, 0 rows affected
 Time: 0.002s

四、撤销用户授权

mysql root@localhost:(none)> revoke all privileges on wxj.* from 'wxj'@'localhost';                                   
 Query OK, 0 rows affected
 Time: 0.002s
 mysql root@localhost:(none)> flush privileges;                                                                        
 Query OK, 0 rows affected
 Time: 0.001s

五、删除用户

 mysql root@localhost:(none)> drop user 'test1'@'localhost';                                                           
 You're about to run a destructive command.
 Do you want to proceed? (y/n): y
 Your call!
 Query OK, 0 rows affected
 Time: 0.002s

备注：由于使用了插件mycli，所以在删除之前让确认

六、查看用户的授权

mysql root@localhost:(none)> show grants for wxj@localhost;                                                           
 +------------------------------------------------------------------------------------------------------------+
 | Grants for wxj@localhost                                                                                   |
 +------------------------------------------------------------------------------------------------------------+
 | GRANT USAGE ON . TO 'wxj'@'localhost' IDENTIFIED BY PASSWORD '4A488726AE5A0B0F0DB967998EE12D87F25C9610' | | GRANT USAGE ON wxj. TO 'wxj'@'localhost' WITH GRANT OPTION                                              |
 +------------------------------------------------------------------------------------------------------------+
 2 rows in set
 Time: 0.030s

未完待续。。。。。。

Mr丶点心永久会员

打赏 收藏 海报 链接