Archives of the TeradataForum

Message Posted: Wed, 26 Nov 2008 @ 17:21:41 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

It's true that

GRANT SELECT ON <database containing tables> TO <database containing views> WITH GRANT OPTION;

will fix the problem, but be careful.

I think it's the case that in order for any user - including the owner - to select from a view, the view's owner needs select with grant on any object or database referenced by the view. Since select w/ grant can't be granted to a role, it needs to be granted directly to the users or databases that will own the views.

If you're in a procedure- or audit-heavy organization, you need to be very careful about making these grants and how the effect the documentation and approval chain for data access.

The thing you have to work out is whether or not you want to grant select w/ grant on entire databases or to individual objects. The broadest approach would be to grant it to users, but then you've lost control over security. Next would be to grant it to a database used to own views, but even that isn't great. Take a user who does not have select on tableX but can create a view in the view database. I believe they would be able to create a view of tableX and select from it. (Can anybody confirm this? I don't have the proper set of rights to test it myself.)

The strictest approach would be to make the grant only on specific tables to specific users. It still means that you've lost control of data access security, but it's over a smaller set of users, and it's easier to implement a security policy that forces those users to take responsibility for allowing access.

Happy Thanksgiving to the US and expat forum-ites, and a great Thursday to everybody else!

Dave

	<Prev	Next>		<<First	<Prev	Next>	Last>>