Tables and columns store the data that applications retrieve and create. Access to this data is managed by the SQL Server 2005 permission hierarchy. You can manage this permission hierarchy with the GRANT, DENY, and REVOKE statements.
Modifying Access to a TableAccess to a table is controlled by the effective permissions that the user has on the table. You can control user access to tables by managing permissions on tables. The permissions that you can manage on tables are specified on Table 2-4. You can assign these permissions to database users and roles.
Granting Access to a TableYou can provide access to database users and roles by using the GRANT statement. The following sample grants SELECT, INSERT, and UPDATE permissions to Sara on the Sales.Customer table. (The code in this and subsequent sections for managing access to tables is included in the sample files as ManagingAccessToTables.sql.) -- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Grant some permissions to Sara on the Sales.Customer table. GRANT SELECT,INSERT,UPDATE ON Sales.Customer TO Sara; Limiting Access to a TableWhen you need to prevent a user from accessing a table, you might face one of two scenarios. If you have granted permission to the user on the table before, you should use the REVOKE statement to clear the previously granted permissions. For example: -- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Revoke SELECT permissions from Sara on the Sales.Customer table REVOKE SELECT ON Sales.Customer TO Sara; However, the user might maintain the revoked permission by belonging to a role with this permission granted. In that case, you need to use the DENY statement in order to deny access for that user. For example: -- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Deny DELETE permission to Sara on the Sales.Customer table, -- regardless of what permissions this user might -- inherit from roles. DENY DELETE ON Sales.Customer TO Sara; Providing Access to Columns IndividuallySQL Server 2005 provides you the option to grant or refuse access to columns individually instead of working with tables. This feature gives you the flexibility to refuse access, for example, to confidential data from some columns. The permissions that you can manage on table columns are described in Table 2-5.
Granting Access to ColumnsYou can grant access to columns individually by using the GRANT option. The following sample grants SELECT and UPDATE permissions to Sara on the Demographics and ModifiedDate columns for the Sales.Individual table. (The code in this and subsequent sections for managing access to columns is included in the sample files as ManagingAccessToColumns. sql.) -- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Grant SELECT and UPDATE permissions to Sara -- on some specific columns of the Sales.Individual table GRANT SELECT,UPDATE ( Demographics, ModifiedDate) ON Sales.Individual TO Sara; Revoking Access to ColumnsSimilarly, you can revoke access to columns individually using the REVOKE statement. Remember that if you want to prevent a user from gaining permission, you need to use the DENY statement. -- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Revoke previosly granted or denied permissions -- from Sara on the Demographics column. REVOKE UPDATE (Demographics) ON Sales.Individual TO Sara; |