pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow 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>
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 20:08 Richard Huxton <dev@archonet.com>
parent: Heather Johnson <hjohnson@nypost.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 20:09 Michael Fuhr <mike@fuhr.org>
parent: 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 20:30 Heather Johnson <hjohnson@nypost.com>
parent: Michael Fuhr <mike@fuhr.org>
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 20:51 Scott Marlowe <smarlowe@g2switchworks.com>
parent: 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 21:04 Heather Johnson <hjohnson@nypost.com>
parent: Scott Marlowe <smarlowe@g2switchworks.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 21:05 Steve Crawford <scrawford@pinpointresearch.com>
parent: Heather Johnson <hjohnson@nypost.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