Database Security in SQL Server 6.5
(Page 1 of 8 )
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).
LOGIN AUTHENTICATION is the first gate through which users must pass, but this only verifies the user’s identity. Because SQL Server can manage multiple databases at one time, authentication must extend beyond server access to include database access authentication too. In that respect, database access offers a second security checkpoint through which a user must pass and provides an identifier for the user, to which you can assign permissions for actions on objects inside the database. This chapter focuses on SQL Server 6.5 alone, because SQL Server 7.0 introduced a significantly different way of managing both database access and database permissions.
NOTE Database security for SQL Server 7.0 and 2000 is covered in Chapter 4.
Note that in the discussion that follows, any reference to Windows NT authenticated logins also applies to accounts managed by Windows 2000 domain controllers, whether they are using a Windows NT domain security database or Active Directory. Also, this discussion applies whether SQL Server itself is running on Windows NT or Windows 2000. Once the user has logged in, SQL Server 6.5 identifies the user solely on the SUID that it assigned when the login was 1created.
| NOTE In order to follow the examples in this chapter, you will need to start by creating a database named Test with 2MB of data and 1MB of log space. In that database, run the script called Chapter_3_ Setup.sql, available with the code download for this book at http://www.WinNetSecurity.com. |
Managing Database Access Logically, I should start this discussion of database security with the creation of the database itself, especially when you consider that the user who creates the database becomes the database’s first user, dbo. As it turns out, it is easier to explain the permissions required to create the database after I cover how database user accounts work; therefore, assume that there is a database named Test for your use.
Database authentication is really very simple. Everything revolves around a table named sysusers in each database, the schema of which is shown in Table 3-1.
Table 3-1. The sysusers Table Schema
| Column | Data Type | Description |
|---|
| suid | smallint | Server user ID, copied from syslogins |
| uid | smallint | User ID, unique in this database |
| gid | smallint | Group ID to which this user belongs |
| name | varchar(30) | Username or group name, unique in this database |
| environ | varchar(255) | Reserved |
The key feature of this table is that each row maps a user’s SUID to a unique identifier within the database known as the
user identifier, or
UID. As mentioned in Chapter 2, a SQL Server 6.5 login is simply an account and password stored in arow in
Master..syslogins, in which the primary key is the SUID, which is a 16-bit integer. SQL Server assigns a SUID for both SQL Server authenticated logins and for Windows authenticated logins, and it is the identifier that SQL Server uses to link the database UID to a login account.
Users receive access to a database in one of three ways:
- The sysusers table contains a row with the user’s SUID.
- The user’s SUID is recognized as an “alias” of another user account already in the database.
- If there is no row in sysusers matching the user’s SUID, then the user can access the database as the guest user, as long as the database has the guest user account enabled.
When a user attempts to access a database, the starting point is to look in sysusers for the SUID assigned to the user’s login. If there is a match, the user’s identity within the database becomes the UID located in the same row as the SUID.
If SQL Server does not find a match for the SUID in sysusers, it looks in a table named sysalternates. The schema for sysalternates consists of two columns that map a SUID to the SUID of an existing database user, as shown in Table 3-2.
Table 3-2. The sysalternates Table
| Column | Data Type | Description |
|---|
| Suid | smallint | Server UID of the user being mapped |
| altsuid | smallint | Server UID of the impersonated user |
A process known as
aliasing allows one person to gain access to a database using another user’s identity in the database. If SQL Server finds the user’s SUID in the
sysalternates table, it searches the
sysusers table again for the SUID found in the
altsuid column. Then it assigns the UID associated with the alternative SUID, and that UID becomes the user’s identity within the database.
If SQL Server does not find the user’s SUID in either sysusers or sysalternates, it looks in sysusers to see if the guest user account has been enabled. If it finds the guest account, the user is granted access as the guest user. If the guest account does not exist, the user is denied access to the database.
 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: Aliasing Example >>
More MS SQL Server Articles
More By Apress Publishing
