Re: Row-level security?

From: paul c <>
Date: Thu, 28 May 2009 15:31:07 GMT
Message-ID: <%6yTl.28361$Db2.15594_at_edtnps83>

Roy Hann wrote:
> lawpoop wrote:

>> How does one implement row-level security?
>> For instance, say you have an internal database for billing. You have
>> a table Clients, a child table Invoices related by client_id to
>>, and a child LineItems related to by
>> LineItems.invoice_id. You want to create a web app so that your
>> clients can log on and see the invoices you've sent them. Of course,
>> you only want the clients to see *their own* invoices, and not anyone
>> else's. One way to handle this is in code, so that queries constructed
>> always have "WHERE client_id = x". But is there a way in relational
>> theory to provide different permissions to rows?

> I don't know if relational theory has a lot to say about permissions
> other than to provide the necessary machinery. What you want is a base
> table to which the user has no access, and a view of that table which is
> restricted to just the rows the user should be able to see. The user
> is then given permission to select only from the view.
> Incidentally you can take the same approach when concealing columns.
> Unfortunately the fly in this otherwise excellent ointment is that web
> applications rarely identify users the way SQL engines do for the
> purpose of granting permissions. However the workaround won't look a
> lot different; it'll just be a little less tidy.

That is the situation with most sql products as far as I know. From a theory viewpoint I have often wondered why dbms' that record both clientids and userids are incapable of joining on those attributes. Nothing in the RM prevents a dbms from including a userid in a view, furthermore every user seeing that view could see a differend userid. This has nothing to do with base tables - I imagine most people whose theory was learned at the foot of arbitrary products would have difficulty imagining such a thing, ie., they are limited to what they have seen done in the past, but it could go much further, even provide the basis for a formal concurrency implementation theory, which we don't have now - there doesn't need to be any 'system' difference between a user not seeing a row and a user not being able to delete that row. Received on Thu May 28 2009 - 17:31:07 CEST

Original text of this message