[Opensource] designs for row-level privilege checking?

[larry hamel]

>>To store the privilege information, a new table, tentatively named ROW_PRIVILEGES, has the following columns:
>>
>>        Table Name, Key, Owner, Group, Privileges
>
>Actually, I think I'd like to see DBObject class name instead of table name.  It is a longer string, but Expresso DOES have a couple of DBObjects that point to the same table, and you might get some weird results without that specific separation.


but the privileges pertain to the row, not to how you access the row.  two DBObject's could be serving the same row; that would be fine.  however, it would not be okay to have one permission for a given row when accessed via a method A and another permission when the same row is accessed via a method B.

One new consideration since I proposed this: perhaps we should add the name of the context as part of the row privileges key.  On the other hand, if we always get the context from the same source as the target table, then any row privileges will automatically pertain only within the correct context.


>>Where an example entry might be:
>><snip> <snip>
>>In the example given, the owner of the row has ID 242, and row's group is named sales_group. We could interpret some kind of primary group of the owner as the group of the row, but the Expresso group system doesn't have this concept of a user's primary group right now, and it seems flexible to specify the group explicitly as part of the privileges.
>
>True, but we could easily implement a primary group of sorts... esp since something like this will be going into Expresso 4.2, so we have the time to work it out.


In my application, it is more clear to attach a group to the row, rather than determining the owner's primary group.  For instance, I want all new rows to default to a read-only status for a large group regardless of the first author of a row.  Also, there is an efficiency advantage to explicitly listing the group in a privilege: you don't have to look up the owner's primary group.


>-  One question, how do you determine the ability for a person to "take ownership" of a record?

I imagined that the ability to write implied the ability to change permissions.  So if a row allowed group writing, then anyone in the group could take ownership of the row.  

An alternative would be to add another bit for the ability to change permissions.  That would mean 9 bits total: for each of owner, group, and others, there would be a read bit, a write bit, and a permissions bit.


>-The more complicated we get with this, the more we're going to really need some sort of transactional capability.  My problem that I see is that right now JDBC transactions are not compatible with JTA transactions in the same app.  Perhaps we could write a wrapper class that could be configured to do either and then we associate the transaction with the DBConnection... just musing here, any thoughts on this?

Good point.  Adding a record would imply writing to two tables: the values table, and the privileges table.  It would be appropriate to put a transaction around that.  However, if we write the permissions first, and thereafter, writing the values fails, we are still in a reasonable state.  It isn't as though we've subtracted from one bank account but not added to another bank account.

After adding the row, updates of values and permissions should be separate, so that a transaction would not be necessary.  When deleting a row, again there should be a transaction, but if we fail to delete both parts, it doesn't seem to cause an irrational state.

So a transaction would be preferred, but does not seem necessary at least for a first attempt.


>That's about all that I can think of looking at it.  I look forward to seeing it, and I'll post a notice when I've finally branched CVS so you could check it in for "tire kicking".

Okay, thanks for the green light.

One of the biggest problems I've hit has to do with storing the primary key.  If the primary key of a target table is an integer, no problem.  But what if it is a concatenation of three string fields?  The problem is in generalizing the length of the field necessary to store an arbitrary primary key.  It seems appropriate to make it a long varchar, but I think some databases would have trouble making a long varchar part of the primary key of the row privileges table (target table name + target row's primary key value).

For a first cut, I suppose I could punt on the long varchar, and just keep it a varchar 255.  Any target table with a primary key which does not fit in this limit just doesn't work for now.  In practice, I expect that the vast majority of primary keys would fit in a varchar 255.

What do you think?

larry