Re: row-level security (Dynamically rewrite queries)

Jonatan Evald Buus wrote:
> Essentially what I'd like to do is implement row-level security (what Oracle
> calls "Virtual Private Database") but as far as I can find the last time
> this was discussed is several years ago and the general consensus ended up
> being "use veil".
> Veil seems overly like an complicated approach for something that (in
> theroy) should be possible with a dynamic query rewrite using search and
> replace prior to execution.

I've never used veil myself, but I can't believe it's less effort to
re-invent the wheel on this. The query-rewrite is what the views are doing.

> Oracle's implementation seems quiete elegant for this, please see
> http://www.oracle.com/technology/pub/articles/10gdba/week14_10gdba.html or
> http://www.devshed.com/c/a/Oracle/RowLevel-Security-with-Virtual-Private-Database/for
> examples.
>
> One other approach that I could think of, would be to create a view for
> every table and use the view for accessing the data:
> CREATE VIEW Transaction_Vw AS
> SELECT * FROM Transaction_Tbl Txn
> INNER JOIN User_Tbl U ON Txn.userid = U.id
> WHERE U.name = 'CURRENT_USER'
> However, can usage this view be enforced by the database by removing SELECT
> priviliges from Transaction_Tbl?

Yes, of course. This also seems to be what veil does for you. Beware -
views are basically macros that rewrite your query and you can get
unexpected results when e.g. inserting multiple rows at once. See
mailing-list archives for examples.

> Also, I suspect that performance would go down the drain if complex joins
> are used? There could potentially be a lot of unnecessary joins to User_Tbl
> if multiple "secure view" where joined together.
> Is the planner capable of taking this into account and auto-magically
> optimize the query?

There's some overhead associated with rewriting the query, but the
planner should cope just fine then. Which isn't to say there aren't
cases where you couldn't write a better query from scratch, but the
planner optimises rewritten queries as well as any other.

> Are there any other alternatives to implementing row-level security that can
> be enforced at the database level?

Use veil. Someone else has gone to the trouble to think this through. If
you write your own solution you'll only have 1 user (you). Even if veil
is only used by its author there will at least be 2 of you.

I frequently use a "my" schema with views that map to the relevant base
tables ("my.clients" etc). However, that's not for applications that
require some guarantee of security database-wide, it's as much about
simplifying my queries.

--
Richard Huxton
Archonet Ltd