Object Ownership and Security

Ownership chaining, which I described in "Crossing the Line: Ownership Chains" (February 2004), offers a limited way to specify what context a particular executable module should use when being executed. The context depends on the owner of the module and the identity of the user executing the module. Ill-considered use of cross-database ownership chaining can cause security holes in SQL Server 2000 and 7.0. Yukon, the next SQL Server release, will give you far more control over the execution context of an object such as a stored procedure or function. My intention in this column is not to fully detail Yukon's features; I only want you to be aware of some of the limitations in SQL Server 2000 and 7.0 so that you'll be better prepared to take advantage of the coming enhancements.

This month, I want to prepare you for a big change you'll notice in Yukon having to do with usernames and object ownership. To be ready for the change, you need to understand the difference between a user and a schema. If you read my column regularly, you might notice that this topic is slightly out of the range of SQL Server behaviors that I usually cover. It involves no clever code, no system-table access, and no undocumented trace flags or DBCC commands. However, I decided to cover this topic because it's crucial to understanding one of the biggest changes in object management in Yukon, and it might even clarify some behaviors in the current releases that new users frequently have trouble with.

Logins vs. Usernames, Round 2

Before discussing users, owners, and schemas, I want to elaborate a bit on one point I made in February when addressing the difference between login names and usernames. I wrote, "DBO is only a username; it maps to a login that is the database owner." In my 17 years of working with SQL Server, people have always been confused about the special username DBO. You can think of DBO as being an acronym for database owner, but I find that just thinking of it as a username—albeit a special, privileged username—can help avoid certain confusions.

A few years ago, I was hired to provide technical review and editing for a SQL Server certification exam simulation program. The practice test had several questions that started something like this: "Assume the database owner is DBO." On the one hand, this wording is completely incorrect because, unlike database objects, databases are owned by logins, not users, and DBO is a username. On the other hand, if the question writer meant that the username used by the database owner is DBO, you don't have to make this assumption because no other possibility exists. The owner of the database can use no other name, and DBO is always the username used by the database owner.

Since SQL Server 7.0 introduced database roles, people have also been confused about the difference between the DBO user and the db_owner database role. The DBO user is always a member of the db_owner role, but not every user in the role is the user DBO. In SQL Server, by default the owner of any object is the username of the user who created the object, so even if user sue is in the db_owner role, objects she creates are owned by user sue. However, when creating an object, users in the db_owner (or db_ddladmin) role can override this default and specify a different owner or can change existing object owners by using the sp_changeobjectowner stored procedure. People on the public newsgroups frequently ask how to set a different default owner for newly created objects, but in the currently available releases, it's impossible.

What's in a Name?

You can refer to most objects in SQL Server by a three-part name, where the first part is the database, the second part is the object owner, and the third is the object name. So a SELECT statement with a fully qualified object might look like this:

SELECT au_fname, au_lnameFROM pubs.dbo.authors

If you don't specify a database name, the default is the current database. If you don't specify an object owner, SQL Server has two possible defaults. First, SQL Server checks whether the current user owns an object of that name; if not, SQL Server checks whether the user DBO owns an object of that name.

The object owner frequently is the user DBO, so many people think that the second part of the three-part name must be the database owner. But that would be pointless—because the database owner always has the username DBO, using DBO in that position of the name qualification wouldn't give any extra information. The second part of a three-part name is the object owner, who might or might not be the user DBO. The object owner might be the user sue, in which case any user but sue would always have to specify the object owner name sue.

In SQL Server 7.0, Microsoft introduced the ability to create a linked server, through which you can access objects on another SQL Server or other data source. Linked server objects have four-part names, with the first part being the server name. Because linked servers don't have to be SQL Servers, the documentation describes the third part of a four-part name as the schema name, even though SQL Server 2000 and 7.0 don't contain the concept of schemas. ANSI defines a schema as a collection of database objects that one user owns and that form a single namespace. A namespace is a set of objects that can't have duplicate names. Thus, two tables can have the same name only if they're in separate schemas, and no two tables in the same schema can have the same name.

SQL Server uses the object owner's name in this third position, but an owner isn't the same as a schema, at least in SQL Server 2000 and 7.0. Yukon will let you create and manipulate schemas, which will not only give greater ANSI compliance but will also solve some problems with object naming.

Because SQL Server 2000 and 7.0 treat schemas and users as the same thing, understanding the difference can be difficult. Every user has a default schema; if a user has permission to create objects, SQL Server puts all new objects that the user creates into that user's default schema (if the user didn't specify a schema). For example, when you create a user named sue, you're creating both a user and a schema. The default schema for the user sue is the schema sue. When the user sue creates a new table (assuming she has creation permission), the table goes into the schema sue, so other users must use the following syntax to access it:

SELECT FROM sue.new_table

What's the Difference?

One way to start getting used to the difference between users and schemas is to realize that you can grant permissions to users, but you reference objects by the schema they're in. Every user has a default schema—which has the same name as the username—and in SQL Server 2000 and 7.0, you can't change this default schema.

A big feature of Yukon is user-schema separation. That is, creating a user won't automatically create a schema of the same name. In addition, although every user must have a default schema, Yukon doesn't require that the default schema have the same name as the username.

For backward compatibility, when you use the SQL Server 2000 or 7.0 stored procedures sp_adduser and sp_grantdbaccess, Yukon will create a user and a schema of the same name and make the new schema the default schema for the new user. However, Microsoft recommends that in Yukon you use the CREATE USER and CREATE SCHEMA commands separately so that you have full control over which schemas are created and which schema is the default for each user.

Yukon's CREATE USER command will let you specify a default schema for the new user. If you don't specify a schema, the default is the DBO schema, which every database always contains. Having the default schema be DBO means that when a user selects from an object without specifying a schema, SQL Server will assume that the object is part of the DBO schema. That behavior isn't much different from that of SQL Server 2000, in which SQL Server checks whether the user DBO owns the object if the current user doesn't own an object of the specified name. However, if a user with the default schema of DBO creates a new object, Yukon will place the new object in the DBO schema. This isn't the current behavior, but as I mentioned, it's an often-requested feature that will be especially handy for members of the db_owner role.

Looking Forward to Yukon

Next month, I'll give you more specific details about working with users and schemas in Yukon, and I'll show you the syntax for some of the new commands. In upcoming columns, I'll also tell you about more new security features in Yukon. And, as the Yukon release date approaches, I'll start giving more complete descriptions and internal details of the new security capabilities. Until then, this bit of forewarning about what to expect can help you be more aware of the behavior of and limitations in the current security model.