Si Se Puede! Achieving Separation of Duties with SQL Server

Lara Rubbelke@sqlgalMicrosoft

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

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

SQL Server 2008 SoD Features

Granular Perms

User-defined

roles

Signed Modules

/ Execute

As

Encryption

Separation of Duties # 1 Rule

SysAdminCONTROL SERVERDB_OWNER

DemoGranular Permissions

PrizefightModule Signing

vs.Execute As

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

Execute As

VerdictEasy to use

Not auditable

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)

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

When the door to your database is closed to

the DBA

Separation of Duties Frameworksqlserversod.codeplex.com

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

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

Demo

Setting up the Separation of Duties Framework

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

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