转 mysql 问题一则

---我们碰到的问题是监控配置的用户给了super 权限，但是发现这个是跟应用用户一个用户，可以涉及到权限过大问题。

解决办法，创建一个新用户并给出相关的权限

--另外需要我们给出如下权限；
--请将ddmondata、dddata用户的表、视图、存储过程的查询权限授权给useropr用户，同时授权useropr用户在10..***和10.***网段的堡垒机上可登录。

grant select on *.* to 'appmonopr'@'10.200.%' IDENTIFIED BY 'appmonopr123';
grant select on mysql.proc to 'appmonopr'@'10.200.%' identified by 'appmonopr123';
grant SHOW VIEW on *.* to 'appmonopr'@'10.200.%' identified by 'appmonopr123';
flush privileges;

#######1 .感谢mysql_user
#### https://dba.stackexchange.com/questions/124066/is-there-any-danger-in-granting-super-privileges-to-a-user

我们知道super 权限是个非常大的权限（但是不如ALL 权限大）。权限如下：

The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or
mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads),
PURGE BINARY LOGS,
configuration changes using SET GLOBAL to modify global system variables,
the mysqladmin debug command,
enabling or disabling logging,
performing updates even if the read_only system variable is enabled,
starting and stopping replication on slave servers, specification of any account in the DEFINER attribute of stored programs and views,
and enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.

虽然说可以通过权限 super_read_only. 控制，但是感觉还是可能存在隐患

所以说，最关键是的是dbuser 如果不需要super 权限可以将其屏蔽
So if you think "dbuser" shouldn't do what SUPER user can do, revoke it. Ideally you should give the least priv.

#### 2. user 表的权限显示有Select_priv，Execute_priv ，等等权限
根据 mysql的表的结果有如下权限：

mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | caching_sha2_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
| Create_role_priv | enum('N','Y') | NO | | N | |
| Drop_role_priv | enum('N','Y') | NO | | N | |
| Password_reuse_history | smallint(5) unsigned | YES | | NULL | |
| Password_reuse_time | smallint(5) unsigned | YES | | NULL | |
+------------------------+-------------------------

mysql>DESC mysql.db;
The following is the output.

+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.06 sec)

根据官方文档， user 下显示每个系统级别的的显示权限如下（包括了Server administration）：DB 表的权限也可以顺延归纳每个database的权限（也可以理解DB 端存储了减去Server administration的权限），方法类似
able 6.2 Permissible Privileges for GRANT and REVOKE

Privilege Grant Table Column Context
ALL [PRIVILEGES] Synonym for “all privileges” Server administration
ALTER Alter_priv Tables
ALTER ROUTINE Alter_routine_priv Stored routines
CREATE Create_priv Databases, tables, or indexes
CREATE ROUTINE Create_routine_priv Stored routines
CREATE TABLESPACE Create_tablespace_priv Server administration
CREATE TEMPORARY TABLES Create_tmp_table_priv Tables
CREATE USER Create_user_priv Server administration
CREATE VIEW Create_view_priv Views
DELETE Delete_priv Tables
DROP Drop_priv Databases, tables, or views
EVENT Event_priv Databases
EXECUTE Execute_priv Stored routines
FILE File_priv File access on server host
GRANT OPTION Grant_priv Databases, tables, or stored routines
INDEX Index_priv Tables
INSERT Insert_priv Tables or columns
LOCK TABLES Lock_tables_priv Databases
PROCESS Process_priv Server administration
PROXY See proxies_priv table Server administration
REFERENCES References_priv Databases or tables
RELOAD Reload_priv Server administration
REPLICATION CLIENT Repl_client_priv Server administration
REPLICATION SLAVE Repl_slave_priv Server administration
SELECT Select_priv Tables or columns
SHOW DATABASES Show_db_priv Server administration
SHOW VIEW Show_view_priv Views
SHUTDOWN Shutdown_priv Server administration
SUPER Super_priv Server administration
TRIGGER Trigger_priv Tables
UPDATE Update_priv Tables or columns
USAGE Synonym for “no privileges” Server administration

