Database Security in SQL Server 6.5

One tricky aspect of security, beyond recognizing that a user is who he says he is, is verifying that he is accessing only the information to which he is entitled. One area that deals with this issue is database access authentication. This article focuses on database access authentication for SQL Server 6.5. It is excerpted from chapter three of SQL Server Security Distilled, second edition, written by Morris Lewis (Apress, 2004; ISBN 1590592190).

Contributed by Apress Publishing
Rating:

/ 19
March 30, 2005

Rate this Article:
MEH MEH++

SEARCH ASP FREE

Database Groups

Let’s consider an example to clarify the way this process works. Assume that JoeS has the SUID of 11 and JaneD has the SUID of 12. In the Accounting database, there is a row in the sysusers table assigning JoeS the UID of 4, but there is no corresponding row containing JaneD’s SUID. The table’s entire contents are shown here:

suid uid gid name
11 4 0 JoeS

However, there is a row in sysalternates mapping JaneD’s SUID of 12 to JoeS’s SUID of 11. sysalternates’s contents are as follows:

suid altsuid
12 11

When JaneD attempts to use the Accounting database, SQL Server queries the sysusers table. Finding no match for SUID 12, it checks the sysalternates table to see if there is a row with 12 in the suid column. Finding that row, SQL Server assigns JaneD the UID 4 and the database username JoeS. At that point, she impersonates JoeS’s identity and receives all of his permissions.

Note that checking the sysalternates table comes after checking the sysusers table and before checking to see if the guest user account is enabled.

If JaneD’s SUID were also in sysusers, she would have gained access using her own credentials, not JoeS’s. The net effect is that a user can have their SUID in either sysusers or sysalternates. If it somehow happens to end up in both tables, the identity in the sysuser’s table takes precedence over the alias in sysalternates.

Advantages of Aliasing

The main reason for using aliases is to allow multiple users to impersonate the database owner account, dbo, which has some permissions in the database that cannot be duplicated by assigning individual permissions to another database user. SQL Server 6.5 permits only one login account to be designated as dbo; therefore, the only way for other users to perform some actions in a database is to be aliased to dbo.

The typical scenario is that multiple database developers need full access to the database during the development phase. The problem is that there can be only one database owner. sysusers has a row for dbo that maps the SUID of the person who created the database to UID 1. That UID has been hard-coded in the database engine with the special permissions. You can change the SUID mapped to dbo, but you cannot change its UID. If more than one person needs dbo permissions, the only way to make it work is by adding rows to sysalternates that map other SUIDs to the SUID assigned to dbo.

sp_addalias is the system stored procedure that performs the task of inserting rows in sysalternates. Its syntax is as follows:

sp_addalias [ @loginame = ] 'login'
, [ @name_in_db = ] 'alias_user'

One real benefit of aliases is that aliasing one SUID to another does not have any effect outside the database. Recall that the sa account always has the SUID of 1 and that SQL Server is written so that it completely bypasses all security checks for sa. The standard practice is to have system administrators create databases; therefore, sa will typically be the database owner, which means the row for dbo in sysusers will contain a SUID of 1. Using aliases, you can grant someone the status of dbo without giving that user all the power of the sa account. Without aliases, the only recourse would be to allow anyone who needed full access to a database to log into SQL Server with the sa account. Although it is a very common practice for developers to use the sa account, it is nonetheless a very bad practice.

Checking Database Access Privileges

It is important to note that SQL Server checks database access whenever a user attempts to access database objects. The typical example is when a user issues the Transact-SQL (T-SQL) USE <database> command. However, if you refer to a table using its full name—that is, <database.owner.tablename>—for a table outside the currently selected database, then SQL Server will perform all three checks in the specified database to see if the user has been granted access.

For example, if a user issues the following command:

USE Northwind
SELECT * FROM pubs.dbo.authors

Having set the current database to Northwind, SQL Server then checks to see if there is a row in sysusers in the Pubs database that contains the user’s SUID before it begins processing the SELECT query. If there is no matching row in sysusers, SQL Server will check the sysalternates table in Pubs for the user’s SUID. If that check fails, it will check to see if the guest database account has been enabled in Pubs. In this case, because the default setup enables the guest user account in the Pubs database, everyone has permission to access Pubs. If all three checks fail, the user will be denied permission to run the query.

Database Usernames

If you look back at the definition of sysusers, you will see it also has a name column. This allows an administrator to give a name to a user, which can be different from the one specified in Master..syslogins. Its usefulness is fairly limited, and Enterprise Manager defaults to creating a user account with the same name as the login account.

The one situation, however, in which it can be useful to have a username different from a login name, is when the user logs in with a Windows NT/2000 authenticated account. The username Joe Smith is easier to read than the login name domain_JoeS for things such as reports and activity logs. For example, on a recent web project, I put the user’s full name in the name column instead of creating a table that pretty much duplicated sysusers. It eliminated the need for a multitable join query to map the login name to the name the user supplied on the web site. It is the only time I have ever used the feature, but it did help performance a little bit.

After the user issues the T-SQL USE <database> command, he will have four names within SQL Server. There are four system functions to find these names:

Two within the server:

The SUID returned by SUSER_ID()

The system username returned by SUSER_NAME()

Two within the database:

The database UID returned by USER_ID()

The database username returned by USER_NAME()

NOTE Note that using an alternative username should not be confused with aliasing. Aliasing maps a user’s SUID to another database user’s SUID, whereas the database username is just another way of referring to a user. SQL Server still uses the UID for identification internally; therefore, the username has no effect beyond giving you the option of using a different name from the
login name.

