MySQL、MariaDB创建用户、授权、取消授权、远程访问权限、删除用户

命令很简单

//查看mysql现有的用户
select host,user,password from user;
//查看用户，mysql 5.7 用下面这个命令
select host,user,authentication_string from user;

MariaDB [mysql]> select host,user,password from user;
+-------------------------+--------------+-------------------------------------------+
| host | user | password |
+-------------------------+--------------+-------------------------------------------+
| localhost | root | *密码 |
| aaa | root | *密码 |
| 127.0.0.1 | root | *密码 |
| ::1 | root | *密码 |
| localhost | | |
| aaa | | |
| % | abc | *密码 |
| localhost | test | *密码 |
+-------------------------+--------------+-------------------------------------------+

//创建新的用户，朱宏亮是你想创建的用户名，%是个通配符，表示用户可以在所有ip登录，最后面填密码
CREATE USER ‘朱宏亮‘@’%‘ IDENTIFIED BY ‘这里填密码‘;
//授权，all是指赋予所有权限，*.*是指所有的数据库和所有的表，后面是用户名和登录ip限制，
GRANT all ON *.* TO ‘朱宏亮‘@’%‘;
//刷新
flush privileges;

//查看用户授权
show grants for ‘朱宏亮‘@’%‘;
系统输出
//USAGE 表示用户没有任何权限
GRANT USAGE ON *.* TO ‘zhuhongliang’@’%’ IDENTIFIED BY PASSWORD ‘你的密码’

MariaDB [mysql]> show grants for ‘朱宏亮’@’%’;
+————————————————————————————————————–+
| Grants for test@% |
+————————————————————————————————————–+
| GRANT ALL PRIVILEGES ON *.* TO ‘朱宏亮’@’%’ IDENTIFIED BY PASSWORD ‘*你的密码’ |
+————————————————————————————————————–+
1 row in set (0.00 sec)

 

//取消权限 
revoke all on *.* from ‘朱宏亮‘@’%‘;
//删除账户
drop user ‘朱宏亮‘@%‘;
//刷新
flush privileges;

 

然后账户即无法登录