Limit rows by user?

[janice]

Hello,

How can I limit the data in a table to a subset of rows for particular users? What I mean is say I have a column called business_unit in a table named org. I only want user A to be able to query rows from the org table where the business_unit is 'Accounting' and I only want user B to be able to see rows in the org table where the business_unit is 'Administration', etc.

Thanks in advance!

[jpcavanaugh]

Can you lock down the main table and only give users access to views which limit correctly?

[janice]

Thanks for your response, jpcavanaugh.

Do you mean that I should not allow the users to read from table and then create a view for each user and control who sees what via the WHERE clause in the view?

Is that possible? According to the documentation:

If a non-owner runs a SELECT query on the view, the view owner must also have SELECT ... WITH GRANT OPTION privileges on the view's base tables or views. This privilege must be directly granted to the owner, rather than through a role.

Won't I have to grant select on the base tables to the users?

[jpcavanaugh]

The wording of the documentation is confusing.

The owner of the view has to have the select privilege not the user who is running the query. So, User1 owns the view and User1 must have select privs on the base table. User2 just needs privs on the view and then it should work fine.

[janice]

Cool, thanks! I was able to create a view like you suggested

[jpcavanaugh]

Glad it worked!