如何查看拥有相同权限的所有用户？含存储过程权限查询示例

嘿，这个需求很常见，我来给你分数据库讲讲具体的实现方法，毕竟不同数据库的权限体系不太一样～

针对SQL Server的解决方案 #

1. 先明确谁拥有该存储过程的执行权限 #

首先，你可以用下面的查询直接获取所有被授予（或拒绝）执行目标存储过程的用户：

USE YourDatabaseName; -- 替换成你的数据库名
GO
SELECT 
    dp.name AS 用户名,
    dp.type_desc AS 用户类型,
    perm.permission_name AS 权限类型,
    perm.state_desc AS 权限状态
FROM 
    sys.database_permissions perm
JOIN 
    sys.database_principals dp ON perm.grantee_principal_id = dp.principal_id
JOIN 
    sys.objects obj ON perm.major_id = obj.object_id
WHERE 
    obj.name = 'YourStoredProcedureName' -- 替换成你的存储过程名
    AND obj.type = 'P'; -- P代表存储过程类型

这个查询会帮你区分哪些用户是直接拥有执行权限的，哪些被明确拒绝了。

2. 找出所有无法执行该存储过程的用户 #

如果要直接列出没有执行权限的用户，你可以先获取数据库里的所有用户，再排除掉拥有权限的用户（包括直接授权和通过角色继承的权限）：

USE YourDatabaseName;
GO
-- 先获取拥有执行权限的角色及关联用户
WITH 角色权限 AS (
    SELECT 
        rp.name AS 角色名,
        obj.name AS 存储过程名
    FROM 
        sys.database_permissions perm
    JOIN 
        sys.database_principals rp ON perm.grantee_principal_id = rp.principal_id
    JOIN 
        sys.objects obj ON perm.major_id = obj.object_id
    WHERE 
        obj.name = 'YourStoredProcedureName'
        AND obj.type = 'P'
        AND perm.permission_name = 'EXECUTE'
        AND perm.state_desc IN ('GRANT', 'GRANT_WITH_GRANT_OPTION')
        AND rp.type = 'R' -- R代表数据库角色
),
角色关联用户 AS (
    SELECT 
        dp.name AS 用户名
    FROM 
        sys.database_role_members drm
    JOIN 
        sys.database_principals dp ON drm.member_principal_id = dp.principal_id
    JOIN 
        sys.database_principals rp ON drm.role_principal_id = rp.principal_id
    JOIN 
        角色权限 rp_perm ON rp.name = rp_perm.角色名
)
-- 最终筛选无执行权限的用户
SELECT 
    name AS 无执行权限的用户
FROM 
    sys.database_principals
WHERE 
    type IN ('U', 'S') -- U是数据库用户，S是SQL登录名，可按需调整
    AND name NOT IN (
        SELECT 用户名 FROM 角色关联用户
        UNION
        SELECT dp.name
        FROM sys.database_permissions perm
        JOIN sys.database_principals dp ON perm.grantee_principal_id = dp.principal_id
        JOIN sys.objects obj ON perm.major_id = obj.object_id
        WHERE obj.name = 'YourStoredProcedureName'
            AND obj.type = 'P'
            AND perm.permission_name = 'EXECUTE'
            AND perm.state_desc IN ('GRANT', 'GRANT_WITH_GRANT_OPTION')
    )
    AND name NOT LIKE '##%' -- 排除系统内置用户，可按需调整
ORDER BY name;

针对MySQL的解决方案 #

1. 查看拥有存储过程执行权限的用户 #

MySQL里可以通过information_schema来查询存储过程的权限情况：

SELECT 
    CONCAT(user, '@', host) AS 用户名,
    db AS 数据库名,
    routine_name AS 存储过程名,
    privilege_type AS 权限类型
FROM 
    information_schema.routine_privileges
WHERE 
    routine_name = 'YourStoredProcedureName' -- 替换成你的存储过程名
    AND routine_type = 'PROCEDURE'
    AND privilege_type = 'EXECUTE';

2. 筛选没有执行权限的用户 #

同样，先获取所有数据库用户，再排除拥有权限的用户（包括角色继承的权限，MySQL 8.0+支持角色）：

-- 先获取拥有执行权限的角色及关联用户
WITH 角色权限 AS (
    SELECT 
        role_name AS 角色名,
        routine_name AS 存储过程名
    FROM 
        information_schema.application_role_privileges
    WHERE 
        routine_name = 'YourStoredProcedureName'
        AND privilege_type = 'EXECUTE'
),
角色关联用户 AS (
    SELECT 
        CONCAT(user, '@', host) AS 用户名
    FROM 
        mysql.user
    JOIN 
        mysql.default_roles dr ON user = dr.user AND host = dr.host
    JOIN 
        角色权限 rp ON dr.role_name = rp.角色名
)
-- 最终筛选无执行权限的用户
SELECT 
    CONCAT(user, '@', host) AS 无执行权限的用户
FROM 
    mysql.user
WHERE 
    CONCAT(user, '@', host) NOT IN (
        SELECT 用户名 FROM 角色关联用户
        UNION
        SELECT CONCAT(user, '@', host)
        FROM information_schema.routine_privileges
        WHERE routine_name = 'YourStoredProcedureName'
            AND routine_type = 'PROCEDURE'
            AND privilege_type = 'EXECUTE'
    )
    AND user NOT IN ('root', 'mysql.session', 'mysql.sys') -- 排除系统用户，可按需调整
ORDER BY user, host;

小提示 #

记得把查询里的YourDatabaseName和YourStoredProcedureName替换成你实际的数据库和存储过程名称，系统用户的排除规则也可以根据你的实际环境调整哦～

内容的提问来源于stack exchange，提问作者Robin MULA