Managing Users with T-SQL

The following is the syntax for the two system stored procedures, sp_adduser and sp_dropuser, used in SQL Server 6.5 to add and drop users, respectively:

sp_adduser [ @loginame = ] 'login'
[ , [ @name_in_db = ] 'user' ]
[ , [ @grpname = ] 'group' ]
sp_dropuser [ @name_in_db = ] 'user'

For the sp_adduser stored procedure

The @loginame parameter specifies the login name from the syslogins table. Note that although SQL Server 6.5 uses the SUID internally, the stored procedure uses the name itself.
The @name_in_db parameter is the name the user will have within the database.

Common practice is to make the database username match the login name, but it is not a requirement. It does get confusing, however, if the two names do not match.

The @grpname parameter specifies the user’s default database group, which I cover in the next section.

NOTE Further details of how to use these stored procedures can be found in SQL Server Books Online for SQL Server 6.5.

This article is excerpted from SQL Server Security Distilled by Morris Lewis (Apress, 2004; ISBN 1590592190). Check it out at your favorite bookstore today. Buy this book now.

Managing Database Permissions

In addition to mapping SUIDs to UIDs, sysusers also contains information about database groups. Similar to Windows NT/2000 groups, database groups are a way of assigning permissions to a set of users as whole instead of individually. However, there is a key difference between Windows NT/2000 groups and SQL Server 6.5 database groups, namely a database user can be a member of only one user-defined group at a time. If you look back at the definition of sysusers, the reason for this limitation will be obvious—it has only one column, gid, to hold group membership information.

The following is a partial listing of sysusers after I added a couple of users and a few groups to the Pubs database:

suid     uid       gid          name
-16387    16387       16387        Users
-16386    16386       16386        Test2
-16385    16385       16385        Test1
-16384    16384       16384        Test
-2       0        0            public
-1        2         0            guest
1         1           0            dbo
11       3           16387        SQLExecutiveCmdExec
12       4         16387        JoeS

Note: The environ column was omitted for brevity.

Two properties distinguish a group in SQL Server 6.5. First, the UID for groups starts at 16384 and increments by one for each new group. Second, the UID and the group identifier (GID) are the same, and the SUID is the negative of the UID. When SQL Server checks permissions, it first finds the user’s UID in sysusers and retrieves the GID, and then it looks up permissions for both the UID and the GID.

The effective set of permissions will be the combination of those granted to the user individually and to the group of which the user is a member.

In the preceding table, you can also see an entry with the name public, which is a default group in which all users are members. If a user has a matching row in sysusers (which must happen to gain access to the database), she is a member of public. Database users, therefore, are members of a maximum of two groups: public and one other user-defined group. Because of this fact, every user also gains the permissions granted to the public group.

When I discuss designing security for various scenarios in later chapters, I cover strategies for using groups to assign permissions. At this point, though, you need to pay attention to one possible problem with enabling the guest database user.

If the guest user is enabled in a database, everyone who can log into SQL Server can also access the database. Because all users are members of the public group, the guest user is too. The end result, therefore, is that enabling the guest account grants the permissions that are granted to public to everyone who can log into the server. Although I have worked on a project where this was exactly the behavior I wanted, it is much more common that enabling the guest user will allow someone to have inappropriate access to the database.

NOTE When you are designing your database security, you must consider carefully whether having the guest user is appropriate for your situation. My recommendation is not to enable it by default.

Object Ownership

In SQL Server 6.5, an object is a table, view, stored procedure, default, rule, constraint, or trigger. All objects have owners. Objects start out being owned by the user who created them, but dbo can reassign ownership of any object in the database. (So can sa, for that matter.) The definition of the sysobjects system table, in which object ownership information is stored, is shown in Table 3-3.

Table 3-3. The sysobjects System Table

Column Data Type Description

name varchar(30) Object name id int Object ID uid smallint User ID of owner object

Table 3-3. The sysobjects System Table (contintued)

Column

type

userstat sysstat indexdel schema refdate crdate version deltrig instrig updtrig seltrig category cache

Data Type

char(2)

smallint smallint smallint smallint datetime datetime datetime int int int int int smallint

Description

One of the following object types: C = CHECK constraint D = Default or DEFAULT constraint F = FOREIGN KEY constraint K = PRIMARY KEY or UNIQUE constraint L = Log P = Stored procedure R = Rule RF = Stored procedure for replication S = System table TR = Trigger U = User table V = View X = Extended stored procedure

Application-dependent type information

Internal-status information

Index delete count (incremented if an index is deleted)

Count of changes in schema of a given object

Reserved for future use

Indicates the date that the object was created

Reserved for future use

Stored-procedure ID of a DELETE trigger

Stored-procedure ID of an insert TRIGGER

Stored-procedure ID of an update TRIGGER

Reserved

Used for publication, constraints, and identity

Reserved

In this table, the uid column holds the UID of the object owner.

One interesting consequence of object ownership is that a database may have two objects with the same name, as long as different users own them. The fully qualified name of an object is <database.owner.objectname>, and the owner may be any user in the database. If you leave off the owner, SQL Server assumes you mean an object owned by dbo; therefore, the standard practice is to have dbo own all objects in a database when it goes into production. Nevertheless, it is possible to have several tables with the same name and different owners.

As a demonstration of this “feature,” you can run the following commands in the Test database:

