This is a very large and complex issue.
I think studying MS-SQL security may be helpful to anyone:
http://msdn.microsoft.com/library/de...urity_05bt.asp
In my opinion, Access and MS-SQLwhen used together, (beyond the
trivial), may not provide adequate security and for this reason I no
longer create such applications.
Suppose J Doe has login privileges to the database, and SELECT, INSERT,
UPDATE, and DELETE permissions for a Table. He/she will need these to
edit data using a form bound to that Table. What will prevent J Doe
from using this login and these permissions from another Access
Database, from another application, or more or less directly through
Enterprise Manager?
Ah, you say, use a View and restrict the View to data that is within J
Doe's authority. How exactly will you do that? If there are just John,
Jane and Jezebel Doe and users almost never change it will not be too
onerous. But if there are 200 users at 100 different sites, and seven
of these change per week?
Groups? In the case I suggest there will be 100 groups.
MS-SQL server has a solution. It is called application roles. When one
uses application roles it is your application that has permissions, not
users. So beyond your application, the user can do nothing.
With ADPs, application roles do not work, or work so poorly that they
cannot be used in any complex application. This is because application
roles are not really application roles, but connection roles, and ADPS
use many connections, some of which are documented and clear, and some
of which are hidden and cannot be identified, and, worst, some of which
seem to change according to the phases of the moon, days of the week,
or smog levels.
Can one use application roles with mdbs and odbc connections? I don't
know but I'd love to hear from someone who has done so successfully.
Further I may say that no one I know of agrees with me.
