MySQL 用户管理和权限管理

在项目中,一个数据库有很多人需要使用,不能所有的人都使用相同的权限,如果人比较多,一人一个用户也很难管理。一般来说,会分超级管理员权限,管理员权限,读写权限,只读权限等,这样方便管理。当然,具体怎么管理权限根据实际情况来确定。无论如何,都需要创建多个用户来管理权限。root 是数据库的超级管理员用户,对于普通开发人员来说,权限太大了,如果不小心做了一些不可逆的操作,后果是非常严重的,并且还不容易查出责任人。所以 root 用户不会让开发人员使用,一般会由 DBA 或运维人员统一管理,如果没有 DBA,统一由超级管理员 root 来分配。

1. 查看所有用户

MySQL中所有的用户及权限信息都存储在默认数据库 mysql 的 user 表中。

进入 mysql 数据库,通过 desc user; 可以查看 user 表的结构。

use mysql; desc user;

MySQL 用户管理和权限管理

可以看到 user 中有40多个字段,字段非常多,只要关注主要字段就行了。其中的主要字段有:

host: 允许访问的主机地址,localhost 为本机,% 为任何主机。

user: 用户名。

authentication_string: 加密后的密码值。

使用 select * from user; 查看 user 表中当前有哪些用户。

select host,user,authentication_string from user;

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

在安装 MySQL 后,有三个默认的用户。

2. 创建用户

使用 create user ‘用户名’@’访问主机’ identified by ‘密码’; 创建用户。

create user 'admin'@'localhost' identified by 'admin';

mysql> create user 'admin'@'localhost' identified by 'admin';
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host     | user         | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| %         | root         | *90CA9738BDB1CD94BC376D2A339531B720EC0721 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | admin         | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)

创建用户后,查看用户,多了刚才创建的 admin,创建成功。

3、查看用户权限

使用 show grants for ‘用户名’@’访问主机’; 查看用户的权限。

mysql> show grants for 'admin'@'localhost';
+-------------------------------------------+
| Grants for admin@localhost               |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)

在创建用户的时候,如果没有指定权限,默认会赋予 USAGE 权限,这个权限很小,几乎为0,只有连接数据库和查询information_schema 数据库的权限。虽然. 表示所有数据库的所有表,但因为 USAGE 的限制,不能操作所有数据库。

退出 root 用户,登录到 admin 用户,只能看到 information_schema 数据库。

[root@localhost mysql]# mysql -u admin -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> show databases
   -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

4、给用户授权

创建 admin 用户,目的是创建一个管理员,所以要给 admin 授权。退出 admin ,重新登录 root 。

在授权时,常用的权限有 CREATE、ALTER、DROP、INSERT、UPDATE、DELETE、SELECT,ALL PRIVILEGES 表示所有权限。

通过 数据库.数据表 指定对哪个数据库的哪个表授权,. 表示所有数据库中的所有表。

通过 ‘用户名’@’访问主机’ 来表示用户可以从哪些主机登录, ‘%’ 表示可以从任何主机登录。

使用 grant 权限 on 数据库.数据表 to ‘用户名’@’访问主机’ identified by ‘密码’; 来给数据库用户授权。 # mysql8之前的版本 grant all privileges on *.* to 'admin'@'%' identified by 'Mysql!123';

#mysql8版本 mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

mysql> use mysql
Database changed
mysql> grant all privileges on *.* to 'admin'@'%' identified by 'admin';
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host     | user         | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| %         | root         | *90CA9738BDB1CD94BC376D2A339531B720EC0721 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | admin         | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| %         | admin         | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
+-----------+---------------+-------------------------------------------+
5 rows in set (0.00 sec)

给 admin 用户授权后,权限从 USAGE 变成了 ALL PRIVILEGES ,表示 admin 拥有了所有权限。

如果授权没有生效,记得刷新一下权限,使权限生效。

flush privileges;

再重新登陆到 admin 用户上,可以操作所有数据库了。

给用户授权的时候,必须要指定 ‘用户名’@’访问主机’ 来指定用户。如果 ‘访问主机’ 不相同,不是给用户授权,而是创建一个同名同密码的用户,这个用户与原用户可以登陆的主机不相同,权限不同。

mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host     | user         | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| %         | root         | *90CA9738BDB1CD94BC376D2A339531B720EC0721 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | admin         | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| %         | admin         | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
+-----------+---------------+-------------------------------------------+
5 rows in set (0.01 sec)

执行上面的语句后,user 表中有两个 admin 用户,用户名和密码都一样,但可以登陆的主机不一样。第一次创建的 admin 访问主机是 localhost,执行上面的语句时指定的访问主机是 % ,访问主机不一样,MySQL 会创建两个用户。虽然用户名密码相同,但这是两个不同的用户,两个用户的权限不一样。给两个用户指定不同的权限,在两个用户都有权限的主机登录时,局部用户的权限会覆盖全局用户的权限,当在 localhost 登录时,’admin’@’localhost’ 的权限会覆盖 ‘admin’@’%’ 的权限。

对于可以从任何主机登录的用户,在查看用户权限时,可以使用 show grants for 用户名; 来查看权限,指定主机的用户在查看权限时,要跟上访问主机才能查看权限。

mysql> show grants for admin;
+--------------------------------------------+
| Grants for admin@%                         |
+--------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' |
+--------------------------------------------+
1 row in set (0.00 sec)

5. 创建用户并授权(mysql8版本不支持此功能)

