Preventing MSAccess Connections
-
Does anyone know of a way to prevent people who have query privledges from Accessing SQL Server with MS Access? Has anyone tried Application Roles for this?
Assume for sake of argument that the reasons for giving them direct query access are valid. MS Access can unfortunately become a powerful tool in dangerously unskilled hands.
-
An application role would work, but it means modifying all your other apps. You could put a trigger on some of the main objects to roll back any change where the app name was Access. It's not just Access either. MS Query, tons of freeware tools, even a bit of VBScript. If they have access as an NT user, you can't stop them, just slow them down.
Andy
-
Andy's a big fan of app roles for this reason and he has some great arguments for this.
Personally I confront them and their boss and explain why they should not do this. A nice administrative smack on the hand usually works.
Steve Jones
-
Well, not so much approles as the idea that applications should authenticate rather than users (to the db). SQL logins work as good as approles without the ugly side affects.
Andy
-
Local disconnected and outdated data sets, locking, etc.
-
Yeah!
I have had similar problems with users who have demanded the ability to query and possibly, sometimes with valid reason.
I end up designing a small app that simply allows query/viewing of the data and lock down any risk of data modifcation.
-
quote:
Does anyone know of a way to prevent people who have query privledges from Accessing SQL Server with MS Access? Has anyone tried Application Roles for this?Assume for sake of argument that the reasons for giving them direct query access are valid. MS Access can unfortunately become a powerful tool in dangerously unskilled hands.
The thread does not explain why it might be important to prevent using Access. My organization allows the use of Access for querying the SQL Server 2000 database. Is there a reason this is not a good idea if the user accounts allow read only?
Edited by - asippert on 07/07/2003 04:26:27 AM
-
quote:
quote:
Does anyone know of a way to prevent people who have query privledges from Accessing SQL Server with MS Access? Has anyone tried Application Roles for this?Assume for sake of argument that the reasons for giving them direct query access are valid. MS Access can unfortunately become a powerful tool in dangerously unskilled hands.
The thread does not explain why it might be important to prevent using Access. My organization allows the use of Access for querying the SQL Server 2000 database. Is there a reason this is not a good idea if the user accounts allow read only?
Edited by - asippert on 07/07/2003 04:26:27 AM
For starters, we usually try to anticipate user access to databases from different query tools. There are a variety of ways this can be accomplished, but suffice it to say, we almost never allow complete read-only access. There is still the possibility that a user can execute a stored proc, or view unauthorized data. We generally create certain specialized views that are for user access (usually filtered by SUSER_SNAME against a local Users table) giving them access to only that information that they are privy to. We also take the time to set access properties on each and every object in a database, so that NO table can be viewed directly by a user, and NO stored proc can be executed unless specifically allowed (and here again, the built-in mechanisms prevent altering or accessing of data that is not specifically allowed by built-in filters). It's a lot of work, but most definitely worth the trouble.
Another technique (and far simpler, IMO) is to never use DSNs - find ways to embed the connection string in your code. I have used various approaches, from encrypted files to registry settings (vice hard-coded IPs). We also install multiple instances of MSSQL on the same box, with the default instance named Test. Production instances are always on different ports. Very few users are savvy enough to figure out how to get to the correct instance in this situation.
As an aside, we stopped using integrated security about a year ago, reverting to MSSQL accounts. Reason was that different probing tools by hackers &c. would try to attack NT accounts, and cause lockouts. This would frequently result in outages for users. While MSSQL accounts aren't perfect, they are somewhat less vulnerable (right now) thanks to the fact that it's just easier to attack NT accounts versus MSSQL accounts. We've recently started migrated our campus to AD, and will be testing integrated security once again in this context; however, we're still looking at alternative methods for controlling access to databases that use "mixed" approaches, such as MSSQL accounts (embedded within apps) that impersonate a user or an app (so that the user never knows how to authenticate against a DB).
-
We also have problems with unskilled Access users because of a third-party application that uses DSNs with too much permission. Since we cannot change the situation, I monitor who is using Access and see what they are doing. I schedule this proc to run every 15 min. on a couple of servers. It sends a pop-up message that they are being watched, and it has cut down Access usage somewhat 🙂 Of course, this can be changed to monitor other aps as well.
Linda
CREATE PROCEDURE prAccessUser
AS
DECLARE @HOST varchar(50)
DECLARE @login varchar(50)
DECLARE @Message varchar(1000)
DECLARE @Program_Name varchar(500)
DECLARE whois CURSOR
FOR SELECT DISTINCT
rtrim(hostname) as hostname,
rtrim(loginame) as loginame,
program_name
FROM master.dbo.sysprocesses
WHERE
program_name = 'Microsoft® Access'
OPEN whois
FETCH NEXT FROM whois INTO @HOST, @login, @Program_Name
WHILE @@fetch_status = 0
BEGIN
SELECT @Message = 'xp_cmdshell ' + char(39) + 'net send ' + @Host + ' ' + @login + ', You are being audited for unauthorized use of the DBname data with ' + @Program_name + '.' + char(39)
EXECUTE (@Message)
PRINT (@Message)
SELECT @Message = 'xp_cmdshell ' + char(39) + 'net send NTloginName ' + @login + ' is unauthorized to use ' + @Program_name + ' with DBname databases.' + char(39)
EXECUTE (@Message)
PRINT (@Message)
FETCH NEXT FROM whois INTO @HOST, @login, @Program_Name
END
DEALLOCATE whois
-
Ok, why unfettered Access, access to a SQL Server database for users unless they really know what they are doing.
Two words: Table Lock
It is really, really easy for someone to run a query that is really really stupid. They qualify on just a non-indexed field, use a function, etc against a big table and force a scan and a table lock.
-
How can you use app role to control access from MS Access? Anyone who has done it before or knows how it can be done should please explain.
Thanks.
-
Using an approle from Access is just like any other app, you call the proc (setapprole?) and then you're authorized. But that works to let people in, not keep people out.
Andy
-
what if you make linked tables Hidden ??
and in "Tools" menu, select "Options" then in "Show" tab, UNselect "Hidden objects"
so any user will not see the tables
what is the problem with that method ??
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply