You want to grant someone permissions to do WHAT?!?!

Lara Rubbelke — Mon, 24 Jan 2011 02:32:00 GMT

Have you ever heard of these types of requests? True story! I have had each of these and many more:

A customer needed to grant a business user the rights to issue a KILL command – without giving them sysadmin or CONTROL SERVER.
A customer wanted to grant a user the rights to update a job – just one job – without any other changes to the job.
There was the case where a customer wanted to give a set of junior admins the rights to unlock a set of logins – without granting any additional rights to alter logins.
And of course, there are many, many customers who are facing internal and external regulations that dictate the DBAs should not have rights to view sensitive data. Period.

Managing security is never easy, and these additional requirements can cause a lot of distress to those who are trying to provide the right level of security while protecting their data, databases, and server infrastructure. Grant too many privileges, and you open up your environment to a host of potential issues. Grant too few privileges, and the users and administrators are unable to do their jobs.

Enter the Separation of Duties Framework. The Separation of Duties Framework was originally designed to address the separation of DBA from sysadmin, but this framework may also be used to temporarily grant users elevation of privileges in a controlled and auditable environment. The SQL Server Separation of Duties Framework will ease the process of setting up a restrictive environment while providing a predefined set of processes a DBA may use to manage restricted instances and sensitive databases. The Separation of Duties Framework is designed to empower the DBA team (or users) to be productive and responsive with processes that are auditable, secure, and extensible while being easy to implement and manage.

The Separation of Duties Framework was originally released in November 2010. Brian Davis (blog and twitter) and I just released v2.0 of the framework. The framework will create database roles, signed stored procedures, and the securables needed to support the environment. The framework is set up in the following steps:

Define the roles and tasks. Each organization will have different regulations that stipulate the security boundaries for individuals and groups. Prior to installing the Separation of Duties Framework, it is necessary to define the types of roles that will engage with SQL Server and the tasks that each role is permitted to execute.
Create folders to represent the defined roles. Create folders in a Procedures directory that will mimic the security roles you identified in the previous step. Remember that these folders are hierarchical, and each folder level will inherit the privileges of the parent folders. The Separation of Duties Framework will create roles based on the folder structure under the Procedures directory.
Add stored procedures sql files to the folders created in the previous step. Create procedures or use existing example procedures available in the framework that represent the tasks each role is allowed to execute. Place these in the appropriate folder which represents the users who are permitted to execute the task. The Separation of Duties Framework install script will create each procedure, sign the procedures with a certificate, and grant EXECUTE permissions to the appropriate roles.
Execute the PowerShell install script.
Place the appropriate users and groups into the newly created Database Roles.

More details on the installation process are available with the download. Brian Davis and I will also be following up with some additional blogs with details on the framework over the next few weeks.

Automating SQL Server 2005/2000 Policy Evaluation

Lara Rubbelke — Sat, 13 Jun 2009 16:11:00 GMT

The Enterprise Policy Management Framework version 3.0, a new version of the framework to support policy automated policy evaluation for SQL Server 2000 and 2005, has been posted to codeplex.

For those who are not familiar with the tool, the Enterprise Policy Management Framework is a reporting solution on the state of the SQL Server enterprise against a desired state defined in a policy. The key capabilities are to extend Policy-Based Management to all SQL Server instances in the enterprise, including SQL Server 2000 and SQL Server 2005. The EPM Framework will automate a scheduled evaluation of a set of policies against a group of servers, and provide reports for DBAs to understand where they have instances and database objects which are not complying with an organization’s defined standards.

The new 3.0 release includes the following enhancements:

Supports nested server groups in the Central Management Server

The previous versions did not support Central Management Server groups that were nested in parent groups. This restriction has been removed and you may now design CMS groups to fit your organization, and leverage these groups for the EPM Framework.

A new parameterized PowerShell execution

The PowerShell script has been updated with parameters. This enhancement will significantly ease how you may deploy the solution, so you only have a single script to manage. The previous versions would have required multiple versions of the PowerShell script you were to design the execution strategy by server group and policy category.

Policy results are stored in a table format

The new version 3.0 will shred the policy result XML document to a PolicyHistoryDetail table during the evaluation. The previous version only stored the XML data and issued queries against XML results stored in a SQL Server table named PolicyHistory. This update will greatly improve performance during reporting and provides a better platform for the community to build customized views and reports. This could also improve storage – you can purge the data in the PolicyHistory table if you do not require the XML results.

New Report Parameters

Based on feedback from the community, the new version includes parameters in the reports to support filtering by Central Management Server group. This will be a very important criteria for large organizations who would like to focus on specific groups of instances.

Fixes to error reporting logic

Not much to say, other than the logic that identifies errors stored in the tables is fixed.

Updated Documentation

The documentation has been updated, and should be much easier to follow when setting up the framework.

The EPM Framework leverages the Central Management Server, PowerShell, Reporting Services 2008, and Policy-Based Management. You will need at least one instance of SQL Server 2008 and an instance of SQL Server 2008 Reporting Services to support the framework. I will dive deeper into installation and configuration of the framework in subsequent blogs.

Please let me know if you are using the framework, and if you have suggestions for future enhancements. I am going to be integrating SQL Server 2008 Policy History centralization into the framework in the next version.

Search results matching tags 'SQL Server 2008', 'compliance', and 'SQL Server 2005'

You want to grant someone permissions to do WHAT?!?!

Automating SQL Server 2005/2000 Policy Evaluation