Database Security


Security is a huge topic that affects almost every action of every SQL Server user, including administrators and developers, and it deserves an entire book of its own. However, some areas of the SQL Server security framework are crucial to understanding how to work with a database or with any objects in a SQL Server database, and vast changes have been made in the security realm for SQL Server 2005, so I can't leave the topic completely untouched here.

SQL Server manages a hierarchical collection of entities. The most prominent of these entities are the server and databases in the server. Underneath the database level are objects. Each of these entities below the server level is owned by individuals or groups of individuals. The SQL Server security framework controls access to the entities within a SQL Server instance. Like any resource manager, the SQL Server security model has two parts: authentication and authorization.

Authentication is the process by which the SQL Server validates and establishes the identity of an individual who wants to access a resource. Authorization is the process by which SQL Server decides whether a given identity is allowed to access a resource.

In this section, I'll discuss the basic issues of database access and then describe the metadata where information on database access is stored. I'll also tell you about the new concept of schemas in SQL Server 2005 and describe how they are used to access objects.

SQL Server 2005 uses some new terms to describe features of the SQL Server security model, and some old terms are used in slightly new ways. In particular, the following two terms now have a broader meaning than in SQL Server 2000:

  • Securable Known as an object in SQL Server 2000, a securable is an entity on which permissions can be granted. Securables include databases, schemas, and objects.

  • Principal Known as a user in SQL Server 2000, a principal is an entity that can access securable objects. A primary principal represents a single user (such as a SQL Server or a Windows login); a secondary principal represents multiple users (such as a role or a Windows group).

Database Access

Authentication is performed at two different levels in SQL Server. First, anyone who wants to access any SQL Server resource must be authenticated at the server level. SQL Server 2005 security provides two basic methods for authenticating logins: Windows Authentication and SQL Server Authentication. In Windows Authentication, SQL Server login security is integrated directly with Windows security, allowing the operating system to authenticate SQL Server users. In SQL Server Authentication, an administrator creates SQL Server login accounts within SQL Server, and any user connecting to SQL Server must supply a valid SQL Server login name and password.

Windows Authentication makes use of trusted connections, which rely on the impersonation feature of Windows. Through impersonation, SQL Server can take on the security context of the Windows user account initiating the connection and test whether the security identifier (SID) has a valid privilege level. Windows impersonation and trusted connections are supported by any of the available network libraries when connecting to SQL Server.

Under Windows 2000 and Windows 2003, SQL Server can use Kerberos to support mutual authentication between the client and the server, as well as to pass a client's security credentials between computers so that work on a remote server can proceed using the credentials of the impersonated client. With Windows 2000 and Windows 2003, SQL Server uses Kerberos and delegation to support Windows Authentication as well as SQL Server Authentication.

The authentication method (or methods) used by SQL Server is determined by its security mode. SQL Server can run in one of two security modes: Windows Authentication Mode (which uses only Windows Authentication) and Mixed Mode (which can use either Windows Authentication or SQL Server Authentication, as chosen by the client). When you connect to an instance of SQL Server configured for Windows Authentication Mode, you cannot supply a SQL Server login name, and your Windows user name determines your level of access to SQL Server.

One advantage of Windows Authentication has always been that it allows SQL Server to take advantage of the security features of the operating system, such as password encryption, password aging, and minimum and maximum length restrictions on passwords. As of SQL Server 2005, when running on Windows 2003, SQL Server Authentication can also take advantage of the Windows password policies. Take a look at the ALTER LOGIN command in Books Online for the full details. Another change in SQL Server 2005 is that if you choose Windows Authentication during setup, the default SQL Server sa login will be disabled. If you switch to Mixed Mode after setup, you can enable the sa login using the ALTER LOGIN command. You can change the authentication mode in SQL Server Management Studio by right-clicking on the server name, choosing properties, and then selecting the security page. Under Server authentication, select the new server authentication mode, as shown in Figure 4-5.

Figure 4-5. Choosing an authentication mode for your SQL Server instance in the Server Properties dialog box


Under Mixed Mode, Windows-based clients can connect using Windows Authentication, and connections that don't come from Windows clients or that come across the Internet can connect using SQL Server Authentication. In addition, when a user connects to an instance of SQL Server that has been installed in Mixed Mode, the connection can always explicitly supply a SQL Server login name. This allows a connection to be made using a login name distinct from the user name in Windows.

All login names, whether from Windows or SQL Server Authentication, can be seen in the sys.server_principals catalog view, which also contains a SID for each server principal. If the principal is a Windows login, the SID is the same SID used by Windows to validate the user's access to Windows resources. The view contains rows for server roles, Windows groups, and logins mapped to certificates and asymmetric keys, but I will not discuss those principals here.

Note

Not everyone who can log in to SQL Server can see the data in the sys.server_principals view. In SQL Server 2005, metadata is fully secured, and unless you are a very privileged user or have been granted the VIEW DEFINITION permission at the server level, you cannot select from this view.


Managing Database Security

Login names can be the owners of databases, as seen in the sys.databases view, which has a column for the SID of the login that owns the database. Databases are the only resource owned by login names. As you'll see, all objects within a database are owned by database users.

