That part works okay, but here is a more complicated situation that
doesn't:
create user user1;
create table t1 (x int, y int);
create table t2 (a int, y int);
create view v1 as select * from t1 where x in (select a from t2);
------------------
create view v2 as select * from v1 where x>3;
revoke all on t1 from public;
revoke all on t2 from public;
revoke all on v1 from public;
revoke all on v2 from public;
grant select on v2 to user1;
\c - user1
select * from v2;
====> v1: Permission denied.
The problem occurs because of the nested select underlined above. Since
user1 has permissions on v2, he should be able to execute v2's select
statement. V2 accesses another view v1. V1 accesses t1, which causes no
problems since t1 is a table. But if v1 has a nested select statement (or
calls a function which executes a select statement), you get a permission
denied error.
The workaround I did was to create a bunch of extra subview which user1
has access to. This works, but seems like the above scenerio shouldn't be
causes the permission violation.
----------------------------------------------------------------
Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
----------------------------------------------------------------
>
> table big_customer_db is owned by user "master"
> you create a view "active_customer_list" also owned by "master"
> now, if you grant someone access to the view, they will be
> able to get the data in it, even though they can't query
> big_customer_db directly.
>
> Is this what you want?
>
> HTH,
> --
> Martijn van Oosterhout <kleptog@cupid.suninternet.com>
> http://cupid.suninternet.com/~kleptog/
>