Why Grant User View Definitions to Database Objects?

Question

Why Grant User View Definitions to Database Objects?

andrew.jones 69458

Old Hand

Points: 321
More actions
September 19, 2017 at 9:10 am

#325438

I have setup a user defined role to grant members of the role certain access to database objects like Tables, Views and Stored Procedures. I have granted them the usual Select, Insert, Delete, Update for Tables; Select for Views and Execute for Stored Procs. My questions is should I also be granting my users n this role the ability to View Definitions for these objects as well? There are loads of examples on the net for how to grant users View Definition ability, but I have not found anything that explains under what circumstances you would want to grant users the View Definition ability. I understand that it allows users to see the metadata for an object, but it is not clear to me why a user would need this ability. I would very much appreciate if someone here could enlighten me on when and why this permission should be granted.Thanks for the info.
Andrew J

Viewing 13 posts - 1 through 12 (of 12 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Andrew

If, for example, the users will be troubleshooting performance problems, then they'll need to know the definitions of stored procedures, data types of columns and so on. That's one example of why they'd need the VIEW DEFINITION permission.

John

andrew.jones 69458 Old Hand Points: 321 More actions · Answer 2

Thanks John. I guess I can just grant View Definitions as the default. Are there any situations you can think of where you would not want to grant View Definitions on a Table, View or Stored Procedure?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Well, yes - if the users don't need the permission, don't grant it. Follow the principle of least privilege.

John

andrew.jones 69458 Old Hand Points: 321 More actions · Answer 4

Fair enough. I understand the principal of granting the minimum privileges required. What is still not clear to me is what is the risk of granting this View Definitions access? Is there some reason why allowing users the ability to View Definitions would be a bad thing?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Probably the best reason not to grant the permission is that you want to keep your database design confidential.

John

andrew.jones 69458 Old Hand Points: 321 More actions · Answer 6

OK. I think I understand. It is only a bad thing if there is some table, view or SP that I don't want people to see the design.

andrew.jones 69458 Old Hand Points: 321 More actions · Answer 7

Sorry. One more clarification. Does View Definition also allow users to see the columns details of a table if trying to connect from an external source like if they want to use the Get Data From SQL Server Database from MS Excel for example? If I revoke View Definitions does that mean they could not see table details when using the Get Data function from Excel?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

I don't know - I've never used that. But it should be fairly easy for you to test out.

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

andrew.jones 69458 - Tuesday, September 19, 2017 9:10 AM
I understand that it allows users to see the metadata for an object, but it is not clear to me why a user would need this ability.

They don't usually. The people who need that permission are those writing queries against the database (so that they can see what structures they're querying), not the users using an existing application.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kevaburg SSCoach Points: 18131 More actions · Answer 10

A point from my side is that if a person doesn't need VIEW DEFINITION and the reason is that a person shouldn't be able to view the underlying code in views, functions r stored procedures is sensitivity then use the ENCRYPTION clause to prevent them being able to query other objects to obtain the definition.

Here a good link:
https://www.mssqltips.com/sqlservertip/2964/encrypting-and-decrypting-sql-server-stored-procedures-views-and-userdefined-functions/

ScottPletcher SSC Guru Points: 101098 More actions · Answer 11

One of the main reasons to not give VIEW DEFINITION to users is so that they can't see the underlying physical table names. If you let them know the physical table names anyway, then there'd be far less reason not to grant them VIEW DEF.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

kevaburg SSCoach Points: 18131 More actions · Answer 12

ScottPletcher - Monday, September 25, 2017 10:15 AM
One of the main reasons to not give VIEW DEFINITION to users is so that they can't see the underlying physical table names. If you let them know the physical table names anyway, then there'd be far less reason not to grant them VIEW DEF.

Which is why access to data should *only* occur over views and not with direct access to the underlying tables. Using the ENCRYPTION clause strengthens this form of obfuscation because the workaround using dynamic views to view a definition doesn't work.