MySQL 用户权限管理

最新推荐文章于 2024-04-15 11:24:03 发布

韩未零

最新推荐文章于 2024-04-15 11:24:03 发布

阅读量130

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 linux

本文链接：https://blog.csdn.net/qq_60271706/article/details/132192010

版权

MySQL 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

了解默认行为

当全新的MySQL数据库安装完毕后，系统就为我们默认地创建了几个用户：管理员用户root以及匿名用户。通过查看名为mysql的系统数据库中的user表，可以看到所有的用户名及其全局权限（Global Privileges）

select * from mysql.user;


root@TENNIS 16:50  mysql>select * from mysql.user\G;
*************************** 1. row ***************************
                  Host: localhost
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *5BD4108F02ABC6F45FF4E6FF0D9E22089027B4FE
      password_expired: N
 password_last_changed: 2023-04-18 12:48:09
     password_lifetime: NULL
        account_locked: N
*************************** 2. row ***************************
                  Host: localhost
                  User: mysql.session
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: Y
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
      password_expired: N
 password_last_changed: 2023-04-17 18:25:36
     password_lifetime: NULL
        account_locked: Y

有4个名为root的用户，它们分别被允许从主机localhost、mysql（安装MySQL时填写的主机名）、127.0.0.1、::1（ipv6地址127.0.0.1的缩写）连接到MySQL服务器。其中第1个root用户有密码，密码被加密后保存，其余3个用户没有密码（password列值为空字符串）。这4个root用户的权限完全相同。这意味着，root用户只能从本机连接到MySQL，不能远程连接。连接时可以提供密码也可以不提供密码。
最后两行中，user列的值为空字符串，表示匿名用户。它们被允许从本机连接，并且没有密码，也没有任何全局权限。允许匿名用户连接表示可以使用任何用户名（不管是否存在）连接到MySQL。

匿名用户有权看到两个数据库：一个名为information_schema的系统数据库和一个test数据库。它对test数据库拥有全部权限，可以在里面创建表，进行DML操作等。对information_schema系统数据库中的表拥有查询权限，但是不能执行DML操作。
但是之前查询mysql.user表时，我们发现匿名用户并没有任何全局权限，为何又能对test数据库拥有全部权限呢？原因在mysql.db表：select * from mysql.db;
从查询结果可见，当MySQL安装完毕后，默认将test数据库和以“test_”开头的数据库的所有权限授给了匿名用户（user列的值为空字符串）。

权限分类

按照权限的授予级别，可分成以下几类：
全局权限Global Privileges：它是管理权限，应用到服务器上的所有数据库上。要授予全局权限，使用 ON *.*的语法。 MySQL 把全局权限保存在 mysql.user 表中

数据库权限Database Privileges：应用到某个特定数据库的所有对象上。要授予数据库权限，使用 ON db_name.* 的语法。 MySQL 把数据库权限保存在 mysql.db 表中

表权限Table Privileges：应用到某个特定表的所有列上。要授予表权限，使用 ON db_name.tbl_name 的语法。 MySQL 把表权限保存在 mysql.tables_priv 表中

列权限Column Privileges：应用到某个特定表的单个列上。要授予列权限，必须在权限名称后面跟小括号，其中写上列名。 MySQL 把列权限保存在 mysql.columns_priv 表中

存储例程权限Stored Routine Privileges：应用到存储过程和函数上。 MySQL 把存储例程权限保存在 mysql.procs_priv表中

代理用户权限Proxy User Privileges：使一个用户成为另一个用户的代理。 MySQL 把代理用户权限保存在 mysql.proxies_priv表中

在grant和revoke语句中可用的权限如下表所示：

权限名称	含义及授予级别
ALL [PRIVILEGES]	授予某个特定级别的所有权限，除了 GRANT OPTION权限
ALTER	允许使用ALTER TABLE语句。Levels: Global, database, table
ALTER ROUTINE	允许修改或删除存储例程。Levels: Global, database, procedure
CREATE	允许创建数据库和表。Levels: Global, database, table
CREATE ROUTINE	允许创建存储例程。Levels: Global, database.
CREATE TABLESPACE	允许创建、修改、删除表空间和日志文件组。Level: Global.
CREATE TEMPORARY TABLES	允许创建临时表。Levels: Global, database.
CREATE USER	允许使用 CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES语句。Level: Global.
CREATE VIEW	允许创建和修改视图。Levels: Global, database, table.
SHUTDOWN	允许使用 mysqladmin shutdown。Level: Global.

权限检查顺序

Mysql数据库下的表user、db、host、tables_priv、columns_priv、procs_priv、proxies_priv共同构成授权表。

权限检查顺序如下图：

和其它数据库不同，MySQL使用用户名、密码和登录位置来验证用户

示例1：创建一个新用户demo，密码为demo，允许从本机登录
create user 'demo'@'localhost' identified by 'demo';

注意：用户名和主机名要分别用引号括起来(如果用户名没有特殊字符，主机名没有特殊字符和通配符%，也可以不加引号)。密码必须用引号括起来
如果不指定主机名，默认的主机名是百分号%，它代表任意主机。