DROP TABLE JaneD.Test
GO
DROP TABLE Joe_Smith.Test
GO
CREATE TABLE JaneD.Test ( col char(20) )
GO
INSERT JaneD.Test VALUES ('Jane''s Table')
GO
CREATE TABLE Joe_Smith.Test ( col char(20) )
GO
INSERT Joe_Smith.Test VALUES ('Joe''s Table')
GO
SELECT * FROM JaneD.Test
SELECT * FROM Joe_Smith.Test
SELECT * FROM Test

What you will find is that the first query returns the result Jane's Table, the second returns Joe's Table, and the third returns the error Invalid object name 'Test'. The standard practice of leaving off the owner comes from the fact that SQL Server uses dbo as the default. Therefore, the query SELECT * FROM dbo.Test is semantically identical to the third query in the example.

In general, any database user can own objects in the database if he has permission to create them. An exception to the rule is that groups cannot own objects. You can assign the permissions to create objects to groups, but the owner will be the user who created the object, not the group.

There is another, more complex problem with having more than one object owner in a database, but I save discussion of that topic until after I cover permissions a little later in the chapter (in the section titled “Ownership Chains”) because the problem stems from how SQL Server checks permissions.

Finally, before I discuss permissions, let me remind you once again that the sa account has special status in SQL Server 6.5 with regard to permissions—it completely bypasses the code that handles permission checking. It is impossible to restrict the sa account in any way. In addition, remember that everyone granted administrator privileges in the SQL Security Manager tool will actually have their Windows NT/2000 accounts mapped to the sa login account. They do not have separate identities within SQL Server’s environment, and they will have every privilege sa has, which is saying they have unlimited control over the server.

CAUTION You should be extremely careful about who has access to the sa account in all versions of SQL Server.

This article is excerpted from SQL Server Security Distilled by Morris Lewis (Apress, 2004; ISBN 1590592190). Check it out at your favorite bookstore today. Buy this book now.

SETUSER

Once inside a database, SQL Server determines what a user (who is not an administrator) can do by looking at the list of object permissions assigned to the UID. SELECT, INSERT, UPDATE, DELETE, REFERENCE, and EXECUTE are object permissions that control what a user can do with tables, views, and stored procedures. Statement permissions control what objects the user can create, alter, or delete in the database.

Permissions are represented by rows in the sysprotects system table, the structure of which is shown in Table 3-4.

*Table 3-4. The* sysprotects *System Table*
Column	Data Type	Description
id	int	ID of object to which this permission applies.
uid	smallint	ID of user or group to which this permission applies.
action	tinyint	One of the following permissions:
		26 = REFERENCES
		193 = SELECT
		195 = INSERT
		196 = DELETE
		197 = UPDATE
		198 = CREATE TABLE
		203 = CREATE DATABASE
		207 = CREATE VIEW
		222 = CREATE PROCEDURE
		224 = EXECUTE
		228 = DUMP DATABASE
		233 = CREATE DEFAULT
		235 = DUMP TRANSACTION
		236 = CREATE RULE
protecttype	tinyint	Either 205 (GRANT) or 206 (REVOKE).

*Table 3-4. The* sysprotects *System Table (continued)*
Column	Data Type	Description
columns	varbinary(32)	Bitmap of columns to which this SELECT or UPDATE
		permission applies. Bit 0 indicates all columns; bit 1
		means permission applies to that column; NULL means
		no information.

The first two columns are the same for most system tables that have an effect on database objects. The id column holds the object ID of the object for which you want to assign a permission. The uid column holds the UID for the user who will receive the permission. The next column, action, holds an 8-bit integer that describes the type of permission being assigned—you can see the types of permission listed in the table. The protecttype column holds a value of either 205 or 206 depending on whether the row describes a GRANT action or a REVOKE action. Finally, the columns column is a bitmap for column-level permissions, which I cover a little later.

The key point to know about permissions is that they are represented by rows in sysprotects. If there is no row corresponding to the combination of the object and UID or the object and GID, the user has no permissions on the object. The only users that have permissions not reflected in sysprotects are sa, the database owner (dbo), and database object owners. Because sa has all database permissions, we will focus on dbo and object owners.

The Database Owner

The database owner, dbo, has special rights in the database that cannot be superseded or abridged by any other user. The only entity that has more rights on the server is sa. As you would expect, dbo only has rights within the database she owns; therefore, someone can be the database owner in one database and a regular user in another. Here is the list of permissions that only dbo has that cannot be granted to other database users:

CHECKPOINT
DBCC
DROP DATABASE

• GRANT and REVOKE statement permissions

• LOAD DATABASE

LOAD TRANSACTION
SETUSER

dbo has built-in permission to run all statements and can grant some of those statement permissions to other database users. Here is the list of statement permissions that may be granted to other users:

CREATE DEFAULT
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW
DUMP DATABASE
DUMP TRANSACTION

Database Object Owner

The purpose of granting permission to execute these statements is to allow other users to create objects in the database. Creating those objects grants the user special status as database object owner (dboo) for that particular object. Whereas there can be only one dbo, every user in the database may own one or more objects. Like dbo, dboo has special rights and privileges not shown in sysprotects.

In particular, dboo has sole authority to decide what permissions on the object will be granted to other users. Only the object owner can use the GRANT and REVOKE commands for his object:

GRANT {ALL | permission_list}
ON {table_name [(column_list)] | view_name [(column_list)]
|
stored_procedure_name | extended_stored_procedure_name}
TO {PUBLIC | name_list}
REVOKE {ALL | permission_list}
ON {table_name [(column_list)] | view_name [(column_list)]
|
stored_procedure_name | extended_stored_procedure_name} FROM {PUBLIC | name_list}

