CODEWITHSUNDEEP
sqlsecurityt-sqlsql-server-2008
littlegreen
littlegreen

Reputation: 7420

Grant SELECT permission on a view, but not on underlying objects

I often read that one purpose of a VIEW is security: to allow some users access to the underlying table, and other users to a derived view only. With that in mind I designed several views that supply restricted datasets to external users.

All very fine, but in practice this doesn't work. After I grant SELECT permission on a view, the users can't access it unless I grant SELECT on all underlying objects too. Same story for stored procedures. The net result is non-functional, for I end up still granting access to sensitive data to the wrong users, as well as annoying, for it is easy to forget one object and the users come back to complain that the view "doesn't work".

Is there a way to grant SELECT permissions on a view or stored procedure without having to expose the underlying objects too?

Upvotes: 29

Views: 82465

Answers (3)

uTILLIty
uTILLIty

Reputation: 475

If you have your views in a different schema than the table, you must either grant the user access to the base table, "AUTHORIZE" the owner of the tables to the view like this:

ALTER AUTHORIZATION ON reporting.MyViewName TO dbo

In the example above dbo is the user owning the tables the reporting.MyViewName is accessing

Upvotes: 3

ChrisLively
ChrisLively

Reputation: 88082

You might find the information in this forum helpful.

The last post has the details of what was run to grant permissions to a view but not the underlying tables:

CREATE USER [Reports] FOR LOGIN [Reports] WITH DEFAULT_SCHEMA = Reports
CREATE SCHEMA Reports AUTHORIZATION Reports --Auth as Reports was the key piece of information that I had missed.
GO
CREATE ROLE Reporting AUTHORIZATION db_securityadmin
GO
exec sp_addrolemember @rolename = 'Reporting', @membername = 'Reports'
GO
GRANT CREATE VIEW TO Reporting
GRANT CREATE TABLE TO Reporting

GRANT SELECT, VIEW DEFINITION ON [dbo].[zName] TO Reporting;

FYI - For stored procedures, you should be granting EXEC to the procedure.

Upvotes: 3

Graeme Perrow
Graeme Perrow

Reputation: 57278

Does the same user who owns the view also own the underlying tables? If not, the owner of the tables needs to grant the view owner permission WITH GRANT OPTION. If the same user owns both the tables and the view, then granting permission on the view should be sufficient.

Upvotes: 25

Related Questions