Database Security in SQL Server 6.5 - Database Groups
(Page 3 of 8 )
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. |
Next: Managing Database Permissions >>
More MS SQL Server Articles
More By Apress Publishing
