包含権限と暗黙権限 (データベース エンジン)

SQL Server の権限階層内では、ある特定の権限を許可すると、他の権限の権利も暗黙的に許可される場合があります。このとき、上位の権限は包含権限、下位のより細かい権限は暗黙権限と表現されることがあります。

dbo.ImplyingPermissions

次のサンプル スクリプトでは、引数にセキュリティ保護可能なクラスの名前と権限の名前を使用します。このサンプルは、指定したノードからルート (サーバーの CONTROL SERVER) まで、権限階層内の全体で実行されます。結果として、暗黙的に指定された権限を含む権限の一覧が返されます。

CREATE FUNCTION dbo.ImplyingPermissions (@class nvarchar(64), 
   @permname nvarchar(64))
RETURNS @ImplPerms table (permname nvarchar(64), 
   class nvarchar(64), height int, rank int)
AS
BEGIN
   WITH 
   class_hierarchy(class_desc, parent_class_desc)
   AS
   (
   SELECT DISTINCT class_desc, parent_class_desc 
      FROM sys.fn_builtin_permissions('')
   ),
   PermT(class_desc, permission_name, covering_permission_name,
      parent_covering_permission_name, parent_class_desc)
   AS
   (
   SELECT class_desc, permission_name, covering_permission_name,
      parent_covering_permission_name, parent_class_desc
      FROM sys.fn_builtin_permissions('')
   ),
   permission_covers(permission_name, class_desc, level,
      inserted_as)
   AS
    (
    SELECT permission_name, class_desc, 0, 0
       FROM PermT
       WHERE permission_name = @permname AND
       class_desc = @class
    UNION ALL
    SELECT covering_permission_name, class_desc, 0, 1
       FROM PermT 
       WHERE class_desc = @class AND 
          permission_name = @permname AND
          len(covering_permission_name) > 0
    UNION ALL
    SELECT PermT.covering_permission_name, 
       PermT.class_desc, permission_covers.level,
       permission_covers.inserted_as + 1
       FROM PermT, permission_covers WHERE
       permission_covers.permission_name =
       PermT.permission_name AND
       permission_covers.class_desc = PermT.class_desc 
       AND len(PermT.covering_permission_name) > 0
    UNION ALL
    SELECT PermT.parent_covering_permission_name,
       PermT.parent_class_desc,
       permission_covers.level + 1,
       permission_covers.inserted_as + 1
       FROM PermT, permission_covers, class_hierarchy
       WHERE permission_covers.permission_name =
       PermT.permission_name AND 
       permission_covers.class_desc = PermT.class_desc
       AND permission_covers.class_desc = class_hierarchy.class_desc
       AND class_hierarchy.parent_class_desc =
       PermT.parent_class_desc AND
       len(PermT.parent_covering_permission_name) > 0
    )
  INSERT @ImplPerms
  SELECT DISTINCT permission_name, class_desc, 
     level, max(inserted_as) AS mia 
     FROM permission_covers
     GROUP BY class_desc, permission_name, level
     ORDER BY level, mia
  RETURN
END

CREATE FUNCTION dbo.ImplyingPermissions (@class nvarchar(64), 
   @permname nvarchar(64))
RETURNS @ImplPerms table (permname nvarchar(64), 
   class nvarchar(64), height int, rank int)
AS
BEGIN
   WITH 
   class_hierarchy(class_desc, parent_class_desc)
   AS
   (
   SELECT DISTINCT class_desc, parent_class_desc 
      FROM sys.fn_builtin_permissions('')
   ),
   PermT(class_desc, permission_name, covering_permission_name,
      parent_covering_permission_name, parent_class_desc)
   AS
   (
   SELECT class_desc, permission_name, covering_permission_name,
      parent_covering_permission_name, parent_class_desc
      FROM sys.fn_builtin_permissions('')
   ),
   permission_covers(permission_name, class_desc, level,
      inserted_as)
   AS
    (
    SELECT permission_name, class_desc, 0, 0
       FROM PermT
       WHERE permission_name = @permname AND
       class_desc = @class
    UNION ALL
    SELECT covering_permission_name, class_desc, 0, 1
       FROM PermT 
       WHERE class_desc = @class AND 
          permission_name = @permname AND
          len(covering_permission_name) > 0
    UNION ALL
    SELECT PermT.covering_permission_name, 
       PermT.class_desc, permission_covers.level,
       permission_covers.inserted_as + 1
       FROM PermT, permission_covers WHERE
       permission_covers.permission_name =
       PermT.permission_name AND
       permission_covers.class_desc = PermT.class_desc 
       AND len(PermT.covering_permission_name) > 0
    UNION ALL
    SELECT PermT.parent_covering_permission_name,
       PermT.parent_class_desc,
       permission_covers.level + 1,
       permission_covers.inserted_as + 1
       FROM PermT, permission_covers, class_hierarchy
       WHERE permission_covers.permission_name =
       PermT.permission_name AND 
       permission_covers.class_desc = PermT.class_desc
       AND permission_covers.class_desc = class_hierarchy.class_desc
       AND class_hierarchy.parent_class_desc =
       PermT.parent_class_desc AND
       len(PermT.parent_covering_permission_name) > 0
    )
  INSERT @ImplPerms
  SELECT DISTINCT permission_name, class_desc, 
     level, max(inserted_as) AS mia 
     FROM permission_covers
     GROUP BY class_desc, permission_name, level
     ORDER BY level, mia
  RETURN
END

A. スキーマに対する ALTER 権限に暗黙的に許可される権限の一覧を表示する

SELECT * FROM dbo.ImplyingPermissions('schema', 'alter')

SELECT * FROM dbo.ImplyingPermissions('schema', 'alter')

B. オブジェクトに対する VIEW DEFINITION 権限に暗黙的に許可される権限の一覧を表示する

SELECT * FROM dbo.ImplyingPermissions('object', 'view definition') 

SELECT * FROM dbo.ImplyingPermissions('object', 'view definition') 

C. ルートに対する TAKE OWNERSHIP 権限に暗黙的に許可される権限の一覧を表示する

SELECT * FROM dbo.ImplyingPermissions('route', 'take ownership')

SELECT * FROM dbo.ImplyingPermissions('route', 'take ownership')

D. XML スキーマ コレクションに対する EXECUTE 権限に暗黙的に許可される権限の一覧を取得する

SELECT * FROM dbo.ImplyingPermissions('xml schema collection', 'execute')

SELECT * FROM dbo.ImplyingPermissions('xml schema collection', 'execute')

関連項目

参照

sys.server_permissions (Transact-SQL)

sys.fn_builtin_permissions (Transact-SQL)

sys.database_permissions (Transact-SQL)

概念

SQL Server の保護