![Page 1: Si Se Puede ! Achieving Separation of Duties with SQL Server](https://reader035.vdocuments.us/reader035/viewer/2022062814/56816705550346895ddb6a9d/html5/thumbnails/1.jpg)
Si Se Puede! Achieving Separation of Duties with SQL Server
Lara Rubbelke@sqlgalMicrosoft
![Page 2: Si Se Puede ! Achieving Separation of Duties with SQL Server](https://reader035.vdocuments.us/reader035/viewer/2022062814/56816705550346895ddb6a9d/html5/thumbnails/2.jpg)
Separation of Duties (SoD)What does it mean?Why do you need to care?How can you achieve your SoD goals?
![Page 3: Si Se Puede ! Achieving Separation of Duties with SQL Server](https://reader035.vdocuments.us/reader035/viewer/2022062814/56816705550346895ddb6a9d/html5/thumbnails/3.jpg)
Additional ResourcesSQL Server Separation of Duties for the DBA Whitepaper: http://bit.ly/pOPsct Download Separation of Duties (SOD) Framework sqlserversod.codeplex.comModule Signing (BOL): http://bit.ly/pwcN13Engine Separation of Duties for the Application Developer: http://bit.ly/pwcN13
![Page 4: Si Se Puede ! Achieving Separation of Duties with SQL Server](https://reader035.vdocuments.us/reader035/viewer/2022062814/56816705550346895ddb6a9d/html5/thumbnails/4.jpg)
SQL Server 2008 SoD Features
Granular Perms
User-defined
roles
Signed Modules
/ Execute
As
Encryption
![Page 5: Si Se Puede ! Achieving Separation of Duties with SQL Server](https://reader035.vdocuments.us/reader035/viewer/2022062814/56816705550346895ddb6a9d/html5/thumbnails/5.jpg)
Separation of Duties # 1 Rule
SysAdminCONTROL SERVERDB_OWNER
![Page 6: Si Se Puede ! Achieving Separation of Duties with SQL Server](https://reader035.vdocuments.us/reader035/viewer/2022062814/56816705550346895ddb6a9d/html5/thumbnails/6.jpg)
DemoGranular Permissions
![Page 7: Si Se Puede ! Achieving Separation of Duties with SQL Server](https://reader035.vdocuments.us/reader035/viewer/2022062814/56816705550346895ddb6a9d/html5/thumbnails/7.jpg)
PrizefightModule Signing
vs.Execute As
![Page 8: Si Se Puede ! Achieving Separation of Duties with SQL Server](https://reader035.vdocuments.us/reader035/viewer/2022062814/56816705550346895ddb6a9d/html5/thumbnails/8.jpg)
Execute As
CREATE PROCEDURE HRUser.MySalaryWITH EXECUTE AS ‘HRAdmin’AS
DECLARE @User NVARCHAR(128);EXECUTE AS CALLER;SELECT @User = USER_NAME();REVERT;SELECT * FROM HR.PAYROLL
WHERE Name = @User;GO
![Page 9: Si Se Puede ! Achieving Separation of Duties with SQL Server](https://reader035.vdocuments.us/reader035/viewer/2022062814/56816705550346895ddb6a9d/html5/thumbnails/9.jpg)
Execute As
VerdictEasy to use
Not auditable
![Page 10: Si Se Puede ! Achieving Separation of Duties with SQL Server](https://reader035.vdocuments.us/reader035/viewer/2022062814/56816705550346895ddb6a9d/html5/thumbnails/10.jpg)
Module Signing
Need ALTER ANY LOGIN server permission to ALTER LOGINNeed to GRANT ALTER ANY LOGIN TO Jason? – No!
ALTER LOGIN Bob ENABLE
Jason(non privileged login)
![Page 11: Si Se Puede ! Achieving Separation of Duties with SQL Server](https://reader035.vdocuments.us/reader035/viewer/2022062814/56816705550346895ddb6a9d/html5/thumbnails/11.jpg)
Module Signing
Jason has permission to call SPSP run under Jason’s context but with elevated privilegeSP protected against tampering
Jason(non privileged login)
SP_ENABLE_LOGIN
ALTER LOGIN Bob ENABLE
Cert_login
ALTER ANY LOGIN
![Page 12: Si Se Puede ! Achieving Separation of Duties with SQL Server](https://reader035.vdocuments.us/reader035/viewer/2022062814/56816705550346895ddb6a9d/html5/thumbnails/12.jpg)
When the door to your database is closed to
the DBA
Separation of Duties Frameworksqlserversod.codeplex.com
![Page 13: Si Se Puede ! Achieving Separation of Duties with SQL Server](https://reader035.vdocuments.us/reader035/viewer/2022062814/56816705550346895ddb6a9d/html5/thumbnails/13.jpg)
Separation of Duties Framework
Empower the DBA team to be• Productive• Responsive
With a process that is • Auditable• Secure• Easy to Implement and Manage• Extensible
![Page 14: Si Se Puede ! Achieving Separation of Duties with SQL Server](https://reader035.vdocuments.us/reader035/viewer/2022062814/56816705550346895ddb6a9d/html5/thumbnails/14.jpg)
Laying Out The Framework1. Decide what tasks the DBA should be
allowed to executea. Create stored procedures or use the
examples included in the framework2. Execute the InstallScript.ps1 script
a. Set up the databaseb. Set up the certificate and login/userc. Create the signed modules
3. Place DBAs/users in the new roles
![Page 15: Si Se Puede ! Achieving Separation of Duties with SQL Server](https://reader035.vdocuments.us/reader035/viewer/2022062814/56816705550346895ddb6a9d/html5/thumbnails/15.jpg)
Demo
Setting up the Separation of Duties Framework
![Page 16: Si Se Puede ! Achieving Separation of Duties with SQL Server](https://reader035.vdocuments.us/reader035/viewer/2022062814/56816705550346895ddb6a9d/html5/thumbnails/16.jpg)
Separation of Duties Framework DisclaimerThe Fine Print
There will always be at least one sysadmin
The Separation of Duties Framework is designed to provide a means to limit the number of sysadmins
The Separation of Duties Framework will complement existing processes
![Page 17: Si Se Puede ! Achieving Separation of Duties with SQL Server](https://reader035.vdocuments.us/reader035/viewer/2022062814/56816705550346895ddb6a9d/html5/thumbnails/17.jpg)
Additional ResourcesSQL Server Separation of Duties for the DBA Whitepaper: http://bit.ly/pOPsct Download Separation of Duties (SOD) Framework sqlserversod.codeplex.comModule Signing (BOL): http://bit.ly/pwcN13Engine Separation of Duties for the Application Developer: http://bit.ly/pwcN13