1 / 17

Si Se Puede ! Achieving Separation of Duties with SQL Server

Si Se Puede ! Achieving Separation of Duties with SQL Server. Lara Rubbelke @sqlgal Microsoft. Separation of Duties (SoD). What does it mean? Why do you need to care? How can you achieve your SoD goals?. Additional Resources.

trygg
Download Presentation

Si Se Puede ! Achieving Separation of Duties with SQL Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Si Se Puede! Achieving Separation of Duties with SQL Server Lara Rubbelke @sqlgal Microsoft

  2. Separation of Duties (SoD) • What does it mean? • Why do you need to care? • How can you achieve your SoD goals?

  3. Additional Resources • SQL Server Separation of Duties for the DBA Whitepaper: http://bit.ly/pOPsct • Download Separation of Duties (SOD) Framework sqlserversod.codeplex.com • Module Signing (BOL): http://bit.ly/pwcN13 • Engine Separation of Duties for the Application Developer: http://bit.ly/pwcN13

  4. SQL Server 2008 SoD Features

  5. Separation of Duties # 1 Rule CONTROL SERVER DB_OWNER SysAdmin

  6. Demo Granular Permissions

  7. PrizefightModule Signing vs.Execute As

  8. Execute As CREATE PROCEDURE HRUser.MySalary WITH EXECUTE AS ‘HRAdmin’ AS DECLARE @User NVARCHAR(128); EXECUTE AS CALLER; SELECT @User = USER_NAME(); REVERT; SELECT * FROM HR.PAYROLL WHERE Name = @User; GO

  9. Execute As Verdict Easy to use Not auditable

  10. Module Signing • Need ALTER ANY LOGIN server permission to ALTER LOGIN • Need to GRANT ALTER ANY LOGIN TO Jason? – No! ALTER LOGIN Bob ENABLE Jason (non privileged login)

  11. Module Signing SP_ENABLE_LOGIN • Jason has permission to call SP • SP run under Jason’s context but with elevated privilege • SP protected against tampering ALTER LOGIN Bob ENABLE ALTER ANY LOGIN Jason (non privileged login) Cert_login

  12. When the door to your database is closed to the DBA Separation of Duties Framework sqlserversod.codeplex.com

  13. 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

  14. Laying Out The Framework • Decide what tasks the DBA should be allowed to execute • Create stored procedures or use the examples included in the framework • Execute the InstallScript.ps1 script • Set up the database • Set up the certificate and login/user • Create the signed modules • Place DBAs/users in the new roles

  15. Demo Setting up the Separation of Duties Framework

  16. Separation of Duties Framework DisclaimerThe Fine Print • There will alwaysbe 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 complementexisting processes

  17. Additional Resources • SQL Server Separation of Duties for the DBA Whitepaper: http://bit.ly/pOPsct • Download Separation of Duties (SOD) Framework sqlserversod.codeplex.com • Module Signing (BOL): http://bit.ly/pwcN13 • Engine Separation of Duties for the Application Developer: http://bit.ly/pwcN13

More Related