mysql认证_MySQL用户授权认证

最新推荐文章于 2024-04-15 22:39:18 发布

保研学长说

最新推荐文章于 2024-04-15 22:39:18 发布

阅读量488

点赞数

文章标签： mysql认证

本文链接：https://blog.csdn.net/weixin_42544663/article/details/113115597

版权

本文介绍了MySQL的用户认证过程，包括权限系统的作用、不同级别的权限控制以及如何创建、授权和回收用户权限。通过示例展示了如何查看和管理用户的权限信息，如在mysql.user、db、tables_priv等系统表中的数据。同时，讨论了如何设置和限制用户资源，如最大查询数、更新数和连接数，并探讨了密码策略和账户锁定机制。

摘要由CSDN通过智能技术生成

msyql用户认证过程

mysql权限系统介绍

权限系统作用是授予来自某一个主机的某一个用户可以查询，插入、修改、删除等数据库操作权限；

不能明确的拒绝某个用户的连接

权限控制(授权与回收)的执行语句包括create user,grant revoke

授权后都会在mysql内部的数据中(MySQL库)，并在数据库启动时将权限信息复制到内存里

MySQL用户的认证信息不光包括　，还要包含连接发起的主，如下

mysql> select user,host from mysql.user where user='wuxinglai';+-----------+--------------+

| user | host |

+-----------+--------------+

| wuxinglai | % |

| wuxinglai | 192.168.18.4 |

+-----------+--------------+

2 rows in set (0.00sec)

mysql> show grants for 'wuxinglai'@'%';+------------------------------------------------+

| Grants for wuxinglai@% |

+------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'wuxinglai'@'%' |

+------------------------------------------------+

1 row in set (0.00sec)

mysql> show grants for 'wuxinglai'@'192.168.18.4';+---------------------------------------------------+

| Grants for wuxinglai@192.168.18.4 |

+---------------------------------------------------+

| GRANT SELECT ON *.* TO 'wuxinglai'@'192.168.18.4' |

+---------------------------------------------------+

1 row in set (0.00 sec)

MySQL权限级别介绍

全局性的管理权限，作用与整个MySQL实例级别

数据库级别的权限，作用于指定的数据库或者所有的数据库上

数据库对象级别的权限，作用与指定的数据库对象上比如表、视图或者所有的的数据库的对象上

权限存储在mysql库的user,db,tables_priv,columns_priv,and procs_priv这几个系统表中，待mysql实例启动后就加载到内存中

查看mysql实例默认root用户的权限

mysql> show grants for root@'localhost';+---------------------------------------------------------------------+

| Grants for root@localhost |

+---------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |

+---------------------------------------------------------------------+

对比root用户在几个权限系统表中的数据(user，db,tables_priv ,columns_priv ,procs_priv )

mysql> select * from user where user='root' and host='localhost';+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+

+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+

| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *08A10AD11A51DC9BFDD81479A3DD3B46A4524FAD | N | 2017-09-13 11:14:00 | NULL | N |

1 row in set (0.00 sec)都是Y

mysql> select * from db where user='root' and host='localhost';

Empty set (0.01 sec)

mysql> select * from tables_priv where user='root' and host='localhost';

Empty set (0.00 sec)

mysql> select * from columns_priv where user='root' and host='localhost';

Empty set (0.00 sec)

mysql> select * from procs_priv where user='root' and host='localhost';

Empty set (0.00 sec)

查看mysql实例默认mysql.sys用户的权限

mysql> show grants for 'mysql.sys'@'localhost';+---------------------------------------------------------------+

| Grants for mysql.sys@localhost |

+---------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'mysql.sys'@'localhost' |

| GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost' |

| GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost' |

+---------------------------------------------------------------+

3 rows in set (0.00 sec)

mysql> select * from user where user='mysql.sys' and host='localhost';+-----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+

+-----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+

| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2017-09-13 11:01:53 | NULL | Y |

1 row in set (0.00 sec)都是N

mysql> select * from db where user='mysql.sys' and host='localhost';+-----------+-----+-----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+

+-----------+-----+-----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+

| localhost | sys | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y |

1 row in set (0.00 sec)

一条记录，在sys数据库上的Trigger_priv字段为’Y’,

mysql> select * from tables_priv where user='mysql.sys' and host='localhost';+-----------+-----+-----------+------------+----------------+---------------------+------------+-------------+

+-----------+-----+-----------+------------+----------------+---------------------+------------+-------------+

+-----------+-----+-----------+------------+----------------+---------------------+------------+-------------+

1 row in set (0.00 sec) //

一条记录，在sys数据库的sys_config表上有select权限

mysql> select * from columns_priv where user='mysql.user' and host='localhost';

Empty set (0.00sec)//空

