How to make linked tables to SQL Server views read only?

  • Thread starter Thread starter Jeffrey Ganping Chen
  • Start date Start date
J

Jeffrey Ganping Chen

Hi,

I have a MS Access as the front end, and create several linked table to SQL
Server views through ODBC. I thought by using the views on SQL Server, the
Access front end will have read only rights (that's what I want). However,
the Access front end has all the rights (insert, update, delete, select)
through the views.

How can I make the linked tables read only?

Thanks for your help.
 
hi Jeffrey,
I have a MS Access as the front end, and create several linked table to SQL
Server views through ODBC. I thought by using the views on SQL Server, the
Access front end will have read only rights (that's what I want). However,
the Access front end has all the rights (insert, update, delete, select)
through the views.
Click to expand...
Execute the following in the Query Analyzer for each view:

USE YourDatabase
REVOKE DELETE, INSERT, UPDATE ON YourView FROM YourUser

mfG
--> stefan <--
 
Jeffrey said:
Hi,

I have a MS Access as the front end, and create several linked table
to SQL Server views through ODBC. I thought by using the views on SQL
Server, the Access front end will have read only rights (that's what
I want). However, the Access front end has all the rights (insert,
update, delete, select) through the views.

How can I make the linked tables read only?

Thanks for your help.
Click to expand...

Easiest way would be to replace the links with Passthrough queries (always
read only).
 
Using a pass-through query will actually usually be faster than using a
linked table. That's because pass-through queries execute on the server, and
only return the appropriate rows.
 
That's what I always thought and have experienced with DB2 Databases but it
hasn't held true for me with a sql server DB I use as a datasource for much
of what I'm doing these days. Probably an issue with that particular database.

Thanks!
 
hi,
That's what I always thought and have experienced with DB2 Databases but it
hasn't held true for me with a sql server DB I use as a datasource for much
of what I'm doing these days. Probably an issue with that particular database.
Click to expand...
Depends of the caching mechanisms of the RDBMS. A passthrough query is
never precompiled, so it is probably using a bad execution plan.


mfG
--> stefan <--
 
Use a view instead unless every sql statement you submit is going to be
different. That would be bad for performance reasons anyway. Using a
view, you can also grant/revoke the applicable permissions for the user,
since that's what the original question was about. If you go with a
passthrough query and there are still full permissions in the server
then anybody (well, maybe not *anybody*) could use that same connection
to insert/update/delete.

So my vote is to make it a view unless you have a compelling reason not to!

Good luck,
Mattias

From: Stefan Hoffmann, on 3/24/2006 8:12 AM:
 
Back
Top