Re: Running untrusted sql safely?

On Sun, Feb 15, 2009 at 4:39 PM, Christophe <xof(at)thebuild(dot)com> wrote:
>
> On Feb 15, 2009, at 2:47 PM, Stuart McGraw wrote:
>
>> I just hoping for some confirmation that the permissions based approach
>> did not have some holes in it that I am
>> not seeing.
>
> Another possibility is to create a set of functions that contain the query
> operations you would like to allow, isolate those in a schema, and make that
> schema the only thing accessible to the (semi-)trusted users.

I can see that getting complex real fast in a big operation, but for a
database that runs a few big reporting queries every day or sits on an
intranet would be workable.

Another option is to create preferred views. These server two
purposes, one they make life easier for your users, because they don't
have to join 7 tables to look at the data anymore, the view does that
for them, or whatever makes the queries ugly. They don't have to
worry about accidentally creating an unconstrained join by accident
unless they step outside the views. The users who know how to writer
bigger and better queries and test them with explain analyze are given
view creation ability, and it's a self sustaining environment.

I've found users faced with lots of tables very receptive to views to
make their job simpler, so there's usually a pretty good buy in on it.