Re: Access restrictions on rows depending on value of the a column

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/