RE: Design pattern for row-level security?

> But herein lies the problem, what is the service program
> checking? There are
> three major parts to the authentication system, who, what and where.
>
> Who being the user
> What being the action (view, edit, delete, add note, change status, etc.)
> Where being the row (protocol 8, attachment 47, etc.)
>
> Then of course there are several paths of inheritance where
> someone doesn't
> have explicit rights to a row, but inherits his/her rights
> because they are
> "associated" with the row (product manager, VP Product, etc.)

You're talking about a role-based system with security based on instance
data.  This is probably the most complex security model.  Not only do you
have design issues, but performance issu will have to use data rules.  You
must decide whether in your environment a single user can have multiple
roles for the same row, and if so, are they additive es as well.

The easiest way to design it is to define the following objects:

1. Rights - this determines the rights for an entity
2. Role - The role a user plays
3. Data rule - A rule used to identify whether a row meets a condition

Now life gets interesting.  A data rule basically is used to determine
whether a certain row passes certain tests.  Since this could overlap
multiple fields, the syntax for the data rule gets complex.

First, you need a way to resolve all the roles for a user for a given row.
Since you are using instance data to determine the links, youor
exclusionary.

Now, you need to begin to apply the rights objects.  Rights should be
assigned by role, but they could also be assigned at a specific user ID
level.  You attach rights to a row/role using data rules.  Therefore, you
have to determine whether inherited rights are additive, subtractive or
exclusionary to specific rights.

At this point, you have identified the rights for a given row.  The fun
thing is that while this is fairly complex, it's really only the tip of the
iceberg: a given rights object can apply only to specific columns in the
row, you can have default rights objects, and you can use foreign tables in
your conditions, just for starters.

No easy model for this one.

Joe