pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to Prevent Certain Kinds of Joins?
7+ messages / 5 participants
[nested] [flat]

* How to Prevent Certain Kinds of Joins?
@ 2005-02-22 19:51 Heather Johnson <hjohnson@nypost.com>
  2005-02-22 20:08 ` Re: [GENERAL] How to Prevent Certain Kinds of Joins? Richard Huxton <dev@archonet.com>
  2005-02-22 20:09 ` Re: [GENERAL] How to Prevent Certain Kinds of Joins? Michael Fuhr <mike@fuhr.org>
  0 siblings, 2 replies; 7+ messages in thread

From: Heather Johnson @ 2005-02-22 19:51 UTC (permalink / raw)
  To: pgsql-admin; pgsql-general@postgresql.org

Is there a way to revoke permission to join two or more tables, even for 
users who have all other permissions (e.g., select, insert, update, 
delete) on those tables?

Heather Johnson
Senior Database Programmer
New York Post



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] How to Prevent Certain Kinds of Joins?
  2005-02-22 19:51 How to Prevent Certain Kinds of Joins? Heather Johnson <hjohnson@nypost.com>
@ 2005-02-22 20:08 ` Richard Huxton <dev@archonet.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Richard Huxton @ 2005-02-22 20:08 UTC (permalink / raw)
  To: Heather Johnson <hjohnson@nypost.com>; +Cc: pgsql-admin; pgsql-general@postgresql.org

Heather Johnson wrote:
> Is there a way to revoke permission to join two or more tables, even for 
> users who have all other permissions (e.g., select, insert, update, 
> delete) on those tables?

I don't think you can, and I'm not sure it makes sense to. If I can 
select from tables ta,tb then I can match them up in my client - if 
needs be I can save the data and import it into a local database.

Could you hide the column(s) being joined on? If so, then you could 
create two views and just grant access to those.
  CREATE TABLE ta (id_a int, notes_a text, joinval_a int)
  CREATE TABLE tb (id_b int, notes_b text, joinval_b int)
  CREATE VIEW va AS SELECT id_a,notes_a FROM ta
  CREATE VIEW vb AS SELECT id_b,notes_b FROM tb
  GRANT ALL ON VIEW va TO ...
  GRANT ALL ON VIEW vb TO ...
  REVOKE ALL ON TABLE ta FROM ...
  REVOKE ALL ON TABLE tb FROM ...
You'll want to add rules for updating/inserting, assuming that can be 
done while concealing the existence of joinval_a/b

-- 
   Richard Huxton
   Archonet Ltd



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] How to Prevent Certain Kinds of Joins?
  2005-02-22 19:51 How to Prevent Certain Kinds of Joins? Heather Johnson <hjohnson@nypost.com>
@ 2005-02-22 20:09 ` Michael Fuhr <mike@fuhr.org>
  2005-02-22 20:30   ` Re: [GENERAL] How to Prevent Certain Kinds of Joins? Heather Johnson <hjohnson@nypost.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Michael Fuhr @ 2005-02-22 20:09 UTC (permalink / raw)
  To: Heather Johnson <hjohnson@nypost.com>; +Cc: pgsql-admin; pgsql-general@postgresql.org

On Tue, Feb 22, 2005 at 02:51:09PM -0500, Heather Johnson wrote:

> Is there a way to revoke permission to join two or more tables, even for 
> users who have all other permissions (e.g., select, insert, update, 
> delete) on those tables?

For what purpose?  If this were possible, then users could still
do joins on the client side if they had access to all the data.
Are you just trying to prevent potentially large queries?  What
problem are you trying to solve?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] How to Prevent Certain Kinds of Joins?
  2005-02-22 19:51 How to Prevent Certain Kinds of Joins? Heather Johnson <hjohnson@nypost.com>
  2005-02-22 20:09 ` Re: [GENERAL] How to Prevent Certain Kinds of Joins? Michael Fuhr <mike@fuhr.org>