NOTE You can read more about the process of granting and revoking permissions in the section "The Art of Assigning Permissions" later in the chapter.

Not even the dbo can assign permissions for objects he does not own, nor can he use another owner’s objects if the owner does not grant permission. Here is the list of permissions dboo receives for the object(s) he owns:

ALTER TABLE
CREATE INDEX
DROP INDEX
CREATE TRIGGER
DROP TRIGGER
DROP TABLE
TRUNCATE TABLE
UPDATE STATISTICS

• GRANT and REVOKE (only on owned objects)

NOTE None of these permissions may be transferred to other users.

This article is excerpted from SQL Server Security Distilled by Morris Lewis (Apress, 2004; ISBN 1590592190). Check it out at your favorite bookstore today. Buy this book now.

Working with Groups

Only dbo (and, of course, sa) can run the SETUSER command. Unlike sa, SQL Server forces dbo to adhere to the permissions set on objects in the database, which means that an object owner can prevent dbo from managing objects in the database she owns. That is clearly not an acceptable situation, so we have the SETUSER command to allow dbo to impersonate another user in the database. While the consequences of the SETUSER command are in effect, dbo has all the rights, privileges, and permissions of the user, including those of dboo if the user owns any objects. Changing permissions on an object is just a matter of executing the SETUSER command and then making the changes while impersonating the dboo. As you will see in some examples in the next section, this ability can be useful when creating objects that will be owned by another user or in testing permissions for a given user.

You’ll shortly explore assigning permissions, but first I need to cover a couple of topics: who can view the tables in your database and who can execute stored procedures.

Viewing System Tables

By default, SQL Server grants permission to query system tables to all users when it creates a new database. No one can update or delete rows in the tables unless he executes a special system stored procedure (i.e., sp_configure 'allow updates', which only sa can use), but he can run SELECT queries on them. So far in 9 years, I have never found a reason to prevent users from seeing the contents of these tables; but you should know they have the ability, in case you have a need to prevent it.

Executing Stored Procedures

Because all the system stored procedures reside in the Master database, and the guest account is active in that database, and because the guest account has EXECUTE permission on the system stored procedures, every database user can run them, with a couple of exceptions. The system stored procedures that perform a task only dbo or sa should be able to perform (for example, sp_adduser) check the UID (which is always 1 for dbo)to prevent unauthorized use. All the rest of the procedures, however, can be run by anyone.

If you need to limit access to a system stored procedure, you must first run sp_adduser to map the SID to a UID in the Master database. Then, you will need to revoke the execute permission from the user. Once again, I have found no reason to worry about this, but you should know about it just in case it makes a difference in your environment.

Finally, because system stored procedures reside in the Master database, individual dbos who do not have sa privileges have no way of limiting their usage within their databases. In addition, there is no way to confine the effects of anything your system administrator does to a single database. Denying someone access to a system stored procedure means that person cannot run the procedure not only in one particular database, but also on the server as a whole. You should, therefore, think carefully about the ramifications of changing the default setup.

The Art of Assigning Permissions

Assigning permissions in SQL Server 6.5 is really more of an art than a science. Although SQL Server does follow some specific rules, the algorithm has eight decision points. This makes it difficult—but not impossible, as you’ll see—to decide exactly what steps you need to follow to change a user’s permissions in a particular way. First, you’ll examine the rules SQL Server follows, and then you’ll go on to look at examples of how you can work with these rules to attain the assignment of your desired permissions.

What Happens in Sysprotects?

Everything starts with the database system table sysprotects; therefore, let’s look at how your assignments of permissions affect it.

GRANT and REVOKE

Each time you issue a GRANT or REVOKE statement, a row is either added or removed from sysprotects to change the list of permissions.

NOTE SQL Server 6.5 has a different behavior prior to Service Pack 5. Because Service Pack 5a (version 6.5.416) is the standard for this book, I focus on how it behaves. Just remember that if you work on a system with an earlier service pack, the description in this section will not be accurate.

Here are the rules SQL Server follows and the steps it takes.

For GRANT statements:

If no row exists matching the object, user, permission, and action, add one and exit.
If a row matches the object, user, permission, and action, do nothing and exit.
If a row matches the object, user, and permission but has a revoke action (a value of 206), change the permission to a grant action (205) and exit.
If the public group, or a group of which the user is a member, has a row matching the object and permission, add a row and exit.

For REVOKE statements:

If no row exists matching the object, user, permission, and action, do nothing and exit.
If a row exists matching the object, user, permission, and action, do nothing and exit.

3. If a row matches the object, user, and permission but has a
grant action (205), remove the row and exit.

   4. If the public group, or a group of which the user is a member,
       has a row matching the object and permission, add a row and
       exit.

Group Permissions

Permissions affecting groups add a little complexity—group and user permissions follow a hierarchy in which permissions assigned to a user override permissions assigned to a user-defined group, and both user-specific permissions and group permissions override permissions assigned to public. In other words, permissions assigned specifically to FredJ override permissions assigned both to the Test group and to public, and permissions assigned to the Test group override permissions assigned to public. The rules for groups are as follows.

For GRANT statements:

If no row exists matching the object, group, permission, and action, add a row and exit.
If a row exists matching the object, group, permission, and action, do nothing and exit.
If a row matches the object, user, and permission but has a revoke action (206), change the action to 205 and exit.

For REVOKE statements:

If the public group has a row matching the object and permission, add a row and exit.
If no row exists matching the object, group, permission, and action, do nothing and exit.
If a row exists matching the object, group, permission, and action, do nothing and exit.
If a row matches the object, user, and permission but has a grant action (205), remove the row and exit.

