Transcript SESSION CODE: DAT310 Peter Ward @wardy [email protected] +61 403 177 761 Chief Technical Architect WARDY IT Solutions – www.wardyit.com KEEPING THE DBA OUT OF THE DATABASE.

SESSION CODE: DAT310
Peter Ward @wardy
[email protected]
+61 403 177 761
Chief Technical Architect
WARDY IT Solutions – www.wardyit.com
KEEPING THE DBA OUT OF THE
DATABASE
About Peter Ward
► Chief Technical Architect WARDY IT Solutions
– Email: [email protected]
– Twitter: @wardy
► SQL Server Microsoft Most Valuable Professional
(MVP)
► Microsoft Virtual Technology Solutions Professional
(TSP)
► WARDY IT Solutions
– Australia’s leading SQL Server specialists
– MAPA Data Platform Partner of the Year since 2009
Agenda
► SQL Server Security Best Practices
► Authorising Elevated Privileges
► Separation of Duties Framework
Unauthorised Database Access
8%
92%
Gartner, 2010
(c) 2011 Microsoft. All rights reserved.
(c) 2011 Microsoft. All rights reserved.
Principals & Securables
► Principals
– Objects that can be granted permissions to access
objects
► Securables
– Objects to which access can be controlled
sysadmin Straw Poll
Question
Are permissions checked if a login is a
member of the sysadmin server role?
sysadmin Straw Poll
Question
Are permissions checked if a login is a
member of the sysadmin server role?
Answer
No, SQL Server will bypass the Permission
Check Algorithm if the login is a member of
the sysadmin fixed server role
sysadmin Fixed Server Role
► Do not use the sysadmin role for everyday tasks
– Limit role to logins for emergency use or as required
► Treat the sysadmin role as a protected role
► Grant individual permissions on the server
► CONTROL SERVER corresponds to sysadmin
CONTROL SERVER
► CONTROL SERVER grants full server level
permissions and full access to all databases
► CONTROL SERVER allows a granular approach to
granting and denying access to securables
► Limited processes that do not function under
CONTROL SERVER
CONTROL SERVER example
USE [master]
GO
GRANT CONTROL SERVER TO [Corp\DBA_Team]
GO
DENY ALTER ANY LOGIN TO [Corp\DBA_Team]
GO
USE [AdventureWorks2008R2]
GO
DENY CONTROL ON [HumanResources].[EmployeePayHistory] TO
[Corp\DBA_Team]
GO
sa account
► sa account should never be used
► sa login is not mapped to an individual and is
high privileged
– Privileges cannot be reduced
► Rename and disable the account
Logon
Login
Logon
Login
- Error: 18456,
failed for user
- Error: 18456,
failed for user
Severity: 14, State: 7
'sa'. [CLIENT: 61.129.123.41]
Severity: 14, State: 7
'sa'. [CLIENT: 218.24.197.235]
Disable and Rename the sa account
USE [master]
GO
ALTER LOGIN sa DISABLE;
ALTER LOGIN sa WITH NAME = [SQLsa];
db_owner
► db_owner is a fixed role in each database
– DBO is an automatic member
– sysadmins map to DBO
– DBO will bypass the permission check algorithm
► db_owner similar to a database-scoped sysadmin
► Members of db_owner may have granular
permissions granted and denied
Restricting db_owner
USE [AdventureWorks2008R2]
GO
CREATE USER [CORP\Helpdesk] FOR LOGIN [CORP\HELPDESK]
GO
EXEC sp_addrolemember N'db_owner', N'CORP\Helpdesk'
GO
DENY ALTER ANY USER TO [CORP\Helpdesk]
GO
Security
Security is assigning permissions as needed
and denying permissions were risk has been
accessed
Granular Server Permissions
► SQL Server supports granting permissions at a
granular level
– Eg. view metadata but not the data
► More then 120 granular permissions at the server
level
► Do not use fixed server-level roles
► Determine what permissions are required to
accomplish the assigned task and only grant those
permissions
Demo
►Demonstrate why you should only grant
those permissions required for an assigned
task
Auditing
► Every installation of SQL Server with have one
sysadmin login
– Compensating controls are auditing and polices
► Need to protect against inside threats
► Auditing captures instance and database level
events
Demo
►Demonstrate how to use auditing to track
highly privileged users who manage
security
Elevated Privileges
► DBA permissions need to be minimized to limit
direct access to SQL Server objects
► At a minimum DBA’s should not be in the
sysadmin role
► Certain tasks required elevated privileges up to
and including sysadmin
► Temporary access can be granted using EXECUTE
AS or signed modules
Authorising Elevated Privileges
► EXECUTE AS temporarily changes the execute
context of the caller to an elevated user
► Signed Modules adds the need permissions to
the caller without changing the primary identity
of the execution context
Demo
►Demonstrate using EXECUTE AS
Signed Modules
► Provide a secure and auditable mechanism to
enable permissions to execute a specific task
► A module may be a Function, Trigger, Assembly
or Stored Procedure
► Allows security to be maintained whilst allowing
a DBA to be responsively empowered
► Separation of Duties Framework Simplifies the
implementation
Demo
►Demonstrate how to implement a Signed
Module
Separation Duties Framework
► Signed Modules increase the granularity of SQL
Server permissions
► SoD Framework designed to simplify the
implementation of Signed Modules
► Supports multiple tiers of access
► Predefined set of processes to manage a
restrictive instance and sensitive databases
Setting up SoD
1.
2.
3.
4.
5.
Define the roles and tasks
Create folders representing the defined roles
Add .sql files to the folders
Execute the Powershell install script
Place users and groups into the database roles
Demo
►Demonstrate implementing the SoD
Framework
Resources
► SQL Server Separation of Duties Framework
– http://bit.ly/SQLSoD
► WARDY IT Solutions SoD Utility
– http://bit.ly/WARDYSoD
Enrol in Microsoft Virtual Academy Today
Why Enroll, other than it being free?
The MVA helps improve your IT skill set and advance your career with a free, easy to access training
portal that allows you to learn at your own pace, focusing on Microsoft technologies.
What Do I get for enrolment?
►
►
►
Free training to make you become the Cloud-Hero in my Organization
Help mastering your Training Path and get the recognition
Connect with other IT Pros and discuss The Cloud
Where do I Enrol?
www.microsoftvirtualacademy.com
Then tell us what you think. [email protected]
© 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other
countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing
market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this
presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
(c) 2011 Microsoft. All rights reserved.
Resources
www.msteched.com/Australia
www.microsoft.com/australia/learning
Sessions On-Demand & Community
Microsoft Certification & Training Resources
http:// technet.microsoft.com/en-au
http://msdn.microsoft.com/en-au
Resources for IT Professionals
Resources for Developers
(c) 2011 Microsoft. All rights reserved.