Security Techniques

[REC]

My db is currently secured with an MDW. One of my tables tracks pricing per
department. One of the MDW security groups I set up allows members to modify
all the records in that table. Is there a way to take security one step
further by allowing users with the ability to modify only records belonging
to their department, but read-only for records belonging to other
departments? All records are kept in one table. Any suggestions on how to
approach this would be much appreciated.

 

[david epsom dot com dot au]

Remove write permissions to the table, and to write records
make them use an "Owner Permission" query instead. In the
query, restrict them to only see records in their own
department. You can do this with a department table that
lists the usernames as returned by CurrentUser().

I don't think you will be able to get a recordset with
some read-only records and some read/write records.

(david)

 

[GPO]

This is probably a bit ugly and therefore I should maybe just be quiet but if
you have relatively stable departments (ie very rarely creating new ones etc)
then if you had one table for each department you could set up permissions
for each table specific to each department. You might have to union query
every time you wanted to use combined data. Would solve the access problem
but terrible database design...