These rules make a lot more sense if you run a few examples, so let’s do that now. Each of the samples in the sections that follow assumes the Test database has the users JaneD, Joe_Smith, and FredJ and the user-defined group test. If you didn’t set this up according to the instructions at the beginning of the chapter, go ahead and do so now.

Granting and Revoking Permissions

Run the following commands:

SETUSER 'Joe_Smith'
SELECT * FROM PermDemo
SETUSER
GRANT SELECT ON PermDemo TO Joe_Smith
SELECT a.name, b.*
FROM sysobjects a, sysprotects b
WHERE a.id = b.id AND a.name = 'PermDemo'
SETUSER 'Joe_Smith'
SELECT * FROM PermDemo
SETUSER

The following table shows the result of the preceding code.

name id uid action protecttype
PermDemo 16003088 3 193 205

You should see that there is a new row in sysprotects.Joe’s first SELECT statement on PermDemo will fail, and his second one will succeed. This is a good demonstration of the fact that if a user does not have permissions on an object, that user cannot do anything with it. If you run the commands a second time, you will notice that the new row is unchanged and that Joe’s first SELECT now works.

In this next example, you revoke the SELECT permission from Joe_Smith:

SETUSER 'Joe_Smith'
SELECT * FROM PermDemo
SETUSER
REVOKE SELECT ON PermDemo FROM Joe_Smith
SELECT a.name, b.*
FROM sysobjects a, sysprotects b
WHERE a.id = b.id AND a.name = 'PermDemo'
SETUSER 'Joe_Smith'
SELECT * FROM PermDemo
SETUSER

The following table shows the result of the preceding code.

name id uid action protecttype

Now if you run this example, you will notice there are no rows in sysprotects, and Joe’s second SELECT on PermDemo fails. This is an example of how the REVOKE command removes GRANT permissions by removing the row from sysprotects. If you run the commands a second time, both of Joe’s SELECTs will fail, and there will be no rows in sysprotects. This is the behavior that was implemented in Service Pack 5.

This article is excerpted from SQL Server Security Distilled by Morris Lewis (Apress, 2004; ISBN 1590592190). Check it out at your favorite bookstore today. Buy this book now.

Stored Procedures

In this example, you assign the SELECT permission to public:

SETUSER 'Joe_Smith'
SELECT * FROM PermDemo
SETUSER
GRANT SELECT ON PermDemo TO public
SELECT a.name, b.*
FROM sysobjects a, sysprotects b
WHERE a.id = b.id AND a.name = 'PermDemo'
SETUSER 'Joe_Smith'
SELECT * FROM PermDemo
SETUSER
SETUSER 'JaneD'
SELECT * FROM PermDemo
SETUSER

The following table shows the result of the preceding code.

name id uid action protecttype
PermDemo 16003088 0 193 205

As you would expect, Joe’s first SELECT on PermDemo fails, but both his second SELECT and Jane’s SELECTs succeed. As in the first example, sysprotects contains a single row, but in this case, uid indicates a group instead of a user, because 0 is the UID for the public group.

Things get interesting in this next example. You’ll see what happens when you repeat the second example and try to revoke the permission to SELECT from Joe_Smith:

The following table shows the result of the preceding code.

name        id          uid action protecttype
PermDemo    16003088    0    193     205
PermDemo    16003088   3    193   206

The difference now is that the public group has permissions assigned to it. SQL Server adds a second row to revoke Joe_Smith’s SELECT permission that he receives from public. Now when the server checks permissions, everyone other than Joe will have SELECT permission. Once again, running the REVOKE command a second time will not affect the rows in sysprotects.

Now let’s see what happens if you revoke the SELECT permission from the public group:

SETUSER 'JaneD'
SELECT * FROM PermDemo
SETUSER
REVOKE SELECT ON PermDemo FROM public
SELECT a.name, b.*
FROM sysobjects a, sysprotects b
WHERE a.id = b.id AND a.name = 'PermDemo'
SETUSER 'JaneD'
SELECT * FROM PermDemo
SETUSER

The following table shows the result of the preceding code.

name id uid action protecttype
PermDemo 16003088 3 193 206

Now, the row in sysprotects granting SELECT permission to the public group is gone, leaving just Joe_Smith’s REVOKE row. It turns out that this series of commands in this order is the only way to explicitly deny a permission to a user. You have to grant the permission to a group, revoke it from a user, and then revoke it from the group to leave just the user with a revoked permission.

Before you see how to eliminate that row, you’ll first look at what happens when you grant the SELECT permission to the test group, of which Joe_Smith is a member:

SETUSER 'Joe_Smith'
SELECT * FROM PermDemo
SETUSER
GRANT SELECT ON PermDemo TO test
SELECT a.name, b.*
FROM sysobjects a, sysprotects b
WHERE a.id = b.id AND a.name = 'PermDemo'
SETUSER 'Joe_Smith'
SELECT * FROM PermDemo
SETUSER
SETUSER 'FredJ'
SELECT * FROM PermDemo
SETUSER
SETUSER 'JaneD'
SELECT * FROM PermDemo
SETUSER

The following table shows the result of the preceding code.

name       id         uid     action    protecttype
PermDemo   16003088   3        193        206
PermDemo   16003088    16384    193       205

Once again, both SELECT commands fail for Joe_Smith. For FredJ, who is also a member of the test group, the SELECT succeeds, but for JaneD it fails, because she is not a member. This is an example of how Joe’s individual permissions take precedence over the group permissions.

