Thread: Rewrite SELECT WHERE clause on per-session bases. Modifing view source code in one session (user's) from another session (admin's)?

Hi!
In my application's security scheme I'm planing to restrict users to view only particular "types" of records,
where "types" is set of WHERE predicates for each view, dynamically defined in application.
I believe it's most efficient way to do such things, instead of calling some decision function for each row
in view's SELECT, for example.

So, for these porposes I need some mechanism allowing "on the fly" modification of view's SELECT's.
At the beggining I review CREATE RULE ON SELECT variant, but rules are "database-wide" not "session-wide" and
SELECT rules don't allow WHERE clause.

My current solution is creating temporary viwes for each application user with
appropriate WHERE restrictions at the session begining  (but rules could be more convient).
By this way view permissions (what "types" user can select fron view) are can't be changed during session
(user can't modify view by design).

So question is: is it possible to modify view source code in one session (user's) from another session (admin's)?
Or may be more convenient way to rewrite SELECT WHERE clause on per-session bases exist (similar to RULES)?
Thanks.

On Jul 14, 2008, at 12:26 AM, sftf wrote:
> In my application's security scheme I'm planing to restrict users
> to view only particular "types" of records,
> where "types" is set of WHERE predicates for each view, dynamically
> defined in application.
> I believe it's most efficient way to do such things, instead of
> calling some decision function for each row
> in view's SELECT, for example.
>
> So, for these porposes I need some mechanism allowing "on the fly"
> modification of view's SELECT's.
> At the beggining I review CREATE RULE ON SELECT variant, but rules
> are "database-wide" not "session-wide" and
> SELECT rules don't allow WHERE clause.
>
> My current solution is creating temporary viwes for each
> application user with
> appropriate WHERE restrictions at the session begining  (but rules
> could be more convient).
> By this way view permissions (what "types" user can select fron
> view) are can't be changed during session
> (user can't modify view by design).
>
> So question is: is it possible to modify view source code in one
> session (user's) from another session (admin's)?
> Or may be more convenient way to rewrite SELECT WHERE clause on per-
> session bases exist (similar to RULES)?
> Thanks.

Search pgFoundry for veil; I believe it will do what you want.
--
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828