mysql> select * from procs_priv where user='mysql.sys' and host='localhost';

Empty set (0.00sec)//空

MySQL权限详解

All/All Privileges权限代表全局或者全数据库对象级别的所有权限

Alter权限代表允许修改表结构的权限，但必须要求有create和insert权限配合。如果是rename表名，则要求有alter和drop原表，create和insert新表的权限

Alter routine权限代表允许修改或者删除存储过程、函数的权限

Create权限代表允许创建新的数据库和表的权限

Create routine权限代表允许创建存储过程、函数的权限

Create tablespace权限代表允许创建、修改、删除表空间和日志组的权限

Create temporary tables权限代表允许创建临时表的权限

Create user权限代表允许创建、修改、删除、重命名user的权限

Create view权限代表允许创建视图的权限

Delete权限代表允许删除行数据的权限

Drop权限代表允许删除数据库、表、视图的权限，包括truncate table命令

Event权限代表允许查询，创建，修改，删除MySQL事件

Execute权限代表允许执行存储过程和函数的权限

File权限代表允许在MySQL可以访问的目录进行读写磁盘文件操作，可使用的命令包括load data infile,select … into outfile,load file()函数

Grant option权限代表是否允许此用户授权或者收回给其他用户你给予的权限

Index权限代表是否允许创建和删除索引

Insert权限代表是否允许在表里插入数据，同时在执行analyze table,optimize table,repair table语句的时候也需要insert权限

Lock权限代表允许对拥有select权限的表进行锁定，以防止其他链接对此表的读或写

Process权限代表允许查看MySQL中的进程信息，比如执行show processlist, mysqladmin processlist, show engine等命令

Reference权限是在5.7.6版本之后引入，代表是否允许创建外键

Reload权限代表允许执行flush命令，指明重新加载权限表到系统内存中，refresh命令代表关闭和重新开启日志文件并刷新所有的表

Replication client权限代表允许执行show master status,show slave status,show binary logs命令

Replication slave权限代表允许slave主机通过此用户连接master以便建立主从复制关系

Select权限代表允许从表中查看数据，某些不查询表数据的select执行则不需要此权限，如Select1+1，Select PI()+2；而且select权限在执行update/delete语句中含有where条件的情况下也是需要的

Show databases权限代表通过执行show databases命令查看所有的数据库名

Show view权限代表通过执行show create view命令查看视图创建的语句

Process权限代表允许查看MySQL中的进程信息，比如执行show processlist, mysqladmin processlist, show engine等命令

Reference权限是在5.7.6版本之后引入，代表是否允许创建外键

Reload权限代表允许执行flush命令，指明重新加载权限表到系统内存中，refresh命令代表关闭和重新开启日志文件并刷新所有的表

Replication client权限代表允许执行show master status,show slave status,show binary logs命令

Replication slave权限代表允许slave主机通过此用户连接master以便建立主从复制关系

Show databases权限代表通过执行show databases命令查看所有的数据库名

Show view权限代表通过执行show create view命令查看视图创建的语句

Shutdown权限代表允许关闭数据库实例，执行语句包括mysqladmin shutdown

Super权限代表允许执行一系列数据库管理命令，包括kill强制关闭某个连接命令，change master to创建复制关系命令，以及create/alter/drop server等命令

Trigger权限代表允许创建，删除，执行，显示触发器的权限

Update权限代表允许修改表中的数据的权限

Usage权限是创建一个用户之后的默认权限，其本身代表”无权限”

mysql>create user test@localhost;

Query OK,0 rows affected (0.01sec)

mysql> show grants fortest@localhost+------------------------------------------+

| Grants for test@localhost |

+------------------------------------------+

| GRANT USAGE ON *.* TO 'test'@'localhost' |

+------------------------------------------+

1 row in set (0.00 sec)

系统权限表

权限存在mysql库的user,db,tables_priv,columns_priv,and procs_priv这几个系统表中

user表：存放用户账户信息以及全局级别(所有数据库)权限，决定了来自哪些主机的哪些用户可以访问的数据库实例，如果有全局权限则意味着对所有数据库都有此权限

db表:存放数据库级别的权限，决定了来那些的用户可以访问的数据库；

Tables_priv表：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个表

Columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段

Procs_priv表：存放存储过程和函数级别的权限

user和db权限表结构

user权限表中特殊字段

Plugin,password,authentication_string三个字段存放用户认证信息

Password_expired设置成’Y’则表明允许DBA将此用户的密码设置成过期而且过期后要求用户的使用者重置密码(alter user/set password重置密码)

Password_last_changed作为一个时间戳字段代表密码上次修改时间，执行create user/alter user/set password/grant等命令创建用户或修改用户密码时此数值自动更新

