Re: Row-level security?

lawpoop wrote:

> On May 28, 10:48 am, Roy Hann <specia..._at_processed.almost.meat> wrote:
>
>> 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.
>
> So basically you'd want to do a view for each client? Or, in general,
> a view for every table for every user where you want row-level access?

No, that would be horrible. No, you just need a DBMS that knows the user id (USER) and can use it in the restriction. So you'd create a single view like

CREATE VIEW userinfo AS
SELECT * FROM clientinfo
WHERE clientid = USER

You would then grant this one view to public. The value of USERID should be different for each user so that single view will appear to have different rows depending on who is looking at it. You can go further with this idea, not only projecting just selected columns but also enforcing time of date restrictions (e.g. office hours only), and so on.

One can imagine various workarounds for DBMSs that don't know or can't provide the value of USER, but you would still really need only one view for all.

-- 
Roy