Thread: 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.

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.

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

Hello--

I need to make sure that every time a row is inserted into a table
called "users" rows are automatically inserted into two other tables:
"join_bd" and "behavior_demographics". The inserts on join_bd and
behavior_demographics need to create rows that are keyed to the users
table with an integer id (called "users_id"). The join_bd row that's
created also needs to contain a key for a record in
behavior_demographics (bd_id). Here's what I did to try and accomplish this:

CREATE FUNCTION insert_bd_join_bd (integer) RETURNS opaque AS '
declare
    r RECORD;
         uid ALIAS FOR $1;
begin
         INSERT INTO behavioral_demographics (users_id) VALUES (uid);
    SELECT INTO r bdid FROM behavioral_demographics WHERE users_id=uid;
    INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, uid);
end;
' LANGUAGE 'plpgsql';

Now I want to trigger this function whenever there is an insert on the
users table, so I did this:

CREATE TRIGGER insert_bd_join_bd_on_users AFTER INSERT ON users
FOR EACH ROW EXECUTE PROCEDURE insert_bd_join_bd();

The problem is that I need to be able to send the value of the users_id
that was generated by the insert into users to insert_bd_join_bd(). The
users id value is generated by a sequence. So I think I'd need something
like this:

CREATE TRIGGER insert_bd_join_bd_on_users AFTER INSERT ON users
FOR EACH ROW EXECUTE PROCEDURE insert_bd_join_bd(***USERS ID GENERATED
BY SEQ FROM LAST USERS INSERT***);

I'm new to writing plpgsql and to triggers, so I'm probably missing
something, or doing this wrong, but I can't figure out from the docs how
to send this value to the function. Can someone help point me to some
docs that might help, or tell me why I'm on the wrong track?

Thanks so much!
Heather Johnson

On Wed, Mar 16, 2005 at 02:34:48PM -0500, Heather Johnson wrote:

> CREATE FUNCTION insert_bd_join_bd (integer) RETURNS opaque AS '

What version of PostgreSQL are you using?  The "opaque" type has
been deprecated since 7.3; recent versions should use "trigger".
And trigger functions aren't declared with arguments -- if the
function needs arguments, then use TG_ARGV.  But I think your code
can use NEW instead of a function argument.

> declare
>         r RECORD;
>         uid ALIAS FOR $1;
> begin
>         INSERT INTO behavioral_demographics (users_id) VALUES (uid);
>         SELECT INTO r bdid FROM behavioral_demographics WHERE users_id=uid;
>         INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, uid);
> end;
> ' LANGUAGE 'plpgsql';

Based on the rest of your description, I think you're looking for
something like this:

CREATE FUNCTION insert_bd_join_bd() RETURNS trigger AS '
DECLARE
    bdid  integer;
BEGIN
    INSERT INTO behavioral_demographics (users_id) VALUES (NEW.uid);
    bdid := currval(''behavioral_demographics_bdid_seq'');
    INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, NEW.uid);

    RETURN NULL;
END;
' LANGUAGE plpgsql VOLATILE;

See the "Trigger Procedures" section of the PL/pgSQL chapter in the
documentation to learn more about NEW (and OLD, TG_ARGV, etc.), and
see the "Sequence Manipulation Functions" section of the "Functions
and Operators" chapter to learn more about currval().

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

Heather Johnson wrote:
> Hello--
>
> I need to make sure that every time a row is inserted into a table
> called "users" rows are automatically inserted into two other tables:
> "join_bd" and "behavior_demographics". The inserts on join_bd and
> behavior_demographics need to create rows that are keyed to the users
> table with an integer id (called "users_id"). The join_bd row that's
> created also needs to contain a key for a record in
> behavior_demographics (bd_id). Here's what I did to try and accomplish
> this:
>
> CREATE FUNCTION insert_bd_join_bd (integer) RETURNS opaque AS '
> declare
>     r RECORD;
>         uid ALIAS FOR $1;
> begin
>     INSERT INTO behavioral_demographics (users_id) VALUES (uid);
>     SELECT INTO r bdid FROM behavioral_demographics WHERE users_id=uid;
>     INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, uid);
> end;
> ' LANGUAGE 'plpgsql';

