授权
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)