March 9, 2009 at 5:49 am
We have a request from client to hide all system views/tables from users in SQL server 2005.
As user assigned to a specific database role, client do not want the user to see all system tables and INFORMATION_SCHEMA views, so they can have a clear view for only user tables in their schema.
However, whenever they connect using Access via ODBC they get a huge list of sys and INFORMATION_SCHEMA views.
Also when connecting from SQL Management Studio, they are getting same list.
We have taken following steps, but no luck.
1. DENY permissions on "View Definition" at all scope levels but still the users can see all these views using ODBC.
2. Tried denying access by changing permissions to deny in the public role, but still the same.
3. Created one Role including deny permissions to all sys and INFORMATION_SCHEMA views and assigned to user, but same issue.
Please advise is there any way of doing it
March 9, 2009 at 12:30 pm
In access they must have their options turned on to show system objects.
If you go to Tools Menu -> Options -> General Tab..
There are checkboxes for "System Objects" and "Hidden Objects" if you unselect those then these tables will now show up when you try to link to them via ODBC.
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 10, 2009 at 4:06 am
See the post from Piotr Rodak at http://www.sqlservercentral.com/Forums/Topic541937-146-1.aspx
I was going to write something similar, but Piotr has said just about all you need. Work out what users you want to deny access to system tables to and put them into a Windows group. Then within SQL Server, apply 'Deny View and Database', etc to this group using the Permissions tab of SSMS.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 20, 2009 at 9:38 am
I've read the article but still no luck.
Could you elaborate on how to do this using simple examples.
After creating a new role and add the deny view definition to it like this on a database level:
create role [no_schema_view_role]
go
--deny schema access to members of this role
deny view definition to no_schema_view_role
go
The add the role to the user of the database incl the datareader role.
This results in getting al the sys and INFORMATION_SCHEMA tables and dropping the dbo tables.
Please help
November 20, 2009 at 10:08 am
Hi,
that is exactly what i get too.
The way described is not the right way what i need.
If i deny view definition to a role in a db then the users see no user tables but the sys and INFORMATION-SCHEMA-views.
But the users want to see only the user tables and nothing else. I get no solution for this problem.
Can anybody help
Thank's
Heinrich
November 24, 2009 at 4:08 am
When I run the script in Piotr Rodak's post at http://www.sqlservercentral.com/Forums/Topic541937-146-1.aspx it works for me. When 'testu' is not a member of [no_schema_view_role] it can see details of user tables, and when 'testu' is a member of that role it can not see details of the user tables.
Please can you test this exact situation can see if it works for you. It it does then that means the functionality you want can work, you just have to make it work in your live situation.
When your users connect to SQL Server, do they use a Windows or a SQL Server login? SQL Server will always try to connect using a Windows login before it trys to use a SQL Server login, so if they can log in using Windows they will do that regardless of if you also supply a SQL Server login. If they have logged in using Windows authentication, is their Windows group or account a member of [no_schema_view_role]?
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 24, 2009 at 4:21 am
EdVassie,
I've tested the exact situation by running the script of Piotr. This however gives me, just like you said the functionality to enable/disable the viewing of the user tables.
This is however not the functionality i am after!
I'm looking for a way to hide the system and INFORMATION_SCHEMA tables in my odbc connection dialog. I only want to see the user tables. It is simply confusing for the enduser.
My users connect through an ODBC connection by using windows authentication. The windows user is a member of the no_schema_view_role indeed.
Changing the memberschip toggles the visibility of the user tables.
Is this the scenario you talked about, or is there a misunderstaning?
Thanks.
November 24, 2009 at 4:24 am
I misunderstood what you wanted to do. I'll try to think of a way to hide the system tables.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 24, 2009 at 4:25 am
EdVassie,
Ok thanks
November 25, 2009 at 4:47 am
One of the fundamental design criteria behind the ODBC standard is the ability to run ad-hoc queries. In order to do this, the ODBC driver needs to access certain system tables. It is therefore impossible to block access to these tables if you are connecting via ODBC - if you were successful in blocking access to the tables then ODBC would no longer work. See http://msdn.microsoft.com/en-us/library/ms712628(VS.85).aspx for details of the Microsoft ODBC driver.
However, some ODBC driver vendors recognise that allowing the end user to see the system catalog views can cause problems, and have an option in the connection string or a registry option to prevent direct access by user SQL to the system tables. If this facility exists in the Microsoft ODBC driver, you may be able to force this option in your MS-Access connection - check the documentation to find more on this.
If all else fails, you can try DENY access to the sysetm tables to the Public role, but this may cause unwanted side effects that stop things you need from working.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 26, 2009 at 2:27 am
Hello,
as you described, that is exact the same situation for me.
I have users who connect via ODBC and MS Access with an SQL Server Navision DB (SQL Server 2005).
They must connect via sql server authenticated login (because they access from outside our domain).
I have implemented a role and grant special select rights to this role. I have tried to deny on schema sys and INFORMATION_SCHEMA etc. but that doesn't work. The users should see only the user tables, but they also are able to sell all sys-views and information_schema views and i am not able to prevent it.
I have looked in web all around but i found no solution for this topic. Therefore a solution for your problem would be a solution for me too.
Thanks for answer
H.Stenner
March 15, 2012 at 11:53 am
Well it is 2012 March and I am using SQL Server 2008 R2 and having the same problems SQL S 2005 folks 3 yrs ago had. Anyone come across a solution or work around for the part when making a ODBC connection to sql server 2008 r2 dbase that the connection does not show information_schema and sys objects? I see this is still a problem but outside of scripting every object in database 'mssqlsystemresource' as deny(not really what I was looking for). Anyone have anything on this?
March 15, 2012 at 12:55 pm
there's a number of threads here on SSC where the requirement is to remove public permissions in order to comply and lock down a SQL server to DoD standards (google Database Security Checklist for examples).
In that ,we just don't care what breaks.
see this thread for an example:
http://www.sqlservercentral.com/Forums/Topic845604-392-1.aspx#bm845742
and take a look at this link for a more comprehensive script.
http://blogs.technet.com/b/fort_sql/archive/2010/02/04/remove-public-and-guest-permissions.aspx
Lowell
March 15, 2012 at 1:08 pm
I created a role and am applying the deny select and deny exec for stored proc to that role, not changing the public access. I am not trying to foobar up the dbase as it is vital right now but need to be able to create a secure connection from ODBC giving users only a select set of objects(mainly tables) to access. I have been searching for sometime now this week and have not found something that would fit yet. Have you tried what I am suggesting or trying to do in a quick dev environment? I am thrown from the fact that others over the years have not solved or work around this yet or that this is resolved or a resolve posted on this for sql server 2008 yet, seems to be a basic thing that many would come up against. Simply trying to set up a role and user id that a ODBC can get too and only see a select set of tables or objects in parent dbase.
thanks for your time and knowledge,
Brett
March 15, 2012 at 1:10 pm
when you use deny VIEW DEFINITION the user cannot see the objects but user can still query(select * from table/view) object if they know the name(s). Again I am using a newly created role and user as not to affect other dbases and systems with this. so this did not work
thanks
Brett
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply