I am trying to create a filtered, secure view of another table, without having to grant read access to the originating table. I have not found a way to do this.
select * from otherdb.dbo.table as t1
WHERE t1.userid = USER
If USER only has access to my_view, he will only see selective columns in t1. However, it seems that for USER to SELECT from my_view, USER has to also have SELECT privileges for otherdb.dbo.table. I wish for users to have access to my_view, but not have select access to otherdb.dbo.table and thus have a restricted view of otherdb.dbo.table.
Is there any way to implement security like this with views?
You can do it, but you have to have all the objects in the same database. In SQL Server there is the concept of Ownership Chaining. If an owner (in this case dbo) wants to grant you select rights to a view, he does not need to grant permissions on any sub-views or tables. If a user (call him A) creates a view based on another user's (B) table, then select permission on A's view will require select permission on B's table.
Now, here is where it gets into muddy water. You are now going from DB to DB. There is no hard link in the name of an owner in one DB vs. another. dbo could be mapped to someone other than sa in any database, so keeping the Ownership Chain across databases can provide you with some problems in security as the owner of one DB can peek at the other's data at will.
In short, yes, you will have to grant select permission on the base table to the users (or guest/public).