row level security

I am looking for a way to implement row level security on my SQL Server 2005 Express database. Thanks in advance for any input.

Row Level Security Defined

Why do I need row level security?

As an architect, developer, or operator of a computer based information system, you mustprotect the sensitive data that you manage. To protect the data, you must ensure that the users of your system have the authority toperform thefunctions they attempt in your system to view, update, delete, insert, and grant privileges onyour system's data.

Depending on the approach you take, this requirementfor data access securitycan dramatically increase the time and expense associated with building and operating your information system.

Row level security (for purposes of this discussion) is a scheme that enforcesdata access security for the rows in a relational database table via a set ofpermission grants(both read and write)assigned to auser or to agroup of users.

The identity ofa user is established(user authentication)when the user makes a connection to the database. The databasealreadynatively supports access control at the object level (table, view, etc.) and even the table column level, but not at the table row level, making row level security an additional scheme implemented on top of the base database product.

When the row level security scheme is in place and functioning, the userwillonly see the rows to which they have at least read level access. They are onlyable to updateand deletedata ina row if they have write level access to the row. They are only able to insert data in a table if they have been granted insert privileges on the table.

From a development cost perspective, selection of atransparent row level security schemeis extremely important – i.e. theSQLSELECT, INSERT, UPDATE and DELETE statements that run against the tables prior to implementing the row level security scheme continue towork without being modified when the row level security scheme is in place and functioning.

A significant reduction in administrative overhead can be achieved if the row level security scheme supports apermission inheritance model where the rows in a child table automatically inherit the permission grants to the parent row in the parent table. This pattern can be repeated for grand children, great grandchildren, etc. This feature makes it possible to create a grant to a single row in a parent table and automatically grant access to all of the appropriate rows in child tables, grandchild tables, etc.

This type of permission control is referred to asjoin-based security because the permissions for a particular row in a table are determined using relational join criteria to locate and inherit permissions from related rows in another table.

Maintainingand operating the information system is greatly simplified ifdata access securityis enforced by definingpermissiongrants to agroups of users (instead of directly assigning the grants to individual users). This separatespermissions configuration andmembership configuration into two distinct tasks.

You can look athttp://www.datanomad.com orhttp://www.technicalmedia.com for more information on row level security in SQL Server 2005.