{"id":2082,"date":"2014-02-18T11:00:46","date_gmt":"2014-02-18T16:00:46","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2082"},"modified":"2014-11-13T13:15:48","modified_gmt":"2014-11-13T18:15:48","slug":"role-based-permission-delegation","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/","title":{"rendered":"Permission Delegation Done Right \u2013 Role Based Delegation"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nYesterday we looked at using roles to prevent the problem caused by cascading revokes<\/a> when delegating permission management using grantable grants. Today I would like to take this a step further and show you how to use roles for permission management without requiring grantable grants at all.\n<\/p>\n

Example of Role Based Permission Delegation<\/h3>\n

\nTo try out role based permission delegation we need to first create a setup similar to the one we used yesterday:\n<\/p>\n

\n[sql]\nCREATE LOGIN TestLogin1 WITH PASSWORD='********', CHECK_POLICY = OFF;
\nCREATE LOGIN TestLogin2 WITH PASSWORD='********', CHECK_POLICY = OFF;<\/p>\n

CREATE USER TestUser1 FOR LOGIN TestLogin1;
\nCREATE USER TestUser2 FOR LOGIN TestLogin2;<\/p>\n

CREATE ROLE TestRole1;
\nGO
\nCREATE SCHEMA TestSchema1;
\nGO
\nCREATE TABLE TestSchema1.tst(id INT);
\nINSERT INTO TestSchema1.tst VALUES(42);
\n[\/sql]\n<\/p><\/div>\n

\nWe again have two users, a role and a table. But this time we are not going to add TestUser1<\/span> to the role and grant the grantable SELECT<\/span> permission to the role. Instead we are going to grant just the SELECT<\/span> permission to the role, not grantable. We will also grant ALTER<\/span> on the role to TestUser1<\/span>:\n<\/p>\n

\n[sql]\nGRANT SELECT ON OBJECT::TestSchema1.tst TO TestRole1;
\nGRANT ALTER ON ROLE::TestRole1 TO TestUser1;
\n[\/sql]\n<\/div>\n

\nThis does two things: First, any member of TestRole1<\/span> has now the SELECT<\/span> privilege on our table. Second, because of the ALTER<\/span> permission, TestUser1<\/span> can now add new members to the role and also remove current members from it. Let's see that in action:\n<\/p>\n

\n\"Role<\/a>\n<\/p>\n

\nTestUser1<\/span> was able to add TestUser2<\/span> to the role. With that TestUser2<\/span> can now successfully access the table.\n<\/p>\n

\nJust as easy, TestUser1<\/span> can remove members from the role:\n<\/p>\n

\n\"Role<\/a>\n<\/p>\n

\nRemoving TestUser2<\/span> form TestRole1<\/span> takes the SELECT<\/span> privilege on the table away again.\n<\/p>\n

This setup has two main advantages. The first is that revoking the ALTER<\/span> permission from TestUser1<\/span> does not in any way mess with the current role members:\n<\/p>\n

\n\"REVOKE<\/a>\n<\/p>\n

\nIn this example TestUser1<\/span> first added TestUser2<\/span> back to the role. Then the ALTER<\/span> privilege was revoked from TestUser1<\/span>. In spite of that, TestUser2<\/span> is still a member of the role and still has access to the table.\n<\/p>\n

The second advantage is that using roles makes managing groups of permissions a lot simpler. A single permission by itself is seldom adequate. A principal usually needs access to several securables and might need different types of access privileges for each securable. Instead of granting several grantable permissions to a delegate and then have the delegate grant all those permissions to every principal in need, we can just select the required permissions once and grant them to a role. From then on every principal that needs those permissions can simply be added to the role.\n<\/p>\n

Summary<\/h3>\n

\nToday's and yesterday's post showed two different ways of using roles to allow for permission management delegation. Yesterday we granted a grantable permission to the role and used the role as a layer of separation between the delegate and the grantees. Today's method did not rely on grantable grants at all but instead showed how roles can be used to simplify permission management and allow for easy delegation. Which method is the appropriate one to use on depends on the situation, but today's solution is my favorite. It reduces permission clutter and permission duplication. Instead of granting the same set of permissions to many grantees, we need to grant those permissions only once - to the role. That alone makes permission management - and its delegation - a lot simpler.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Role base permission management can make permission management and permission management delegation a lot simpler. Read on to see how roles can be used to make grantable grants obsolete.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[5,34],"tags":[88,50,98,100,38,58,15],"class_list":["post-2082","post","type-post","status-publish","format-standard","hentry","category-general","category-security","tag-grant","tag-permission","tag-revoke","tag-revoke-cascade","tag-security-2","tag-security-management","tag-sql-server"],"acf":[],"yoast_head":"\nPermission Delegation Done Right \u2013 Role Based Delegation - sqlity.net<\/title>\n<meta name=\"description\" content=\"Role based permission delegation can make grantable grants obsolete. Using roles makes permission management and with it its delegation a lot simpler.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Permission Delegation Done Right \u2013 Role Based Delegation - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Role based permission delegation can make grantable grants obsolete. Using roles makes permission management and with it its delegation a lot simpler.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-02-18T16:00:46+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:15:48+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Role_Based_Permission_Delegation_in_Action.jpg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2082\\\/role-based-permission-delegation\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2082\\\/role-based-permission-delegation\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Permission Delegation Done Right \u2013 Role Based Delegation\",\"datePublished\":\"2014-02-18T16:00:46+00:00\",\"dateModified\":\"2014-11-13T18:15:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2082\\\/role-based-permission-delegation\\\/\"},\"wordCount\":631,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2082\\\/role-based-permission-delegation\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Role_Based_Permission_Delegation_in_Action.jpg\",\"keywords\":[\"GRANT\",\"Permission\",\"REVOKE\",\"REVOKE CASCADE\",\"security\",\"security management\",\"SQL Server\"],\"articleSection\":[\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2082\\\/role-based-permission-delegation\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2082\\\/role-based-permission-delegation\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2082\\\/role-based-permission-delegation\\\/\",\"name\":\"Permission Delegation Done Right \u2013 Role Based Delegation - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2082\\\/role-based-permission-delegation\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2082\\\/role-based-permission-delegation\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Role_Based_Permission_Delegation_in_Action.jpg\",\"datePublished\":\"2014-02-18T16:00:46+00:00\",\"dateModified\":\"2014-11-13T18:15:48+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Role based permission delegation can make grantable grants obsolete. Using roles makes permission management and with it its delegation a lot simpler.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2082\\\/role-based-permission-delegation\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2082\\\/role-based-permission-delegation\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2082\\\/role-based-permission-delegation\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Role_Based_Permission_Delegation_in_Action.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Role_Based_Permission_Delegation_in_Action.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2082\\\/role-based-permission-delegation\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Permission Delegation Done Right \u2013 Role Based Delegation\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Permission Delegation Done Right \u2013 Role Based Delegation - sqlity.net","description":"Role based permission delegation can make grantable grants obsolete. Using roles makes permission management and with it its delegation a lot simpler.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/","og_locale":"en_US","og_type":"article","og_title":"Permission Delegation Done Right \u2013 Role Based Delegation - sqlity.net","og_description":"Role based permission delegation can make grantable grants obsolete. Using roles makes permission management and with it its delegation a lot simpler.","og_url":"https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-02-18T16:00:46+00:00","article_modified_time":"2014-11-13T18:15:48+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Role_Based_Permission_Delegation_in_Action.jpg","type":"","width":"","height":""}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Permission Delegation Done Right \u2013 Role Based Delegation","datePublished":"2014-02-18T16:00:46+00:00","dateModified":"2014-11-13T18:15:48+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/"},"wordCount":631,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Role_Based_Permission_Delegation_in_Action.jpg","keywords":["GRANT","Permission","REVOKE","REVOKE CASCADE","security","security management","SQL Server"],"articleSection":["General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/","url":"https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/","name":"Permission Delegation Done Right \u2013 Role Based Delegation - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Role_Based_Permission_Delegation_in_Action.jpg","datePublished":"2014-02-18T16:00:46+00:00","dateModified":"2014-11-13T18:15:48+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Role based permission delegation can make grantable grants obsolete. Using roles makes permission management and with it its delegation a lot simpler.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Role_Based_Permission_Delegation_in_Action.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Role_Based_Permission_Delegation_in_Action.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2082\/role-based-permission-delegation\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Permission Delegation Done Right \u2013 Role Based Delegation"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-xA","jetpack-related-posts":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2082","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=2082"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2082\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2082"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2082"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2082"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}