Password_lifetime代表从password_last_changed时间开始此密码过期的天数

Account_locked代表此用户被锁住，无法使用

Tables_priv和columns_priv权限表结构

Timestamp和grantor两个字段暂时没用

Tables_priv和columns_priv权限值

procs_priv权限表结构

Routine_type是枚举类型，代表是存储过程还是函数

Timestamp和grantor两个字段暂时没用

系统权限表字段长度限制表

权限认证中的大小写敏感问题

字段user,password,authencation_string,db,table_name大小写敏感

字段host,column_name,routine_name大小写不敏感

User用户大小写敏感

mysql> create user test@'localhost';

ERROR1396 (HY000): Operation CREATE USER failed for 'test'@'localhost'mysql> create user Test@'localhost';

Query OK,0 rows affected (0.00 sec)

Host主机名大小写不敏感

mysql> create user ttest@'Localhost';

Query OK,0 rows affected (0.00 sec)

查看用户权限

查看已经授权用户的权限

ysql> show grants for wuxinglai@'192.168.18.4';+---------------------------------------------------+

| Grants for wuxinglai@192.168.18.4 |

+---------------------------------------------------+

| GRANT SELECT ON *.* TO 'wuxinglai'@'192.168.18.4' |

+---------------------------------------------------+

1 row in set (0.00sec)

mysql> show grants for wuxinglai@'%';+------------------------------------------------+

| Grants for wuxinglai@% |

+------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'wuxinglai'@'%' |

+------------------------------------------------+

1 row in set (0.00 sec)

MySQL授权用户

MySQL的授权用户由两部分组成：用户名和登录主机名

表达用户的语法为‘user_name’@‘host_name’

单引号不是必须，但如果其中包含特殊字符则是必须的

‘’@‘localhost’代表匿名登录的用户

Host_name可以使主机名或者ipv4/ipv6的地址。Localhost代表本机，127.0.0.1代表ipv4的本机地址，::1代表ipv6的本机地址

Host_name字段允许使用%和_两个匹配字符，比如’%’代表所有主机，’%.mysql.com’代表来自mysql.com这个域名下的所有主机，‘192.168.1.%’代表所有来自192.168.1网段的主机

MySQL修改权限的生效

1.执行Grant,revoke,set password,rename user命令修改权限之后，MySQL会自动将修改后的权限信息同步加载到系统内存中 2.如果执行insert/update/delete操作上述的系统权限表之后，则必须再执行刷新权限命令才能同步到系统内存中，刷新权限命令包括：flush privileges/mysqladmin flush-privileges/mysqladmin reload 3.如果是修改tables和columns级别的权限，则客户端的下次操作新权限就会生效 4.如果是修改database级别的权限，则新权限在客户端执行use database命令后生效 5.如果是修改global级别的权限，则需要重新创建连接新权限才能生效6.--skip-grant-tables可以跳过所有系统权限表而允许所有用户登录，只在特殊情况下暂时使用

MySQL用户连接

[root@wxl ~]# mysql -h(host.ip) -u user -p(password) dbname

[root@wxl~]# mysql -S PATH/mysqld.sock

创建用户

有两种方式创建MySQL授权用户

执行create user/grant命令(推荐方式)

通过insert语句直接操作MySQL系统权限表

//先create 在grant

mysql> create user test1@'%' identified by 'test1';

Query OK,0 rows affected (0.00sec)

mysql> grant select,create on test.* to test1@'%';

Query OK,0 rows affected (0.00 sec)

//直接grant 方式

mysql> grant select,delete,update on test.* to test2@'192.168.18.0' identified by 'test2';

Query OK,0 rows affected, 1 warning (0.00 sec)

//查看用户权限

mysql> show grants for test1@'%';+-------------------------------------------------+

| Grants for test1@% |

+-------------------------------------------------+

| GRANT USAGE ON *.* TO 'test1'@'%' |

| GRANT SELECT, CREATE ON `test`.* TO 'test1'@'%' |

+-------------------------------------------------+

2 rows in set (0.00sec)

mysql> show grants for test2@'192.168.18.0';+--------------------------------------------------------------------+

| Grants for test2@192.168.18.0 |

+--------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'test2'@'192.168.18.0' |

| GRANT SELECT, UPDATE, DELETE ON `test`.* TO 'test2'@'192.168.18.0' |

+--------------------------------------------------------------------+

2 rows in set (0.00 sec)

回收MySQL用户权限

通过revoke命令回收用户权限

mysql> show grants for wuxinglai@'%';+------------------------------------------------+

| Grants for wuxinglai@% |

+------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'wuxinglai'@'%' |

+------------------------------------------------+

1 row in set (0.00sec)

mysql> revoke delete on *.* from 'wuxinglai'@'%';

