时间：2021-07-01 10:21:17 帮助过：15人阅读
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
MySQL授权认证
标签：min 访问 lte 开始 shell file temp sage 删除表