MySQL用户权限
在MySQL数据库系统中，权限分配是按照 user表>db表>table_pric表>columns_priv表 的顺序来分配的。
即先判断user表中的权限是否为Y，如果user表中的值是Y，就不需要检查后面的表。否则依次检查db表、table_priv表和columns_priv表。
mysql 5.7.30
新建用户
默认 情况用户只有登录权限（USAGE），不能查看任何的库表
create user foo identified by "123456";
show grants for foo;
新建用户并指定密码
mysql> create user foo identified by "123456";
Query OK, 0 rows affected (0.00 sec)
mysql5.7 新建用户需要带密码 5.6不需要
mysql> show grants for foo;
+---------------------------------+
| Grants for foo@% |
+---------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'%' | # 只有 USAGE 权限，表示只有登录权限，看不到任何的库表
+---------------------------------+
1 row in set (0.00 sec)
#查看mysql.user表 foo对应的权限全为 NO
mysql> select * from mysql.user;
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| % | foo | 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 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | N | 2021-06-17 19:58:19 | NULL | N |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
8 rows in set (0.03 sec)
赋权-只读
GRANT SELECT ON hive.* TO 'foo'@'%';
GRANT SELECT,insert,update,delete,create,drop ON hive.* TO 'foo'@'%';
给某些用户 对 某些库 进行赋权
mysql> GRANT SELECT ON hive.* TO 'foo'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for foo;
+---------------------------------------+
| Grants for foo@% |
+---------------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'%' |
| GRANT SELECT ON `hive`.* TO 'foo'@'%' |
+---------------------------------------+
2 rows in set (0.00 sec)
GRANT 权限 ON 库.表 TO '用户'@'登录的地址';
此时的mysql.user下 关于 foo用户的权限 仍然都为 NO
从mysql.db 可以看到 foo用户 对那些库有权限
mysql> select * from mysql.db;
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| % | hive | foo | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
6 rows in set (0.00 sec)
mysql> GRANT SELECT,insert,update,delete,create,drop ON hive.* TO 'foo'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for foo;
+-----------------------------------------------------------------------------+
| Grants for foo@% |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `hive`.* TO 'foo'@'%' |
+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from mysql.db;
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| % | hive | foo | Y | Y | Y | Y | Y | Y | N | N | N | N | N | N | N | N | N | N | N | N | N |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
6 rows in set (0.00 sec)GRANT all ON hive.* TO 'foo'@'%';
mysql> GRANT all ON hive.* TO 'foo'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for foo;
+-----------------------------------------------+
| Grants for foo@% |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'%' |
| GRANT ALL PRIVILEGES ON `hive`.* TO 'foo'@'%' |
+-----------------------------------------------+
2 rows in set (0.00 sec)
#mysql.user 不变 都是NO
#mysql.db 可以看出 foo用户 对某个库 拥有很多权限
mysql> select * from mysql.db;
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| % | hive | foo | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
6 rows in set (0.00 sec)GRANT all ON *.* TO 'foo'@'%';
对某个用户 赋 所有库 所有权 登录地址不限
对某个用户
mysql> GRANT all ON *.* TO 'foo'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for foo;
+-----------------------------------------------+
| Grants for foo@% |
+-----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'foo'@'%' |
| GRANT ALL PRIVILEGES ON `hive`.* TO 'foo'@'%' |
+-----------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from mysql.user;
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| % | foo | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | 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 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | N | 2021-06-17 19:58:19 | NULL | N |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
8 rows in set (0.00 sec)
去除权限
revoke ALL ON `hive`.* from 'foo'@'%';
mysql> show grants for foo;
+-----------------------------------------------------------------------------+
| Grants for foo@% |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `hive`.* TO 'foo'@'%' |
+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql> revoke ALL ON `hive`.* from 'foo'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for foo;
+---------------------------------+
| Grants for foo@% |
+---------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'%' |
+---------------------------------+
1 row in set (0.00 sec)
用户锁定
ALTER USER 'foo'@'%' ACCOUNT LOCK;
ALTER USER 'foo'@'%' ACCOUNT UNLOCK;
mysql> ALTER USER 'foo'@'' ACCOUNT LOCK;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ALTER USER 'foo'@'localhost' ACCOUNT LOCK;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ALTER USER 'foo'@'%' ACCOUNT LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.user;
+-----------+---------------+---+-------------------+----------------+
| Host | User | d | password_lifetime | account_locked |
+-----------+---------------+---+-------------------+----------------+
| % | foo | | NULL | Y |
+-----------+---------------+---+-------------------+----------------+
8 rows in set (0.00 sec)
[root@]# mysql -ufoo -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3118 (HY000): Access denied for user 'foo'@'localhost'. Account is locked
解锁
ALTER USER 'foo'@'%' ACCOUNT unLOCK;密码过期
用户密码设置过期 但是可以登录 ，但是不能操作任何东西 ，提示要重设置密码
ALTER USER 'foo'@'localhost' PASSWORD EXPIRE;
set password=password('123456');
mysql> ALTER USER 'foo'@'localhost' PASSWORD EXPIRE;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ALTER USER 'foo'@'%' PASSWORD EXPIRE;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.user;
+-----------+---------------+-+------------------+--+-------------------+----------------+
| Host | User | | password_expired | | password_lifetime | account_locked |
+-----------+---------------+-+------------------+--+-------------------+----------------+
| % | foo | | Y | | NULL | N |
+-----------+---------------+-+------------------+--+-------------------+----------------+
8 rows in set (0.00 sec)
用户密码设置过期 但是可以登录 ，但是不能操作任何东西 ，提示要重设置密码
[root@ ~]# mysql -ufoo -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 78539
Server version: 5.7.30
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>
mysql> set password=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| sys |
+--------------------+
9 rows in set (0.00 sec)
从MySQL 5.7.4版开始，用户的密码过期时间这个特性得以改进，可以通过一个全局变量default_password_lifetime来设置密码过期的策略，此全局变量可以设置一个全局的自动密码过期策略。
用法示例：
可以在MySQL的配置文件中设置一个默认值，这会使得所有MySQL用户的密码过期时间都为90天，MySQL会从启动时开始计算时间。my.cnf配置如下：
[mysqld]
default_password_lifetime=90如果要设置密码永不过期的全局策略，可以这样：（注意这是默认值，配置文件中可以不声明）
[mysqld]
default_password_lifetime=0在MySQL运行时可以使用超级权限修改此配置：
mysql> SET GLOBAL default_password_lifetime = 90;
Query OK, 0 rows affected (0.00 sec)还可以使用ALTER USER命令为每个具体的用户账户单独设置特定的值，它会自动覆盖密码过期的全局策略。要注意ALTER USER语句的INTERVAL的单位是“天”。
设置单独用户过期时间
ALTER USER ‘testuser'@‘localhost' PASSWORD EXPIRE INTERVAL 30 DAY;禁用密码过期：
ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE NEVER;让用户使用默认的密码过期全局策略：
ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE DEFAULT;删除用户
drop user foo;
删除后 mysql.user mysql.db 关于该用户的所有都会被删除
DROP USER 'jack'@'localhost';
版权声明：本文为bck1453925668原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。