Query OK,0 rows affected (0.00sec)

mysql> show grants for wuxinglai@'%';+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for wuxinglai@% |

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| GRANT SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'wuxinglai'@'%' |

1 row in set (0.00 sec)

删除用户

mysql> selectuser,host from mysql.user;+---------------+--------------+

| user | host |

+---------------+--------------+

| test1 | % |

| wuxinglai | % |

| test2 | 192.168.18.0 |

| wuxinglai | 192.168.18.4 |

| Test | localhost |

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

| test | localhost |

| ttest | localhost |

+---------------+--------------+

10 rows in set (0.00sec)

mysql> drop user test1@'%';

Query OK,0 rows affected (0.00sec)

mysql> selectuser,host from mysql.user;+---------------+--------------+

| user | host |

+---------------+--------------+

| wuxinglai | % |

| test2 | 192.168.18.0 |

| wuxinglai | 192.168.18.4 |

| Test | localhost |

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

| test | localhost |

| ttest | localhost |

+---------------+--------------+

9 rows in set (0.00 sec)

设置MySQL用户资源限制

mysql> CREATE USER 'test1'@'localhost' IDENTIFIED BY 'frank' WITH MAX_QUERIES_PER_HOUR 20 MAX_UPDATES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 5 MAX_USER_CONNECTIONS 2;

Query OK,0 rows affected (0.00sec)

资源限制解释：//MAX_QUERIES_PER_HOUR ：每小时发出的查询数//MAX_UPDATES_PER_HOUR :每小时发出的更新数//MAX_CONNECTIONS_PER_HOUR :每小时用户连接服务器次数//MAX_USER_CONNECTIONS 2;单一用户同时连接的数量

取消某项资源限制既是把原先的值修改成0 当针对某个用户的max_user_connections非0时，则忽略全局系统参数max_user_connections，反之则全局系统参数生效

设置mysql用户密码

执行create user创建用户和密码

CREATE USER'test'@'localhost' IDENTIFIED BY 'mypass';

修改用户密码的方式包括：

ALTER USER'test'@'localhost' IDENTIFIED BY 'mypass';

SET PASSWORD FOR'test'@'localhost' = PASSWORD('mypass');

GRANT USAGE ON*.* TO 'test'@'localhost' IDENTIFIED BY 'mypass';

mysqladmin-u user_name -h host_name password "new_password"修改本身用户密码的方式包括：

ALTER USER USER() IDENTIFIED BY'mypass';

SET PASSWORD= PASSWORD('mypass');

设置MySQL用户密码过期策略

设置系统参数default_password_lifetime作用于所有的用户账户

default_password_lifetime=180设置180天过期

default_password_lifetime=0 设置密码不过期

如果为每个用户设置了密码过期策略，则会覆盖上述系统参数

mysql> ALTER USER 'test'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

Query OK, 0 rows affected (0.00 sec)//密码有效期是90天

mysql> ALTER USER 'test'@'localhost'PASSWORD EXPIRE NEVER;

Query OK, 0 rows affected (0.00 sec //密码不过期

mysql> ALTER USER 'test'@'localhost' PASSWORD EXPIRE DEFAULT;

Query OK, 0 rows affected (0.00 sec)//默认过期策略

手动强制某个用户密码过期

mysql> ALTER USER 'test'@'localhost' PASSWORD EXPIRE;

Query OK, 0 rows affected (0.00 sec)

强制密码过期验证

[root@wxl ~]# /usr/local/mysql/bin/mysql -S /data/mysql/mysql3306/tmp/mysql.sock1 -utest -pmypass

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

mysql>mysql>use test;

ERROR1820(HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> set password="123456";

Query OK,0 rows affected (0.00sec)

mysql>use test;

ERROR1044 (42000): Access denied for user 'test'@'localhost' to database 'test

MySQL用户lock

通过执行create user/alter use命令带account lock/unlock字句设置lock状态

create user默认的用户是unlock状态的

mysql> create user test5@'localhost' identified by '123456' account lock;

Query OK, 0 rows affected (0.00 sec)

mysql> show create user test5@'localhost';

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| CREATE USER for test5@localhost |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| CREATE USER 'test5'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

Alter user语句默认不会修改用户的lock/unlock状态

mysql>alter user ‘test6’@localhost account lock;

Query OK,0 rows affected (0.00sec)

mysql> alter user 'test6'@localhost account unlock;

Query OK,0 rows affected (0.00 sec)

当客户端使用lock状态的用户登录MySQL时，会收到如此报错

[root@wxl ~]# /usr/local/mysql/bin/mysql -S /data/mysql/mysql3306/tmp/mysql.sock1 -utest5 -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR3118 (HY000): Access denied for user 'test5'@'localhost'. Account is locked.