Access restrictions on rows depending on value of the a column

[Renato Cramer]

Hello All,

There is data of several enterprises (ours clients) in a single database.
All tables have a column on primary key what identify the enterprise called
id_enterprise.
My objective is restrict users access on rows depending on value of the
column id_enterprise.
That is, an user can access data only of his enterprise.

I don't want use where clause.
There is some way of determine this in database?

Thanks in advance,

Renato Cramer.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Bruno Wolff III]

On Thu, Aug 12, 2004 at 09:54:25 -0300,
Renato Cramer <re****@domsis. com.br> wrote:

Hello All,

There is data of several enterprises (ours clients) in a single database.
All tables have a column on primary key what identify the enterprise called
id_enterprise.
My objective is restrict users access on rows depending on value of the
column id_enterprise.
That is, an user can access data only of his enterprise.

I don't want use where clause.
There is some way of determine this in database?

You can use a view to do this. If they need to modify the data, then you
will need to use rules to make an updateable view. The easy way to do
this is to have a table that maps users to the id_enterprise values that
they are allowed to access and for each table you want to grant access
to, join this special table in the view using the current_user as the
the user to test against.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Michael Fuhr]

On Thu, Aug 12, 2004 at 09:54:25AM -0300, Renato Cramer wrote:

There is data of several enterprises (ours clients) in a single database.
All tables have a column on primary key what identify the enterprise called
id_enterprise.
My objective is restrict users access on rows depending on value of the
column id_enterprise.
That is, an user can access data only of his enterprise.
By "user" do you mean a PostgreSQL user, i.e., a user that can
connect directly to the database? Or do the users access the
database via an application? How is a user associated with a
particular id_enterprise value?

What kind of access to users require? Select only? Or also
insert, update, and delete?
I don't want use where clause.
There is some way of determine this in database?

For selects you could use views that join the tables against
a lookup table using id_enterprise and CURRENT_USER. Queries
against a view would get a subset of what's in a table, based
on who's doing the query. This assumes that the database user
can be mapped to one or more id_enterprise values via the
lookup table.

If you need to restrict inserts, updates, and deletes, then you
could use rules and/or triggers.

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html