目录
一、MySQL用户管理
1、新建用户
语法格式：
create USER 'username'@'host' [identified by [password] 'password']；
## 创建一个用户，名为lili 密码为abc123 允许在本地登录
mysql> create user 'lili'@'localhost' identified by 'abc123'; ## 创建新用户lili
Query OK, 0 rows affected (0.00 sec)
###验证：
mysql> use mysql #### 切换数据库
mysql> show tables; #### 显示当前数据库的表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
...............................
mysql> select User,authentication_string from user ## 在user中查找显示User和
authentication_string两个字节
-> ;
+---------------+-------------------------------------------+
| User | authentication_string |
+---------------+-------------------------------------------+
| root | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| lili | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | ### 新用户创建成功
+---------------+-------------------------------------------+
4 rows in set (0.00 sec)
[root@zwb_mysql ~]# mysql -ulili -pabc123 ###使用lili用户名进行登录测试
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
2、删除用户
语法格式：
drop user 'username'@'localhost';
mysql> select User,authentication_string from user ### 查询用户
-> ;
+---------------+-------------------------------------------+
| User | authentication_string |
+---------------+-------------------------------------------+
| root | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| lili | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
+---------------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql> drop user 'lili'@'localhost'; ### 删除用户lili
Query OK, 0 rows affected (0.00 sec)
mysql> select User,authentication_string from user ### 验证，查询用户
-> ;
+---------------+-------------------------------------------+
| User | authentication_string |
+---------------+-------------------------------------------+
| root | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-------------------------------------------+
3 rows in set (0.00 sec)
3、重命名用户
语法格式：
rename user 'old_name'@'localhost' to 'new_name'@'localhost';
### 创建用户wangyi
mysql> create user 'wangyi'@'localhost' identified by 'abc123';
Query OK, 0 rows affected (0.00 sec)
### 查看新创建用户
mysql> select User,authentication_string from user;
+---------------+-------------------------------------------+
| User | authentication_string |
+---------------+-------------------------------------------+
| root | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| wangyi | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
+---------------+-------------------------------------------+
4 rows in set (0.00 sec)
### 把用户名wangyi 修改为 wangwu
mysql> rename user 'wangyi'@'localhost' to 'wangwu'@'localhost';
Query OK, 0 rows affected (0.01 sec)
###查看修改后结果
mysql> select User,authentication_string from user;
+---------------+-------------------------------------------+
| User | authentication_string |
+---------------+-------------------------------------------+
| root | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| wangwu | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
+---------------+-------------------------------------------+
4 rows in set (0.00 sec)
4、给用户设置密码
①修改当前登录的用户的密码
语法格式：(使用password（）函数对密码进行加密)
mysqladmin -u用户名 -p旧密码 password 新密码
## 修改root用户密码。设置为abc123
[root@zwb_mysql ~]# mysqladmin -uroot -p123123 password abc123;
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@zwb_mysql ~]# mysql -uroot -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.20 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
二、授权控制
授权控制目的：
我们使用create user 创建的用户，只能登录进入数据库，但是无法做任何事情
还需要grant 命令进行 用户授权。同时，此命令，如果当用户不存在，也可以创建该用户。
1、授予权限
授权控制遵循的原则：
①只授予能满足要求的最小权限，防止用户误操作
②创建用户时限制用户的登录主机
③初始化数据库时删除没有密码的用户
④为每个用户设置复杂的符合要求的密码
⑤定期清理不需要的用户
2、授予权限的语法格式
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名 ' identified by '密码' with grant option ；
权限列表：多个权限用逗号‘,’ 隔开，如 'select,update'
数据库名.表名：用于指定授权操作的数据库名和表名，可以使用通配符' *' 表示所有。如 *.* 表示所有库，所有表。
'用户名'@'主机名 '：用户指定用户名和可以登录的客户端地址
identified by :用于设置用户连数据库时候的密码，在新建用户时，如果省略此部分，则用户密码为空。但是我们在配置文件中设置了'NO_AUTO_CREATE_USER'项，所以是无法创建空密码用户。会报错。
with grant option :让被授权的用户，可以将相同的权限授权给他人。
###授权 所有库的所有表的select权限给用户 test，登录密码是abc123,只能本地登录
mysql> grant select on *.* to 'test'@'localhost' identified by 'abc123';
### 退出数据库，使用用户 test ，密码abc123 本地登录mysql
[root@zwb_mysql ~]# mysql -utest -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.20 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
### 查看MySQL服务器的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| AAA |
| class |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
### 切换数据库
mysql> use AAA;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
#### 查看表
mysql> SHOW TABLES;
+---------------+
| Tables_in_AAA |
+---------------+
| tongxunlu |
+---------------+
1 row in set (0.00 sec)
### 查看表的基本结构
mysql> desc tongxunlu;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| 序号 | int(3) | NO | PRI | NULL | |
| 姓名 | varchar(20) | YES | | NULL | |
| 手机号 | int(15) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
### 插入数据，提示拒绝添加数据
mysql> insert into tongxunlu values(4,大圣，12345);
ERROR 1142 (42000): INSERT command denied to user 'test'@'localhost' for table 'tongxunlu'
mysql> select * from tongxunlu;
+--------+--------+------------+
| 序号 | 姓名 | 手机号 |
+--------+--------+------------+
| 1 | 张三 | 188888888 |
| 2 | 张二 | 1888888888 |
+--------+--------+------------+
2 rows in set (0.00 sec)
三、查看用户权限
①用户显示自身的访问权限
语法格式：show grants;
mysql> show grants; ### 以test普通用户的身份登录
+-------------------------------------------+
| Grants for test@localhost |
+-------------------------------------------+
| GRANT SELECT ON *.* TO 'test'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)②管理员查看已拥有授权用户权限
语法格式：show grants for '用户名'@'登录地点'
mysql> show grants for 'test'@'localhost'; ### 以root身份登录的
+-------------------------------------------+
| Grants for test@localhost |
+-------------------------------------------+
| GRANT SELECT ON *.* TO 'test'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)
四、撤销用户权限
语法格式：revoke 权限 on 库名.表名 from '用户名'@'登录地点' ;
mysql> revoke select on *.* from 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
## 查看结果
mysql> show grants for 'test'@'localhost';
+------------------------------------------+
| Grants for test@localhost |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
+------------------------------------------+
1 row in set (0.01 sec)
91
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
