# 创建用户
mysql> create user '{username}'@'{host}' identified by '{password}';
Query OK, 0 rows affected (0.05 sec)## 参数
username:你将创建的用户名
host: 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost,如果想让该用户可以从任意远程主机登陆,可以使用通配符%
password:该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器
# create user 'dog'@'localhost' identified by '123456';# create user 'pig'@'192.168.1.101_' identified by '123456';# create user 'pig'@'%' identified by '123456';# create user 'pig'@'%' identified by '';# create user 'pig'@'%';# 用户授权
mysql> grant {privileges} on {databasename}.{tablename} to '{username}'@'{host}';
Query OK, 0 rows affected (0.01 sec)## 参数
privileges: 用户的操作权限,如SELECT,INSERT,UPDATE等,如果要授予所的权限则使用ALL
databasename:数据库名
tablename: 表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示,如*.*
# grant select,insert on test.sz_log to 'test'@'%';# grant all on *.* to 'test'@'%';# grant all on test.* to 'test'@'%';# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)# 设置与更改用户密码
mysql>set password for'test'@'%'='123456';
Query OK, 0 rows affected (0.01 sec)
查看用户及权限
# \g 相当于’;’# \G使每个字段打印到单独的行,也有’;'的作用
mysql> SELECT user,host FROM mysql.user;
+---------------+-----------+
| user |host|
+---------------+-----------+
| gxejy | % || mysql.session | localhost || mysql.sys | localhost || root | localhost |
+---------------+-----------+
# 查看单个用户
mysql> SELECT user,host FROM mysql.user where user ='gxejy';
+-------+------+
| user |host|
+-------+------+
| gxejy | % |
+-------+------+
mysql>select * from mysql.user where user ='root'\G
*************************** 1. row ***************************
Host: %
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher: 0x
x509_issuer: 0x
x509_subject: 0x
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: caching_sha2_password
authentication_string: $A$005$6ga{OR mup26
EKeIYo7Rf3zPsGxrJbUYd2RlK4T9xxidYA1P2civHVp7A
password_expired: N
password_last_changed: 2021-12-02 03:16:38
password_lifetime: NULL
account_locked: N
Create_role_priv: Y
Drop_role_priv: Y
Password_reuse_history: NULL
Password_reuse_time: NULL
Password_require_current: NULL
User_attributes: NULL