#######3 关于参数权限 super_read_only ，解释如下：
When the read_only system variable is enabled, the server permits no client updates except from users who have the SUPER privilege. This variable is disabled by default.
The server also supports a super_read_only system variable (disabled by default), which has these effects:
If super_read_only is enabled, the server prohibits client updates, even from users who have the SUPER privilege.
setting super_read_only to ON implicitly forces read_only to ON.
Setting read_only to OFF implicitly forces super_read_only to OFF.
Even with read_only enabled, the server permits these operations:

select user,host,Super_priv,Shutdown_priv from user;
+---------------+-----------+------------+
| user | host | Super_priv |
+---------------+-----------+------------+
| root | localhost | Y |
| mysql.session | localhost | Y |
| mysql.sys | localhost | N |
| user | % | Y |
| lepus_user | localhost | N |
| lepus_user | % | N |
| user | % | Y |
| resync | 56.% | N |
+---------------+-----------+------------+

select user,host,Create_priv,Update_priv,Select_priv from user;
+---------------+-----------+-------------+-------------+-------------+
| user | host | Create_priv | Update_priv | Select_priv |
+---------------+-----------+-------------+-------------+-------------+
| root | localhost | Y | Y | Y |
| mysql.session | localhost | N | N | N |
| mysql.sys | localhost | N | N | N |
| user | % | Y | Y | Y |
| lepus_user | localhost | N | N | N |
| lepus_user | % | N | N | N |
| user | % | Y | Y | Y |
| resync | 56.% | N | N | N |
+---------------+-----------+-------------+-------------+-------------+

select user,host,db,Create_priv,Update_priv,Select_priv from db;

mysql> select user,host,db,Create_priv,Update_priv,Select_priv from db;
+---------------+-----------+--------------------+-------------+-------------+-------------+
| user | host | db | Create_priv | Update_priv | Select_priv |
+---------------+-----------+--------------------+-------------+-------------+-------------+
| mysql.session | localhost | performance_schema | N | N | Y |
| mysql.sys | localhost | sys | N | N | N |
+---------------+-----------+--------------------+-------------+-------------+-------------+
4 rows in set (0.01 sec)

---4：

SHOW GRANTS FOR 'appmonopr'@'10.200.%';

mysql> SHOW GRANTS FOR 'appmonopr'@'10.200.%';
+----------------------------------------------------------+
| Grants for appmonopr@10.200.% |
+----------------------------------------------------------+
| GRANT SELECT, SHOW VIEW ON *.* TO 'appmonopr'@'10.200.%' |
| GRANT SELECT ON `mysql`.`proc` TO 'appmonopr'@'10.200.%' |
+----------------------------------------------------------+
2 rows in set (0.00 sec)

--我们做了测试测试如下：

--这里感谢Ankith Reddy

4.1 How to add super privileges to MySQL database?
--方法
https://www.tutorialspoint.com/how-to-add-super-privileges-to-mysql-database

--super 权限（不包括shutdown 权限，不包括select any table,updata any table,create any table 权限）
--UPDATE mysql.user SET Super_Priv='Y' WHERE user='user' AND host='%';
GRANT SUPER ON *.* TO user@'localhost' IDENTIFIED BY 'passwordName';
FLUSH PRIVILEGES;

-all 权限（包括shutdown 权限，select any table,updata any table,create any table 权限）
GRANT all ON *.* TO user@'%';
FLUSH PRIVILEGES;

结论all 权限（dba）远远大于 super 权限(基本管理权限)，也即是super权限可以维持监控需要，不需要调整，

4.2 --- How to create MySQL user with limited privileges?
-- 每一个主机加网段都是独立的一组用户，权限，比如user@'%' 和 user@'localhost' 是2个单独的用户。
--感谢George John
https://www.tutorialspoint.com/how-to-create-mysql-user-with-limited-privileges