The SID used by a principal determines which databases that principal has access to. Each database has a sys.database_principals catalog view, which you can think of as a mapping table that maps login names to users in that particular database. Although a login name and a user name can have the same value, they are separate things. The following query shows the mapping of users in the AdventureWorks database to login names, and it also shows the default schema (which I will discuss shortly) for each database user:

SELECT s.name as [Login Name], d.name as [User Name],      default_schema_name as [Default Schema]   FROM sys.server_principals s      JOIN sys.database_principals d   ON d.sid = s.sid;


In my AdventureWorks database, these are the results I get back:

Login Name User Name  Default Schema ---------- ---------- -------------- sa         dbo        dbo sue        sue        sue


Note that the login sue has the same value for the user name in this database. There is no guarantee that other databases that sue has access to will use the same user name. The login name sa has the user name dbo. This name is a special login that is used by the sa login, by all logins in the sysadmin role, and by whatever login is listed in sys.databases as the owner of the database. Within a database, it is users, not logins, who own objects, and users, not logins, to whom permissions are granted.

The preceding results also indicate the default schema for each user in my AdventureWorks database. In this case, the default schema is the same as the user name, but that doesn't have to be the case, as you'll see in the next section.

Databases vs. Schemas

In the ANSI SQL-92 standard, a schema is defined as a collection of database objects that are owned by a single user and form a single namespace. A namespace is a set of objects that cannot have duplicate names. For example, two tables can have the same name only if they are in separate schemas, so no two tables in the same schema can have the same name. You can think of a schema as a container of objects. (In the context of database tools, a schema also refers to the catalog information that describes the objects in a schema or database. In SQL Server Analysis Services, a schema is a description of multidimensional objects such as cubes and dimensions.)

Separation of Principals and Schemas

The previous version, SQL Server 2000, provides a CREATE SCHEMA statement, but it effectively does nothing because there is an implicit relationship between users and schemas that cannot be changed or removed. In fact, the relationship is so close that many users of SQL Server 2000 are unaware that users and schemas are different things. Every user is the owner of a schema that has the same name as the user. If you create a user sue, for example, SQL Server 2000 creates a schema called sue, which is sue's default schema. Permissions are granted to users, but objects are placed in schemas.

In SQL Server 2000, the statement GRANT CREATE TABLE TO sue refers to the user sue. Let's say sue then creates a table:

CREATE TABLE mytable (col1 varchar(20));


This table is put in the schema sue because that is sue's default schema. If another user wants to retrieve data from this table, he can issue this statement:

SELECT col1 FROM sue.mytable;


In this statement, sue refers to the schema that contains the table.

In the new version, SQL Server 2005 breaks apart the linking of users to schemas. Schemas can be owned by either primary or secondary principals. Although every object in a SQL Server 2005 database is owned by a user, we never reference an object by its owner; we reference it by the schema in which it is contained. In addition, a user is never added to a schema; schemas contain objects, not users. For backward compatibility, if you execute the sp_adduser or sp_grantdbaccess procedure to add a user to a database, SQL Server 2005 creates both a user and a schema, and it makes the schema the default schema for the new user. However, you should get used to using the new DDL CREATE USER and CREATE SCHEMA. When you create a user, you can optionally specify a default schema, but the default for the default schema is the dbo schema.

Default Schemas

When you create a new database in SQL Server 2005, several schemas are included in it. These include schemas that correspond to the default user names in SQL Server 2000: dbo, INFORMATION_SCHEMA, and guest. In addition, every database has a schema called sys, which provides a way to access all the system tables and views. Finally, every predefined database role from SQL Server 2000 has a schema in SQL Server 2005.

Users can be assigned a default schema that might or might not exist when the user is created. A user can have at most one default schema at any time. As mentioned earlier, if no default schema is specified for a user, the default schema for the user is dbo. A user's default schema is used for name resolution during object creation or object reference. This can be both good news and bad news for backward compatibility. The good news is that if you've upgraded a database from SQL Server 2000, which has many objects in the dbo schema, your code can continue to reference those objects without having to specify the schema explicitly. The bad news is that for object creation, SQL Server will try to create the object in the dbo schema rather than in a schema owned by the user creating the table. The user might not have permission to create objects in the dbo schema, even if that is the user's default schema. To avoid confusion, in SQL Server 2005 you should always specify the schema name for all object access as well as object management.

Regardless of a user's default schema, SQL Server 2005 always checks the sys schema first for any object access. For example, if a user Sue runs the query select col1 from mytable and the default schema for Sue is SueSchema, the name resolution process is as follows:

  1. Look for sys.table1.

  2. Look for SueSchema.table1.

  3. Look for dbo.table1.

Note that when a login in the sysadmin role creates an object with a single part name, the schema is always dbo. However, a sysadmin can explicitly specify an alternate schema in which to create an object.

To create an object in a schema, you must satisfy the following conditions:

  • The schema must exist.

  • The user creating the object must have permission to create the object (CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, and so on), either directly or through role membership.

  • The user creating the object must be the owner of the schema or a member of the role that owns the schema, or the user must have ALTER rights on the schema or have the ALTER ANY SCHEMA permission in the database.

We'll look again at schemas and the objects within them in Chapter 6, when we discuss metadata and tables.



Inside MicrosoftR SQL ServerT 2005. The Storage Engine
Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)
ISBN: 0735621055
EAN: 2147483647
Year: 2004
Pages: 115
Authors: Kalen Delaney

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