{"id":5093,"date":"2017-12-07T11:56:01","date_gmt":"2017-12-07T10:56:01","guid":{"rendered":"http:\/\/andreas-wolter.com\/?p=5093"},"modified":"2017-12-19T15:43:24","modified_gmt":"2017-12-19T14:43:24","slug":"1712-separation-of-duties-sqlserver-role-based-security","status":"publish","type":"post","link":"https:\/\/andreas-wolter.com\/en\/1712-separation-of-duties-sqlserver-role-based-security\/","title":{"rendered":"Separation of Duties (SoD) and role-based security conception in SQL Server"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_heading-5342db0558182f3f2328fdfefea12e0a\">\n#top .av-special-heading.av-av_heading-5342db0558182f3f2328fdfefea12e0a{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-av_heading-5342db0558182f3f2328fdfefea12e0a .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-av_heading-5342db0558182f3f2328fdfefea12e0a .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-av_heading-5342db0558182f3f2328fdfefea12e0a 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\"  >Separation of Duties (SoD) and role-based security conception in SQL Server<\/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>Writer: Andreas Wolter<\/p>\n<p>Published: December 7<sup>th<\/sup> 2017<\/p>\n<p>Applies to: SQL Server 2016-2017<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_hr-1deae294de86921cadf9622fb2da0879\">\n#top .hr.hr-invisible.av-av_hr-1deae294de86921cadf9622fb2da0879{\nheight:25px;\n}\n<\/style>\n<div  class='hr av-av_hr-1deae294de86921cadf9622fb2da0879 hr-invisible  avia-builder-el-2  el_after_av_textblock  el_before_av_textblock '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/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\" ><h5><span style=\"color: #333333;\">Introduction<\/span><\/h5>\n<p>With the upcoming implementation of the European General Data Protection Regulation (GDPR) in May 2018, having a security concept in place is essentially required by law. Microsoft SQL Server, just like other database systems, carries the main asset to protect: the data itself. Therefore, it is time for an article from a more strategic angle in terms of how I go about protecting SQL Server and its data.<!--more--><\/p>\n<p>There are several known security principles in IT Technology when designing for security. The most common ones, and those are the ones that I keep in mind when designing for security, are probably the following:<\/p>\n<ul>\n<li><b><strong>Least Privilege<\/strong><\/b><\/li>\n<li><b><strong><strong>Separation \/ Segregation of Duties<\/strong><\/strong><\/b><\/li>\n<li>Reconstruction of Events<\/li>\n<li>Delegation of Authority<\/li>\n<li>Reality Checks<\/li>\n<li>External Inspection<\/li>\n<li>Well-formed Transactions<\/li>\n<li>(Continuity of Operation)<\/li>\n<\/ul>\n<p>The first two can be seen very closely related and complementing each other when it comes to implementation and will be the main subject of this paper.<\/p>\n<\/div><\/section>\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-4  el_after_av_textblock  el_before_av_textblock '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/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\" ><h5><span style=\"color: #333333;\">Separation of Duties and Least Privilege Security principles<\/span><\/h5>\n<p>The principle of \u201c<strong>Least Privilege<\/strong>\u201d essentially means that users should not have more privileges than needed to complete their daily task. To secure data and the system in general from potential damage, it is essential to <strong>identify a comprehensive hierarchy of users and separate duties<\/strong> and to provide each individual with his or her own user ID and with permissions as minimal as possible to complete his or her daily task.<br \/>\nThis is then also called \u201c<strong>Separation of Duties<\/strong>\u201d or<strong> \u201cSegregation of Duties\u201d<\/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_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-6  el_after_av_textblock  el_before_av_textblock '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/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\" ><h5><span style=\"color: #333333;\">DAC vs MAC<\/span><\/h5>\n<p>In the Microsoft SQL Server domain, a <strong>role-based security concept<\/strong> is generally being used for implementing the above. It enforces security using the GRANT\/DENY-system of SQL Server, which is also known as <strong><em>Discretionary Access Control (DAC)<\/em><\/strong><\/p>\n<p>Another common concept, especially used in government-agencies, is <strong>label-based access control (LBAC)\/-security<\/strong>.<\/p>\n<p>Label-based security can be implemented by the use of row- and cell-level security using technologies like Always Encrypted and Row Level Security (RLS) within SQL Server. It allows for a more granular level of control than <em>Discretionary Access Control<\/em> and works by classifying data, also called <strong><em>Mandatory Access Control (MAC)<\/em><\/strong>.<\/p>\n<p>Label-based Security will not be discussed further in this paper. I recommend the Whitepaper <a href=\"http:\/\/download.microsoft.com\/download\/8\/8\/0\/880F282A-AA4E-4351-83C0-DFFA3B56A19E\/SQL_Server_RLS-CLS_White_paper.docx\" target=\"_blank\" rel=\"noopener\">Implementing Row- and Cell-Level Security in Classified Databases<\/a> as a start. Though it is based on SQL Server 2008 it gives a good idea on the concept itself.<\/p>\n<\/div><\/section>\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_textblock  el_before_av_textblock '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/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\" ><h5><span style=\"color: #333333;\">Role-based security<\/span><\/h5>\n<p>Coming back to role-based security, it should be noted that nobody is exempt from the principle of \u201cleast privilege\u201d. It does not only apply to the end users of the database application but includes administrators, support-personnel and even developers.<br \/>\nIt is in my view easier to develop a secure database if the developers and DBAs use only an account that gives them sufficient privileges for the <strong>role<\/strong> they are taking on, referred to as using the \u201c<strong>least-privileged user account<\/strong>\u201d.<br \/>\nIn other words, \u201c<strong>role-based security<\/strong>\u201d does not only mean:<\/p>\n<p style=\"padding-left: 30px;\"><em>User = [\u201chas\u201d] Role<\/em><\/p>\n<p>but rather:<\/p>\n<p style=\"padding-left: 30px;\"><em>User1 + TaskA = [\u201cperforms\u201d] <\/em>\u00a0<em>RoleA<\/em><\/p>\n<p>And<\/p>\n<p style=\"padding-left: 30px;\"><em>User1 + TaskB = [\u201cperforms\u201d] RoleB<\/em><\/p>\n<p>Sometimes it helps to visualize:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-a1f2fa5d9403a05842409d143f7aeb60\">\n.avia-image-container.av-av_image-a1f2fa5d9403a05842409d143f7aeb60 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-a1f2fa5d9403a05842409d143f7aeb60 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-a1f2fa5d9403a05842409d143f7aeb60 av-styling- avia-align-center  avia-builder-el-10  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-5106 avia-img-lazy-loading-not-5106 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1712_Role_based_Security_new.png\" alt='' title='1712_Role_based_Security_new'  height=\"240\" width=\"450\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1712_Role_based_Security_new.png 450w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1712_Role_based_Security_new-300x160.png 300w\" sizes=\"(max-width: 450px) 100vw, 450px\" \/><\/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>I.e. DBAs should only use the <em>SysAdmin<\/em> role when its privileges are essential for the action they need to take. Developers who have access to privileged accounts should only use the more powerful accounts when they really need one of its privileges, and should use a normal account with fewer privileges for their regular work.<br \/>\nIt may feel more complicated at first, because people will be forced out of the \u201ccomfort zone\u201d of having all permissions, but after a period of adaption the outcome will be a far more secure system.<\/p>\n<p>By using appropriately restricted accounts during development rather than \u201c<em>root<\/em>\u201d\/\u201d<em>superuser<\/em>\u201d powers, a developer will become aware of potential security problems early in stage and also accidental deletion or alteration of certain objects will be prevented.<\/p>\n<p>Now for a successful <strong>real-world<\/strong> implementation, there is one more principle: <strong>KISS: Keep it simple, stupid<\/strong>:<\/p>\n<h6><span style=\"color: #333333;\">\u201creality-check\u201d<\/span><\/h6>\n<p>If the User \/ Role-separation is being followed to the extreme, it is like with the \u201c6<sup>th<\/sup> normal form\u201d: <strong>great advantages in theory, but totally impractically<\/strong>. Having the user switch to a different Role by using a second, third and so on login to create a table while his regular job maybe altering procedure code, will not make sense usually.<br \/>\nIn fact the cases where one can really \u201clive\u201d this principle should be restricted to the most sensitive tasks.<br \/>\nThe best example is a \u201c<strong>SecurityDeployment-Role<\/strong>\u201d vs the regular DBA. This is crucial for a system that needs to be <strong>tamper-proof<\/strong> for example.<br \/>\nAnother common example is the Deployment-process from Development to Test\/to Production, which is only run at certain times. Or in a Datawarehouse-system the ETL-process: when done right, security-wise, it will only succeed when run via the intended Job\/Proxy-Account, and not when a regular user or even developer is executing it, because of the different areas accessed by it.<\/p>\n<p>With that comes an <strong>organizational challenge<\/strong>: how do you ensure adherence of your rules?<br \/>\nThe answer to that lies in the big topic <strong>compliance<\/strong>, which goes beyond the scope of this paper, although I will give some hints at certain points.<\/p>\n<p>Now with all this in the back of your mind, the following ideas of mine, which I am sharing here, will hopefully make more sense.<\/p>\n<\/div><\/section>\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-12  el_after_av_textblock  el_before_av_textblock '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/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\" ><h5><span style=\"color: #333333;\">Role Concept<\/span><\/h5>\n<p>To control security in a SQL Server environment, the use of roles is an ultimate principle. <strong>SQL Server<\/strong> provides <strong>Server roles and database roles<\/strong> which can be customized for ones needs by the use of the extensive permission system that came with SQL Server 2005.<\/p>\n<p>When I work with customers, one of the first steps is to identify <strong>processes<\/strong>. Yes, processes, not yet roles.<br \/>\nBecause the roles are just a means of enabling processes and users to do their work. (Obviously a \u201cprocess\u201d can be a technical user as well as a logical definition of a <strong>workflow<\/strong>. When necessary I will define the use case more accurately.)<\/p>\n<p>Over the years of working in this area, several roles have emerged as typically needed.<br \/>\nHere are a few <strong>examples<\/strong> that are often in use:<\/p>\n<p><strong>Development\/ \u201cDeveloper<\/strong>\u201d<\/p>\n<ul>\n<li>Develops the database objects, Analysis Services cubes, Reporting Services Reports, Integration Services packages and other<\/li>\n<\/ul>\n<p><strong>Deployment<\/strong><\/p>\n<ul>\n<li>Transfers releases from one environment to the other<\/li>\n<\/ul>\n<p><strong>Application\/Project Support<\/strong>, \u201c<strong>App-Support\u201d<\/strong><\/p>\n<ul>\n<li>Conduct support for one project at a time. One project can be one or more applications, mostly one.<\/li>\n<\/ul>\n<p><strong>Monitoring\/Operations<\/strong><\/p>\n<ul>\n<li>Support on Server-Level.<\/li>\n<\/ul>\n<p><strong>ETL-Processes<\/strong><\/p>\n<ul>\n<li>Unattended routines for import &#038; export of data, especially in Datawarehouse-systems<\/li>\n<\/ul>\n<p><strong>Auditor<\/strong><\/p>\n<ul>\n<li>Solely for conducting Audits on the whole Server. Will not change anything anywhere.<\/li>\n<\/ul>\n<p>And you will always have:<\/p>\n<p><strong>Database Administrator<\/strong><\/p>\n<ul>\n<li>Has extensive permissions but no (daily) security related duties<\/li>\n<\/ul>\n<p><strong>System Administrators<\/strong>\/ \u201c<strong>Sysadmins<\/strong>\u201d<\/p>\n<ul>\n<li>have full permissions on the systems<\/li>\n<\/ul>\n<p><strong>Enduser<\/strong><\/p>\n<ul>\n<li>only access the data via Reports, Excel or other Frontends<\/li>\n<\/ul>\n<p>This may sound simple, and SQL Server has a huge set of permissions, but the devil is in the details.<\/p>\n<\/div><\/section>\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-14  el_after_av_textblock  el_before_av_textblock '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/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\" ><h6>Role examples, obstacles and hints to solutions<\/h6>\n<p>Following are some examples of roles and the obstacles that you will meet when trying to secure them.<\/p>\n<\/div><\/section>\r\n\r\n<div  class='tabcontainer av-av_tab_container-0bacd910096d11a3cec6dfb538798966 top_tab  avia-builder-el-16  el_after_av_textblock  el_before_av_textblock ' role='tablist'>\r\n<section class='av_tab_section av_tab_section av-av_tab-2e7d67f529121ab6a510ee6d6e71cbd9'  itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='tab active_tab' role='tab' tabindex='0' data-fake-id='#tab-id-1' aria-controls='tab-id-1-content'  itemprop=\"headline\" >Monitoring<\/div><div id='tab-id-1-content' class='tab_content active_tab_content' aria-hidden=\"false\"><div class='tab_inner_content invers-color'  itemprop=\"text\" ><p>How can you enable certain roles (i.e. Developers) to get Performance Data from Production or Test-Environment without granting excessive permissions? For Extended Event sessions you have ALTER TRACE, but that allows access to data that may be sensitive. And how about Performance Monitor which is on Windows Level? There are 2 possible roles: <em>Performance Monitor Users<\/em> and <em>Performance Log Users<\/em>.<\/p>\n<p>A possible solution that I have successfully implemented is to have a set of PerfMon Data Collector Sets with Performance Counters and Extended Event Traces prepared to be started via special jobs. Access to the Result-Files has to be implemented on an Windows File Share basis.<br \/>\nSpeaking of files: don\u2019t forget the output-files of SQL Agent Jobs which might contain valuable information for troubleshooting.<\/p>\n<p>For viewing Server State data based on the commonly used DMVs (sys.dm_exec_requests, sys.dm_exec_query_stats, sys.dm_os_memory_clerks and many more) there is a necessity to filter those to the appropriate databases by project.<br \/>\nIn order to accomplish that one can implement a set of special stored procedures, which naturally need to be properly signed with a certificate to access data outside the current database scope and prevent a path for <em>privilege elevation<\/em>. The outcome can also be stored in a custom Database. By <em>Sarpedon Quality Lab<\/em> Methology the code and data for these eventually resides in different Databases: SQL_Analysis_Code, SQL_Analysis_Data.<\/p>\n<p>An alternative can be the use of a Third-Party Monitoring Software with a built-in role-concept that a professional tool like <a href=\"https:\/\/www.sentryone.com\/platform\/sql-server-performance-monitoring\" target=\"_blank\" rel=\"noopener\">SQLSentry\u00ae<\/a> offers.<\/p>\n<\/div><\/div><\/section>\r\n<section class='av_tab_section av_tab_section av-av_tab-9d696ad9aceae4a13fa15fc5601f7ee6'  itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='tab' role='tab' tabindex='0' data-fake-id='#tab-id-2' aria-controls='tab-id-2-content'  itemprop=\"headline\" >SQL Agent Jobs<\/div><div id='tab-id-2-content' class='tab_content' aria-hidden=\"true\"><div class='tab_inner_content invers-color'  itemprop=\"text\" ><p>Any SQL Server will contain Jobs for maintenance, but also very frequently for scheduled processes that are connected to an application. That can be ETL (Import, Export) or other batch-processes.<\/p>\n<p>One thing is, to transfer those jobs from development server to production, which will be covered in the next part, another thing is who gets permissions to start them manually, to check the history for troubleshooting-purposes or others and maybe change them when necessary. The built-in Security system for SQL Server Agent is very limited and not really flexible. For example:<\/p>\n<ul>\n<li>a job can have exactly one owner (Cannot be a Windows Group Login\/role although a Group can have permissions)<\/li>\n<li>one can either read all history or the history of jobs owned (again: but only a single Login can own a job)<\/li>\n<li>one can either start all jobs or the ones owned.<\/li>\n<\/ul>\n<p>Here again is a case where a <strong>custom security framework<\/strong> that involves some coding is needed to allow for more freedom on who can do what.<\/p>\n<\/div><\/div><\/section>\r\n<section class='av_tab_section av_tab_section av-av_tab-7bac9ef19e15bc3a9fc4dd044e965b71'  itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='tab' role='tab' tabindex='0' data-fake-id='#tab-id-3' aria-controls='tab-id-3-content'  itemprop=\"headline\" >Deployment<\/div><div id='tab-id-3-content' class='tab_content' aria-hidden=\"true\"><div class='tab_inner_content invers-color'  itemprop=\"text\" ><p>The problem with the Deployment-process is not a \u201cCREATE TABLE\/PROCEDURE\u201d etc. Those permissions can easily be granted on Database-level. It does get more complicated in cases of multi-tenant-databases, but cases of different permissions on different schemas are certainly an absolute exception. From my experience, the use of schemas as security-boundaries itself is still rare (more on that here: <a href=\"https:\/\/andreas-wolter.com\/en\/schema-design-for-sql-server-recommendations-for-schema-design-with-security-in-mind\/\">Schema-design for SQL Server: recommendations for Schema design with security in mind<\/a>). Having to use a combination of Database-Level-Permissions to use Schema-Level Permissions certainly does not help, but that is a rather minor issue.<\/p>\n<p>The real problems arise when Developers create Logins, Databases and other Server-Level objects, especially security-related ones. You can grant the necessary permissions like ALTER ANY LOGIN, ALTER ANY SERVER ROLE and CREATE SERVER ROLE to the Deployment-Role, but you will need an organizational process to make sure that only validated commands of this kind are run at the Production Server, and nobody hides any \u201cbackdoor-admin\u201d within a huge ALTER TABLE-Script.<\/p>\n<p>On Database-Level, the <em>db_owner<\/em>-membership is necessary to edit role memberships for <u>built-in roles<\/u> (like <em>db_datareader<\/em>). This may not be acceptable for your security-level. If an application uses custom, user-defined roles, membership in the roles <em>db_accessadmin<\/em> and <em>db_securityadmin<\/em> (for granting permissions) can be used. The experienced Security specialist will realize that this bears a <strong>risk of privilege elevation<\/strong> though. So again, you cannot blindly trust the permission set and hence need a custom process around it.<\/p>\n<p>Now another thing on SQL Agent Jobs in msdb: You need to be aware that at Deployment\/Creation time, Jobs will be owned by the very creator. But mostly you will want Jobs to be able to be started by a different Role (like a real Person that has a \u201cSupport\u201d-Role).<br \/>\nThat means the Owner needs to be changed after creation. Steps with access to subsystems like CMD have to be set to use the appropriate proxy account.<br \/>\nAnd who will have permissions to do that? To secure this routine again one can use signed stored procedures to avoid tampering.<\/p>\n<p><em>By now, the persistent reader will have realized that truly securing a system is real work\u2026 \ud83d\ude09<\/em><\/p>\n<\/div><\/div><\/section>\r\n<section class='av_tab_section av_tab_section av-av_tab-e5030455c0b33bf7810f60ca5c9ddd9d'  itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='tab' role='tab' tabindex='0' data-fake-id='#tab-id-4' aria-controls='tab-id-4-content'  itemprop=\"headline\" >Maintenance<\/div><div id='tab-id-4-content' class='tab_content' aria-hidden=\"true\"><div class='tab_inner_content invers-color'  itemprop=\"text\" ><p>Maintenance naturally involves a lot of power on a system. You won\u2019t get away with less than sysadmin for that, unless you go the extra mile to grant permissions on each database. I.e. you need ALTER TABLE to REBUILD\/REGORGANIZE Indexes (and ALTER ANY CONNECTION for the \u201cABORT_AFTER_WAIT = BLOCKERS\u201d \u2013 option) and UPDATE STATISTICS. For DBCC CHECKDB, db_owner is the minimum though. Then you will think about cleaning up in system tables and output files on OS-levels, and come to the conclusion that using <strong>sysadmin via SQL Agent<\/strong> is mostly acceptable.<\/p>\n<\/div><\/div><\/section>\r\n<section class='av_tab_section av_tab_section av-av_tab-c39dd15255367a6edfd436e2d0547eea'  itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='tab' role='tab' tabindex='0' data-fake-id='#tab-id-5' aria-controls='tab-id-5-content'  itemprop=\"headline\" >Database Administrator<\/div><div id='tab-id-5-content' class='tab_content' aria-hidden=\"true\"><div class='tab_inner_content invers-color'  itemprop=\"text\" ><p>To control Database administrators, a strict separation from \u201c<strong>Security Administrators<\/strong>\u201d has to be followed.<br \/>\nUnfortunately the CONTROL SERVER command cannot be considered completely safe at the time of this writing (SQL Server 2017).<\/p>\n<p>In <strong>SQL Azure Database<\/strong> the concept is slightly different. A <strong>dbmanager for example does not necessarily own all databases. For creating Logins there is the loginmanager-role which can be granted separately.<\/strong><\/p>\n<p>Also, it may be necessary to encrypt the content of certain sensitive tables in the database. For that means <strong><em>Always Encrypted<\/em><\/strong> has the ability to keep the decryption key completely outside SQL Server and is a good example of separation between those who own the data (and can read it) and those who manage the data (but should not be able to read it).<\/p>\n<\/div><\/div><\/section>\r\n<section class='av_tab_section av_tab_section av-av_tab-54a1c1ff7cd5d53d88de6b984e49a363'  itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='tab' role='tab' tabindex='0' data-fake-id='#tab-id-6' aria-controls='tab-id-6-content'  itemprop=\"headline\" >Hotfixing<\/div><div id='tab-id-6-content' class='tab_content' aria-hidden=\"true\"><div class='tab_inner_content invers-color'  itemprop=\"text\" ><p>Now whether \u201cHotfixing\u201d is considered a valid use-case certainly depends on the strictness of one\u2019s development lifecycle. I have found this exception to be a useful one.<\/p>\n<p>The challenge here is to grant very sensitive permissions with 2 conditions:<\/p>\n<ul>\n<li>only after clearance by a superior<\/li>\n<li>only for a limited time.<\/li>\n<\/ul>\n<p>This a nice use-case for a <strong><em>time-based permission system<\/em><\/strong> that can be implemented with custom code in SQL Server.<\/p>\n<p>Besides that, Auditing is absolutely crucial here because of the high permissions involved. &#8211; <strong><em>Auditing<\/em><\/strong> is a built-in Feature of SQL Server, based on Extended Events infrastructure. In fact, for certain cases one will have to use <strong><em>Extended Events <\/em><\/strong>additionally.<\/p>\n<\/div><\/div><\/section>\r\n<section class='av_tab_section av_tab_section av-av_tab-046f1ddb097203fedefa1dfa25b62325'  itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='tab' role='tab' tabindex='0' data-fake-id='#tab-id-7' aria-controls='tab-id-7-content'  itemprop=\"headline\" >Auditing<\/div><div id='tab-id-7-content' class='tab_content' aria-hidden=\"true\"><div class='tab_inner_content invers-color'  itemprop=\"text\" ><p>If you take security seriously \u2013 and if you read until this point, chances are very high \u2013 you absolutely need to Audit all Security-relevant activities like changing role memberships, permissions.<br \/>\nAnd don\u2019t forget to Audit any changes to the Audit (Audit-Traces) as well. A simple \u201cAUDIT SESSION CHANGED\u201d may not be sufficient for your security classification \ud83d\ude09<\/p>\n<\/div><\/div><\/section>\r\n<\/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\" ><h6><span style=\"color: #333333;\">Final remarks<\/span><\/h6>\n<p>These were some real-life examples of roles that I implement for customers with a need for an (almost) bullet-proof Separation of Duties. Of course, <u>there are more roles to consider<\/u>.<br \/>\nWhile SQL Server has indeed earned its title of the most secure database in many years now (by count of vulnerabilities), attacks from inside are a very present and constant danger to any system. To protect against such, the pure absence of security-related bugs is far from sufficient if there are no borders or only weak borders implemented. That can be the commonly shared sa-account (\u201cevery day is emergency day\u201d), other high privileged accounts, or the pure possibility to change data in production when running an update.<\/p>\n<p>A few <strong>remarks on Reporting Services and Analysis<\/strong>:<\/p>\n<p><strong>Reporting Services<\/strong> also allows the definition of custom Roles but the concept is quite different from SQL Server, where a User is a member of the role throughout the whole database: in SSRS a user fulfills a certain role on a specified level in the (folder-)hierarchy and a different role one on another. A very interesting approach that is easy to follow because of the strictly hierarchical structure and the almost trivial permissions compared to the one of a complex RDBMS like SQL Server.<\/p>\n<p><strong>Analysis Services<\/strong> knows customizable roles on Database-level only with even less permissions, which can be difficult to secure all the way, especially when it comes to certain administrative tasks. When it comes to the Endusers, a kind of label-based security can be implemented on Hierarchy-\/Cell level.<\/p>\n<p>There are a few official <strong>whitepapers<\/strong> on SoD in SQL Server which I absolutely recommend as a technical background to this subject:<\/p>\n<ul>\n<li><a href=\"http:\/\/download.microsoft.com\/download\/D\/2\/D\/D2D931E9-B6B5-4E3B-B0AF-22C749F9BB7E\/SQL_Server_Separation_of_Duties_White_Paper_Jul2011.docx\" target=\"_blank\" rel=\"noopener\">SQL Server Separation of Duties<\/a> by Lara Rubbelke<\/li>\n<li><a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/cc974525.aspx\" target=\"_blank\" rel=\"noopener\">Engine Separation of Duties for the Application Developer<\/a> by Craig Gick, Jack Richins<\/li>\n<li><a href=\"http:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=39269\" target=\"_blank\" rel=\"noopener\">Separation of Duties in SQL Server 2014<\/a> by Bob Beauchemin<\/li>\n<\/ul>\n<p>I also recommend Erland Sommarskog\u2019s article on <a href=\"http:\/\/www.sommarskog.se\/grantperm.html\" target=\"_blank\" rel=\"noopener\">Packaging Permissions in Stored Procedures<\/a> which is an essential technique to be used for almost all of the depicted solutions.<\/p>\n<p>I hope my article is a helpful addition in terms of practical scenarios.<br \/>\nBe sure to also read my other articles on security, especially:<br \/>\n<a href=\"https:\/\/andreas-wolter.com\/en\/control-server-vs-sysadmin-sa\/\">CONTROL SERVER vs. sysadmin\/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation caveats<\/a> and <a href=\"https:\/\/andreas-wolter.com\/en\/new-permissions-in-sql-server-2014-and-the-old-control-server\/\">New Permissions in SQL Server 2014: IMPERSONATE ANY LOGIN, SELECT ALL USER SECURABLES, CONNECT ANY DATABASE and the old CONTROL SERVER<\/a><\/p>\n<\/div><\/section>\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-18  el_after_av_textblock  el_before_av_textblock '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/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\" ><h5><span style=\"color: #333333;\">Wishlist to the security team<\/span><\/h5>\n<p>I would like to close with a wishlist for the database security teams in Redmond and Israel.<\/p>\n<p>1) As certain roles can be dangerous to combine, and there is no way to prevent accidentally granting specific roles, all that one can do is to conduct regular checks. For that, Policy-based Management with ExecuteSQL-Expressions or plain SQL-Jobs can be scheduled. I would wish for a kind of <strong>anti-affinity<\/strong>, and \u201cexception-bit\u201d technique <strong>for (mainly server-) roles<\/strong>.<\/p>\n<p>2) Msdb is a real pain to handle. First of all, it can be used for privilege elevation attacks, and secondly the whole ownership\/permission-set for Jobs is far too limited. A hierarchical system for jobs (similar to SSRS and SSISDB somewhat) with permissions on certain levels would be great.<br \/>\nFor many use-cases it would certainly be great to have jobs residing within a user-database. Maybe the solution is to have certain jobs within a user-database, and others within their own database (but not msdb). I understand though that there is a lot of complexity involved. Because somehow SQL Agent needs to access every database and check for jobs then. Part one would be great already.<\/p>\n<p>3) Extended Events on Database level. That one should be almost ready to ship, if I look at SQL Azure Database \ud83d\ude42<\/p>\n<p>4) An \u201coriginating database\u201d bit in DMVs for automatic filtering \u2013 similar to how system tables and permissions work.<\/p>\n<p>5) Finish the work on decoupling certain system procedures\/DBCC commands from the sysadmin-bit. A lot has been done in this area already, so I am confident this will eventually be finished.<\/p>\n<p>6) In general, I would like to have more built-in roles, but more important is the possibility to craft \u201cjust the permissions needed\u201d. So, I go for \u201cmore permissions\u201d to enable the above wishes.<br \/>\n<br class=\"\u201cclear\u201c\" \/>Andreas Wolter, <a href=\"http:\/\/www.sarpedonqualitylab.com\/\" target=\"_blank\" rel=\"noopener\">Sarpedon Quality Lab<\/a><\/p>\n<\/div><\/section>\r\n\r\n<div  class='hr av-av_hr-0ff602b3e980a3377077ff3c1c834df6 hr-default  avia-builder-el-20  el_after_av_textblock  el_before_av_one_full '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\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-21  el_after_av_hr  el_before_av_social_share  first flex_column_div av-zero-column-padding  '     ><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='av-social-sharing-box av-av_social_share-8644d330ffb238fff0cfa858c5295467 av-social-sharing-box-default  avia-builder-el-23  el_after_av_one_full  el_before_av_hr  av-social-sharing-box-fullwidth'><div class=\"av-share-box\"><h5 class='av-share-link-description av-no-toc '>Share<\/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\/1712-separation-of-duties-sqlserver-role-based-security\/&#038;t=Separation%20of%20Duties%20%28SoD%29%20and%20role-based%20security%20conception%20in%20SQL%20Server\" 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=Separation%20of%20Duties%20%28SoD%29%20and%20role-based%20security%20conception%20in%20SQL%20Server&#038;url=https:\/\/andreas-wolter.com\/en\/?p=5093\" 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=Separation%20of%20Duties%20%28SoD%29%20and%20role-based%20security%20conception%20in%20SQL%20Server&#038;url=https:\/\/andreas-wolter.com\/en\/1712-separation-of-duties-sqlserver-role-based-security\/\" 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-24  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":"Introduction With the upcoming implementation of the European General Data Protection Regulation (GDPR) in May 2018, having a security concept in place is essentially required by law. Microsoft SQL Server, just like other database systems, carries the main asset to protect: the data itself. Therefore, it is time for an article from a more strategic [&hellip;]","protected":false},"author":4,"featured_media":5106,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[57],"tags":[255,228,256,257,27,232,258],"class_list":["post-5093","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-security-en","tag-impersonation-en","tag-privilege-escalation-en","tag-sa-en","tag-schema-en","tag-security-en","tag-sicherheit-en","tag-sysadmin-en"],"_links":{"self":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/5093","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=5093"}],"version-history":[{"count":24,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/5093\/revisions"}],"predecessor-version":[{"id":5772,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/5093\/revisions\/5772"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media\/5106"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=5093"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/categories?post=5093"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/tags?post=5093"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}