kobolds <dreammes2007@gmail.com> wrote:
> - I create 2 users (user1 and user2)
> - drop schema public;
> - create table sh1.tb1 (emp_no integer , emp_name varchar(50)) ;
> - insert some records into sh1.tb1
> - revoke all privileges on table sh1.tb1 from public;
> - grant all privileges on all tables in schema sh1 to user1;
>
> - /create view sh1.vw1 as select * from sh1.tb1;/
> - /revoke all privileges on sh1.vw1 from public;/
> - /grant select on sh1.vw1 to user1;/
>
> as you can see I only grant access to user1 and not user2
> from user2 i execute
> db1=> /select * from sh1.tb1;/
> ERROR: permission denied for relation tb1
>
> this is correct
>
> - /create view sh1.vw2 as select * from sh1.vw1;/
> - /revoke all privileges on sh1.vw2 from public;/
> - /grant select on sh1.vw2 to user2;/
>
> from user2 when i execute
> /select * from sh1.vw2;/
>
> it return records from sh1.tb1 in which user2 donot has
> privileges on sh1.vw1 or sh1.tb1 ;
>
> anyway for me to prevent this to happen?
Yes; don't give permission to create views to users who can't be
trusted not to grant access to their views to the correct set of
users.
A view runs with the permissions of its owner. This is more secure
than having it run with the permissions of the user referencing the
view, since otherwise a user without a privilege could write a view
which ran a function to do something naughty, like copying data the
user didn't have rights to view to a table created by the owner,
and then tricking a more privileged user into selecting from the
view.
Also, what if it is OK to allow user2 to view a *summary* of the
data from view1, or a subset of its rows? Using a view in this way
is a common way to provide that feature.
Besides, if whoever has rights to grant SELECT rights on the view
to vw2 could no longer do that, they could run:
CREATE TABLE xxx AS SELECT * FROM sh1.vw2;
... and grant user2 rights to select from it. Or for that matter,
print out the results and hand them the paper. So what you are
asking for would really protect nothing and would break some useful
techniques for enforcing security that people use every day.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company