mysql认证指南_MySQL授权认证

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

• 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 abc@localhost;

ERROR 1396 (HY000): Operation CREATE USER failed for 'abc'@'localhost'

mysql> create user Abc@localhost;

Query OK, 0 rows affected (0.01 sec)

• host主机名大小写不敏感。

mysql> create user abc@Localhost;

ERROR 1396 (HY000): Operation CREATE USER failed for 'abc'@'localhost'

5、查看用户权限信息。

• 查看已经授权给用户的权限信息。

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 |

• 查看用户的其他非授权信息。

mysql> show create user root@localhost;

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

| CREATE USER for root@localhost |

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

| CREATE USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$!R?h6

d9T&7E\"1J

g?kZt7zeCo8YGvF/w7XKbsI2lfW1Y/7qDJmYtkmXQu6g1' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |

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

1 row in set (0.00 sec)

6、MySQL授权用户

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

•  表达用户的语法为'user_name'@'host_name'

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

•  ''@'licalhost'代表匿名登录的用户

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

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

6、MySQL修改权限的生效。

•  执行grant,revoke,set password,rename user命令修改权限之后，MySQL会自动将修改后的权限信息同步加载到系统内存中。

•  如果执行insert/update/delete操作上述的系统权限表之后，则必须在执行刷新权限命令才能同步到系统内存中，刷新权限命令包括：flush privileges/mysqladmin flush-privileges/mysqladmin reload 。

•  如果是修改tables和columns级别的权限，则客户端的下次操作新权限就会生效。

•  如果是修改database级别的权限，则需要重新创建连接新权限才能生效。

• --skip-grant-tables可以跳过所有系统权限表而允许所有用户登录，只在特殊情况下暂时使用。

•  MySQL用户连接。

• shell> mysql --user=finley --password db_name

• shell> mysql -u finley -p db_name

• shell> mysql --user=finley --password=password db_name

• shell> mysql -u finley -ppassword db_name

7、创建MySQL用户

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

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

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

# 创建一个finley用户并设置some_pass密码 设置本机登录

• mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';

# 授权finley用户全部权限

• mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost' WITH GRANT OPTION;

# 创建一个finley用户并设置密码，所有主机可以登录

• mysql> CREATE USER 'finley'@'%' IDENTIFIED BY 'some_pass';

# 授权finley用户全部权限。

• mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'%‘ WITH GRANT OPTION;

# 创建admin用户

• mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin_pass';

# 授权admin所有权限

• mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

# 允许哪个库使用这个用户

• mysql> grant select(id) on test.temp to cdq@localhost;

# 查看admin用户权限

mysql> show grants for 'admin'@'localhost';

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

| Grants for admin@localhost |

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

| GRANT RELOAD, PROCESS ON *.* TO `admin`@`localhost` |

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

1 row in set (0.00 sec)

# 查看admin用户详细权限。

mysql> show create user 'admin'@'localhost'\G

*************************** 1. row ***************************

CREATE USER for admin@localhost: CREATE USER 'admin'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$:)ogQjpMuD}6^@H

fYUAi7CIanth/aprDiyhZydyJhAYuQrou5J0nFX4In5' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT

1 row in set (0.01 sec)

• mysql> CREATE USER 'custom'@'localhost' IDENTIFIED BY 'obscure';

• mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

-> ON bankaccount.*

-> TO 'custom'@'localhost';

• mysql> CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'obscure';

• mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

-> ON expenses.*

-> TO 'custom'@'host47.example.com';

• mysql> CREATE USER 'custom'@'%.example.com' IDENTIFIED BY 'obscure';

• mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

-> ON customer.*

-> TO 'custom'@'%.example.com';

• 通过revoke命令收回用户权限。

# 查看这个用户的权限

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.sys的select权限

mysql> revoke select on `sys`.`sys_config` from 'mysql.sys'@localhost;

Query OK, 0 rows affected (0.01 sec)

