Thread: Access restrictions on rows depending on value of the a column
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.
On Thu, Aug 12, 2004 at 09:54:25 -0300, Renato Cramer <renato@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.
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/