主页 / dba / 问题 / 229511
Accepted
Kimo
Asked: 2019-02-13 06:31:10 +0800 CST

如何列出架构的权限？

  • 772

在 SSMS 中，如果您按照路径 [Database] > Security > Schemas 并查看任何架构属性，您将拥有一个“权限”选项卡，其中列出了每个用户对该特定架构拥有的所有权限。

我想做一个查询，它给我与那个选项卡相同的输出。我尝试使用 sys.database_principals、database_permissions 和 sys_objects，但我无法检索链接到模式的权限。

sql-server ssms

3 个回答

  1. Best Answer

    使用List schema permissions接受的答案，这可能会给你你想要的。

    --demo setup
CREATE DATABASE listschema
GO

USE listschema
GO

CREATE SCHEMA TestSchema
GO

CREATE USER TestUser WITHOUT LOGIN
GO

GRANT SELECT
    ON SCHEMA::TestSchema
    TO TestUser

DENY INSERT
    ON SCHEMA::TestSchema
    TO TestUser
GO

--The actual query
SELECT state_desc
    ,permission_name
    ,'ON'
    ,class_desc
    ,SCHEMA_NAME(major_id)
    ,'TO'
    ,USER_NAME(grantee_principal_id)
FROM sys.database_permissions AS PERM
JOIN sys.database_principals AS Prin
    ON PERM.major_ID = Prin.principal_id
        AND class_desc = 'SCHEMA'
WHERE major_id = SCHEMA_ID('TestSchema')
    AND grantee_principal_id = user_id('TestUser')
    --AND    permission_name = 'SELECT'
GO

--cleanup
USE tempdb
GO

DROP DATABASE listschema

    | state_desc | permission_name | (No column name) | class_desc | (No column name) | (No column name) | (No column name) |
|------------|-----------------|------------------|------------|------------------|------------------|------------------|
| DENY       | INSERT          | ON               | SCHEMA     | TestSchema       | TO               | TestUser         |
| GRANT      | SELECT          | ON               | SCHEMA     | TestSchema       | TO               | TestUser         |
    • 6

  2. 此外，如果您在 SSMS 启动时需要确切的查询，它们的可读性不强，并且有几个用于获得 SSMS 中显示的结果。

    在我的测试架构上打开权限时的基本查询：

    exec sp_executesql N'SELECT
grantee_principal.name AS [Grantee],
CASE grantee_principal.type WHEN ''R'' THEN 3 WHEN ''A'' THEN 4 ELSE 2 END - CASE ''database'' WHEN  ''database'' THEN 0 ELSE 2 END AS [GranteeType]
FROM
sys.schemas AS s
INNER JOIN sys.database_permissions AS prmssn ON prmssn.major_id=s.schema_id AND prmssn.minor_id=0 AND prmssn.class=3
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(s.name=@_msparam_0)',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'TEST'

    为每个授予的权限产生一行

    Grantee GranteeType
guest   2
bla 2
bla 2

    （用于授予的查询）

    GRANT EXECUTE ON SCHEMA :: test TO bla;
GRANT INSERT ON SCHEMA :: test TO bla;
GRANT INSERT ON SCHEMA :: test TO guest;

    第二个查询，针对每个主体，在我的示例中为 bla

    exec sp_executesql N'SELECT
ascii(prmssn.state) AS [PermissionState],
null AS [Code],
grantor_principal.name AS [Grantor],
prmssn.type AS [SqlCodePP],
CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN CASE (SELECT oc.type FROM sys.database_principals AS oc WHERE oc.principal_id = prmssn.major_id) WHEN ''R'' THEN CASE prmssn.class WHEN 4 THEN 201 ELSE 301 END WHEN ''A'' THEN 202 ELSE CASE prmssn.class WHEN 4 THEN 200 ELSE 101 END END ELSE prmssn.class END AS [HiddenObjectClass]
FROM
sys.schemas AS s
INNER JOIN sys.database_permissions AS prmssn ON prmssn.major_id=s.schema_id AND prmssn.minor_id=0 AND prmssn.class=3
INNER JOIN sys.database_principals AS grantor_principal ON grantor_principal.principal_id = prmssn.grantor_principal_id
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(grantee_principal.name=@_msparam_0)and((s.name=@_msparam_1))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'bla',@_msparam_1=N'TEST'

    不是真正可读的结果

    PermissionState Code    Grantor SqlCodePP   HiddenObjectClass
71              NULL     dbo       EX           3
71              NULL     dbo       IN           3

    结合这两个查询，您可以获得更具可读性的内容

    DECLARE @SCHEMA varchar(255) = 'test'
SELECT DISTINCT
CASE WHEN prmssn.state = 'D' then 'Deny'  WHEN prmssn.state = 'R' THEN 'REVOKE' WHEN prmssn.state = 'G' THEN 'Grant'   ELSE  ' Grant With Grant Option' end as permissionstate,
grantor_principal.name AS [Grantor],
prmssn.permission_name AS [name],
class_desc,Grantees.grantee
FROM
sys.schemas AS s
INNER JOIN sys.database_permissions AS prmssn ON prmssn.major_id=s.schema_id AND prmssn.minor_id=0 AND prmssn.class=3
INNER JOIN sys.database_principals AS grantor_principal ON grantor_principal.principal_id = prmssn.grantor_principal_id
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
INNER JOIN (SELECT
grantee_principal.name AS [Grantee]
FROM
sys.schemas AS s
INNER JOIN sys.database_permissions AS prmssn ON prmssn.major_id=s.schema_id AND prmssn.minor_id=0 AND prmssn.class=3
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(s.name= @SCHEMA)) as Grantees
on Grantees.grantee = grantee_principal.name
WHERE
((s.name=@SCHEMA))

    导致：

    permissionstate Grantor name    class_desc  grantee
Grant            dbo    EXECUTE SCHEMA      bla
Grant            dbo    INSERT  SCHEMA      bla
Grant            dbo    INSERT  SCHEMA      guest
    • 1

  3. 使用以下命令，我设法得到了更正确的响应：

    SELECT state_desc
    ,permission_name
    ,'ON'
    ,class_desc
    ,SCHEMA_NAME(major_id)
    ,'TO'
    ,USER_NAME(grantee_principal_id)
FROM sys.database_permissions AS PERM
JOIN sys.database_principals AS Prin
    ON PERM.grantee_principal_id = Prin.principal_id
        AND PERM.class_desc = 'SCHEMA'

    问题是斯科特的回答我认为和之间的联系sys.database_permissions是sys.database_principals不正确的。为什么要加入，major_id因为那代表架构而不是受让人（委托人）。

    但感谢斯科特！你的回答很有帮助！

    • 1

相关问题