Обсуждение: How restrict select on a view ?
Hi, I'd like to have a view only to be used by certain users. The tables are public. Can this only be done by restricting access to the tables?
On 15/12/2008 17:55, Andreas wrote: > I'd like to have a view only to be used by certain users. > The tables are public. It doesn't seem to make a lot of sense to have public access to the tables but restrict access to the views. The usual pattern is the other way around - restrict access to the tables, and use views to give public (or at least less restricted) to those subsets of the data you choose. If you have views with restricted access but leave the tables public, the users with access to the views can bypass them and go straight to the tables instead, circumventing your attempts at security. Then again, maybe I've misunderstood what you're trying to achieve... can you give more detail? Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
Andreas wrote: > I'd like to have a view only to be used by certain users. > The tables are public. > > Can this only be done by restricting access to the tables? > GRANT/REVOKE works on views revoke all on aview from public; grant select on aview to user1; As Raymond pointed out, if user2 knows what the definition of aview is, they can just run it against the raw tables. e.g. create view aview as select * from pg_proc; revoke all on aview from public; grant select on aview to user1; set session authorization user2; select * from aview; -- fails select * from pg_proc; -- works and gives the same result klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: kgore4@une.edu.au
On Mon, Dec 15, 2008 at 9:38 PM, Klint Gore <kgore4@une.edu.au> wrote: > Andreas wrote: >> >> I'd like to have a view only to be used by certain users. >> The tables are public. >> >> Can this only be done by restricting access to the tables? >> > > GRANT/REVOKE works on views > revoke all on aview from public; > grant select on aview to user1; > > As Raymond pointed out, if user2 knows what the definition of aview is, they > can just run it against the raw tables. > e.g. > create view aview as select * from pg_proc; > revoke all on aview from public; > grant select on aview to user1; > set session authorization user2; > select * from aview; -- fails > select * from pg_proc; -- works and gives the same result Yes, but: * you can still \d the view (or \d equivalent in sql) which shows it's definition * if you can \d view, you can 'create temporary view' with the same definition on public tables what does this get you? merlin
Merlin Moncure schrieb: > what does this get you? > > Not as much as I first hoped. In hindsight it was a silly question. The point was that the view gets linked into an Access-Client. There are some Users who shouldn't bother to figure over the results as it does some performance statistics. The users arent able to install other clients like pgAdmin to enter the DB w/o Access. Most of them are likely not interested enough to figure out how one would create a query anyway. A query-object just waiting to get clicked in an idle moment is another thing though. Thanks a lot anyway as I learned 1-2 thingies :)