用户管理和授权

授权

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| day01db            |
| employees          |
| mysql              |
| mysql01            |
| mysql02            |
| performance_schema |
| resume             |
| rsd                |
| sys                |
| test               |
| user               |
+--------------------+
12 rows in set (0.00 sec)

mysql> select user,authentication_string,host from mysql.user;
+---------------+-------------------------------------------+-----------+
| user          | authentication_string                     | host      |
+---------------+-------------------------------------------+-----------+
| root          | *A0CC2113497D7CD66405477E353878D44203D40D | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
+---------------+-------------------------------------------+-----------+
3 rows in set (0.00 sec)

创建和删除用户

用户管理

-- 创建用户

mysql> create user along@127.0.0.1 identified by 'root123';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,authentication_string,host from mysql.user;
+---------------+-------------------------------------------+-----------+
| user          | authentication_string                     | host      |
+---------------+-------------------------------------------+-----------+
| root          | *A0CC2113497D7CD66405477E353878D44203D40D | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| along         | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | 127.0.0.1 |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| yujinlong     | %         |
| along         | 127.0.0.1 |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

mysql> create user "yujinlong1"@"%" identified by "20020115";
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| yujinlong     | %         |
| yujinlong1    | %         |
| along         | 127.0.0.1 |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
6 rows in set (0.00 sec)

-- 删除用户

mysql> drop user "yujinlong1"@"%";
Query OK, 0 rows affected (0.00 sec)

-- 修改用户名	
mysql> rename user 'yujinlong'@'%' to 'along1'@'%';
Query OK, 0 rows affected (0.00 sec)

-- 修改密码
mysql> set password for 'along1'@'%' = password('20020115');
Query OK, 0 rows affected, 1 warning (0.00 sec)

授权管理

创建好用户以后可以对用户进行授权

• 授权

grant 权限 on 数据库.表 to '用户'@'ip地址'

mysql> grant all privileges on *.* TO 'along'@'127.0.0.1'; -- 用户along拥有所有数据库的所有表的权限
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on user.info to 'along1'@'%'; -- 用户along1拥有user.info表中所有权限
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on user.users to 'along1'@'%'; 		-- 用户along1拥有user.users的查看权限
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

-- 登录along账号后查询数据库 这个时候我们只能看到他具有权限的数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| user               |
+--------------------+
2 rows in set (0.00 sec)

-- 登录along账号后查询数据库中的数据表 这个时候我们只能看到他具有权限的数据表
mysql> show tables;
+----------------+
| Tables_in_user |
+----------------+
| info           |
| users          |
+----------------+
2 rows in set (0.00 sec)

• 查看授权

mysql> show grants for 'along1'@'%';
+-------------------------------------------------------+
| Grants for along1@%                                   |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO 'along1'@'%'                    |
| GRANT ALL PRIVILEGES ON `user`.`info` TO 'along1'@'%' |
| GRANT SELECT ON `user`.`users` TO 'along1'@'%'        |
+-------------------------------------------------------+
3 rows in set (0.00 sec)

• 取消授权

revoke 权限 ON 数据库.表 from  '用户名'@'ip地址';

mysql> revoke USAGE ON *.* from  'along1'@'%';
Query OK, 0 rows affected (0.00 sec)