I'm working on an .mdb in 2010 given the built-in security that 2k7 and later abandoned (sadly). What I'd like to do seems rather rudimentary for a database suite that would bother to include "user-level" permissions functionality at all. In a nutshell:
* Create a table of records
* When any one of the above records is edited such that field_2 is changed from a value of "no" to "yes", only select users can see them now
Not simple, apparently. I thought perhaps that I could permit-all on a query that only sought the "no" records, and permit-elite on a query for the yes's. Nope. Folks can gleefully and at will just open the underlying table in datasheet view and snoop away. Lock down that table? Nope--what applies to the table applies to the query by extension, so there's no virtual difference between them in permissions.
Hide the fields in table view? Nope--users can just unhide them, since "hide" isn't part of the table design and again, users can chuckle away as they strip the veil from the table with ease.
I can't use two separate tables either, because the application is an incident ticket application. Thus one user with low entitlements might create, and even assign a ticket, while the assignee might then take custody of that ticket and proceed to write confidential things in it. This is akin to email, where all the world has write access to your inbox, and no read access.
What the...? Really?
Sure could use help--or a sanity check.
Thanks.