Managing Access to Tables and Columns


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.

  • GRANT Allows a user or role to perform the operation specified by the granted permission.

  • DENY Denies a user or role the specified permission and prevents them from inheriting this permission.from other roles.

  • REVOKE Drops a previously denied or granted permission.

Modifying Access to a Table

Access 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.

Table 2-4. Table Permissions

Permission

Description

ALTER

Can modify table properties

CONTROL

Provides ownership-like permissions

DELETE

Can delete rows from the table

INSERT

Can insert rows in the table

REFERENCES

Can reference the table from a foreign key

SELECT

Can select rows from the table

TAKE OWNERSHIP

Can take ownership of the table

UPDATE

Can update rows on the table

VIEW DEFINITION

Can access the the table's metadata


Granting Access to a Table

You 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 Table

When 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 Individually

SQL 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.

Table 2-5. Column Permissions

Permission

Description

SELECT

Can select the column

UPDATE

Can update the column

REFERENCE

Can reference the column from a foreign key


Granting Access to Columns

You 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 Columns

Similarly, 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;





Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net