Re: Data Modeling: User Specific Column Retrieval

<sriniv79_at_my-deja.com> wrote in message news:8bvqpn$ut9$1_at_nnrp1.deja.com...
> Hi
>
> I have a question for modeling gurus.
>
> I have a table which stores documents, one per record. The document has
> 16 sections, each represented by a column in the table.
>
> I have several users accessing this data, each user represented by a
> userID in Users Table.
>
> Some users can access only some columns in the document table. The
> access rules are table wide generally, but would like to make them row
> specific, if possible.
>
> I need to design a schema, where I can represent the access rules for
> each user, preferably at row level, table level would be fine for now.
>

I can hardly pass as a model guru, but allow me to dispense some thoughts anyway.

The easiest way to achieve column-based access rules is probably to use the RDBMS's built-in security and authorization features. In Oracle you can use roles to represent the various access-rules, and grant those roles to database-users. You probably won't define each real user as a database-user, in which case you must enforce a mapping mechanism between the two.

document: docid, col1, col2,..., col16

create role readcol1Role not identified; grant select (col1) on document to readcol1Role; ...
create user col1-4-5-reader identifed by xxx;

grant readcol1Role to col1-4-5-reader;
grant readcol4Role to col1-4-5-reader;
grant readcol5Role to col1-4-5-reader;

To enforce more fine-grained (row specific) access rules, a different attack applies. Consider the following tables:

document: docid, col1, col2,..., col16
user: username, passwd, <userinfo>
privileges: username, docid, col-name, privilege // e.g. "R", "RW" etc. PM=username, docid, col-name

Now the database user used to run the sessions, should not be provided any access to the doc-table directly, but should be allowed to access stored procedures which now enforce the access control rules. The stored procedures takes user/passwd as part of the parameters and checks the privilieges for the current user before revealing a particular column-value of a specific row.

--

Thomas