{"id":2169,"date":"2014-03-03T11:00:31","date_gmt":"2014-03-03T16:00:31","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2169"},"modified":"2014-11-13T13:13:41","modified_gmt":"2014-11-13T18:13:41","slug":"role-owner","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2169\/role-owner\/","title":{"rendered":"The Unexpected Security Implications for Role Owners"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nOn Friday, I wrote about the ownership concept. I demonstrated how to change the owner on a securable and that the owner has unrestricted access to its securables<\/a>. One of the securable types that allow an owner to be specified is the database role. As with any other securable, the owner of a role has unrestricted access to that role. However, what exactly does that mean, and are there any hidden implications that you might not expect at first?\n<\/p>\n

Role Ownership Example<\/h3>\n

\nTo see what the security implications are, we need to first create a database role. To start out, create two database users with this statement batch:\n<\/p>\n

\n[sql]\nCREATE LOGIN TestLogin1 WITH PASSWORD='********',CHECK_POLICY=OFF;
\nCREATE USER TestUser1 FROM LOGIN TestLogin1;
\nCREATE LOGIN TestLogin2 WITH PASSWORD='********',CHECK_POLICY=OFF;
\nCREATE USER TestUser2 FROM LOGIN TestLogin2;
\n[\/sql]\n<\/div>\n

\nWith the users in place, executing the following two statements will create the role TestRole1<\/span>, set TestUser1<\/span> as the owner and then add TestUser2<\/span> as role member to it.\n<\/p>\n

\n[sql]\nCREATE ROLE TestRole1 AUTHORIZATION TestUser1;
\nALTER ROLE TestRole1 ADD MEMBER TestUser2;
\n[\/sql]\n<\/div>\n

\nFinally, we also need a securable. A schema and table should do:\n<\/p>\n

\n[sql]\nCREATE SCHEMA TestSchema1;
\nGO
\nCREATE TABLE TestSchema1.tst(id INT);
\nGRANT SELECT ON SCHEMA::TestSchema1 TO TestRole1;
\n[\/sql]\n<\/div>\n

\nJust to confirm that we did not cause any unexpected ownership changes on those two objects, let us execute this query:\n<\/p>\n

\n[sql]\nSELECT T.name AS table_name,USER_NAME(T.principal_id) AS table_owner_name,s.name AS schema_name,USER_NAME(S.principal_id) AS schema_owner_name
\nFROM sys.tables AS T
\nJOIN sys.schemas AS S
\n ON T.schema_id = S.schema_id
\nWHERE T.name = 'tst';
\n[\/sql]\n<\/div>\n

\nThat query produces this result, showing that the schema and the table are both owned by dbo.\n<\/p>\n

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

\nRemember, a NULL<\/span> principal_id<\/span> on the table means that the table is owned by the schema owner<\/a>.\n<\/p>\n

\nNow let use actually look into the implied permissions that TestUser1<\/span> might have attained through the ownership of TestRole1<\/span>. First we should check if TestUser1<\/span> is now considered a member of the role. We know, a direct membership check would come back negative, as TestUser1<\/span> was never actually added to the role. However, the permissions granted to the role might transfer to TestUser1<\/span> like it would for a real member. Let's see:\n<\/p>\n

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

\nThat is not the case. The role owner does not magically inherit permissions that were granted to the role. For the role's permissions to apply, the owner would also have to be a regular role member.\n<\/p>\n

\nNext let us check, if the owner of the role attains any permissions directly on the role members. For that check we can use this query:\n<\/p>\n

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

\nAnd again, no ugly surprises. TestUser1<\/span> does not have any permission on the role member TestUser2<\/span>. This is encouraging. No unexpectedly implicated permission due to the concept of a role owner.\n<\/p>\n

\nHowever, there is one thing that you do need to be aware of. The owner has unrestricted access to the role itself:\n<\/p>\n

\ntitle=\"TestUser1<\/a>\n<\/p>\n

\nUnrestricted access includes in particular the ability to add new members to the role. With that, there is nothing stopping role owners from adding themselves to their role like this:\n<\/p>\n

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

\nWhile the example in this post is based on a database role, the same behavior can be observed with server roles.\n<\/p>\n

Summary<\/h3>\n

\nThe permissions granted to a role do not automatically transfer to the role owner. However, role owners do have the ability to add themselves as member to their role and attaining the role's permission that way. Make sure to keep this in mind when selecting role owners and, more importantly, when auditing your permissions.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

In SQL Server a server or database role can have an owner specified. Are you aware of the security implications of specifying a role owner? Read on to find out now.<\/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,99],"tags":[57,125,50,38,58,55],"class_list":["post-2169","post","type-post","status-publish","format-standard","hentry","category-general","category-security","category-security-pitfalls","tag-database-roles","tag-owner","tag-permission","tag-security-2","tag-security-management","tag-server-roles"],"acf":[],"yoast_head":"\nThe Unexpected Security Implications for Role Owners - sqlity.net<\/title>\n<meta name=\"description\" content=\"In SQL Server a server or database role can have an owner specified. Are you aware of the security implications of specifying a role owner? Find out now.\" \/>\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\/2169\/role-owner\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Unexpected Security Implications for Role Owners - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"In SQL Server a server or database role can have an owner specified. Are you aware of the security implications of specifying a role owner? Find out now.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2169\/role-owner\/\" \/>\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-03-03T16:00:31+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:13:41+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/Table_and_Schema_both_owned_by_dbo.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\\\/2169\\\/role-owner\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2169\\\/role-owner\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"The Unexpected Security Implications for Role Owners\",\"datePublished\":\"2014-03-03T16:00:31+00:00\",\"dateModified\":\"2014-11-13T18:13:41+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2169\\\/role-owner\\\/\"},\"wordCount\":648,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2169\\\/role-owner\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/Table_and_Schema_both_owned_by_dbo.jpg\",\"keywords\":[\"database roles\",\"owner\",\"Permission\",\"security\",\"security management\",\"server roles\"],\"articleSection\":[\"General\",\"Security\",\"Security Pitfalls\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2169\\\/role-owner\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2169\\\/role-owner\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2169\\\/role-owner\\\/\",\"name\":\"The Unexpected Security Implications for Role Owners - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2169\\\/role-owner\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2169\\\/role-owner\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/Table_and_Schema_both_owned_by_dbo.jpg\",\"datePublished\":\"2014-03-03T16:00:31+00:00\",\"dateModified\":\"2014-11-13T18:13:41+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"In SQL Server a server or database role can have an owner specified. Are you aware of the security implications of specifying a role owner? Find out now.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2169\\\/role-owner\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2169\\\/role-owner\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2169\\\/role-owner\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/Table_and_Schema_both_owned_by_dbo.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/Table_and_Schema_both_owned_by_dbo.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2169\\\/role-owner\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Unexpected Security Implications for Role Owners\"}]},{\"@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":"The Unexpected Security Implications for Role Owners - sqlity.net","description":"In SQL Server a server or database role can have an owner specified. Are you aware of the security implications of specifying a role owner? Find out now.","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\/2169\/role-owner\/","og_locale":"en_US","og_type":"article","og_title":"The Unexpected Security Implications for Role Owners - sqlity.net","og_description":"In SQL Server a server or database role can have an owner specified. Are you aware of the security implications of specifying a role owner? Find out now.","og_url":"https:\/\/sqlity.net\/en\/2169\/role-owner\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-03-03T16:00:31+00:00","article_modified_time":"2014-11-13T18:13:41+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/Table_and_Schema_both_owned_by_dbo.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\/2169\/role-owner\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2169\/role-owner\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"The Unexpected Security Implications for Role Owners","datePublished":"2014-03-03T16:00:31+00:00","dateModified":"2014-11-13T18:13:41+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2169\/role-owner\/"},"wordCount":648,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2169\/role-owner\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/Table_and_Schema_both_owned_by_dbo.jpg","keywords":["database roles","owner","Permission","security","security management","server roles"],"articleSection":["General","Security","Security Pitfalls"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2169\/role-owner\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2169\/role-owner\/","url":"https:\/\/sqlity.net\/en\/2169\/role-owner\/","name":"The Unexpected Security Implications for Role Owners - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2169\/role-owner\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2169\/role-owner\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/Table_and_Schema_both_owned_by_dbo.jpg","datePublished":"2014-03-03T16:00:31+00:00","dateModified":"2014-11-13T18:13:41+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"In SQL Server a server or database role can have an owner specified. Are you aware of the security implications of specifying a role owner? Find out now.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2169\/role-owner\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2169\/role-owner\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2169\/role-owner\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/Table_and_Schema_both_owned_by_dbo.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/Table_and_Schema_both_owned_by_dbo.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2169\/role-owner\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"The Unexpected Security Implications for Role Owners"}]},{"@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_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-yZ","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2169","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=2169"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2169\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2169"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2169"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2169"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}