How to create MySQL user with limited privileges?
MySQLMySQLi Database
To create MySQL user with limited privileges, following is the syntax −

CREATE USER 'yourUserName'@'yourHostName' IDENTIFIED BY 'yourPassword';
Following is the syntax to set limited privileges for user −

GRANT SELECT, INSERT, UPDATE, etc. REFERENCES
ON yourDatabaseName.* TO 'yourUserName'@'yourHostName';
Let us implement the above syntaxes in order to create a MySQL user with limited privileges −

mysql> CREATE USER 'David'@'localhost' IDENTIFIED BY 'david';
Query OK, 0 rows affected (0.20 sec)
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, REFERENCES
ON test.* TO 'David'@'localhost';
Query OK, 0 rows affected (0.21 sec)
Let us check the user has been created with name ‘David’ or not.

mysql> select user, host from MySQL.user;
This will produce the following output −

4.3 How can we revoke privileges from a MySQL user?
https://www.tutorialspoint.com/How-can-we-revoke-privileges-from-a-MySQL-user
感谢Srinivas Gorla

MySQLMySQLi Database
With the help of MySQL REVOKE statement, we can revoke one or more or all privileges from a MySQL user. Its syntax would be as follows −

REVOKE privilege_type [(column_list)]
[, priv_type [(column_list)]]...
ON [object_type] privilege_level
FROM user [, user]...
Explanation of the above syntax is as follows −

First, we need to specify a list of privileges that we want to revoke from a user right after the REVOKE keyword. We need to separate privileges by commas.
Second, we need to specify the privilege level at which privileges is revoked in the ON clause.
Third, we need to specify the user account that we want to revoke the privileges in the FROM clause.

Example
In the given example we are going to revoke the grants from user abcd@localhost −

mysql> SHOW GRANTS FOR abcd@localhost;
+---------------------------------------------------------------------+
| Grants for abcd@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'abcd'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
The following query will revoke the grants from user ‘abcd@localhost’ −

mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM abcd@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS for abcd@localhost;
+------------------------------------------+
| Grants for abcd@localhost |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'abcd'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)

4.4 Check privileges (grants) for a specific user in MySQL?

感谢Anvi Jain
https://www.tutorialspoint.com/check-privileges-grants-for-a-specific-user-in-mysql

If you want to check privileges for a specific user, then use the below syntax −

SHOW GRANTS FOR 'yourUserName'@'yourHostName';
The above syntax will check privileges for a specific user.

To check the privileges for a specific user, then use FOR. Let’s say we have a username ‘JOHN‘ and host is ‘%’. Following is the query to get the privileges for user “JOHN” −

mysql> SHOW GRANTS FOR 'JOHN'@'%';
This will produce the following output −

+---------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
--------------+
| Grants for JOHN@%
|
+---------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
--------------+
| 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
`JOHN`@`%` WITH GRANT OPTION |
| GRANT
BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GR
OUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_A
DMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USE
R_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `JOHN`@`%` WITH
GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
--------------+
2 rows in set (0.00 sec)

4.6.感谢潇湘隐者
https://www.cnblogs.com/kerrycode/p/11857781.html

那么在MySQL中能否实现这个功能呢？找了很多资料，没有看到有这方面的功能，官方文档没有涉及这样的权限，网上有个方法：
可以通过授予用户查询mysql.proc这样的权限来间接实现这个功能

grant select on mysql.proc to usrname@'xxx.xxx.xxx.xxx';
个人简单测试了一下，这样授权后，发现还是有一些其他问题。

mysql> show create procedure prc_insert; --没有授权前报这个错误。
ERROR 1305 (42000): PROCEDURE prc_insert does not exist
mysql> show create procedure prc_insert\G; --授权后
*************************** 1. row ***************************
Procedure: prc_insert
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `prc_insert`(in cnt int)
begin
declare i int;
set i=1;
while i < cnt do
insert into test(id, name) select i, CONCAT('name',i) from dual;

set i = i+1;

