Deny read permission can be circumvented with a view?

Consider user is denied access to a table of financial secrets:

SELECT * FROM Transactions
SELECT permission denied on object 'Transact开发者_如何转开发ions'

No problem:

CREATE VIEW dbo.Transactions2 AS SELECT * FROM Transactions
Command(s) completed succesfully.

SELECT * FROM Transactions2
(84,387,982 row(s) affected)

Are users supposed to be able to bypass deny permissions on a table by aliasing the table?

Edit: Sauce:

This is working as advertised

It's called "ownership chaining"

• dbo owns both table and view/function/stored proc
• view/function/stored proc references table
• table permissions are not checked at all (for GRANT, DENY, whatever)

If you don't want someone to see a column/table, don't have it in the view/function/stored proc. Or add logic/joins to check permissions according to whatever model you've used.

Previous answers: one, two

It's been in SQL Server and Sybase since, well, long time.

Isn't this part of the intent of views in the first place? To enable visibility to certain, specific information from tables when the user does not otherwise have select permissions on the underlying table(s)?

Sounds like the problem here is that the user in question has rights to create a view in the first place.

For example, say you wanted to expose the non-confidential information in that table; you could do that with a view that limits the results only to what you wanted to be seen.