Security


Security has been a big push for Microsoft ever since the company kicked off its Trustworthy Computing Initiative. The goal of Microsoft’s Trustworthy Computing Initiative is to make all Microsoft products more secure and more reliable. As you would expect, being a part of the Trustworthy Computing Initiative, SQL Server 2005 is the recipient of a number of very significant security enhancements. Microsoft’s security push for SQL Server is focused on making the product more secure and more robust from its design through its deployment. When designing the security enhancements for SQL Server 2005, Microsoft followed some basic security tenets. First, it wanted to make the system secure right out of the box by gearing all of the default installation settings toward creating a secure environment. While it left options open for users to select less secure settings, selecting these options required deliberate choices. Next, Microsoft followed the principle of least privileges in their system design. The system is designed so that an individual should have only the require privileges to perform a given function and no more. Finally, Microsoft wanted to reduce the potential exposure surface area by providing the ability to install only those components that are needed.

All of the new security features found in SQL Server 2005 were deeply influenced by the things Microsoft discovered during its security push in early 2002 and were carried into the SQL Server 2005 design and implementation. Some of the core security feature enhancements found in SQL Server 2005 that you’ll read about in this section include the separation of users from schemas, the new stored procedure execution context, more granular control of permissions, new password policy enforcement, changes to row-level security, and enhanced security for catalogs.

User-Schema Separation

The most significant new security-related change found in SQL Server 2005 is user-schema separation. A user, or perhaps more accurately, a principal, is any entity against whom database objects are secured. A principal can be a Windows user, a SQL Server user, a role, or an application role. With SQL Server 2000, directly owned by users database objects and the users themselves were found in the sys_users system table. That’s all changed with SQL Server 2005. Now database objects are owned by schemas. Users no longer directly own database objects; instead they own schemas. With SQL Server 2005, users and other security principals can be found in the new sys.database_principals view. SQL Server 2005’s list of schemas can be found in the new sys.schemas view.

A schema is a container of objects. The schema is identified by the third part of the four-part object naming syntax used by SQL Server. The following example illustrates SQL Server 2005’s naming syntax, where each part of the name gets increasingly more specific.

Server_name.Database_name.Schema_name.Object_name

In all of the previous releases of SQL Server, the schema name and the owner name were essentially the same thing. With SQL Server 2005, the owner has been separated from the schema. When SQL Server 2000 and earlier releases resolved object names, SQL Server first looked for Database_name.User_name.Object_name, and if that failed, it then looked for Database_name.dbo.Object_name.

The main reason for this separation of user and schema in SQL Server 2005 is to address the problem of needing to change the ownership of multiple database objects if a given user (aka the old object owner) leaves the organization. In addition, the action of changing a database object’s ownership also resulted in a name change. For instance, if the owner of Table1 in database MyDB is changed from UserA to UserB, then the qualified name of Table1 will be changed from MyDB.UserA.Table1 to MyDB.UserB.Table1. To help prevent this problem, many organizations adopted the standard of having all database objects owned by dbo, but there was nothing in the server that forced this practice.

SQL Server 2005’s implementation of the concept of a database schema introduces a level of abstraction in the chain of database object ownership. You can see SQL Server 2005’s database object ownership chain in Figure 1-3.

image from book
Figure 1-3: SQL Server 2005’s object ownership chain

With SQL Server 2005, database objects are contained in schemas, and the schemas are in turn owned by users. This new level of abstraction makes the problem of changing database object ownership much more manageable. Dropping a user that owns database objects in SQL Server 2005 means that the DBA now needs to change only the ownership of the schema and not all of the individual database objects. This vastly reduces the number of objects that the DBA needs to touch in order to change the ownership of the objects in a database. To change the ownership of all the objects in a SQL Server 2005 database, you simply change the owner of the schema, and then you can drop the old user. Changing the owner of database object doesn’t change an object’s name because the schema name doesn’t change, just its ownership.

As you might expect, the new schema object changes the way that SQL Server performs database object name resolution. Each user now has a default schema associated with that user, and SQL Server 2005 will first look for an unqualified object name using the user’s default schema. If that fails, SQL Server will look for the object using the schema name of dbo. For instance, if UserA has a default schema of MySchema1 and that user performs a query looking for Table1, then the server will first attempt to resolve the name using MySchema1.Table1 and then fall back to dbo.Table1.