Wouldn't it be nicer to put the second part of that SP in a trigger on
behavioral_demographics? That can only work if inserting into users is
the only way to insert records into behavioral_demographics, of course.

I don't know the names of your columns in behavioral_demographics, but
it would look something like this:

CREATE FUNCTION insert_bd () RETURNS trigger AS '
begin
     INSERT INTO behavioral_demographics (users_id)
     VALUES (NEW.users_id);    -- fires trigger on bd
end;
' LANGUAGE 'plpgsql';

CREATE FUNCTION insert_join_bd () RETURNS trigger AS '
begin
     INSERT INTO join_bd (bd_id, users_id)
     VALUES (NEW.bd_id, NEW.users_id);
end;
' LANGUAGE 'plpgsql';

CREATE TRIGGER insert_bd_on_users
AFTER INSERT ON users
FOR EACH ROW EXECUTE PROCEDURE insert_bd();

CREATE TRIGGER insert_join_bd_on_users
AFTER INSERT ON behavioral_demographics
FOR EACH ROW EXECUTE PROCEDURE insert_join_bd();


Considering these SP's are basically simple SQL statements, the triggers
could also be implemented as a set of query rewrite rules (see CREATE
RULE). That's usually more efficient, but I don't have a lot of
experience with those...

Regards,

--
Alban Hertroys
MAG Productions

Thank you! That *is* nicer. And thanks to Michael Fuhr too for his
reference to the appropriate docs and suggestions. Between your two
responses, I have a much better sense of how to go about this and where
to look for extra help.

Heather

Alban Hertroys wrote:
> Heather Johnson wrote:
>
>> Hello--
>>
>> I need to make sure that every time a row is inserted into a table
>> called "users" rows are automatically inserted into two other tables:
>> "join_bd" and "behavior_demographics". The inserts on join_bd and
>> behavior_demographics need to create rows that are keyed to the users
>> table with an integer id (called "users_id"). The join_bd row that's
>> created also needs to contain a key for a record in
>> behavior_demographics (bd_id). Here's what I did to try and accomplish
>> this:
>>
>> CREATE FUNCTION insert_bd_join_bd (integer) RETURNS opaque AS '
>> declare
>>     r RECORD;
>>         uid ALIAS FOR $1;
>> begin
>>     INSERT INTO behavioral_demographics (users_id) VALUES (uid);
>>     SELECT INTO r bdid FROM behavioral_demographics WHERE users_id=uid;
>>     INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, uid);
>> end;
>> ' LANGUAGE 'plpgsql';
>
>
> Wouldn't it be nicer to put the second part of that SP in a trigger on
> behavioral_demographics? That can only work if inserting into users is
> the only way to insert records into behavioral_demographics, of course.
>
> I don't know the names of your columns in behavioral_demographics, but
> it would look something like this:
>
> CREATE FUNCTION insert_bd () RETURNS trigger AS '
> begin
>     INSERT INTO behavioral_demographics (users_id)
>     VALUES (NEW.users_id);    -- fires trigger on bd
> end;
> ' LANGUAGE 'plpgsql';
>
> CREATE FUNCTION insert_join_bd () RETURNS trigger AS '
> begin
>     INSERT INTO join_bd (bd_id, users_id)
>     VALUES (NEW.bd_id, NEW.users_id);
> end;
> ' LANGUAGE 'plpgsql';
>
> CREATE TRIGGER insert_bd_on_users
> AFTER INSERT ON users
> FOR EACH ROW EXECUTE PROCEDURE insert_bd();
>
> CREATE TRIGGER insert_join_bd_on_users
> AFTER INSERT ON behavioral_demographics
> FOR EACH ROW EXECUTE PROCEDURE insert_join_bd();
>
>
> Considering these SP's are basically simple SQL statements, the triggers
> could also be implemented as a set of query rewrite rules (see CREATE
> RULE). That's usually more efficient, but I don't have a lot of
> experience with those...
>
> Regards,
>