注意：如果两个用户具有相同的用户名但是主机名不同，MySQL把他们看做不同的用户
如果创建的用户已经存在，则抛出异常。

当create user语句执行成功后，便在授权表mysql.user中写上一行。

一个新用户创建出来后，它可以连接到数据库，并有权看到两个数据库：一个名为information_schema的系统数据库和一个test数据库。
它对test数据库拥有全部权限，可以在里面创建表，进行DML操作等。对information_schema系统数据库中的表拥有查询权限，但是不能执行DML操作。
如果还想进行其它操作，必须给他授权。

修改用户名

语法：该语句不影响用户的密码

RENAME USER old_user  TO  new_user
    [, old_user  TO  new_user] ...

示例：将用户demo1和demo2分别改名为newdemo1和newdemo2

rename user
   'demo1'@'localhost‘  to newdemo1'@'127.0.0.1', 
   'demo3'@'%'  to  'newdemo2'@'127.0.0.1';

修改密码

SET PASSWORD [FOR user_name] = password_option;
password_option: {
    PASSWORD('auth_string')
   | ‘auth_string‘  --只适合5.7.6及其之后的版本
   | ‘hash_string‘  --只适合5.7.6之前的版本
}

‘auth_string’：未加密的明文字符串密码。PASSWORD函数将其加密后保存。 ‘hash_string’：已经加密的密码。

注意：从5.7.6 版本开始，该语句不提倡使用（ is deprecated ）。使用ALTER USER语句代替：

ALTER USER  user_name  IDENTIFIED BY  'auth_string';

示例：修改自己的密码

set password = password('123456');

如果你具有mysql数据库的update权限，就可以修改别人的密码

示例：修改demo用户的密码

set password for 'demo'@'%’ = password('demo');

给用户授权

注意：如果被授权的用户不存在，那么grant语句会自动创建新的账户，除非设置参数sql_mode包含“NO_AUTO_CREATE_USER” 。从5.7.7版本开始，默认的sql_mode就包含“NO_AUTO_CREATE_USER” （grant语句不再创建新的账户）

授予表权限和列权限

示例：授予用户jim可以查询players表

GRANT select 
  on players 
  to jim;

该语句创建了一个新的用户’jim’@’%’，它连接到数据库后，可以使用use tennis改变当前数据库，然后可以查询players表，而不管是谁创建了该表。

注意：在以jim用户登录前，首先删除授权表mysql.user中的所有本地匿名用户（user列值为空字符串的用户），否则以jim登录时， localhost的匿名用户账户将占先。结果是，jim将被视为匿名用户。原因是匿名用户账户的Host列值比‘jim'@'%'账户更具体，这样在user表排序顺序中排在前面

MySQL匹配用户名的过程如下：

当MySQL把user表中的行读入到内存中时，他会按照Host列的值进行排序：字面量主机名或者ip地址排在最前面，%主机名排在最后面。排序结果如下图：

当一个用户试图连接时，MySQL就遍历排好序的行，使用找到的第一个匹配的行。对于用户 ‘jeffrey’@’localhost’，有两行匹配它：一个是’’@’localhost’，另一个是‘jeffrey’@’%’。因为localhost行排在前面，因此jeffrey被当做匿名用户。

注意：权限都是单独授予的，对某个表具有update权限不会导致自动得到select权限。

授予数据库权限

表权限只针对某一张表，可针对整个数据库授权。

例：授予bob可以对tennis数据库中的所有表进行查询

grant  select
  on  tennis.*
  to  bob;

授予全局权限

应用到所有数据库上
示例：授予jim可以创建、修改、删除数据库以及对所有数据库中的所有表进行create、alter和drop

grant create,alter,drop
  on  *.*
  to  jim;

查看自己的权限：

root@TENNIS 17:05  mysql>show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

权限的传递

with grant option子句

通过在grant语句的最后使用该子句，就允许被授权的用户把得到的权限继续授给其它用户

示例：授予jim对teams表具有references权限，并允许他把权限授给其它用户

grant references
  on TEAMS
  to jim with grant option;

以jim连接，执行：

grant references
  on TEAMS
  to bob;

废除权限

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

废除用户在所有级别上的权限：

REVOKE  ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] …

常见问题

主机名使用localhost还是127.0.0.1？
使用localhost，是通过socket来连接；使用127.0.0.1，是通过tcp/ip来连接

将权限表中的匿名账户删除，不允许匿名登录

不要把mysql数据库的权限授给用户

不要随便授予super权限

用户具有usage权限意味着“没有权限”，它只表示该用户可以连接到数据库。无法废除该权限。

韩未零

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL 用户权限管理

当全新的MySQL数据库安装完毕后，系统就为我们默认地创建了几个用户：管理员用户root以及匿名用户。通过查看名为mysql的系统数据库中的user表，可以看到所有的用户名及其全局权限（Global Privileges）有4个名为root的用户，它们分别被允许从主机localhost、mysql（安装MySQL时填写的主机名）、127.0.0.1、::1（ipv6地址127.0.0.1的缩写）连接到MySQL服务器。
复制链接

扫一扫