具有 SQL Server 后端的 MS Access 应用程序的安全模型（部署）
我们有一个应用程序，由 MS Access 前端（2007，mdb 格式）、一些 .net 库和 SQL Server (2008) 后端组成。我正在开发一个安装程序，它会自动安装 MS Access Runtime、我们的应用程序、我们的库、SQL Server Express 并配置所有内容。
显然，MS Access 应用程序和库（在正常的非管理员用户上下文中运行）需要访问 SQL Server 数据库。 授予应用程序访问权限的最佳方式是什么？
这是我想到的。不幸的是，所有这些似乎都有缺点：
SQL Server Compact Edition：不支持视图。
应用程序角色：这似乎是最佳实践。但是，它需要在访问数据库之前执行存储过程（我无法在连接字符串中传递应用程序凭据）。因此，我无法使用它将 SQL Server 表附加为 Access MDB 中的链接表，这是我们的 Access 应用程序的要求。
SQL Server 用户实例 ：引自 MSDN：“此功能将在 Microsoft SQL Server 的未来版本中删除。避免在新的开发工作中使用此功能...”
SQL 身份验证：Microsoft 表示：“如果可能，请使用 Windows 身份验证。”< /p>
使用 Windows 身份验证并授予 BUILTIN\USERS 完全访问权限：这是迄今为止最简单的解决方案，但不知何故它这样做“似乎是错误的”...
该应用程序面向非技术受众，因此要求用户配置权限不是一个选项。
编辑：一些澄清：它是一个“本地”应用程序，即 SQL Server 与应用程序位于同一台计算机上；从网络访问 SQL Server 既没有必要，也不需要。该软件（用于管理库存、发票等的常规业务应用程序）将可供免费下载，因此它应该在各种环境（域/非域、不同操作系统等）和 IT 环境中运行安装它不需要知识 - 除了通常的“单击 setup.exe，确认 UAC 提示，确认安装目录等”。我预计最常见的场景是“Windows XP，本地管理员用户”和“Windows Vista/7，启用 UAC 的本地管理员用户”。由于我们希望遵循良好实践，因此在后一种情况下运行应用程序不应要求“以管理员身份运行”。
We have an application, consisting of an MS Access frontend (2007, mdb format), a few .net libraries and an SQL Server (2008) backend. I am working on an installer, which automatically installs the MS Access Runtime, our application, our libraries, SQL Server Express and configures everything.
Clearly, the MS Access application and the libraries (running in a normal, non-admin user context) need access to the SQL Server database. What is the best way to grant access to the application?
This is what I came up with. Unfortunately, all of these seem to have drawbacks:
SQL Server Compact Edition: Does not support views.
Application Roles: This seems to be best practice. However, it requires executing a stored procedure before accessing the database (I cannot pass the app credentials in the connection string). Thus, I cannot use this to attach the SQL Server tables as a linked tables in the Access MDB, which is a requirement of our Access application.
SQL Server User Instance: To quote from MSDN: "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work..."
SQL Authentication: Microsoft says: "When possible, use Windows Authentication."
Using Windows authentication and granting BUILTIN\USERS full access: This is by far the easiest solution, but somehow it "seems wrong" to do that...
The application is targeted at a non-technical audience, so asking the user to configure permissions is not an option.
EDIT: Some clarification: It's a "local" application, i.e., the SQL Server is located on the same machine as the application; SQL Server access from the network is neither necessary nor desired. The software (a regular business application for managing stocks, invoices, etc.) will be available to download for free, so it should run in a variety of environments (domain/non-domain, different operating systems, etc.), and IT knowledge should not be required to install it -- apart from the usual "click on setup.exe, confirm UAC prompt, acknowledge the installation directory, etc.". I expect the most common scenarios to be "Windows XP, local admin user" and "Windows Vista/7, local admin user with UAC enabled". Since we want to follow good practices, running the application should not require "Run as Administrator" in the latter case.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
@Heinzi 写道：
这里通常的方法是添加自定义用户组（例如“db-users”）并将用户放入该组中。这样您就可以准确控制允许访问的人员。
@Heinzi write:
The usual approach here is to add a custom user group (e.g., "db-users") and put the users in that group. That way you can control exactly who is allowed access.
怎么样：
How about:
如果听起来你只知道了冰山一角。当涉及到销售和部署访问 SQL 应用程序时。
我采取了不同的路线。我有虚拟计算机作为独立工作站，域服务器和工作站都是虚拟的。
我写了一个脚本，它们是 VBA 和 VBScript 的组合。
问
DB和App是运行在单台电脑上还是不同的电脑上。
如果不同的计算机，数据库所在的计算机的名称是什么。
DB 和 App 是否位于工作组、家庭组或域环境中
DB计算机是否已安装SQL Express或更高版本
应用程序计算机是否已安装 Access 或 Access Runtime。
如果是哪个版本。
所有用户还是只有有限的用户有权访问。
如果受到限制，有权访问数据的用户的用户组名称是什么。
该群组是否已存在
如果否，列出应添加到组中的用户的名称
还有有关管理员用户和组的问题
该脚本启动虚拟机并执行一系列步骤来代表 MDB 和 SQL DB 进行部署。然后为服务器安装创建 MSI，其中包含设置环境的自定义脚本。最后将 MDB 打包到一个漂亮的 MSI 中。
此后，我增强了该流程，以便在服务器安装开始时就可以回答一些问题。这意味着可以根据先前提出的问题从工作站或域中的列表中选择用户组和用户。
如果用户是应用程序用户是工作站或域的管理组的成员。他们有额外的菜单选项。这允许他们在工作站或域的数据库用户组中添加或删除成员。我发现这很有帮助。
我现在正进入下一阶段，考虑将我的评估应用程序托管为 SasS（软件即服务）（租赁）。因此，该应用程序可以在任何 HTML5 浏览器、Windows 或 Mac 作为虚拟桌面或 Android 和 Apple 设备中使用。话虽如此，Access 在移动设备上有点难看。
当我启动并运行时，我会将这个平台提供给其他人。
If sound like you have only got the tie of the iceberg. When it comes to selling and deploying access SQL applications.
I have take a different route. I have virtual computers as standalone workstation and domain server and workstation all virtual.
I have write a scripts they are a combination of VBA and VBScript.
Ask
Is the DB and App to run on single computer or different computers.
If different computer what is the name of the computer the DB is located on.
Is the DB and App to in a workgroup, homegroup or domain environment
Is the DB computer already have SQL Express or above
Is the App computer already have Access or Access Runtime installed.
If yes which version.
Will all or only limited users have access.
If limited what is the user group name of user to be have access to the data.
Does this group already exist
If No List the Name of the Users that Should Be Added to the Group
Also questions about the Admin Users and Group
The script start the virtual machines and goes through a series of steps to rep the MDB and SQL DB for deployment. Then creates an MSI for the Server Install with include a custom script that sets up the environment. Finally packages MDB in a nice MSI.
I have since enhanced the process to allow some questions to be answered at the beginning of the server installation. This means the user groups and users can be selected from the lists in the workstation or domain depending on prior questions asked.
If user the app user is a member of the Admin Group of the Workstation or Domain. They get extra menu options. That allow them to add or remove members from the DB user group for the workstation or domain. This I find is helpful.
I am now moving to the next stage and looking at hosting my assess app as an SasS (Software as a Service) (Rental). So the app can be use in any HTML5 Browser, Windows or Mac as Virtual Desktop or Android and Apple device. Having said that Access is a bit ugly on mobile devices.
When I am up and running I will make the platform available to others.