使用 grant 权限 on 数据库.数据表 to ‘用户名’@’访问主机’ identified by ‘密码’; 来创建一个用户并指定权限,与上面授权使用的语句相同。

grant create,select on *.* to 'creater'@'%' identified by 'baoyu1234';

mysql> grant create,select on *.* to 'creater'@'%' identified by 'baoyu1234';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show grants for creater;
+----------------------------------------------+
| Grants for creater@%                         |
+----------------------------------------------+
| GRANT SELECT, CREATE ON *.* TO 'creater'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)

创建了一个有读写权限的用户 creater,这个用户拥有所有数据库的 SELECT 和 CREATE 权限,可以从任何主机登录数据库。

6. 修改用户的权限

使用 grant 权限 on 数据库.数据表 to ‘用户名’@’访问主机’ identified by ‘密码’; 修改用户的权限,其实前面的授权就是修改权限。

grant all privileges on *.* to 'creater'@'%' identified by 'baoyu1234';

mysql> grant all privileges on *.* to 'creater'@'%' identified by 'baoyu1234';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show grants for creater;
+----------------------------------------------+
| Grants for creater@%                         |
+----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'creater'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)

7、删除用户

使用 drop user ‘用户名’@’访问主机’; 来删除用户。

drop user 'admin'@'localhost';

mysql> drop user 'admin'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host     | user         | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| %         | root         | *90CA9738BDB1CD94BC376D2A339531B720EC0721 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| %         | admin         | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| %         | creater       | *90CA9738BDB1CD94BC376D2A339531B720EC0721 |
+-----------+---------------+-------------------------------------------+
5 rows in set (0.00 sec)

执行删除操作后,user 表中不再有该用户。

8、修改用户名和访问主机

使用 rename user ‘用户名’@’访问主机’ to ‘新用户名’@’新访问主机’; 来修改用户名和用户的访问主机。

rename user 'creater'@'%' to 'create'@'localhost';

修改之后,creater 用户改名 create ,访问主机从 % 变成了 localhost 。

mysql> rename user 'creater'@'%' to 'create'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host     | user         | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| %         | root         | *90CA9738BDB1CD94BC376D2A339531B720EC0721 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| %         | admin         | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| localhost | create       | *90CA9738BDB1CD94BC376D2A339531B720EC0721 |
+-----------+---------------+-------------------------------------------+
5 rows in set (0.00 sec)

9、修改用户密码

# mysql8之前的版本
update user set password=password('新密码') where user='用户名';
flush privileges; --刷新MySQL的系统权限相关表

#mysql8版本
ALTER USER '用户名'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
flush privileges; --刷新MySQL的系统权限相关表

文章来源:https://www.cnaaa.net,转载请注明出处:https://www.cnaaa.net/archives/4305

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022年6月9日 下午3:33
下一篇 2022年6月9日 下午7:15

相关推荐

  • Centos7.6安装MySQL(超详细)

    1、查看是否已经安装Mysql rpm -qa | grep mysql 我已经安装过了 如果你查看出来有东西,可以使用下面命令将其删除 rpm -e 文件名 如果提示warning: waiting for transaction lock on /var/lib/rpm/.rpm.lock,则需要执行 2、下载官方Mysql包 wget -i -c ht…

    2022年6月9日
    19000
  • Centos7忘记root密码怎么办

    重启linux,进入系统的GRUB菜单界面,选择最下方的,按下小写字母e进入编辑界面 按下方向键,找到以字符串Linux16开头的行,光标移动到结尾,然后输入init=/bin/bash enforcing=0 代表登录系统后,加载bash解释器,然后关闭selinux 按下ctrl+x组合键以单用户模式启动Linux 输入如下命令,重新挂载根目录,进入可写…

    2022年6月11日
    8800
  • MySQL5.7主从同步、读写分离

    MySQL5.7主从同步、读写分离 安装MySQL5.7 下载rpm包 安装 启动mysql查看密码 登录mysql 主从同步 关闭每台服务器的防火墙和selinux 主从服务器时间同步 主服务器使用本地时钟源 修改配置文件 开启ntpd 从服务器同步主服务器时间 安装ntp工具 启动ntp和主服务器进行时间同步 主服务器mysql配置 修改配置文件 重启m…

    2022年7月31日
    10000
  • 解决Redis、MySQL缓存双写不一致问题

    redis、mysql双写缓存不一致: 但是在更新缓存方面,对于更新完数据库,是更新缓存呢,还是删除缓存。又或者是先删除缓存,再更新数据库,其实大家存在很大的争议。目前没有一篇全面的博客,对这几种方案进行解析。 正文 给缓存数据设置过期时间 先做一个说明,从理论上来说,给缓存设置过期时间,是保证最终一致性的解决方案。这种方案下,我们可以对存入缓存的数据设置过…

    2022年8月21日
    14300
  • 用户管理命令

    useradd 命令 创建普通用户 指定uid和组 创建用户,禁止登录且不创建家目录 使用 -M -s 参数 userdel命令 删除用户与相关文件 案例 passwd命令 passwd命令修改用户密码和过期时间等,root可以改普通用户,反之不可以 一条命令设置密码,企业常用 echo “密码” | passwd — stdin 用户名 –s…

    2022年6月11日
    8700

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

在线咨询: QQ交谈

邮件:712342017@qq.com

工作时间:周一至周五,8:30-17:30,节假日休息

关注微信