mysql8学习笔记4--mysql授权认证
mysql> show grants for 'root'@'%'; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@% | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, 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, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
select * from user where user='root' and host='%';##都是’Y’ select * from db where user='root' and host='%';##无记录 select * from tables_priv where user='root' and host='%';##无记录 select * from columns_priv where user='root' and host='%';##无记录 select * from procs_priv where user='root' and host='%';##无记录
mysql.user里存放的是全局性的管理权限
mysql.db里存放的是数据库级别的权限
mysql.tables_priv里存放的是数据库的对象级别的权限
mysql> select * from db where user='mysql.sys' and host='localhost'; +-----------+-----+-----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | 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 | +-----------+-----+-----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | 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)
mysql> show grants for 'mysql.sys'@'localhost'; +---------------------------------------------------------------+ | Grants for mysql.sys@localhost | +---------------------------------------------------------------+ | GRANT USAGE ON *.* TO `mysql.sys`@`localhost` |##usage是无权限的同义词,所以在user表可以查看到这个用户的权限基本是N | GRANT TRIGGER ON `sys`.* TO `mysql.sys`@`localhost` |##有trigger权限,可以看到db表里仅有trigger_priv是Y | GRANT SELECT ON `sys`.`sys_config` TO `mysql.sys`@`localhost` |##有select权限,可以看到tables_priv 里tables_priv的值是select +---------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> select * from user where user='mysql.sys'; +-----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+ | 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 | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | +-----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+ | 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 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2020-06-27 03:12:11 | NULL | Y | N | N | NULL | NULL | NULL | +-----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+ 1 row in set (0.00 sec) mysql> select * from db where user='mysql.sys'; +-----------+-----+-----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | 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 | +-----------+-----+-----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | 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) mysql> select * from tables_priv where user='mysql.sys'; +-----------+-----+-----------+------------+----------------+---------------------+------------+-------------+ | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv | +-----------+-----+-----------+------------+----------------+---------------------+------------+-------------+ | localhost | sys | mysql.sys | sys_config | root@localhost | 2020-06-27 03:12:11 | Select | | +-----------+-----+-----------+------------+----------------+---------------------+------------+-------------+ 1 row in set (0.00 sec) mysql>
MySQL权限详解
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) mysql> use company; ERROR 1044 (42000): Access denied for user 'njh'@'localhost' to database 'company' mysql> select user(); +---------------+ | user() | +---------------+ | njh@localhost | +---------------+ 1 row in set (0.00 sec)
mysql> use mysql;
ERROR 1044 (42000): Access denied for user 'njh'@'localhost' to database 'mysql'
mysql>
使用root用户给njh授权
mysql> show grants for 'njh'@'localhost'; +-----------------------------------------+ | Grants for njh@localhost | +-----------------------------------------+ | GRANT USAGE ON *.* TO `njh`@`localhost` | #*.*第一个星号表示所有数据库 ,第二个星号表示数据库对象 +-----------------------------------------+ 1 row in set (0.00 sec) mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> grant select on *.* to 'njh'@'localhost'; Query OK, 0 rows affected (0.15 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'njh'@'localhost'; +------------------------------------------+ | Grants for njh@localhost | +------------------------------------------+ | GRANT SELECT ON *.* TO `njh`@`localhost` | +------------------------------------------+ 1 row in set (0.00 sec) mysql>
然后重新用njh进入命令行,发现mysql和company库的表都能查到,因为select授权是全局性的管理权限
mysql> show databases; +--------------------+ | Database | +--------------------+ | company | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use company; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-------------------+ | Tables_in_company | +-------------------+ | customers | | customers2 | | orderitems | | orders | | productnotes | | products | | vendors | +-------------------+ 7 rows in set (0.01 sec) mysql> select * from customers ; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 5 rows in set (0.00 sec) mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from 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 | +-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | localhost | performance_schema | mysql.session | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | localhost | sys | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | +-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ 2 rows in set (0.00 sec) mysql>
但是执行insert语句时会提示没权限。
mysql> insert into customers value(2,'b'); ERROR 1142 (42000): INSERT command denied to user 'njh'@'localhost' for table 'customers' mysql>
接着给njh用户授权能给company库的表插入数据,查看mysql.user 和 mysql.db可以发现select权限是存放在user表,insert是存放在db表,因为授权select时,是所有数据库,而授权insert时,只是company库
mysql> grant insert on company.* to 'njh'@'localhost'; Query OK, 0 rows affected (0.02 sec) mysql> show grants for 'njh'@'localhost'; +--------------------------------------------------+ | Grants for njh@localhost | +--------------------------------------------------+ | GRANT SELECT ON *.* TO `njh`@`localhost` | | GRANT INSERT ON `company`.* TO `njh`@`localhost` | +--------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select select_priv,insert_priv from user where user='njh'; +-------------+-------------+ | select_priv | insert_priv | +-------------+-------------+ | Y | N | +-------------+-------------+ 1 row in set (0.00 sec) mysql> select select_priv,insert_priv from db where user='njh'; +-------------+-------------+ | select_priv | insert_priv | +-------------+-------------+ | N | Y | +-------------+-------------+ 1 row in set (0.00 sec) mysql>
重新用njh进入命令行,这次没提示权限了
mysql> insert into customers value(2,'b'); ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql>
收回njh用户的insert和select权限,并再授予company库下的select权限,可以看到user表的select_priv变成N,db表的select_priv变成Y。
mysql> revoke select on *.* from 'njh'@'loaclhost'; ERROR 1141 (42000): There is no such grant defined for user 'njh' on host 'loaclhost' mysql> revoke select on *.* from 'njh'@'localhost'; Query OK, 0 rows affected (0.09 sec) mysql> grant select on company.* to 'njh'@'localhost'; Query OK, 0 rows affected (0.09 sec) mysql> revoke insert on company.* from 'njh'@'localhost'; Query OK, 0 rows affected (0.10 sec) mysql> select select_priv,insert_priv from user where user='njh'; +-------------+-------------+ | select_priv | insert_priv | +-------------+-------------+ | N | N | +-------------+-------------+ 1 row in set (0.00 sec) mysql> select select_priv,insert_priv from db where user='njh'; +-------------+-------------+ | select_priv | insert_priv | +-------------+-------------+ | Y | N | +-------------+-------------+ 1 row in set (0.00 sec) mysql>
验证下:
mysql> select user(); +---------------+ | user() | +---------------+ | njh@localhost | +---------------+ 1 row in set (0.00 sec) mysql> select * from mysql.user; ERROR 1142 (42000): SELECT command denied to user 'njh'@'localhost' for table 'user' mysql> select * from company.customers; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 5 rows in set (0.00 sec) mysql> insert into company.customers value(1,2); ERROR 1142 (42000): INSERT command denied to user 'njh'@'localhost' for table 'customers' mysql>
在数据库级别上授权njh用户对company下所有表有select权限,然后再数据库对象级别授权njh用户对company.customers有select权限
mysql> grant select on company.customers to 'njh'@'localhost'; Query OK, 0 rows affected (0.11 sec) mysql> select * from tables_priv t; +-----------+---------+---------------+------------+----------------------+---------------------+------------+-------------+ | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv | +-----------+---------+---------------+------------+----------------------+---------------------+------------+-------------+ | localhost | company | njh | customers | root@localhost | 0000-00-00 00:00:00 | Select | | | localhost | mysql | mysql.session | user | boot@connecting host | 0000-00-00 00:00:00 | Select | | | localhost | sys | mysql.sys | sys_config | root@localhost | 2020-06-27 03:12:11 | Select | | +-----------+---------+---------------+------------+----------------------+---------------------+------------+-------------+ 3 rows in set (0.00 sec) mysql> select select_priv,insert_priv from db where user='njh'; +-------------+-------------+ | select_priv | insert_priv | +-------------+-------------+ | Y | N | +-------------+-------------+ 1 row in set (0.00 sec) mysql>
测试发现njh用户可以对除customers外的表select。
mysql> select * from customers; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 5 rows in set (0.00 sec) mysql> select * from orderitems ; +-----------+------------+---------+----------+------------+ | order_num | order_item | prod_id | quantity | item_price | +-----------+------------+---------+----------+------------+ | 20005 | 1 | ANV01 | 10 | 5.99 | | 20005 | 2 | ANV02 | 3 | 9.99 | | 20005 | 3 | TNT2 | 5 | 10.00 | | 20005 | 4 | FB | 1 | 10.00 | | 20006 | 1 | JP2000 | 1 | 55.00 | | 20007 | 1 | TNT2 | 100 | 10.00 | | 20008 | 1 | FC | 50 | 2.50 | | 20009 | 1 | FB | 1 | 10.00 | | 20009 | 2 | OL1 | 1 | 8.99 | | 20009 | 3 | SLING | 1 | 4.49 | | 20009 | 4 | ANV03 | 1 | 14.99 | +-----------+------------+---------+----------+------------+ 11 rows in set (0.00 sec)
给njh用户授予company.orderitem表授予insert权限:
mysql> grant insert on company.orderitems to 'njh'@'localhost'; Query OK, 0 rows affected (0.05 sec) mysql> select select_priv,insert_priv from db where user='njh'; +-------------+-------------+ | select_priv | insert_priv | +-------------+-------------+ | Y | N | +-------------+-------------+ 1 row in set (0.00 sec) mysql> select select_priv,insert_priv from user where user='njh'; +-------------+-------------+ | select_priv | insert_priv | +-------------+-------------+ | N | N | +-------------+-------------+ 1 row in set (0.00 sec) mysql> select * from tables_priv t where user='njh'; +-----------+---------+------+------------+----------------+---------------------+------------+-------------+ | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv | +-----------+---------+------+------------+----------------+---------------------+------------+-------------+ | localhost | company | njh | customers | root@localhost | 0000-00-00 00:00:00 | Select | | | localhost | company | njh | orderitems | root@localhost | 0000-00-00 00:00:00 | Insert | | +-----------+---------+------+------------+----------------+---------------------+------------+-------------+ 2 rows in set (0.00 sec) mysql>
测试下,可以insert成功:
mysql> use company; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into customers value('1','2'); ERROR 1142 (42000): INSERT command denied to user 'njh'@'localhost' for table 'customers' mysql> insert into orderitems value('20009',5,'ANV03',1,11.99); Query OK, 1 row affected (0.14 sec) mysql>
mysql的权限控制,可以到字段,只需要在授权时设置便可以:
注:要注意原有是否有全局性的管理权限和数据库级别的权限,有的话需要revoke
mysql> show grants for 'njh'@'localhost'; +-------------------------------------------------------------+ | Grants for njh@localhost | +-------------------------------------------------------------+ | GRANT USAGE ON *.* TO `njh`@`localhost` | | GRANT INSERT ON `company`.`orderitems` TO `njh`@`localhost` | +-------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> grant select(cust_name) on company.customers to 'njh'@'localhost'; Query OK, 0 rows affected (0.07 sec) mysql> show grants for 'njh'@'localhost'; +--------------------------------------------------------------------------+ | Grants for njh@localhost | +--------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `njh`@`localhost` | | GRANT SELECT (`cust_name`) ON `company`.`customers` TO `njh`@`localhost` | | GRANT INSERT ON `company`.`orderitems` TO `njh`@`localhost` | +--------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql>
测试下,生效了:
mysql> select * from company.customers ; ERROR 1143 (42000): SELECT command denied to user 'njh'@'localhost' for column 'cust_id' in table 'customers' mysql> select cust_name from company.customers; +----------------+ | cust_name | +----------------+ | Coyote Inc. | | Mouse House | | Wascals | | Yosemite Place | | E Fudd | +----------------+ 5 rows in set (0.00 sec) mysql>
系统权限表
![](https://img2020.cnblogs.com/blog/947742/202006/947742-20200628181552824-2081533225.png)
Tables_priv和columns_priv权限表结构
Timestamp和grantor两个字段暂时没用
![](https://img2020.cnblogs.com/blog/947742/202006/947742-20200628183026665-1497544797.png)
procs_priv权限表结构
![](https://img2020.cnblogs.com/blog/947742/202006/947742-20200628183209719-432785784.png)
系统权限表字段长度限制表
![](https://img2020.cnblogs.com/blog/947742/202006/947742-20200628183242478-1341123652.png)
权限认证中的大小写敏感问题
![](https://img2020.cnblogs.com/blog/947742/202006/947742-20200628183822691-798921183.png)
MySQL修改权限的生效
mysql> create user 'user_20200701'@'localhost' identified by 'mypass'; Query OK, 0 rows affected (0.21 sec) mysql>
测试下登录:
[root@localhost ~]# mysql -u user_20200701 --password=mypass ##这种输入密码方式不推荐,因为可以通过history查看历史命令,从而获取到密码。 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 46 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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> exit Bye [root@localhost ~]# mysql -u user_20200701 --password=mypass2 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'user_20200701'@'localhost' (using password: YES) [root@localhost ~]#
修改密码方式一:
mysql> alter user 'user_20200701'@'localhost' identified by 'mypass2'; Query OK, 0 rows affected (0.16 sec) mysql> [root@localhost ~]# mysql -u user_20200701 --password=mypass2 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 48 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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>
修改密码方式二:
[root@localhost ~]# mysqladmin -u user_20200701 -h localhost -p password 'mypass9'# mypass9是newpassword Enter password: #这里输入oldpassword,输入错误,就验证失败 mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'user_20200701'@'localhost' (using password: YES)' [root@localhost ~]# mysqladmin -u user_20200701 -h localhost -p password 'mypass9' Enter password: #输入正确,就返回下面两行警告信息 mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety. [root@localhost ~]# mysql -u user_20200701 --password='mypass9'#测试下新密码能登录成功。 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 39 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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>
![](https://img2020.cnblogs.com/blog/947742/202006/947742-20200628184218511-2059475946.png)
回收MySQL用户权限
mysql> alter user 'user_20200701'@'localhost' password expire interval 90 day; Query OK, 0 rows affected (0.01 sec) mysql> select user,host,password_lifetime from mysql.user ; +------------------+-----------+-------------------+ | user | host | password_lifetime | +------------------+-----------+-------------------+ | root | % | 0 | | mysql.infoschema | localhost | NULL | | mysql.session | localhost | NULL | | mysql.sys | localhost | NULL | | njh | localhost | NULL | | user_20200701 | localhost | 90 | +------------------+-----------+-------------------+ 6 rows in set (0.00 sec)
测试下手动强制密码过期:
其实就是把password_expired的值改成Y
mysql> select user,host,password_lifetime,password_expired from mysql.user; +------------------+-----------+-------------------+------------------+ | user | host | password_lifetime | password_expired | +------------------+-----------+-------------------+------------------+ | root | % | 0 | N | | mysql.infoschema | localhost | NULL | N | | mysql.session | localhost | NULL | N | | mysql.sys | localhost | NULL | N | | njh | localhost | NULL | N | | user_20200701 | localhost | 90 | N | +------------------+-----------+-------------------+------------------+ 6 rows in set (0.00 sec) mysql> alter user 'user_20200701'@'localhost' password expire; Query OK, 0 rows affected (0.38 sec) mysql> select user,host,password_lifetime,password_expired from mysql.user; +------------------+-----------+-------------------+------------------+ | user | host | password_lifetime | password_expired | +------------------+-----------+-------------------+------------------+ | root | % | 0 | N | | mysql.infoschema | localhost | NULL | N | | mysql.session | localhost | NULL | N | | mysql.sys | localhost | NULL | N | | njh | localhost | NULL | N | | user_20200701 | localhost | 90 | Y | +------------------+-----------+-------------------+------------------+ 6 rows in set (0.00 sec) [root@localhost ~]# mysql -u user_20200701 --password='mypass10' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. 。。。。。。 mysql> show databases; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> alter user user() identified by 'mypass11'; Query OK, 0 rows affected (0.02 sec) mysql> exit Bye [root@localhost ~]# mysql -u user_20200701 --password='mypass11'。。。。。。 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) mysql> mysql> select user,host,password_lifetime,password_expired from mysql.user; +------------------+-----------+-------------------+------------------+ | user | host | password_lifetime | password_expired | +------------------+-----------+-------------------+------------------+ | root | % | 0 | N | | mysql.infoschema | localhost | NULL | N | | mysql.session | localhost | NULL | N | | mysql.sys | localhost | NULL | N | | njh | localhost | NULL | N | | user_20200701 | localhost | 90 | N | +------------------+-----------+-------------------+------------------+ 6 rows in set (0.00 sec) mysql>
mysql> show grants for 'app2'@'localhost';#查看app2没有权限。 +------------------------------------------+ | Grants for app2@localhost | +------------------------------------------+ | GRANT USAGE ON *.* TO `app2`@`localhost` | +------------------------------------------+ 1 row in set (0.01 sec) mysql> show grants for 'app1'@'localhost';#查看app1没有权限。 +------------------------------------------+ | Grants for app1@localhost | +------------------------------------------+ | GRANT USAGE ON *.* TO `app1`@`localhost` | +------------------------------------------+ 1 row in set (0.00 sec) mysql> create role 'app_readonly'@'localhost';#创建角色 app_readonly Query OK, 0 rows affected (0.04 sec) mysql> show grants for 'app_readonly'@'localhost';#查看app_readonly权限 +--------------------------------------------------+ | Grants for app_readonly@localhost | +--------------------------------------------------+ | GRANT USAGE ON *.* TO `app_readonly`@`localhost` | +--------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from mysql.role_edges;#这个表记录角色与哪些用户关联。 Empty set (0.00 sec) mysql> select * from mysql.default_roles;#这个表记录哪些角色的用户激活了。 Empty set (0.00 sec) mysql> grant select on *.* to 'app_readonly'@'localhost';#给角色授予所有库的查询权限。 Query OK, 0 rows affected (0.29 sec) mysql> show grants for 'app_readonly'@'localhost'; +---------------------------------------------------+ | Grants for app_readonly@localhost | +---------------------------------------------------+ | GRANT SELECT ON *.* TO `app_readonly`@`localhost` | +---------------------------------------------------+ 1 row in set (0.00 sec)
给role指定用户
mysql> grant 'app_readonly'@'localhost' to 'app1'@'localhost'; Query OK, 0 rows affected (0.06 sec) mysql> select * from mysql.role_edges;#可以查到role与user对应关系 +-----------+--------------+-----------+---------+-------------------+ | FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION | +-----------+--------------+-----------+---------+-------------------+ | localhost | app_readonly | localhost | app1 | N | +-----------+--------------+-----------+---------+-------------------+ 1 row in set (0.00 sec) mysql> select * from mysql.default_roles;#不过没激活 Empty set (0.01 sec)
没激活的情况下,测试权限有没生效
[root@localhost ~]# mysql -u app1 --password='apppass1' mysql: [Warning] Using a password on the command line interface can be insecure. 。。。。。。 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) mysql> show grants for user(); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '()' at line 1 mysql> show grants for 'app1'@'localhsot'; ERROR 1141 (42000): There is no such grant defined for user 'app1' on host 'localhsot' mysql> show grants for 'app1'@'localhost'; +--------------------------------------------------------+ | Grants for app1@localhost | +--------------------------------------------------------+ | GRANT USAGE ON *.* TO `app1`@`localhost` | | GRANT `app_readonly`@`localhost` TO `app1`@`localhost` | +--------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show grants for 'app1'@'localhost' using app_readonly; ERROR 3530 (HY000): `app_readonly`@`%` is not granted to `app1`@`localhost` mysql> show grants for 'app1'@'localhost' using app_readonly@'localhost'; #通过加using role可以查到权限。 +--------------------------------------------------------+ | Grants for app1@localhost | +--------------------------------------------------------+ | GRANT SELECT ON *.* TO `app1`@`localhost` | | GRANT `app_readonly`@`localhost` TO `app1`@`localhost` | +--------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select host,user from mysql.user; ERROR 1142 (42000): SELECT command denied to user 'app1'@'localhost' for table 'user' mysql>
激活role
mysql> set default role 'app_readonly'@'localhost' to 'app1'@'localhost'; Query OK, 0 rows affected (0.01 sec) mysql> select * from mysql.role_edges; +-----------+--------------+-----------+---------+-------------------+ | FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION | +-----------+--------------+-----------+---------+-------------------+ | localhost | app_readonly | localhost | app1 | N | +-----------+--------------+-----------+---------+-------------------+ 1 row in set (0.00 sec) mysql> select * from mysql.default_roles; +-----------+------+-------------------+-------------------+ | HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER | +-----------+------+-------------------+-------------------+ | localhost | app1 | localhost | app_readonly | +-----------+------+-------------------+-------------------+ 1 row in set (0.00 sec) mysql>
测试成功:
[root@localhost ~]# mysql -u app1 --password='apppass1' mysql: [Warning] Using a password on the command line interface can be insecure. 。。。。。。 mysql> show databases; +--------------------+ | Database | +--------------------+ | company | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> select host,user from mysql.user ; +-----------+------------------+ | host | user | +-----------+------------------+ | % | root | | localhost | app1 | | localhost | app2 | | localhost | app_readonly | | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | njh | | localhost | user_20200701 | +-----------+------------------+ 9 rows in set (0.00 sec) mysql>
收回给用户赋予的角色:
mysql> set default role 'app_readonly'@'localhost' to 'app2'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> select * from mysql.default_roles; +-----------+------+-------------------+-------------------+ | HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER | +-----------+------+-------------------+-------------------+ | localhost | app1 | localhost | app_readonly | | localhost | app2 | localhost | app_readonly | +-----------+------+-------------------+-------------------+ 2 rows in set (0.00 sec) mysql> mysql> mysql> revoke 'app_readonly'@'localhost' from 'app1'@'localhsot'; ERROR 3523 (HY000): Unknown authorization ID `app1`@`localhsot` mysql> revoke 'app_readonly'@'localhost' from 'app1'@'localhost'; #收回给app1赋予的角色。 Query OK, 0 rows affected (0.36 sec) mysql> select * from mysql.default_roles; +-----------+------+-------------------+-------------------+ | HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER | +-----------+------+-------------------+-------------------+ | localhost | app2 | localhost | app_readonly | +-----------+------+-------------------+-------------------+ 1 row in set (0.00 sec) mysql> select * from mysql.role_edges; +-----------+--------------+-----------+---------+-------------------+ | FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION | +-----------+--------------+-----------+---------+-------------------+ | localhost | app_readonly | localhost | app2 | N | +-----------+--------------+-----------+---------+-------------------+ 1 row in set (0.00 sec) mysql> show grants for 'app1'@'localhost'; +------------------------------------------+ | Grants for app1@localhost | +------------------------------------------+ | GRANT USAGE ON *.* TO `app1`@`localhost` | +------------------------------------------+ 1 row in set (0.00 sec) mysql> 、
mysql> revoke select on *.* from 'app_readonly'@'localhost'; Query OK, 0 rows affected (0.08 sec) mysql> select * from mysql.role_edges; +-----------+--------------+-----------+---------+-------------------+ | FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION | +-----------+--------------+-----------+---------+-------------------+ | localhost | app_readonly | localhost | app2 | N | +-----------+--------------+-----------+---------+-------------------+ 1 row in set (0.01 sec) mysql> show grants for 'app_readonly'@'localhost'; +--------------------------------------------------+ | Grants for app_readonly@localhost | +--------------------------------------------------+ | GRANT USAGE ON *.* TO `app_readonly`@`localhost` | +--------------------------------------------------+ 1 row in set (0.00 sec) mysql> show grants for 'app2'@'localhost'; +--------------------------------------------------------+ | Grants for app2@localhost | +--------------------------------------------------------+ | GRANT USAGE ON *.* TO `app2`@`localhost` | | GRANT `app_readonly`@`localhost` TO `app2`@`localhost` | +--------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show grants for 'app2'@'localhost' using 'app_readonly'@'localhost'; +--------------------------------------------------------+ | Grants for app2@localhost | +--------------------------------------------------------+ | GRANT USAGE ON *.* TO `app2`@`localhost` | | GRANT `app_readonly`@`localhost` TO `app2`@`localhost` | +--------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
PS:如果删除了role,则所有权限都消失。mysql.edgs_roles 和mysql.default_roles里的记录也消失。