[Opensource] designs for row-level privilege checking?

[Michael Rimov]

At 11:22 AM 8/14/2002 -0700, you wrote:

> >>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.

Hi Larry,

Ok, a couple of thoughts here:

1 - We change table names more than we do DBObject classnames.  [As the two 
table name changes in Expresso 4.1 show :) ].... if this is the case then 
we mess up the row level permissions more often than the classnames in the 
tables.  So you could save some database maintenance time this way.

2 - This one is a philosophical perspective... since DBObjects aren't just 
persistance but include 'behavior' as well, then it's quite possible that 
you would want different behavior for different dbobjects.  And in that 
case  you WOULD want the row permissions to be different for each dbobject.




>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.

Actually, you won't need the context since by it's very nature there will 
be one row privileges table per data context.  [And other db map tables 
will map to a context that automatically has a table... which come to think 
of it is another reason why you want classname in the table since you CAN 
have duplicate table names using the other db map, but you cannot have 
duplicate class names in a context]



>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.

Sounds fair to me! :)

> >-  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.

Actually, I'd like then perhaps a new permission bit since Writing in some 
cases doesn't completely imply ownership capabilities... For example, an 
Admin could seize ownership of a user's file, and grant him write 
permission to that file... but you don't want the user grabbing ownership 
of the record again.


>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.

See above :)  That sounds good to me.  A 2 byte number is pretty good on 
memory and it still gives us bits to expand upon if there's something we 
forgot.

> >-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.

You've convinced me on that one.  That's fine by me for now.


>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.

Yes I agree with you.


>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.

I agree with you.  Although now that I think of it, InterBase may have a 
problem with a varchar(255) key... I think the biggest I could ever do when 
I was playing with it was varchar(160). [Can a current Firebird/InterBase 
user confirm?]



>What do you think?

I think we're definitely on the right track!
                                                 -Mike