@ 2005-02-22 20:30   ` Heather Johnson <hjohnson@nypost.com>
  2005-02-22 20:51     ` Re: [GENERAL] How to Prevent Certain Kinds of Joins? Scott Marlowe <smarlowe@g2switchworks.com>
  2005-02-22 21:05     ` Re: How to Prevent Certain Kinds of Joins? Steve Crawford <scrawford@pinpointresearch.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Heather Johnson @ 2005-02-22 20:30 UTC (permalink / raw)
  To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-admin; pgsql-general@postgresql.org

One of the tables contains personally identifiable information (PII) 
(e.g., email, first and last name, etc.). The other contains "click 
stream" data (information about where on our website users have gone). 
In order to be sensitive to users privacy, we don't want to ever (even 
accidentally) run queries that would combine PII and click stream data. 
So we're looking for ways to put "walls" up against combining the data. 
We understand that anyone with ample access to the database can 
deliberately combine the data if they chose to do so in blatent 
violation of our policies. But we want to set something up that would 
put an obstacle or two in the path of anyone who might accidentally run 
such a query.

The ideas about setting up views might work. I guess we'd just have to 
change the permissions of users who have access to the db so that they 
can only use the views and not query the tables directly.

Michael Fuhr wrote:
> On Tue, Feb 22, 2005 at 02:51:09PM -0500, Heather Johnson wrote:
> 
> 
>>Is there a way to revoke permission to join two or more tables, even for 
>>users who have all other permissions (e.g., select, insert, update, 
>>delete) on those tables?
> 
> 
> For what purpose?  If this were possible, then users could still
> do joins on the client side if they had access to all the data.
> Are you just trying to prevent potentially large queries?  What
> problem are you trying to solve?
> 



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] How to Prevent Certain Kinds of Joins?
  2005-02-22 19:51 How to Prevent Certain Kinds of Joins? Heather Johnson <hjohnson@nypost.com>
  2005-02-22 20:09 ` Re: [GENERAL] How to Prevent Certain Kinds of Joins? Michael Fuhr <mike@fuhr.org>
  2005-02-22 20:30   ` Re: [GENERAL] How to Prevent Certain Kinds of Joins? Heather Johnson <hjohnson@nypost.com>
@ 2005-02-22 20:51     ` Scott Marlowe <smarlowe@g2switchworks.com>
  2005-02-22 21:04       ` Re: [GENERAL] How to Prevent Certain Kinds of Joins? Heather Johnson <hjohnson@nypost.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Scott Marlowe @ 2005-02-22 20:51 UTC (permalink / raw)
  To: Heather Johnson <hjohnson@nypost.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-admin; pgsql-general@postgresql.org

On Tue, 2005-02-22 at 14:30, Heather Johnson wrote:
> One of the tables contains personally identifiable information (PII) 
> (e.g., email, first and last name, etc.). The other contains "click 
> stream" data (information about where on our website users have gone). 
> In order to be sensitive to users privacy, we don't want to ever (even 
> accidentally) run queries that would combine PII and click stream data. 
> So we're looking for ways to put "walls" up against combining the data. 
> We understand that anyone with ample access to the database can 
> deliberately combine the data if they chose to do so in blatent 
> violation of our policies. But we want to set something up that would 
> put an obstacle or two in the path of anyone who might accidentally run 
> such a query.
> 
> The ideas about setting up views might work. I guess we'd just have to 
> change the permissions of users who have access to the db so that they 
> can only use the views and not query the tables directly.

Why not change the keys that currently connect them to something
different (i.e. random noise) and make a NEW table that could join them
with those random keys that is restriced access wise to only the chosen
few.

Or do you need to actually ever re-reference the two datasets?  If not,
then just lose the connecting data when you insert the rows.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] How to Prevent Certain Kinds of Joins?
  2005-02-22 19:51 How to Prevent Certain Kinds of Joins? Heather Johnson <hjohnson@nypost.com>
  2005-02-22 20:09 ` Re: [GENERAL] How to Prevent Certain Kinds of Joins? Michael Fuhr <mike@fuhr.org>
  2005-02-22 20:30   ` Re: [GENERAL] How to Prevent Certain Kinds of Joins? Heather Johnson <hjohnson@nypost.com>
  2005-02-22 20:51     ` Re: [GENERAL] How to Prevent Certain Kinds of Joins? Scott Marlowe <smarlowe@g2switchworks.com>
@ 2005-02-22 21:04       ` Heather Johnson <hjohnson@nypost.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Heather Johnson @ 2005-02-22 21:04 UTC (permalink / raw)
  To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-admin; pgsql-general@postgresql.org



Scott Marlowe wrote:

> Why not change the keys that currently connect them to something
> different (i.e. random noise) and make a NEW table that could join them
> with those random keys that is restriced access wise to only the chosen
> few.

This might work rather nicely. It would enable us to restrict direct 
access to only a single table---a table with no purpose other than to 
faciliate a join of these two other tables. And staff that needs direct 
access to the original tables can continue to have it.

> Or do you need to actually ever re-reference the two datasets?  If not,
> then just lose the connecting data when you insert the rows.

Yes, unfortunately we do need to re-reference them. But I think the idea 
above will work out pretty well. Thank you for your help!

Heather



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to Prevent Certain Kinds of Joins?
  2005-02-22 19:51 How to Prevent Certain Kinds of Joins? Heather Johnson <hjohnson@nypost.com>
  2005-02-22 20:09 ` Re: [GENERAL] How to Prevent Certain Kinds of Joins? Michael Fuhr <mike@fuhr.org>
  2005-02-22 20:30   ` Re: [GENERAL] How to Prevent Certain Kinds of Joins? Heather Johnson <hjohnson@nypost.com>
@ 2005-02-22 21:05     ` Steve Crawford <scrawford@pinpointresearch.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Steve Crawford @ 2005-02-22 21:05 UTC (permalink / raw)
  To: Heather Johnson <hjohnson@nypost.com>; +Cc: pgsql-general@postgresql.org

On Tuesday 22 February 2005 12:30 pm, Heather Johnson wrote:
> One of the tables contains personally identifiable information
> (PII) (e.g., email, first and last name, etc.). The other contains
> "click stream" data (information about where on our website users
> have gone). In order to be sensitive to users privacy, we don't
> want to ever (even accidentally) run queries that would combine PII
> and click stream data. So we're looking for ways to put "walls" up
> against combining the data. We understand that anyone with ample
> access to the database can deliberately combine the data if they
> chose to do so in blatent violation of our policies. But we want to
> set something up that would put an obstacle or two in the path of
> anyone who might accidentally run such a query.

How about separate databases? If there is absolutely no need to 
"relate" the data in two tables, I can't see much reason other, 
perhaps, than convenience of user/group management to have them both 
in the same database. This _will_ prevent joins as long as you don't 
install the contrib/dblink module.

Of course anyone with access to both pieces of data can still export 
the data and reimport it to whatever database system they want in 
order to combine the data.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2005-02-22 21:05 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-22 19:51 How to Prevent Certain Kinds of Joins? Heather Johnson <hjohnson@nypost.com>
2005-02-22 20:08 ` Richard Huxton <dev@archonet.com>
2005-02-22 20:09 ` Michael Fuhr <mike@fuhr.org>
2005-02-22 20:30   ` Heather Johnson <hjohnson@nypost.com>
2005-02-22 20:51     ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-02-22 21:04       ` Heather Johnson <hjohnson@nypost.com>
2005-02-22 21:05     ` Steve Crawford <scrawford@pinpointresearch.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox