MySQL测试权限 [英] MySQL test privileges

问题描述

假设我们在执行某个程序之前必须测试MySQL数据库的一些权限(程序在登录MySQL之前会自我守护，因此我们无法轻松检查是否登录成功).

Let's assume that we have to test some privileges of MySQL database before to execute some program (program daemonizes itself before logging to MySQL, so we can't easily check whether logging will success).

解析 SHOW GRANTS 输出是一种折磨，尤其是所有这些转义、% 等.

Parsing SHOW GRANTS output is a torture, especially with all these escapes, %, etc.

是否有一种简单的方法来测试用户权限(连接用户可以使用的方式，而不是 root 用户可以使用的方式)?

Is there an easy way to test user privileges (way that connected user can use, not root)?

SELECT 很容易测试，但是像 DELETE、INSERT 这样的操作呢?假设我们有 INSERT 权限，但没有 DELETE，所以 INSERTING smth 不是一个解决方案.我们可以插入和删除VOID"只是为了测试权限吗?

SELECT is easy to test, but what about such operations like DELETE, INSERT? Say we have INSERT privilege, but haven't DELETE, so INSERTING smth is not a solution. Can we INSERT and DELETE "VOID" just to test privileges?

推荐答案

可以在不同级别(用户级别、架构级别、表级别甚至列级别)授予权限.下面的查询组合了这些(列级别除外)，并显示了每个表(或 VIEW)的组合权限.

Privileges can be granted on different level (user level, schema level, table level and even column level). The query below combines those (except column level) and shows you the combined privileges per table (or VIEW).

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, GRANTEE
 , GROUP_CONCAT(DISTINCT PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE) PRIVILEGE_TYPES
FROM (
 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, GRANTEE, PRIVILEGE_TYPE
  FROM information_schema.TABLES
  INNER JOIN information_schema.user_privileges USING (TABLE_CATALOG)
  WHERE NOT TABLE_SCHEMA IN ('information_schema', 'performance_schema')
   AND GRANTEE=CONCAT("'", REPLACE(CURRENT_USER(), '@', "'@'"), "'")
 UNION ALL
 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, GRANTEE, PRIVILEGE_TYPE
  FROM information_schema.TABLES
  INNER JOIN information_schema.schema_privileges USING (TABLE_CATALOG, TABLE_SCHEMA)
  WHERE NOT TABLE_SCHEMA IN ('information_schema', 'performance_schema')
   AND GRANTEE=CONCAT("'", REPLACE(CURRENT_USER(), '@', "'@'"), "'")
 UNION ALL
 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, GRANTEE, PRIVILEGE_TYPE
  FROM information_schema.TABLES
  INNER JOIN information_schema.table_privileges USING (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
  WHERE NOT TABLE_SCHEMA IN ('information_schema', 'performance_schema')
   AND GRANTEE=CONCAT("'", REPLACE(CURRENT_USER(), '@', "'@'"), "'")
) u
 GROUP BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, GRANTEE
;

请注意，我在 CURRENT_USER 周围加上引号的方式很难看，但只要您不在用户名中使用单引号或 @ 符号，就应该没问题.我想.

Note that the way I put quotes around the CURRENT_USER is ugly, but as long as you don't use single quotes or @ signs in your user names, you should be fine. I think.

这篇关于MySQL测试权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！