一. 用户管理
创建用户命令:
create user 用户名@'主机名' identified by 'Abc@12345'
create //创建(关键字)
user //用户名(关键字)
用户名自定义
@ //分隔符(关键字)
'主机名' //允许登录的主机
identified //身份认证(关键字)
'Abc@12345' //用户设置的密码
实例:
root用户进入MySQL,创建用户,并查看
mysql> create user user111@'localhost' identified by 'Abc@12345';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from mysql.user\G
*************************** 7. row ***************************
Host: localhost
User: user111
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: mysql_native_password
authentication_string: *CB127D7831EA20E5301ECADBA8FC5D23C5A8E26D
password_expired: N
password_last_changed: 2020-03-15 19:02:46
password_lifetime: NULL
account_locked: N
7 rows in set (0.00 sec)
删除用户命令:
drop user '用户名'@'主机名';
drop //删除(关键字)
user //用户名(关键字)
实例:
删除用户user111,再次查询用户信息,只剩下6个用户了
mysql> drop user 'user111'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.user\G
6 rows in set (0.00 sec)
修改用户密码:
root用户修改自己密码
mysqladmin -uroot -p'旧密码' password '新密码'
或
set password=password('新密码'); //函数方式修改
flush privileges; //刷新密码
实例1:
[root@localhost ~]# mysqladmin -uroot -p'QianFeng@123' password 'QianFeng@12345'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@localhost ~]# mysql -uroot -p'QianFeng@12345'
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
实例2:
mysql> set password=password('QianFeng@123');
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
root用户修改普通用户密码同上
set password for 'user2001'@'%' = password('QianFeng@12345');
或
update user set password=password('123') where user='root' and host='localhost';
实例:
mysql> set password for 'user2001'@'%' = password('QianFeng@12345');
Query OK, 0 rows affected, 1 warning (0.00 sec)
二. 权限管理
权限级别分类:
1. Global level //所有库所有表的权限
2. Database level //某个数据库所有表的权限
3. Table level //库中某个表的权限
4. Column level //表中某个字段的权限
授予目标用户权限:
GRANT ALL ON bbs.* TO admin3@'%' IDENTIFIED BY 'QianFeng@13910604684';
grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' [identified by '密码' with option参数];
权限列表:
all //所有权限(不包括授权)
select,update //查询更新
库名表名:
*.* //所有库所有表
school.* //school库下所有表
查看用户的权限:
SHOW GRANTS FOR admin3@'%'\G //查看用户权限
SHOW GRANTS\G //查看自身权限
实例:
更改user2001的权限,并查看
mysql> SHOW GRANTS FOR user2001@'%'\G
*************************** 1. row ***************************
Grants for user2001@%: GRANT USAGE ON *.* TO 'user2001'@'%'
1 row in set (0.00 sec)
mysql> GRANT ALL ON *.* TO user2001@'%' IDENTIFIED BY 'QianFeng@123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> SHOW GRANTS FOR user2001@'%'\G
*************************** 1. row ***************************
Grants for user2001@%: GRANT ALL PRIVILEGES ON *.* TO 'user2001'@'%'
1 row in set (0.00 sec)
撤销用户权限:
REVOKE 权限列表 ON 数据库名 FROM 用户名@‘客户端主机’
实例:
撤回user2001 的权限,并查看
mysql> revoke all privileges on *.* from user2001@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR user2001@'%'\G
*************************** 1. row ***************************
Grants for user2001@%: GRANT USAGE ON *.* TO 'user2001'@'%'
1 row in set (0.00 sec)
完成