当前位置: 首页> 教育> 锐评 > 用户管理和授权

用户管理和授权

时间:2025/8/25 17:55:31来源:https://blog.csdn.net/yujinlong2002/article/details/141787786 浏览次数:0次

授权

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)
关键字:用户管理和授权

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com

责任编辑: