image Home       image Fowles,       image Fitzgerald,       image r04 06 (9)       image R 22MP (3)       image 45 (3)       

Linki

[ Pobierz całość w formacie PDF ]
Introducing
SQL
Server
®
2008
Peter DeBetta
Microsoft
®
Chapter 1
Security and Administration
Introduction
With all the complexity of today’s IT shops and with stronger security measures being re-
quired by both the high-tech industry and by government regulations, we are going to need
some new tools to help us make our way through the new world of IT. SQL Server 2005 made
leaps and bounds in advances for security and management, providing the ability to more
easily manage and secure servers with features such as certificate and key-based encryption
and more full-featured tools. SQL Server 2008 takes these measures even further, provid-
ing the ability to more easily manage and secure database servers, with new features such
as policy-based management, external key management, server and database auditing, and
transparent data encryption.
Policy-Based Management
Have you ever had to ensure that only Windows logons or groups were added to Microsoft
SQL Server, or that xp_cmdshell was disabled, or that no stored procedure names started
with “sp_”? Did you ever have to do this to more than one server in your enterprise? I have,
and it was always such a hassle to go from server instance to server instance, querying sys-
tem objects, checking various configuration settings, and scouring through all sorts of places
to ensure that your SQL Server instances were all compliant. That process has changed in SQL
Server 2008.
Policy Management in SQL Server 2008
Yes, it’s true. SQL Server 2008 introduces a new feature known as the Policy-Based
Management. This framework allows you to define policies on a variety of objects and then
either manually or automatically prevent changes based on said policies. Management is also
very simple using SQL Server Management Studio (preferred), or you can write your own
code to manage policies. But I am getting ahead of myself. Let’s start at the beginning.
This management framework allows you to easily and proactively manage a variety of poli-
cies, ranging from security to metadata. It does this in two ways:
N
It allows you to monitor changes to these policies, with options to manually check poli-
cies, check policies on schedule, check policies on change and log violations, or check
policies on change and prevent the change if the policy is violated.
1
2
Introducing SQL Server 2008
N
It allows you to manage one or more SQL Server instances on a single server or across
multiple servers.
Rather than waiting for something to go awry, you can set policies based on your server
specifications and then have the framework proactively prevent changes based on these
policies or inform you via policy logs when these policies are being violated. The ability to
prevent certain changes depends on the type of feature, or facet, for which you are creating
a policy. For example, if you want to ensure that xp_cmdshell is never turned on for any sever
you are managing, you can create a policy and have it inform you when a change occurs or
even have it check for changes on a schedule, but you cannot prevent it from being changed.
The ability to prevent changes varies from facet to facet.
Policy-Based Management in SQL Server Management Studio
The practice of creating and enforcing policies is easily achieved using SQL Server
Management Studio. Policy-Based Management is accessed primarily by the Policy
Management node in Object Explorer, which can be found under the Management node of
the SQL Server instance, as shown in Figure 1-1.
FIGURE 1-1
Policy Management in Object Explorer
Within this node of Object Explorer, you find the three base items of the framework: Policies,
Conditions, and Facets. Although not shown as a node, Policy Category Management can
also be accessed from here by right-clicking on the Policy Management node of Object
Explorer and choosing Manage Categories. So what does each of the objects do to help you
Chapter 1 Security and Administration
3
implement policy-based management? Let’s dig into each of them in more detail and dis-
cover how they are used.
Policy-Based Management Objects
Policy-Based Management uses fi ve different objects to manage policies: facets, conditions,
policies, targets, and categories.
Facets
Facets are the base units of this framework. Facets are types of objects, such as a Surface
Area feature, server, logon, database, user, and so on. Each facet has a set of predefi ned
properties against which conditions can be created.
As of the Community Technology Preview 6 (CTP6) release, there are a total of 47 facets, with
a whopping 1,492 total properties. SQL Server Management Studio has a list of these facets
under the Facets node in Objects Explorer (found under Management, Policy Management).
Alas, if you want to see each list of properties, you need to open each facet’s properties in-
dividually. If you want a quick list of all facets and properties, however, you can use the SQL
Server Management Objects (SMO) to iterate through all available facets and properties, as
shown here:
FacetInfoCollection fic = PolicyStore.Facets;
IEnumerable<FacetInfo> fic_sorted = from fic_i in fic
orderby fic_i.DisplayName
select fic_i;
Int32 pcount;
foreach (FacetInfo fi in fic_sorted)
{
Console.WriteLine(“FACET: “ + fi.DisplayName);
IEnumerable<PropertyInfo> fi_sorted = from fi_i in fi.FacetProperties
orderby fi_i.Name
select fi_i;
pcount = 0;
foreach (PropertyInfo pi in fi_sorted)
{
if (pcount++ > 0)
Console.Write(“, “ + pi.Name);
else
Console.Write(pi.Name);
}
Console.WriteLine();
Console.ReadLine();
}
Console.WriteLine(“---End of List---”);
Console.ReadLine();
4
Introducing SQL Server 2008
Facets by themselves cannot do anything in establishing policies. They can be used by condi-
tions, however, to define what rules you want to create and against which servers, databases,
or other objects the policies should check.
Conditions
A condition is an expression that defines the desired state of a facet. You express a condition
by setting a facet property, a comparative operator, and a value. Each property condition’s
state is set according to its respective data type. For example, the Name property of the
Stored Procedure facet is of type String and can have a condition operator of equal (=), not
equal (!=), LIKE, NOT LIKE, IN, or NOT IN. Thus it can be compared with a string or a list of
strings. The SQL Mail property of the Surface Area facet is of data type Boolean, and thus it
has only the equality and inequality operators and can only be set to a value of true or false.
Note
There is an advanced expression editor (the Advanced Edit dialog box) available if you
need to create a specialized condition check. For example, you can check that the name of a
table doesn’t equal the schema name or that all tables have a primary key. The advanced expres-
sion editor allows a lot of flexibility, but when used in a condition, its respective policy can only
be executed On Demand.
Both the field and expression value can be set using the advanced expression editor. In addition
to providing a custom expression, it also provides an explanation of the available functions and
a description of the facet properties. So if you are not sure what the property represents, you do
not need to go to the facet and open it; you can simply click the ellipsis button (…) and examine
the properties from there.
Furthermore, a condition can also only contain properties from a single facet type. For ex-
ample, you can create a condition that states “SQL Mail is disabled and Database Mail is
disabled” because both of these properties are part of the Surface Area facet. You cannot,
however, create a condition that states “stored procedure names must begin with ‘pr’ and
xp_cmdshell is disabled” because these two properties are part of two different facets (the
Stored Procedure facet and Surface Area facet, respectively).
You can, however, create multiple conditions based on the same underlying facets. So you
can create a condition that states “SQL Mail is disabled and Database Mail is disabled,” and
you can create a second condition that states “SQL Mail is disabled and Database Mail is en-
abled.” Of course, you wouldn’t want to have both policies on the same server because one
of the policies will always be in violation.
SQL Server 2008 comes with an assortment of predefined conditions that you can imme-
diately put into use. For example, one of my favorites is the condition named Auto Shrink
Disabled, which can be used by a policy to ensure that databases do not enable the auto
shrink option. Figure 1-2 shows this particular condition in the Open Condition window.
[ Pobierz całość w formacie PDF ]

  • zanotowane.pl
  • doc.pisz.pl
  • pdf.pisz.pl
  • zolka.keep.pl

    		•