# 查看mysql.sys已经没有select权限。

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` |

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

2 rows in set (0.00 sec)

• 删除MySQL用户

• 通过执行drop user命令删除MySQL用户。

# 删除admin用户

mysql> drop user 'admin'@'localhost';

Query OK, 0 rows affected (0.02 sec)

8、设置MySQL用户资源限制。

• 通过设置全局变量max_user_connections可以限制所有用户在同一时间连MySQL实例的数量，但此参数无法对每个用户区别对待，所以MySQL提供了对每个用户的资源限制管理。

• max_queries_per_hour：一个用户在一个小时内可以执行查询的次数(基本包含所有语句)

• max_updates_per_hour：一个用户在一个小时内可以执行修改的次数(仅包含修改数据库或表的语句)

• max_connections_per_hour：一个用户在一个小时内可以连接MySQL的次数。

• max_user_connections：一个用户可以在同一时间连接MySQL实例的数量。

• 从5.0.3版本开始，对用户'user'@'%.example.com'的资源限制是指所有通过example.com域名主机连接user用户的连接，而不是分别指从host1.example.com和host2.example.com主机过来的连接。

• 通过执行create user/alter user设置/修改用户的资源限制。

# 创建一个用户并设置资源限制。

• mysql> CREATE USER 'zhang'@'localhost' IDENTIFIED BY 'frank'

-> WITH MAX_QUERIES_PER_HOUR 20 # 一个用户在一个小时内可以执行查询的20次

-> MAX_UPDATES_PER_HOUR 10 # 一个用户在一个小时内可以执行修改的次数

-> MAX_CONNECTIONS_PER_HOUR 5 # 一个小时内最大连接5次。

-> MAX_USER_CONNECTIONS 2; # 并行最大连接数2.

# 修改这个用户的资源限制为100。

• mysql> ALTER USER 'zhang'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;

# 取消某项资源限制即是把原来的值改成0。0代表没限制。

mysql> ALTER USER 'zhang'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;

# 查看这个用户做的什么限制。

mysql> show create user zhang@localhost;

# 查看定义资源限制的表。

mysql> select * from mysql.user where user='zhang';

• 当针对某个用户的max_user_connections非0时，则忽略全局系统参数max_user_connections，反之则全局系统参数生效。

• 执行create user创建用户和密码。

# 创建admin用户并授予admin密码。

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

Query OK, 0 rows affected (0.02 sec)

# 修改admin用户密码为 admin123。

mysql> alter user 'admin'@'localhost' identified by 'admin123';

Query OK, 0 rows affected (0.02 sec

# 使用mysqladmin修改密码。

mysqladmin -u user_name -h host_name password "new_password"

# 修改本身用户密码的方式包括。

• mysql> ALTER USER USER() IDENTIFIED BY 'mypass';

9、设置MySQL用户密码过期策略。

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

• default_password_lifetime=180 # 设置180天过期

• default_password_lifetime=0 # 设置密码不过期

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

• ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY; # 密码90天过期

• ALTER USER ‘jeffrey’@‘localhost’ PASSWORD EXPIRE NEVER; # 密码不过期

• ALTER USER ‘jeffrey’@‘localhost’ PASSWORD EXPIRE DEFAULT; # 默认过期策略

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

# 强制这个用户密码过期。

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;

# 查询就报错了。

mysql> SELECT 1;

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

# 修改当前用户的密码为mysql 。user() 为当前用户。

mysql> ALTER USER USER() IDENTIFIED BY 'mysql';

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT 1; | 1 |

10、MySQL用户lock。

• 通过执行create user/alter user命令中带account lock/unlock子句设置用户的lock状态。

• create user语句默认的用户是unlock状态。

• mysql> create user abc2@localhost identified by 'mysql' account lock;

Query OK, 0 rows affected (0.01 sec)

# 把这个用户lock住，不让登陆。(意思就是进行锁定，无法登录这个用户)

• mysql> alter user 'mysql.sys'@localhost account lock;

Query OK, 0 rows affected (0.00 sec)

# 解开这个用户。(解开这个用户，可以进行登录。)

• mysql> alter user 'mysql.sys'@localhost account unlock;

Query OK, 0 rows affected (0.00 sec)

# 进行查看 account_locked是否开始，Y代表开启，N代表关闭。

mysql> select user,account_locked from mysql.user where user='zhang';

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

| user | account_locked |

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

| zhang | N |

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

1 row in set (0.00 sec)

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

Access denied for user 'user_name'@'host_name'.

Account is locked.

11、企应用中的常规MySQL用户。

• 企业生产系统中MySQL用户的创建通常由DBA统一协调创建，而且按需创建。

• DBA通常直接使用root用户来管理数据库。

• 通常会创建指定业务数据库上的增删改查，临时表，执行存储过程的权限给应用程序来连接数据库。

# 创建一个用户并授予密码。

• create user zhang identified by 'mysql';

# 授予这个用户权限。

mysql> grant select,update,insert,delete,create temporary tables,execute on esn.* to zhang@'localhost';

# 查看这个用户的权限。

mysql> show grants for zhang@'localhost';

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

| grants for app_full@10.0.0.% | • +------------------------------------------------------------------------------------------------------------+ • | GRANT USAGE ON *.* TO 'app_full'@'10.0.0.%' |

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE ON `esn`.* TO 'app_full'@'10.0.0.%' |

• 通常也会创建指定业务数据库上的只读权限给特定应用程序或某些高级别人员来查询数据，防止数据被修改。

# 授予可查询权限。

mysql> grant select on esn.* to zhang@'localhost';

# 查看这个库中有多少用户以及权限。

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

# 查看具体一个用户的权限。

mysql> select * from mysql.user where user='zhang' \G