I've been wrestling with a problem for the past while, and was curious
if anyone here may have some insights to help make some headway.
Basically, I'm trying to come up with a method of having row-level
permissions in a table for more than one user. The potential exists
for several hundred people to be able to access a particular row. In
addition, users may be added or removed dynamically, which would force
the corresponding rows to be updated.
I looked at the method of appending a username field at the end of
each row, using a delimiter to separate the different ids, but that
doesn't seem to be applicable, owing to the fact that the size of the
field would quickly become unmanageable (not to mention the
performance hit of searching each row's field).
Another more promising approach was assigning each user a unique prime
number, and using the product of all of the users with permission as
the field value. Although that number quickly becomes huge, it would
be possible to use a division method to find permissions. There are
some performance issues here, too. I've also toyed with an array of
bits, using XOR operations and salts to generate the final bit hash.
I don't know enough of the underlying crypto theory to realistically
implement this, though, and it doesn't seem as though it would be that
efficient to implement for each row.
It seems like there should also be a method using a series of lookup
tables as well. There are several fields which could potentially be
used to group similar sets of data, while still having the possibility
that any arbitrary row could be accessed by only one particular user.
In general, the sets of users with permissions for a row will be
similar, which would reduce the number of lookup keys significantly.
It feels like I'm still missing something about this approach, though.
Anyway, if anyone has thoughts and/or comments, or has done anything
like this, I'd be much obliged if you could provide some insights.
Thanks!