Just as SQL Server 2000 databases could contain multiple users and roles, SQL Server 2005 databases can contain multiple schemas. Each schema has an owning principal, which is typically either a user or a role. For name resolution purposes, each user has a default schema. The actual database objects are then contained in a schema. To create new database objects inside a schema, you must have CREATE permission for the database object itself and ALTER or CONTROL permission for the schema. Ownership chaining is still based on actual owners, not schemas.

SQL Server 2005 introduces several DDL changes for dealing with the new user and schema separation, including a CREATE/DROP/ALTER statement for USER, ROLE, and SCHEMA objects. The following listing demonstrates how a database schema is created and assigned:

/* Create a login */ CREATE LOGIN UserA WITH PASSWORD = 'ABC123#$' GO /* Create a user for that login – the schema doesn't need to exist*/ CREATE USER UserA FOR LOGIN UserA   WITH DEFAULT_SCHEMA = MySchema GO /* Create the schema and assign its owner */ CREATE SCHEMA MySchema AUTHORIZATION UserA GO /* Create a Table in the new schema */ CREATE TABLE MySchema.Table1 (col1 char (20)) GO

The first line in this listing creates a new login named UserA and sets a password for that login. The next line creates a new user named UserA for the login and sets the default schema for UserA to MySchema. The actual schema does not need to exist at the time it is specified in the CREATE USER statement. If you don’t specify a default schema when you create a new user, then the default schema will be set to dbo. Next, the CREATE SCHEMA statement is used to create a new schema named MySchema. The AUTHORIZATION clause sets the owner of the schema to be UserA. Finally, a table named Table1 is created in the schema named MySchema. The owner for MySchema and its objects such as Table1 is UserA.

Stored Procedure Execution Context

While Microsoft has referred to one new feature as a stored procedure execution context, it really applies to modules rather than just stored procedures. A module can be a stored procedure, a function, or an assembly. Setting the execution context for a module causes all statements that are contained in that module to be checked for permissions against the specified execution context. In other words, by setting the execution context of a given module, you cause all the statements that are contained in that module to be executed using the authority of the user that you specify rather than the actual caller of the module. This new feature enables you to get advantages similar to those realized through SQL Server 2000’s ownership chaining, but it is more flexible, as it doesn’t have the same limitations. For example, unlike SQL Server 2000’s ownership chaining, which didn’t allow you to alter the execution context for dynamic SQL, SQL Server 2005’s module execution context applies to dynamic SQL just as it does to static SQL. To better understand this, take a look at Figure 1-4, which demonstrates SQL Server 2000’s ownership chaining.

image from book
Figure 1-4: SQL Server 2000’s ownership chains

For UserA to execute dbo.Proc1, UserA must have execute permission for that object. However, when dbo.Proc1 accesses dbo.Table1, no permissions are checked, because dbo is the owner of both objects. This is an example of an intact ownership chain. In the next scenario, for UserA to execute UserB.Proc2, UserA must have Execute permissions for that object. Then, when UserB.Proc2 attempts to access UserC.Table2, Select permissions from UserA must be checked. In this case, because UserB.Proc2 and UserC.Table2 have different owners, the ownership chain is broken.

SQL Server 2005’s execution simplifies this scenario, as shown in Figure 1-5. In this scenario, when UserA attempts to execute UserB.Proc2, SQL Server checks to ensure that UserA has Execute permissions for the UserB.Proc1. If the object UserB.Proc1 is created with an execution context that specifies that a stored procedure will be executed as UserZ, then when the UserB.Proc1 stored procedure attempts to access UserC.Table1, SQL Server will check for Select permissions only on the user specified in the execution context, which in this case is UserZ. No Select permissions are required for UserA, who is the actual caller.

image from book
Figure 1-5: SQL Server 2005’s execution context

The following listing shows how you change the execution context of a stored procedure named MyProc1:

ALTER PROC MySchema.Proc1 WITH EXECUTE AS USER UserB

This statement shows the new WITH EXECUTE clause. Here, the stored procedure named Proc1 that’s contained in MySchema is set to execute under the context of UserB. You must specify a user name for the execution content. You cannot specify the name of a role. Changes in execution context are stored in the new sys.sql_modules view.

