CREATE VIEW for sensitive info

Question

CREATE VIEW for sensitive info

mgraulich

Ten Centuries

Points: 1294
More actions
May 26, 2006 at 8:01 am

#114373

I have two dbs... test and production.
I want to be able to create a view in the test db that points to the production db. The production db has a ton of sensitive information so through this view I would do this filtering. Problem is, I can't seem to get around the fact that in order for the user to run the view on test they need to have select access on production. So now they don't need to run my filtered view. They can just run their query against production.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

rduniway SSC-Addicted Points: 419 More actions · Answer 1

Have you considered returning the data from a stored procedure, and then only giving the user the rights to execute the stored procedure?

Bob

mgraulich Ten Centuries Points: 1294 More actions · Answer 2

mgraulich

Ten Centuries

Points: 1294

May 26, 2006 at 8:14 am

#640330

Maybe I am lost on this how would this work?

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 3

trying putting 'execute as' into the view definition put it as dbo of whatever the production table needs. And then just grant access rights on view to your test users.

www.sql-library.com[/url]

mgraulich Ten Centuries Points: 1294 More actions · Answer 4

Forgive my ignorance but I am running SQL2000. Is 'execute as' available in this version?

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 5

ah nope i dont think so.

I dont think what you are asking for can be.

Perhaps the only way to do it would be to prepopulate another table, maybe re-populating it nightly, with only the rows that your test user are allowed to see then grant them access to that table.

www.sql-library.com[/url]

stax68 SSChampion Points: 11711 More actions · Answer 6

You can write a stored procedure to run the query, which work well if your view represents a finished free-standing query which is going to be returned to client:

--ultra simple stored proc
create proc ProcName
as
select col1,col2
from securetable
where secure = 0
go

You would then just run exec ProcName to run the query.

You can also select the results into a precreated table:

create table TableName(col1 int, col2 int)
insert TableName(col1,col2)
exec ProcName

The permissions of a stored procedure are independent of the user running it, as long as they have execute permissions on the stored proc itself. However, if you want to use the view as a reusable object (joined to other tables, etc.), it would be better to leave it as a view. Provided the view is owned by the same user as the source table, you can issue select permissions on the view to users who don't have permissions on the base table, which is what you are after. Select permissions on a view won't override denied permissions on the underlying table, though. Denied permissions are negative permissions which always 'trump' permissive er, permissions.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant