Re: Row-level security?
Date: Thu, 28 May 2009 15:31:07 GMT
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 >> Clients.id, and a child LineItems related to Invoices.id 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