I'm talking about SQL Server in particular. I suppose a user tried it and got an error (you don't have select permissions on hidden underlying view B not explicitly mentioned in query). The main view A joins a table and another view (view B) together, and they don't have permission for view B.
I'm not in charge of permissions for the database, but I'm curious how it works.
Do you need to grant permissions to every object built underneath the views? Or just the view itself? Or either one?
I thought one of the purposes of views was a snapshot of underlying objects that you DON'T want to give full permissions for. That's what is unusual to me.
Best Answer
Permissions need to be granted to the person executing the query for every object referenced by the view. Except if they are owned by the view owner. In that eventuality Ownership Chains come into play.
The below creates two tables. T1 (owned by TestUser1) and T2 (owned by TestUser2) and a view that references both tables and is owned by TestUser1.
A third user is granted SELECT permissions on the view but none of the underlying objects. Selecting from the view initially fails for them. However it succeeds after they are granted select permissions on T2. There is no need to grant them permissions on T1 as this is owned by the same user as the view.
Setup
Test 1 (Fails)
Test 2 (Succeeds)
Cleanup