Let’s look now at how to remove Joe’s row from sysprotects:

The following table shows the result of the preceding code.

name         id        uid    action protecttype
PermDemo    16003088 3      193     205
PermDemo     16003088 16384 193     205

At this point, both Joe_Smith and FredJ can query the PermDemo table, but JaneD cannot. Notice that the GRANT statement did not remove Joe’s row from sysprotects. Instead, it changed the action value from a 206 to a 205, which means Joe_Smith has now been explicitly granted SELECT permission on PermDemo. Should you revoke the test group permission, Joe would still be able to query the table.

What happens if you revoke Joe’s permission right now is another interesting question:

The following table shows the result of the preceding code.

name id uid action protecttype
PermDemo 16003088 16384 193 205

The result of running these commands is that the ability to query the table stays the same, but Joe’s row is deleted from the sysprotects table. If you have a user with a single explicit REVOKE entry on an object, the way to remove it is by granting the same permission on the object and then revoking that permission. The same process will take care of a single explicit GRANT entry too. The key is to remember that the first statement reverses the action, and the second statement removes the row.

Permission Hierarchies

This next to last example rounds off the discussion by showing the hierarchy of permission assignment:

REVOKE SELECT ON PermDemo FROM test
SELECT a.name, b.*
FROM sysobjects a, sysprotects b
WHERE a.id = b.id AND a.name = 'PermDemo'
--Should be no rows in sysprotects now
GRANT SELECT ON PermDemo TO public
REVOKE SELECT ON PermDemo FROM test
GRANT SELECT ON PermDemo TO Joe_Smith
SELECT a.name, b.*
FROM sysobjects a, sysprotects b
WHERE a.id = b.id AND a.name = 'PermDemo'
SETUSER 'Joe_Smith'
SELECT * FROM PermDemo
SETUSER
SETUSER 'FredJ'
SELECT * FROM PermDemo
SETUSER
SETUSER 'JaneD'
SELECT * FROM PermDemo
SETUSER

The following table shows the result of the preceding code.

name       id         uid    action    protecttype
PermDemo   16003088 0     193        205
PermDemo    16003088 3      193        205
PermDemo    16003088   16384   193       206

JaneD can query the table because she is a member of the public group. FredJ cannot query the table because the Test group has an explicit REVOKE entry. On the other hand, Joe_Smith’s SELECT statement works because he has a row in sysprotects granting him SELECT permission.

These examples certainly do not cover every permutation, but they should give a sense of why the order of your commands is extremely important. In general, the real complexity comes from assigning permissions to groups. Without them, the algorithm is very simple, although a bit limited, particularly if you want to assign explicit REVOKE permissions to a user.

It is when sysprotects contains permissions for groups that you have to pay attention because SQL Server will add rows in that situation when it would not otherwise. In addition, removing group rows from sysprotects does not also remove the rows for the group’s members. As you saw, if you want to remove those rows, you have to go through a two-step process for each user. All of these considerations combined make designing an easily managed security plan difficult for SQL Server 6.5.

What you will find is that the limitation of two groups (public and one other one) will force you to assign permissions on an individual basis most of the time. You should start by assigning the permissions that all users should have to the public group. You can then use other groups to grant or revoke permissions to smaller sets of users.

NOTE Just remember that if the public group has a row in sysprotects, REVOKE statements will add a row in most situations. It is, in fact, the only way to set up explicit REVOKE permissions for both users and groups.

After you have exhausted the limited options for assigning permissions through groups, you will need to assign all the rest of the permissions on a user-by-user basis.

The main point for network administrators to take from this is that SQL Server 6.5 groups do not behave like Windows NT/2000 groups. Whereas the most logical way to manage users’ permissions in Windows is through groups, SQL Server 6.5

almost forces you to manage permissions without them. At least now when you read Chapter 4, you will have an appreciation of why SQL Server 7.0 and 2000 are such an improvement.

This article is excerpted from SQL Server Security Distilled by Morris Lewis (Apress, 2004; ISBN 1590592190). Check it out at your favorite bookstore today. Buy this book now.

At first, having multiple object owners may seem like a good way to segment one database into multiple sections or to allow users to manage access to their own tables. As it turns out, having more than one object owner in a database causes problems when SQL Server checks permissions.

Views

The problem arises when one object (for example, a view) refers to one or more objects that are owned by two or more database users. A simple example could be a view created by JaneD that uses two tables owned by Joe_Smith:

SETUSER 'JaneD'
CREATE VIEW JaneD.Order_Totals (CustID, Order_Total)
AS
SELECT CustID, SUM(Total)
FROM Joe_Smith.Orders
GROUP BY CustID
SETUSER

This is known as an ownership chain and is shown in Figure 3-1.

Figure 3-1. A simple ownership chain

NOTE Run the script ownershipChains_setup.sql now. You”ll find it in the code download for this book at http://www.WinNetSecurity. com, from which you can create all the tables and the views in this section.

Both JaneD and Joe_Smith have the right to assign permissions on their own objects because they are the owners. To be able to reference his tables, Joe_Smith would need to also grant SELECT permission on them to JaneD. However, JaneD does not have this permission. JaneD can grant SELECT, INSERT, UPDATE, and DELETE permissions to any other user for her view, but she cannot grant any permissions to Joe’s underlying tables at all.

