Home » Blog » How to find all Implied Permissions using sys.fn_builtin_permissions

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:

[sql] 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;
[/sql]

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:

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.