end while;
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

ERROR:
No query specified

问题1：这样授权后，你能看到所有数据库存储过程的定义（不仅仅是某个某个存储过程，或某个数据库的存储过程的定义），这里就涉及一个权限放大的问题。例如，我本来打算只授予用户A查看存储过程PRC_A的定义权限，但是那样授权后，A能查看很多存储过程的定义，甚至还能查看一些没有访问权限数据库的存储过程的权限。严格意义上来说，这个授权是不合理的，而且是有问题的。

问题2：MySQL 8.0开始抛弃了mysql.proc，而且从MySQL 8.0开始，如果你要用SHOW CREATE PROCEDURE或SHOW CREATE FUNCION的话，需要有什么权限呢？。

MySQL 8.0开始，存储过程存储在mysql.routines和mysql.parameters这些系统表中，但是这些表无法直接访问，只能访问INFORMATION_SCHEMA.ROUTINES。像MySQL 8.0之前那样的授权行不通了，

Where are stored procedures stored?

Stored procedures are stored in the mysql.routines and mysql.parameters tables, which are part of the data dictionary. You cannot access these tables directly. Instead, query the INFORMATION_SCHEMA ROUTINES and PARAMETERS tables. See Section 25.29, “The INFORMATION_SCHEMA ROUTINES Table”, and Section 25.19, “The INFORMATION_SCHEMA PARAMETERS Table”.

You can also use SHOW CREATE FUNCTION to obtain information about stored functions, and SHOW CREATE PROCEDURE to obtain information about stored procedures. See Section 13.7.7.9, “SHOW CREATE PROCEDURE Statement”.

个人测试发现，授予alter routine后，就能查看存储过程的定义，但是这个授权也带来一个问题，授予权限的用户不仅可以查看存储过程定义，而且可以删除这个存储过程（这个也是一个问题）。这个当然，不清楚是否还有其它授权来实现。

mysql> grant alter routine on procedure MyDB.prc_2 TO test@'192.168%';

Query OK, 0 rows affected (0.08 sec)

mysql>

问题3：在MySQL 8.0, 如果用户test创建存储过程的时候，指定了DEFINER（没有指定的不会有这个问题），然后对于用户test2，即使授予了ALTER ROUTINE，那么它依然无法查看这些存储过程定义(网友反馈)，如下测试所示：

CREATE DEFINER=`test`@`192.168%` PROCEDURE PRC_TEST1(CNT INT)

BEGIN

DECLARE V_LOOP INT;

SET V_LOOP = CNT;

WHILE V_LOOP>0 DO

INSERT INTO TEST(NAME,CREATE_DATE) VALUES(REPEAT('A',800),DATE_ADD(NOW(),INTERVAL -RAND()*100000 MINUTE) );

SET V_LOOP = V_LOOP - 1;

END WHILE;

END

test2用户，使用命令SHOW CREATE PROCEDURE PRC_TEST1查看存储过程定义为NULL，也就是说即使授予了ALTER ROUTINE权限，但是如果存储过程有指定DEFINER，这样依然无法看到存储过程的定义

4.7
监控用户的权限如下SUPER,PROCESS,REPLICATION CLIENT

-》super 权限在最上面有，
-》The PROCESS privilege controls access to information about threads executing within the server (that is, information about statements being executed by sessions). Thread information available using the SHOW PROCESSLIST statement, the mysqladmin processlist command, and the INFORMATION_SCHEMA.PROCESSLIST table is accessible as follows:
-》REPLICATION CLIENT Enables use of the SHOW MASTER STATUS, SHOW SLAVE STATUS, and SHOW BINARY LOGS statements. Grant this privilege to accounts that are used by slave servers to connect to the current server as their master.

posted @ 2020-07-13 16:45 feiyun8616 阅读(160) 评论(0) 编辑收藏举报

刷新页面返回顶部

feiyun8616的作坊 (半个程序员and dba) 脚本化 python

转 mysql 问题一则

公告