How to find all Implied Permissions using sys.fn_builtin_permissions

2014-02-27 - General, Queries, Security

Introduction

You probably know by now that a specific permission granted or denied will potentially imply many other permissions to also be granted or denied. During the post about how to Identify All Possible Permissions using sys.fn_builtin_permission two days ago, I showed you that that very catalog function does also return information about other permissions that would imply the current one. However, that is only one step along the permissions hierarchy ladder. So, how can we get the complete list of permissions implied by a chosen one?

Finding all Implied Permissions

This kind of question calls for a recursive approach. There are many ways to implement recursion in SQL Server. Our goal here is to get the complete list of implied permissions in a result set, so a recursive CTE seems to be the best approach:

WITH  parent_child
        AS (
             SELECT PP.class_desc,
                    PP.permission_name,
                    CP.class_desc AS implied_class_desc,
                    CP.permission_name AS implied_permission_name,
                    CASE WHEN CP.class_desc = PP.class_desc THEN 'P'
                         ELSE 'C'
                    END AS relationship
             FROM   sys.fn_builtin_permissions(DEFAULT) AS PP
             LEFT JOIN sys.fn_builtin_permissions(DEFAULT) AS CP
                    ON (
                       CP.covering_permission_name = PP.permission_name
                       AND CP.class_desc = PP.class_desc
                       )
                       OR (
                       CP.parent_covering_permission_name = PP.permission_name
                       AND CP.parent_class_desc = PP.class_desc
                       )
           ),
      any_anchestor
        AS (
             SELECT PC.class_desc,
                    PC.permission_name,
                    PC.implied_class_desc,
                    PC.implied_permission_name,
                    1 AS steps,
                    CAST(PC.relationship AS VARCHAR(MAX)) AS relationship_path
             FROM   parent_child AS PC
             UNION ALL
             SELECT AP.class_desc,
                    AP.permission_name,
                    PC2.implied_class_desc,
                    PC2.implied_permission_name,
                    AP.steps + 1,
                    AP.relationship_path + PC2.relationship
             FROM   any_anchestor AP
             JOIN   parent_child PC2
                    ON AP.implied_class_desc = PC2.class_desc
                       AND AP.implied_permission_name = PC2.permission_name
                       AND PC2.implied_class_desc IS NOT NULL
           )
  SELECT DISTINCT
          A.class_desc,
          A.permission_name,
          A.implied_class_desc,
          A.implied_permission_name,
          A.steps,
          A.relationship_path
  FROM    any_anchestor AS A
  ORDER BY A.class_desc,
          A.permission_name,
          A.implied_class_desc,
          A.implied_permission_name;

The parent_child part of the CTE converts the two possible parents of a given permission into two rows. The parent permission is mentioned first in the class_desc and permission_name columns. The two columns starting with "implied" contain the same information for the child or implied permission. I also added a relationship column that indicates if the implied permission is a step down the securable hierarchy (marked as "C" for class) or the privilege hierarchy (marked as "P" for privilege).

The next step in the CTE, any_achestor, is where the recursion happens. It joins the result of parent_chiled to itself to travers the hierarchies. It also counts the steps necessary for this and collects the relationship (different Privilege or different Class) into a path.

The result of the above query looks like this:

A query that is traversing the implied permissions hierarchy.

On SQL Server 2012 it returns 1342 rows. If the two "implied" columns are NULL for a given permission, no further implied permissions exists. Also, note that this query does not take the principal hierarchy into account at all. It only looks at securables and privileges.

Summary

To get from the list of the two directly covering permissions, returned by sys.fn_builtin_permissions, to a complete list of implied permissions, including those permissions that are implied over several hops, a recursive query against that catalog function can be used. This post demonstrated one possible implementation of such a query, using the recursive CTE functionality.

Categories: General, Queries, Security

Leave a Reply