\nI've been lying pretty low lately, doing research on some topics in a slightly different space (but at least obliquely SQL Server 2005 related). Came across a list of interesting student comments today, and I was back trying out (picking at?) one of my favorite spare-time topics to answer a question about. SQL Server 2005 metadata. The question was whether or not the INFORMATION_SCHEMA views were security-sensitive, and…of course they are. There are now (at least) 4 different ways to list the user tables in a database (all subject to security) and they are:\n<\/p>\n
\nselect * from sysobjects where type = 'U'
\nselect * from sys.objects where type = 'U'
\nselect * from sys.tables — I always wanted a systables in earlier versions
\nselect * from INFORMATION_SCHEMA.tables where TABLE_TYPE = 'BASE_TABLE'\n<\/p>\n
\nThe Dec CTP closed the last loophole; using system procedures like sp_help is now permission sensitive.\n<\/p>\n
\nBut what if you WANT a user to be able to list all of the tables in the database? The “smallest“ permission you can give a user is VIEW DEFINITION privilege on a specific object. As an example, if I had a user named 'fred' that I wanted to 'see' the authors table, I would give:\n<\/p>\n
\ngrant view definition on object::authors to 'fred'\n<\/p>\n
\nA specific resource is called a securable. Securables exist in a hierarchy. So although there's no straightforward way to give VIEW DEFINITION on all of the tables (that I can see) in a specific schema or database in a single statement, I could give a user access to all of the objects (Table, View, Function, Procedure, Queue, Type, Rule, Default, Synonym, Aggregate, XML Schema Collection) in a particular schema with this one statement.\n<\/p>\n
\ngrant view definition on schema::dbo to 'fred' — all in dbo schema
\ngrant view definition on database::dbo to 'fred' — all in the database\n<\/p>\n
\nNote the VIEW DEFINITION is the “smallest“ permission; it gives a user permission to see that an object exists without being able to see the data. For fred's case, “select * from authors“ would still fail. Giving SELECT also gives view definition privilege, so if you can SELECT against an object, it also shows up in your metadata list. Makes perfect sense. You can tell I'm liking the new metadata views…and the new “all permissions grantable“ concept. Wait until the first user calls up and asks “where did all of the tables in my dropdown list go?“.<\/p>\n","protected":false},"excerpt":{"rendered":"
I've been lying pretty low lately, doing research on some topics in a slightly different space (but at least obliquely SQL Server 2005 related). Came across a list of interesting student comments today, and I was back trying out (picking at?) one of my favorite spare-time topics to answer a question about. SQL Server 2005 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,28],"tags":[],"class_list":["post-977","post","type-post","status-publish","format-standard","hentry","category-security","category-sql-server-2005"],"yoast_head":"\n