Database Security in SQL Server 6.5 - Managing Database Permissions

(Page 4 of 8 )

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.

Next: SETUSER >>
 

More MS SQL Server Articles
More By Apress Publishing

Comments On: Database Security in SQL Server 6.5

>>> Be the FIRST to comment on this article!