CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
命令解释:
username:新建用户名
hostname:可用于登录的远程主机,loaclhost为本地登录,%为任意主机均可登录
password:用于登录的密码
命令执行演示:
mysql> create user 'admin'@'localhost' identified by 'admin';
Query OK, 0 rows affected (0.04 sec)
删除用户
DROP USER 'username'@'hostname';
命令解释:
username:要删除的用户名
hostname:可用于登录的远程主机,必须和配置的一致,否则删除失败
命令执行演示:
mysql> drop user 'admin'@'%';
ERROR 1396 (HY000): Operation DROP USER failed for 'admin'@'%'
mysql> drop user 'admin'@'localhost';
Query OK, 0 rows affected (0.03 sec)
用户重命名
RENAME USER 'username'@'hostname' TO 'newname'@'hostname';
命令解释:
username:要重命名的用户名
hostname:可用于登录的远程主机,必须和配置的一致,否则删除失败
newname:新用户名
命令执行演示:
mysql> rename user 'jack'@'%' to 'mike'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from user\G
*************************** 1. row ***************************
user: mike
host: %
*************************** 2. row ***************************
……
修改密码
参考官方文档及个人实践,MySQL8总共有3种可行的密码修改方式:
方法1:用mysqladmin命令:
mysqladmin -u username -p password
命令解释:
-u username:要修改的用户名
-p :使用密码验证(如已设置过密码)
password:需要执行修改密码操作
命令执行演示:
[[email protected] ~]# mysqladmin -u root -p password
Enter password: #输入原始密码
New password: #输入新密码
Confirm new password: #输入确认密码
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
方法2:用SET PASSWORD语句:
SET PASSWORD FOR 'username'@'hostname'='password';
FLUSH PRIVILEGES;
命令解释:
username:要修改的用户名
hostname:可用于登录的远程主机,必须和配置的一致,否则修改失败
password:新密码
命令执行演示:
mysql> set password for 'guest'@'localhost'='guest';
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> set password for 'guest'@'%'='guest';
Query OK, 0 rows affected (0.03 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
mysql> set password for 'guest'@'localhost'=PASSWORD('guest');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('guest')' at line 1
踩到的坑:使用此种方式修改密码后必须使用
FLUSH PRIVILEGES;
语句刷新权限,否则无法使用Navicat等工具进行连接访问!
方法3:用ALTER USER语句:
ALTER USER ‘username’@'hostname' IDENTIFIED BY 'passwd';
命令解释:
username:要修改的用户名
hostname:可用于登录的远程主机,必须和配置的一致,否则修改失败
password:新密码
命令执行演示:
mysql> alter user 'guest'@'localhost' identified by '1234';
ERROR 1396 (HY000): Operation ALTER USER failed for 'guest'@'localhost'
mysql> alter user 'guest'@'%' identified by '1234';
Query OK, 0 rows affected (0.01 sec)
[WITH GRANT OPTION]:可选项,如当前登录用户权限是由其他高级权限用户授予,则此时该用户不能对其他用户继续授权,但当使用可选项语句后,该用户即可对其他用户进行传递授权
命令执行演示:
#当前使用root用户登录
mysql> create user 'jack'@'localhost' identified by 'jack';
Query OK, 0 rows affected (0.05 sec)
mysql> grant select,update on mysql.user to 'jack'@'localhost';
Query OK, 0 rows affected (0.04 sec)
#当前使用jack用户登录
#无权授权其他用户
mysql> grant select on mysql.user to 'guest'@'%';
ERROR 1142 (42000): GRANT command denied to user 'jack'@'localhost' for table 'user'
#使用root用户重新登录并改为使用[WITH GRANT OPTION]授权
mysql> grant select,update on mysql.user to 'jack'@'localhost' with grant option;
Query OK, 0 rows affected (0.02 sec)
#退出root用户并使用jack用户重新登录尝试为其他用户授权
#授权成功
mysql> grant select on mysql.user to 'guest'@'%';
Query OK, 0 rows affected (0.03 sec)
撤销授予的权限
REVOKE privileges ON databasename.tablename FROM 'username'@'hostname';
mysql> revoke all on *.* from 'guest'@'%';
Query OK, 0 rows affected (0.05 sec)
mysql> revoke insert on mysql.user from [email protected]'localhost';
Query OK, 0 rows affected (0.03 sec)
需注意MySQL操作权限又区分为全局层级、数据库层级、表层级、列层级、子程序层级。
全局层级例如:
GRANT ALL ON *.*
和
REVOKE ALL ON *.*
此类
数据库层级例如:
GRANT ALL ON databasename.*
和
REVOKE ALL ON databasename.*
表层级例如:
GRANT ALL ON databasename.tablename
和
REVOKE ALL ON databasename.tablename
如上概述,在授权及撤销权限时应做到对应,否则会出现无法撤销对应权限的情况!
查询用户权限方法
方法1:SHOW GRANTS语句
SHOW GRANTS FOR 'username'@'hostname';
命令解释:
username:需查询权限用户名
hostname:可用于登录的远程主机,必须和配置的一致,否则查询失败
命令执行演示:
mysql> show grants for [email protected]'localhost';
+----------------------------------------------------------------------------------------+
| Grants for [email protected] |
+----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `jack`@`localhost` |
| GRANT SELECT, INSERT, UPDATE ON `mysql`.`user` TO `jack`@`localhost` WITH GRANT OPTION |
+----------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
方法2:SELECT语句
SELECT * FROM user WHERE user='username'\G;
命令解释:
第一个user:指代mysql数据库中user表,所以在使用之前应先使用
USE mysql
语句切换到mysql数据库
username:需查询权限的用户名
\G:在Linux或DOS终端中使用有效,将查询结果旋转90°纵向展示
命令执行演示:
mysql> use mysql
Database changed
mysql> select * from user where user='jack'\G;
*************************** 1. row ***************************
Host: localhost
User: jack
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: caching_sha2_password
authentication_string: xxx
password_expired: N
password_last_changed: 2019-10-25 20:57:32
password_lifetime: NULL
account_locked: N
Create_role_priv: N
Drop_role_priv: N
Password_reuse_history: NULL
Password_reuse_time: NULL
Password_require_current: NULL
User_attributes: NULL
1 row in set (0.00 sec)
ERROR:
No query specified