You can manage user access to your database by using several techniques. In the past few T-SQL Admin columns, I've discussed how you can use usernames and roles to control database access for individuals and groups. You control user access to specific data by using permissions. Permissions let you control exactly which users have access to what information.
You always grant SQL Server permissions to users within a database, not to login names. For working with permissions, SQL Server 2000 and 7.0 provide three commands that are similar to the commands you use to grant permissions at the Windows OS level: GRANT, DENY, and REVOKE. System tables keep track of permissions information, and SQL Server offers several ways you can extract information about existing permissions.
Permissions Commands
SQL Server has two types of permissions, both of which use the same basic commands. Statement permissions allow users to perform actions such as backing up a database or creating a table, a view, or a procedure. For users in the master database only, you can grant permissions to create a new database. Only users in the db_owner or db_securityadmin roles can control who has statement permissions. Note that the username dbo is always in the db_owner role, so dbo can always control permissions; users in the server role sysadmin automatically have the username dbo in every database, so they can also control permissions.
Object permissions (the second type of permissions) control data access. Users in the db_owner or db_securityadmin roles and object owners can use object permissions to control access to objects. Owners of tables or views can determine who can insert, update, delete, or select from their objects and who can use their objects as the referenced object for a foreign key. Owners of stored procedures or scalar functions can determine who has permission to execute their routines.
When you use T-SQL syntax, the statements for controlling statement permissions and object permissions look similar. The only real differences between statement and object permissions are that object permissions need an object name, and you can include a list of columns for SELECT or UPDATE permissions when you specify object permissions.
As I mentioned in "Managing Multiple-User Access," October 2002, InstantDoc ID 26430, you can use the commands GRANT, DENY, and REVOKE when you're working with permissions for the guest username. These are the same commands you use for granting permissions to anybody in the database. GRANT lets a user, OS group, or role execute a statement or access data. When granting object permissions, you can also use the WITH GRANT OPTION clause, which lets users who have permissions grant those permissions to others. DENY prevents a user, OS group, or role from executing a statement or accessing data. REVOKE removes a previously granted or denied permission.
In SQL Server 6.5 and earlier releases, the DENY command isn't available, so the REVOKE command serves double duty. REVOKE can remove a previously granted permission or it can prevent a particular user from performing an action. Numerous bugs and ambiguities have occurred because of this double meaning.
As it does in Windows OS permission control, a DENY that's applied to any role or group that a user belongs to means that the user has no permissions. For example, if user sue is a member of the clerks role and you execute the following two statements (in any order), sue won't be able to select from the inventory table:
GRANT SELECT ON inventory TO sue
DENY SELECT ON inventory TO clerks
Denying permissions to the clerks role overrides sue's individual user permissions. However, you usually grant permissions at a high level (e.g., a group or a role) and deny permissions at a lower level (e.g., an individual user):
GRANT SELECT ON inventory TO clerks
DENY SELECT ON inventory TO sue
These two statements let everybody in the clerks role except sue select from the inventory table.
You should be particularly careful about denying permissions to the public role, because public includes everybody except members of the sysadmin server role, for whom SQL Server never checks permissions. If a non-sysadmin object owner denies access to the public role, she also denies herself access to her own objects. Because these relationships can be complex, you need to keep track of which users have which permissions. The system tables contain this information.
System Tables
The two most important tables you use when you manage permissions are the sysusers and syspermissions tables. However, if you read the code for most of the system stored procedures that deal with permissions (e.g., sp_helprotect), you'll see that the code contains few references to the syspermissions table; instead, the code references the sysprotects virtual table. The Microsoft white paper "Microsoft SQL Server 2000 Security" (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_security2000.asp) explains the difference between syspermissions and sysprotects. In SQL Server 6.5 and earlier, the sysprotects system table maintained all permissions. In SQL Server 7.0, Microsoft introduced the syspermissions table, which exists in every database to track permissions that are granted or denied to users. Syspermissions also stores object permissions. The sysprotects table still exists for backward compatibility. In SQL Server 7.0 and later, sysprotects is a pseudo table, which appears as a normal table in the system but SQL Server generates its contents only when you access it. Thus, a pseudo table takes up no storage space. However, SQL Server treats a pseudo table just like it treats a real table in all code and system stored procedures. (The sysfiles system table is an example of a pseudo table. For a description of how sysfiles works, see "Roll Your Own System Stored Procedures," November 2001, InstantDoc ID 22485.) Most of the system stored procedures in SQL Server 7.0 and later still contain references to sysprotects, and the information available through sysprotects is much easier to work with.
Prev. page
[1]
2
next page 
