Обсуждение: How to Prevent Certain Kinds of Joins?
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
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
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/
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? >
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.
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