Let’s stop for a moment and think about the ramifications of this situation. JaneD has created a view on Joe_Smith’s tables, and because she is the owner of that view, she can assign permissions however she wants. Assume Joe_Smith granted SELECT permission on his tables to JaneD but not to anyone else. Should JaneD essentially be able to transfer her permissions to another user by creating a view on Joe_Smith’s table? In other words, should FredJ be trusted with Joe_Smith’s tables because Joe_Smith trusts JaneD and JaneD trusts FredJ? This kind of transitive permission assignment violates the concept that the owner of an object should have complete control over who has permission to use it, so SQL Server does not allow it.

At least that is what SQL Server Books Online (BOL) says should happen. In one of the instances in which this book contradicts the documentation, let me demonstrate what actually does happen.

After running the script to build the objects and set the permissions shown in Figure 3-1, you have a situation in which JaneD has no permissions whatsoever to use Joe_Smith’s Customers and Orders tables. You can prove that fact by running a query like this:

SETUSER JaneD
SELECT * FROM Joe_Smith.Customers
SETUSER

This will return an error. Because SQL Server checks permissions when a view is used, not when it is created, it will allow JaneD to create the view on Joe_Smith’s table without generating an error. It is when someone tries to use the view that you should get an error. In fact, BOL states the following in the article titled “Ownership Chains”:

“. . . if Joe has permission on the CREATE VIEW statement, he can define a view based on the [pubs..]authors table even if he does not have SELECT permission on authors. However, the view would be useless to everyone, including Joe.”

Two simple tests in following the script can prove this statement wrong. First, JaneD can select from her own view, even though she has no permissions on the underlying table. Second, when she grants FredJ the SELECT permission on her view, as shown in the following code, he can also query Joe_Smith’s tables through it. FredJ cannot query Joe_Smith’s tables directly, but he can query JaneD’s view:

SETUSER 'JaneD'
PRINT 'Testing JaneD''s permissions on Joe_Smith.Orders
and JaneD.Order_Totals'
SELECT * FROM Joe_Smith.Orders
SELECT * FROM JaneD.Order_Totals
GRANT SELECT ON JaneD.Order_Totals TO FredJ
GO
SETUSER
SETUSER 'FredJ'
PRINT ' '
PRINT 'Testing FredJ''s permissions on Joe_Smith.Orders
and JaneD.Order_Totals'
SELECT * FROM Joe_Smith.Orders
SELECT * FROM JaneD.Order_Totals
SETUSER
go

What is supposed to happen is that when FredJ uses the view in a SELECT statement, SQL Server first checks the permissions on the view and then checks his permissions for each of the tables, because the view and the tables have different owners. If FredJ does not have SELECT permission on all of them, his SELECT statement should fail. Testing shows that SQL Server 6.5 has a flaw in the way it handles permission checking for a view that refers just to base tables.

Remember, the standard version of SQL Server 6.5 for this book is Service Pack 5a. I ran these tests on Service Pack 5a and the post–Service Pack 5a update. I also spent about 6 hours combing through Microsoft’s site and several sites dedicated to SQL Server looking for recognition of this problem. Finally, I even tested the scripts by logging into new sessions with the JaneD, Joe_Smith, and FredJ logins from another computer using a regular Windows NT user account just in case I was somehow getting administrator privileges without knowing it. Unfortunately, this is one of those times when a few simple tests prove that you cannot always trust the documentation.

The next example involves a more complicated broken ownership chain (see also Figure 3-2), in which the expected behavior does occur:

CREATE VIEW Joe_Smith.Totals_by_Name(Cust.name,Order.total) AS
SELECT a.name, b.Order_Total
FROM Joe_Smith.Customers a, JaneD.Order_Totals b
WHERE a.CustID = b.CustID

Figure 3-2. A complex ownership chain

In this example (also in ownershipChains_setup.sql), Joe_Smith creates a view that joins the output of JaneD’s view with Joe_Smith.Customers, to provide the customer’s name instead of the customer number. The following script tests the permissions on the new view:

SETUSER 'Joe_Smith'
SELECT * FROM Joe_Smith.Totals_by_Name
GRANT SELECT ON Joe_Smith.Totals_by_Name TO FredJ
SETUSER
GO
SETUSER 'FredJ'
SELECT * FROM Joe_Smith.Totals_by_Name
SETUSER
GO

Here is the result:

=====================================================
SELECT permission denied on object Order_Totals, database Test, owner JaneD
============================================================

You will see that Joe_Smith cannot use his own view because JaneD has not granted him SELECT permission on her view. FredJ, however, can query both JaneD.Order_Totals and Joe_Smith.Totals_by_Name because he has SELECT permission on both views (note that neither JaneD nor FredJ can query Joe_Smith’s tables directly). The results of FredJ’s query are shown in the following table.

Cust_Name                     Order_Total
John's Game Emporium           60.00
Bonnie's Dog House             60.00
Erin's House of Giggles       60.00
Rebekah's Kitty Kennel         60.00

Just to test to make sure that moving another level up the chain does not give Joe_Smith permissions he should not have, the next example adds one more view named Joe_Smith.OrderTotals:

CREATE VIEW Joe_Smith.TotalOrders (Total.Amount)
AS
SELECT SUM(Order_Total)
FROM Joe_Smith.Totals_by_Name

Figure 3-3 shows the resulting ownership chain.

Figure 3-3. A more complex ownership chain

This is a view based on the Joe_Smith.Totals_by_Name view; therefore, Joe_Smith owns all the objects referenced. Nevertheless, SQL Server recognizes that Joe_Smith does not own all the objects in the chain and thus checks permissions on each referenced object. In this case, when you run the following script, Joe_Smith still receives an error when he tries to use the OrderTotals view, and FredJ receives an error for OrderTotals because he has not been granted SELECT permission on it, even though he can access the view on which OrderTotals is based:

