{"id":81007,"date":"2018-09-26T02:12:39","date_gmt":"2018-09-26T02:12:39","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=81007"},"modified":"2019-01-22T16:45:10","modified_gmt":"2019-01-22T16:45:10","slug":"calculating-a-security-principals-effective-rights","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/calculating-a-security-principals-effective-rights\/","title":{"rendered":"Calculating a Security Principal&#8217;s Effective Rights"},"content":{"rendered":"<p>Security. Oh that most painful of topics. I discussed it a few months earlier when I discussed the need to give rights only through roles to users, so everything is the same in dev and prod except the users who are placed in each role (<a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/sql-server-database-security-and-source-control\/\">SQL Server Database Security And Source Control<\/a>). As I was updating some of our security to use this method, the problem arose that it is hard to know if we got it right. How to make sure that userX who had access to a certain set of objects, still does, even though we have given them access via a different method. This is something that we often wish to know. &#8220;Can user X access resource Y?&#8221;<\/p>\n<p>So, I went about to build a small utility, most of which is part of this blog. SQL Server gives up tools to determine if a principal has access to an object, so I will introduce them, and then use them to check the &#8220;normal&#8221; security case, which is trying to determine a user&#8217;s rights to access to objects in the database. In a later blog, I will attempt to expand the security beyond object access to all of the various types of security a user may have that we need to track.<\/p>\n<p>SQL Server provides us with two functions that can be used effectively to discover the rights that a user has to an object. It doesn&#8217;t tell us HOW the achieved this right, but it tells us if they do have it (how is a bit more complex, and something I plan to tackle some other day!) For example, say a user has SELECT access to TableY. This user could have it directly granted, inherited from a grant at the schema level, inherited through a user-defined role\u2019s access, or even from a built-in role (database or server!)<\/p>\n<p>This is fairly complex, but the general need I am aiming to solve today is to determine what a user has access to. The functions (available in the on-premises and Azure versions) allow you to determine what the current security principal has access to. They are:<\/p>\n<ul>\n<li><strong>HAS_PERMS_BY_NAME<\/strong> &#8211; This is a scalar function that you can pass in various things like tables and columns, database and server rights.<\/li>\n<li><strong>fn_my_permissions<\/strong> &#8211; This is a table valued function that you can use to return the rights you have for a certain object<\/li>\n<\/ul>\n<p>The parameters for both include the securable you are checking, and securable class (and subclass for certain things like column level security. The securable class I will be focusing on in this blog is simply &#8216;OBJECT&#8217;, for objects in the database such as tables, procedures, etc. There are quite a few other ones, if you were interested in a complete view of all of the rights a user may have: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, ENDPOINT, FULLTEXT CATALOG, LOGIN, MESSAGE TYPE, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVER, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION. For more details, check: <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/functions\/has-perms-by-name-transact-sql?view=sql-server-2017\">HAS_PERMS_BY_NAME<\/a> and <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-functions\/sys-fn-my-permissions-transact-sql?view=sql-server-2017\">fn_my_permissions<\/a>.<\/p>\n<p>To get started, I will build a small scenario that will allow me to grant rights, and then change rights around and verify that things match as desired.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE DATABASE PermissionsTest;\r\nGO\r\nUSE PermissionsTest;\r\nGO\r\nCREATE SCHEMA Demo;\r\nGO\r\nCREATE TABLE Demo.Table1 (Table1Id int);\r\nCREATE TABLE Demo.Table2 (Table2Id int);\r\nGO\r\n\r\nCREATE PROCEDURE Demo.Procedure1 \r\nAS SELECT Table1Id FROM Demo.Table1;\r\nGO\r\n\r\n--To this database, we will add a few logins users, and \r\n--give them direct access to several of the objects. \r\nCREATE LOGIN Login1 WITH PASSWORD = '12345';\r\nCREATE USER User1 FROM LOGIN Login1;\r\nCREATE LOGIN Login2 WITH PASSWORD = '12345';\r\nCREATE USER User2 FROM LOGIN Login2;\r\nCREATE LOGIN DboLogin WITH PASSWORD = '12345';\r\nCREATE USER DboUser FROM LOGIN DboLogin;\r\n\r\n--Give User1 access to Table1 and procedure, User2 to Table2\r\nGRANT SELECT ON Demo.Table1 TO User1;\r\nGRANT EXECUTE ON Demo.Procedure1 TO User1;\r\nGRANT SELECT ON Demo.Table2 TO User2;\r\n\r\n--put DboUser in the dbo role\r\nALTER ROLE db_owner ADD MEMBER DboUser;<\/pre>\n<p>If you want to check if a different user other than the one logged in has access to an object, you will either need to:<\/p>\n<ol>\n<li>Login as that user<\/li>\n<li>Impersonate that user<\/li>\n<\/ol>\n<p>As we typically will be working as a database owner\/system administrator when configuring security, we will usually use impersonation to check the user&#8217;s rights. For example, if you want to ask SQL Server about a particular object, you might use HAS_PERMS_BY_NAME, in the following manner:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EXECUTE AS USER = 'User1';\r\n\r\n--verify the user that you are effectively executing as, \r\n--when testing to avoid mistakes\r\nSELECT SUSER_NAME() AS server_principal, \r\n       USER_NAME() AS database_principal;\r\n\r\n--There are additional parameters to check column level \r\n--permissions if you need them\r\nSELECT HAS_PERMS_BY_NAME('Demo.Table1','OBJECT','SELECT'); <\/pre>\n<p>This returns:<\/p>\n<p><code>server_principal\u00a0 \u00a0 \u00a0database_principal<\/code><br \/>\n<code>-------------------- ------------------------<\/code><br \/>\n<code>Login1\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0User1<\/code><\/p>\n<p><code>-----------<\/code><br \/>\n<code>1<\/code><\/p>\n<p>Now, checking to see if User1 can access Table2:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT HAS_PERMS_BY_NAME('dbo.Table2','OBJECT','SELECT');<\/pre>\n<p>This returns:<\/p>\n<p>&#8212;&#8212;&#8212;&#8211;<br \/>\n0<\/p>\n<p>This function works great if you are checking one item, like from an application before trying to execute it. But if you want to see all of a user&#8217;s effective rights on an object, use fn_my_permissions.<\/p>\n<p>For example, if we need to see User1 rights to Table1, we can execute (still in the security context of User1 from the previous code):<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT *\r\nFROM   fn_my_permissions('Demo.Table1', 'OBJECT') AS permissions;   <\/pre>\n<p>This returns the following, with the row with subentity_name as &#8221; being table permissions, and Table1Id being the column permission:<\/p>\n<p><code>entity_name\u00a0 \u00a0 \u00a0 subentity_name\u00a0 \u00a0 permission_name<\/code><br \/>\n<code>---------------- ----------------- ------------------------<\/code><br \/>\n<code>dbo.Table1\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0SELECT<\/code><br \/>\n<code>dbo.Table1\u00a0 \u00a0 \u00a0 \u00a0Table1Id\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT<\/code><\/p>\n<p>Now, let&#8217;s change back to the security context of the user that is a member of the db_owner role:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">REVERT;\r\nEXECUTE AS USER = 'DboUser';\r\n\r\nSELECT permission_name\r\nFROM   fn_my_permissions('Demo.Table1', 'OBJECT') AS permissions\r\nWHERE  permissions.subentity_name = ''; --ignore column rights <\/pre>\n<p>This will show that the dbo user has every possible, logical, right to the table:<\/p>\n<p><code>permission_name<\/code><br \/>\n<code>--------------------------------<\/code><br \/>\n<code>SELECT<\/code><br \/>\n<code>UPDATE<\/code><br \/>\n<code>REFERENCES<\/code><br \/>\n<code>INSERT<\/code><br \/>\n<code>DELETE<\/code><br \/>\n<code>VIEW CHANGE TRACKING<\/code><br \/>\n<code>VIEW DEFINITION<\/code><br \/>\n<code>ALTER<\/code><br \/>\n<code>TAKE OWNERSHIP<\/code><br \/>\n<code>CONTROL<\/code><\/p>\n<p>Finally, taking this to the final step for this blog, let&#8217;s get all of the object level rights for a user in the entire database:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">REVERT;<\/pre>\n<p>To get all of the user\u2019s rights, take all of the objects in the database, ignoring any objects that have a parent_object_id such as triggers or constraints, and use CROSS APPLY to execute the function for every object.<\/p>\n<p>To make this easier to user, I am going to compile this into a view, called Utility.EffectiveSecurity (and give rights to it to Public, so every user can execute it.)<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE SCHEMA Utility;\r\nGO\r\nCREATE OR ALTER VIEW Utility.EffectiveSecurity\r\nAS\r\nWITH objects AS (\r\n\tSELECT objects.name AS object_name,\r\n\t\t   schemas.name AS schema_name,\r\n\t\t   object_id, objects.type_desc AS object_type\r\n\tFROM   sys.objects\r\n\t         JOIN sys.schemas\r\n\t\t    ON objects.schema_id = schemas.SCHEMA_ID\r\n\tWHERE objects.parent_object_id = 0 \r\n     --no constraints that have the parent_object_id reference\r\n     --or triggers\r\n\t\t)\r\n\tSELECT object_type,\r\n\t\t   schema_name, \r\n\t\t   object_name,\r\n\t\t   permissions.permission_name\r\n\tFROM   objects\r\n\t         CROSS APPLY fn_my_permissions(schema_name + \r\n                                     '.' +  OBJECT_NAME, 'Object') AS permissions   \r\n        --I am ignoring column level permissions. \r\n\tWHERE  permissions.subentity_name = '' \r\n\t--hide this object from view\r\n\t  AND NOT (objects.schema_name = 'Utility' \r\n               AND objects.object_name = 'EffectiveSecurity');\r\nGO\r\n--let every user check their permissions\r\nGRANT SELECT ON Utility.EffectiveSecurity TO PUBLIC; <\/pre>\n<p>Now I can execute:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EXECUTE AS USER = 'User1';\r\nGO\r\nSELECT SUSER_NAME() AS server_principal, \r\n       USER_NAME() AS database_principal;\r\n\r\nSELECT *\r\nFROM   Utility.EffectiveSecurity;\r\n\r\nREVERT;<\/pre>\n<p>This returns:<\/p>\n<p><code>object_type\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0schema_name\u00a0 \u00a0 \u00a0object_name\u00a0 \u00a0 \u00a0permission_name<\/code><br \/>\n<code>----------------------- --------------- --------------- ---------------------------<\/code><br \/>\n<code>USER_TABLE\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Demo\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Table1\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT<\/code><br \/>\n<code>SQL_STORED_PROCEDURE\u00a0 \u00a0 Demo\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Procedure1\u00a0 \u00a0 \u00a0 EXECUTE<\/code><\/p>\n<p>The real value here, is that what if we need to verify a change in security works.<\/p>\n<p>The following example is similar to what I recently have done for our system (though a bit less derived!) Let\u2019s take User1, and put the security that it currently has into two roles, one that can execute the stored procedure, one that can select from the table. We first execute:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EXECUTE AS USER = 'User1';\r\n\r\nSELECT *\r\nINTO   #preChangeSecurity --Or save to a permanent table\r\nFROM   Utility.EffectiveSecurity;\r\n\r\nREVERT;<\/pre>\n<p>This saves off the current security, giving us the ability to compare User1\u2019s rights before the security change. Now, make your changes to the security:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">REVOKE SELECT ON Demo.Table1 FROM User1;\r\nREVOKE EXECUTE ON Demo.Procedure1 FROM User1;<\/pre>\n<p>Query from Utility.EffectiveSecurity as User1 and you will see no output as User1 has been stripped of all rights, other than the rights to select from the EffectiveSecurity view. Then execute the following:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">--create a role to access Table1 (and \u201caccidentally include Table2\u201d)\r\nCREATE ROLE Table1Reader;\r\nGRANT SELECT ON Demo.Table1 TO Table1Reader;\r\nGRANT SELECT ON Demo.Table2 TO Table1Reader;\r\n\r\n--create the role to access Procedure1\r\nCREATE ROLE Procedure1Executor;\r\nGRANT EXECUTE ON Demo.Procedure1 TO Procedure1Executor;\r\n\r\n--put User1 in the two roles\r\nALTER ROLE Table1Reader ADD MEMBER User1;\r\nALTER ROLE Procedure1Executor ADD MEMBER User1;<\/pre>\n<p>And now check to make sure you got the security right, in that it matches what User1 previously had for security:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EXECUTE AS USER = 'User1';\r\n\r\nSELECT CASE WHEN EffectiveSecurity.object_name IS NULL \r\n                     THEN 'DELETED'\r\n\t\t\tWHEN #preChangeSecurity.object_name IS NULL \r\n                     THEN 'NEW'\r\n\t\t\tELSE 'Same' END AS permission_disposition,\r\n\t   COALESCE(#preChangeSecurity.object_type, EffectiveSecurity.object_type) AS object_type,\r\n\t   COALESCE(#preChangeSecurity.schema_name, EffectiveSecurity.schema_name) AS schema_name,\r\n        COALESCE(#preChangeSecurity.object_name,    EffectiveSecurity.object_name) AS object_name,\r\n        COALESCE(#preChangeSecurity.permission_name, EffectiveSecurity.permission_name) \r\n                                                                              AS permission_name\r\nFROM   #preChangeSecurity \r\n\t FULL OUTER JOIN Utility.EffectiveSecurity\r\n\t\tON EffectiveSecurity.object_name = #preChangeSecurity.object_name\r\n\t\t   AND EffectiveSecurity.object_type = #preChangeSecurity.object_type\r\n\t\t   AND  EffectiveSecurity.permission_name = #preChangeSecurity.permission_name\r\n\t\t   AND EffectiveSecurity.schema_name = #preChangeSecurity.schema_name\r\nWHERE   EffectiveSecurity.object_name IS NULL \r\n   OR   #preChangeSecurity.object_name IS NULL;\r\n\r\nREVERT;<\/pre>\n<p>You can see that I added a new permission in the mix when I was creating my roles, so this code outputs:<\/p>\n<p><code>permission_disposition object_type\u00a0 \u00a0 \u00a0schema_name\u00a0 \u00a0 object_name\u00a0 \u00a0 \u00a0 \u00a0 \u00a0permission_name<\/code><br \/>\n<code>---------------------- --------------- -------------- ------------------- ----------------------<\/code><br \/>\n<code>NEW\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 USER_TABLE\u00a0 \u00a0 \u00a0 Demo\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Table2\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT<\/code><\/p>\n<p>Remove that right:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">REVOKE SELECT ON Demo.Table2 FROM Table1Reader;<\/pre>\n<p>Run the previous query again, and you will see that everything is the same in the current security configuration for User1 as it was before we started. So we have achieved the desired effect by removing rights from User1, and giving back rights through a couple of roles.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Security. Oh that most painful of topics. I discussed it a few months earlier when I discussed the need to give rights only through roles to users, so everything is the same in dev and prod except the users who are placed in each role (SQL Server Database Security And Source Control). As I was&#8230;&hellip;<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[68855],"coauthors":[19684],"class_list":["post-81007","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-sql-provision"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/81007","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=81007"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/81007\/revisions"}],"predecessor-version":[{"id":81016,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/81007\/revisions\/81016"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=81007"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=81007"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=81007"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=81007"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}