May 26, 2005 at 8:38 am
I have 3 regions that share a single database. I want create views of certain tables so that each region will only see their own data, but I want the view names to be the same as the underlying table. That is, I want something like this:
Table = dbo.CORP
Views = reg1.CORP
reg2.CORP
reg3.CORP
This works as long as the regional users have permissions to both their own view and table dbo.CORP. However, I would like to NOT grant SELECT permission to the underlying table if possible since I don't want a regional user to be able to directly query the table. No luck so far, since if I revoke permission to the table, none of the views work.
I know I could just give each region a unique view name owned by dbo, but that is not what the customer is asking for. Is this possible?
May 26, 2005 at 8:42 am
1 - Create a table with all the usernames and associate it with their region
2 - Create table dbo.CORP
3 - Create view dbo.vwCorp
4 - Create a function that returns the region depending on the username
5 - add a where condition to the view :
where tableregion = dbo.fnGetRegionFromUserName (UserName)
6 - Grant everyone select to the view
May 26, 2005 at 8:49 am
forgot to mention to create the view using : WITH CHECK OPTION
This will forbid them to change the region_id, so their information will always remain accessible.
May 26, 2005 at 11:03 pm
Take a look in BOL at the REFERENCES permission, this should give you what you are looking for:
GRANT SELECT, REFERENCES ON reg1.CORP TO reg1 AS dbo
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply