Re: Row-level security?

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.

-- 
Roy