More Granular Permissions Control

SQL Server 2005 also provides more granular control over permissions. With SQL Server 2005, Microsoft added more permissions at different scopes. The scopes to permissions that can be applied are: server, database, schema, object, and principal. The design idea behind SQL Server 2005’s enhanced permissions is the principle of least privileges, giving the DBA the ability to control exactly what permissions are assigned. The new granular permissions do not do away with SQL Server’s existing fixed roles. All of the older roles are still present and can coexist with no problems alongside the new permissions. One specific scenario that these new granular permissions are meant to address is the case of auditing. On SQL Server 2000, you needed to be a member of the sysadmins group in order to perform auditing. However, membership in this group also enables many other, more far-reaching capabilities. Some of the new permissions available in SQL Server 2005 enable auditing functions to be performed without requiring that the user be a part of the sysadmins group.

The same basic permission states of GRANT, DENY, and REVOKE that were used in previous versions of SQL Server still apply. One thing that’s different about the way that SQL Server 2005 uses permissions is that the same permission can be applied at multiple scopes. For example, if you apply a permission to the database scope, it applies to all objects in the database. If you apply a permission to the schema level, it applies to just those objects contained in the schema. With the exception of the DENY permission, the higher-scope permission is always used. However, a DENY at any level always takes precedence. Table 1-1 lists some of the most important new SQL Server 2005 permissions. The server permissions are found in the sys.server_permissions view, while the database permissions are found in the sys.database_permissions view.

Table 1-1: Some New Permissions in SQL Server 2005

Permission

Description

ALTER

Grants the ability to change the properties of an object. Also grants the ability to execute CREATE/DROP/ALTER statements.

ALTER ANY ‘X'

Grants the ability to alter any object of type X. For instance, if you substituted TABLE for X, this would grant the ability to alter any table in the database.

ALTER TRACE

Grants the ability to perform auditing and run Profiler.

CONTROL

Grants a principal owner-like permissions.

SELECT

Grants the ability to access an object. Now applies to the schema and database levels rather than just the object level.

EXECUTE

Grants the ability to execute a procedure, assembly, or function. Now applies to the schema and database levels rather than just the object level.

IMPERSONATE

Grants a login or a user the ability to impersonate another user.

TAKE OWNERSHIP

Grants the ability to assume the ownership of an object.

VIEW DEFINITION

Grants the ability to view an object's metadata.

Password Policy Enforcement

Another important new security feature found in SQL Server 2005 is support for password policies. This new policy enforcement feature follows local Windows password policies and enables you to implement a consistent enterprise-wide security policy, not just for your Windows server systems but also for your SQL Server database systems. SQL Server 2005 now has the capability to enforce password strength, password expiration, and account lockout policies. As you would expect, the password strength policy forces passwords to consist of a given complexity. The password expiration policy ensures that passwords expire and must be reset at a given interval. And the account lockout policy enables an account to be locked out after a given number of bad password attempts. All of these new password policies are supported on Windows Server 2003. However, only the password complexity policy is supported on Windows 2000 Server. Following Microsoft’s security push, all of the policies are enabled in the default installation, but they can also be reconfigured on a per-login basis. SQL Server 2005 stores the new password policies in the sys.sql_logins catalog view.

Catalog Security

The final security-related enhancement that I’ll cover in this section is the new catalog security provided by SQL Server 2005. The system tables that were used by SQL Server 2000 in the individual databases and in the master database are now implemented as catalog views in SQL Server 2005. The server’s metadata exposed in these views is secured by default, and there are minimal public permissions. SQL Server 2005’s catalog views employ row-level security, limiting access to the data contained in those views to only those objects that you own or have permissions to. Naturally, sa is an exception to this. The sa account still has access to all of the objects in the server.

To enable a user or role to access the metadata, the DBA needs to use the new VIEW DEFINITION permission. The VIEW DEFINITION permission can be used to grant a user who is not the owner and doesn’t have permissions to access an object the ability to view the object’s metadata. The VIEW DEFINITION permission can be applied at the server, database, schema, and object scopes.




Microsoft SQL Server 2005 New Features
Microsoft SQL Server 2005 New Features
ISBN: 0072227761
EAN: 2147483647
Year: 2005
Pages: 80
Authors: Michael Otey

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net