Обсуждение: How to limit access only to certain records?

Hi,

is there a way to limit access for some users only to certain records?

e.g. there is a customer table and there are account-managers.
Could I limit account-manager #1 so that he only can access customers 
only acording to a flag?

Say I create a relation  cu_am ( customer_id, account_manager_id ).
Could I let the database control that account-manager #1 can only see 
customers who are assigned to him in the cu_am-relation?

For now I do this in the front-end but this is easily circumvented for 
anyone who has a clue and uses some other client like psql.


Regards
Andreas

Andreas <maps.on@gmx.net> wrote:

> Hi,
>
> is there a way to limit access for some users only to certain records?
>
> e.g. there is a customer table and there are account-managers.
> Could I limit account-manager #1 so that he only can access customers  
> only acording to a flag?

Yea, it's possible.

Write functions to access to the table (for select, for insert and so
on) as superuser, with secutity definer, revoke all rights from the
user.

Users can only access to the table with the functions, within this
functions check if the current_user has rights for the record.

There are some examples how to do that, please use google ;-)



Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

<font face="sans-serif" size="2">HI,</font><br /><font face="sans-serif" size="2">> <br /> > is there a way to
limitaccess for some users only to certain records?<br /> > <br /> > e.g. there is a customer table and there are
account-managers.<br/> > Could I limit account-manager #1 so that he only can access customers <br /> > only
acordingto a flag?<br /> > <br /> > Say I create a relation  cu_am ( customer_id, account_manager_id ).<br />
>Could I let the database control that account-manager #1 can only see <br /> > customers who are assigned to him
inthe cu_am-relation?<br /> > <br /> > For now I do this in the front-end but this is easily circumvented for <br
/>> anyone who has a clue and uses some other client like psql.<br /> Using a VIEW?</font><br /><font
face="sans-serif"size="2">Regards,</font><br /><font face="sans-serif" size="2">Jayadevan<br /></font><br /><font
face="sans-serif"size="2"><br /><br /><br /><br /> DISCLAIMER:</font><font size="3"> </font><font color="#a2a2a2"
face="Tahoma"size="1"><br /><br /> "The information in this e-mail and any attachment is intended only for the person
towhom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in
error,kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or
implied,nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any
attachmentand is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct
orindirect."</font><font size="3"><br /></font><font size="1"><br /></font><font size="3"><br /></font><br /><br /> 

Andreas <maps.on@gmx.net> writes:

> Hi,
>
> is there a way to limit access for some users only to certain records?
>
> e.g. there is a customer table and there are account-managers.
> Could I limit account-manager #1 so that he only can access customers
> only acording to a flag?

Maybe something like the following:

CREATE TABLE test1 ( id serial NOT NULL, val text NOT NULL, _user text NOT NULL, PRIMARY KEY (id)
);

COPY test1 (val, _user) FROM stdin;
for user1#1    user1
for user1#2    user1
for user2#1    user2
\.

CREATE VIEW test1v AS
SELECT id, val
FROM test1
WHERE _user = current_user;

<div class="moz-cite-prefix">On 06/22/2012 07:36 PM, Andreas wrote:<br /></div><blockquote
cite="mid:4FE458AB.4000109@gmx.net"type="cite">Hi, <br /><br /> is there a way to limit access for some users only to
certainrecords? <br /><br /> e.g. there is a customer table and there are account-managers. <br /> Could I limit
account-manager#1 so that he only can access customers only acording to a flag?<br /></blockquote><br /> What you
describeis called row-level access control, row level security, or label access control, depending on who you're
talkingto. It's often discussed as part of multi-tenant database support.<br /><br /> As far as I know PostgreSQL does
notcurrently offer native facilities for row-level access control (except possibly via SEPostgreSQL <a
href="http://wiki.postgresql.org/wiki/SEPostgreSQL_Introduction">http://wiki.postgresql.org/wiki/SEPostgreSQL_Introduction</a>).
There'sdiscussion of adding such a feature here <a
href="http://wiki.postgresql.org/wiki/RLS">http://wiki.postgresql.org/wiki/RLS</a>.<br /><br /> As others have noted
thetraditional way to do this in DBs without row level access control is to use a stored procedure (in Pg a SECURITY
DEFINERfunction), or a set of access-limited vies, to access the data. You then REVOKE access on the main table for the
userso they can *only* get the data via the procedure/views.<br /><br /> See:<br />   <a
href="http://www.postgresql.org/docs/9.1/static/sql-createview.html">http://www.postgresql.org/docs/current/static/sql-createview.html</a><br
/>  <a href="http://www.postgresql.org/docs/9.1/static/sql-createfunction.html">http://www.postgresql.org/docs/</a><a
href="http://www.postgresql.org/docs/9.1/static/sql-createview.html">current</a><a
href="http://www.postgresql.org/docs/9.1/static/sql-createfunction.html">/static/sql-createfunction.html</a><br/>   <a
href="http://www.postgresql.org/docs/9.1/static/sql-grant.html">http://www.postgresql.org/docs/current/static/sql-grant.html</a><br
/>  <a
href="http://www.postgresql.org/docs/9.1/static/sql-revoke.html">http://www.postgresql.org/docs/current/static/sql-revoke.html</a><br
/>  <br /> Hope this helps.<br /><br /> --<br /> Craig Ringer<br /> 

2012/6/24 Craig Ringer <ringerc@ringerc.id.au>:
> As far as I know PostgreSQL does not currently offer native facilities for
> row-level access control (except possibly via SEPostgreSQL
> http://wiki.postgresql.org/wiki/SEPostgreSQL_Introduction).

Yes. Row-level access was in SEPostgreSQL's drafts but after many discussions
the conclusion is that since PostgreSQL doesn't support row-level access
sepgsql, that is in contrib [1] nowadays, does not support it either.

[1] http://www.postgresql.org/docs/9.1/static/sepgsql.html

Regards.
-- 
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br