SETUSER 'Joe_Smith'
SELECT * FROM Joe_Smith.TotalOrders
SETUSER
GO
SETUSER 'FredJ'
SELECT * FROM Joe_Smith.TotalOrders
SETUSER
GO

Here are the results:

=================================================
SELECT permission denied on object Order_Totals, database
Test, owner JaneD
SELECT permission denied on object TotalOrders, database
Test, owner Joe_Smith
=======================================================

Consider an alternative scenario in which Joe_Smith owns all the objects referenced in the chain. In this case, SQL Server’s designers assume that Joe_Smith will assign permissions on an object to FredJ only if he wants FredJ to see the contents of the underlying tables; therefore, SQL Server checks FredJ’s permissions only on the top-level object, not on the tables. In fact, FredJ may have no assigned permissions on the tables and views below Joe_Smith.OrderTotals and still SELECT data through the view. You can test this scenario by changing the script to make Joe_Smith the owner of all the views, removing FredJ’s SELECT permission from Order_Totals, and then executing the following SELECT statement:

SETUSER 'FredJ'
SELECT * FROM Joe_Smith.Customers
SELECT * FROM Joe_Smith.Orders
SELECT * FROM Joe_Smith.Order_Totals
SELECT * FROM Joe_Smith.Totals_by_Name
SELECT * FROM Joe_Smith.TotalOrders
SETUSER

Only the last SELECT statement will work.

This article is excerpted from SQL Server Security Distilled by Morris Lewis (Apress, 2004; ISBN 1590592190). Check it out at your favorite bookstore today. Buy this book now.

In this discussion, I have only covered ownership chains with views, but the same rules apply to stored procedures. The one exception is that SQL Server will check permissions on base tables for stored procedures. For example, the following simple stored procedure queries Joe_Smith.Orders based on the @CustID parameter:

CREATE PROCEDURE JaneD.Customer_Total(@CustID int)
as
   SELECT Sum(Total) as Total_Sales
   FROM Joe_Smith.Orders
   WHERE CustID = @CustID
go

If you execute the procedure as JaneD, you will get an error saying she does not have SELECT permission on the Joe_Smith.Orders table. If you grant EXECUTE permission to FredJ, he will get the same error. This is the proper response.

Suppose you change the stored procedure to use JaneD’s view instead:

CREATE PROCEDURE JaneD.Customer_Total2(@CustID int)
as
SELECT * From JaneD.Order_Totals
WHERE CustID = @CustID
go

In this case, SQL Server allows JaneD to execute the procedure. The flaw in the permission checking for views affects stored procedures that use those views as well. It is not possible to protect the underlying tables by wrapping them in a stored procedure.

Triggers

Triggers are special kinds of stored procedures, so they follow the same rules. If a table’s trigger references an object owned by another user, the person executing the statement that caused the trigger to fire will need to have appropriate permissions on the referenced object. A permission violation will cause the trigger to fail and the entire transaction to roll back. An exception to the rule is that because the Inserted and Deleted temporary transaction tables do not really have owners, they are considered to be owned by the table owner. Triggers can only be created by the table owner anyway, so SQL Server never does permission checking on the Inserted and Deleted tables.

Recommendations on Ownership

Because of the issues with permission checking that I have discussed, the standard practice is to have dbo own all objects in a database. Having the same owner on all objects means that SQL Server will check the permissions just on the top-level object and will not have to check permissions throughout the entire chain. There are two main benefits of this approach:

The management of permissions is far easier and more straightforward.
Overall performance is significantly better for databases with large numbers of objects and/or large numbers of dependencies between those objects.

Once you have chains with more than two levels, you will find that having dbo own everything in the database is the best way to go. To help you reset object ownership to dbo throughout the database, you can use the following stored procedure:

DROP PROCEDURE TakeOwnership
go
exec sp_configure 'allow updates', 1
Reconfigure with Override
go
Create Procedure TakeOwnership
as
   UPDATE sysobjects
   SET uid = 1
   WHERE type = 'U' or type = 'V' or type = 'P'
go
exec sp_configure 'allow updates', 0
Reconfigure with Override
exec sp_tables
go
exec TakeOwnership
exec sp_tables
go

Although you should always approach direct updates to the system tables with extreme caution, this is one of the stored procedures Microsoft should have put in the standard installation. You can modify this procedure to change the ownership of a single object or to change ownership of one set of objects.

Another way to make sure that dbo owns all database objects is to set up aliases for the dbo user account for everyone who has permission to execute one of the object creation statements. This technique will eliminate the chance that someone could create a stored procedure or view that might work in development but not in production. Aliases were created to solve just this kind of problem—it makes sense to use them if you can.

Summary

This chapter has covered all the ways to authorize users and secure data in a database, but it has not really discussed much in the area of best practices. The sheer number of different environments in which SQL Server is used precludes an exhaustive list of best practices. Remember to give careful thought to who can access the database and to the permissions assigned to the public group. In almost all cases I recommend that you not enable the guest database user so that all users must be explicitly granted access. Finally, grant the most general permissions to groups, and then assign more specific permissions on a user-by-user basis. What you will find is that SQL Server 7.0 and 2000 offer a richer set of options for securing databases than SQL Server 6.5, but with a little thought you should be able to meet your needs.

This article is excerpted from SQL Server Security Distilled by Morris Lewis (Apress, 2004; ISBN 1590592190). Check it out at your favorite bookstore today. Buy this book now.