October 24, 2008 at 6:15 am
Good afternoon,
I have the following issue: I created an ODBC connection to a Server on our network. The ODBC connection has been set up to use Windows Authentication, so the user's domain credentials are used to authenticate to the server. Mixed Mode Authentication is not enabled, so all users HAVE to connect to the server using their domain credentials. The user has been granted access to a single database, and that user is currently part of a Schema called "Auditing".
The user is not part of any other schema, and also hasn't been granted "db_datareader" permissions on the database, so he can only see the tables that are necessary to his work.
When the user connects via SSMS (2005), he can only see the tables in the database that are part of the schema he belongs to, which is exactly what I want.
The issue comes in when the user decides to connect to the Server via the ODBC connection as described above. When the user selects the connection from “MACHINE DATA SOURCES” via MS Access 2007, he sees ALL of the system tables such as "sys." and "INFORMATION_SCHEMA". This is both a security risk and an inconvenience.
Is there a way to only display the tables that the user has access to such as what is happening when connecting via SSMS?
Your help would be greatly appreciated.
Kind Regards,
Colin Macguire
October 24, 2008 at 9:42 am
Those are all available to the user in SSMS as well, they are just found in the Views section and Access is showing them as tables. Those are available to every user unless you explicitly DENY access mainly because SQL Server uses them behind the scenes to determine permissions, relationships, etc...
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 24, 2008 at 11:06 am
Colin Macguire (10/24/2008)
The issue comes in when the user decides to connect to the Server via the ODBC connection as described above. When the user selects the connection from “MACHINE DATA SOURCES” via MS Access 2007, he sees ALL of the system tables such as "sys." and "INFORMATION_SCHEMA". This is both a security risk and an inconvenience.
I doubt that it is nearly as much of a security risk as you are assuming. As Jack mentioned, normally everyone can see these. The reason being that it is often important to be able to know the defintion of a table before you query it (etc.). However, most of the significant SYS Views are also row-filtered, to only show the definitions of objects that the requesting user already has some kind of access to.
So they really should not be able to see much that they are not already supposed to know.
I can also tell you that there are numerous tools (like Crystal Reports) that will break if they cannot do this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 26, 2008 at 10:52 pm
Hi Jack / Barry,
Thanks for the replies. OK, so there's no security risk, glad to hear that. From the inconvenience point of view: If I understand your replies correctly, users will always be able to view these tables / views, and ONLY the tables that they have been given SELECT permissions on, or will ALL tables and views be visible to all users?
The main reason why I initially wanted to hide these tables was because there are over 100 tables in this database (and growing), and if these tables are visible to all users it becomes an issue to find the correct table.
Regards,
Colin
October 27, 2008 at 5:38 am
I'm pretty sure that users will only be able to see tables which they have access to, but I'm not sure. I typically do not grant direct table access to users anyway. I only allow access to views and stored procedures.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 27, 2008 at 5:46 am
Hi Jack,
I'll test it out and let you know.
Kind Regards,
Colin.
October 29, 2008 at 2:13 am
Hi Jack,
Looks like the user can only see the schemas that he / she has been allowed access to. Seems like the users will have to get used to looking past all of those "sys." and INFORMATION tables to find what they're looking for.
Regards,
Colin
October 29, 2008 at 6:25 am
Most tools (like management studio) either filter them out or shunt them off in a "system" folder.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply