[SQL] How to limit access only to certain records?

[Andreas]

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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] How to limit access only to certain records?

[Andreas Kretschmer]

Andreas  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°

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] How to limit access only to certain records?

[Jov]

no,I think there is no such way.

what about create view for the user you want to limit,and revoke select
privilege from the base table ?

2012/6/22 Andreas 

> 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
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-sql
>

Re: [SQL] How to limit access only to certain records?

[Jayadevan M]

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.
Using a VIEW?
Regards,
Jayadevan





DISCLAIMER: 

"The information in this e-mail and any attachment is intended only for 
the person to whom 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 
attachment and is not liable for any errors, defects, omissions, viruses 
or for resultant loss or damage, if any, direct or indirect."

Re: [SQL] How to limit access only to certain records?

[hari . fuchs]

Andreas  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;


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql