部署数据库架构更改的权限
我有一个连接到 Microsoft SQL Server 2000 的 Python 应用程序。该应用程序在启动时检查更新并自动应用它们。很快，它还需要处理数据库架构更改。根据我的研究，似乎为当前数据库创建一个基线脚本，然后为每个模式更改创建一个新脚本是可行的方法。这样，任何版本的数据库都可以更新到最新版本。
我的问题是如何管理这些更新的权限？目前大约有 50 人使用我的应用程序，其中大多数具有只读访问权限。理想情况下，我希望任何用户都能够执行必要的更改，例如创建或更改表，以便第一个接收新更新的人将应用新的架构更改。如果没有发生这种情况，那么他/她可能根本无法使用该应用程序，直到具有适当权限的人更新数据库。
如果每个用户都可以更新架构，我可以看到出现问题。什么会阻止他们登录 SQL Server Management Studio 并导致删除表等问题？
目前，该应用程序仅部署在一个位置，因此我可以轻松手动管理架构更改。但我们确实有计划部署到更多地区，我希望所有这些都能自动处理。
I've got a Python application that connected to Microsoft SQL Server 2000. The application checks for updates on startup and automatically applies them. Soon, it will need to handle database schema changes as well. Based on my research, it seems that creating a baseline script of my current database and then creating a new script for each schema change is the way to go. That way, any version of database can be updated to the newest version.
My question is how do I manage the permissions for these updates? Right now there's about 50 people using my application, most of which have read-only access. Ideally, I'd like any user to be able to perform the necessary changes such as creating or altering tables so that the first person to receive the new update will apply the new schema changes. If that doesn't happen, then he/she might not be able to use the application at all until someone with appropriate permissions updates the database.
I can see a problem occurring if every user can update the schema. What would prevent them from logging into the SQL Server Management Studio and causing issues like dropping tables, etc.?
Right now, this application is only deployed in once place, so it's easy for me to manage schema changes manually. But we do have plans to deploy to more areas and I'd for all this to be handled automatically.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您想要限制用户的管理权限，但同时希望他们执行管理活动...有点“先有鸡还是先有蛋”的问题:-)
选项 1：使用 webapp
将您的程序转换为作为 Web 应用程序运行。
然而，有点激进，维护要简单得多。您的用户不再需要安装Python、集中升级和共享数据库连接池。
这解决了您的数据库身份验证问题。用户向 Web 应用程序（而不是数据库）验证自己的身份。
我使用 liquibase 来管理我的数据库模式。它有一个 servlet 侦听器，可以在部署我的应用程序的新版本时自动升级数据库。 （Liquibase 还有一个命令行界面，供 Python 等替代技术使用）。
选项 2：加密管理员密码
您可以对管理员密码进行加密，并将其作为可从公司 Web 服务器检索的文本文件提供，从而使任何用户应用程序都可以在启动时下载它。
这个解决方案存在一个安全问题......为了解密管理员密码，需要在你的Python应用程序中内置一个共享秘密......这是默默无闻的安全性。
You want to restrict users admin privileges, but at the same time you want them to perform admin activities... Bit of a "chicken and egg" problem :-)
Option 1: Use webapp
Convert your program to run as a web application.
Bit drastic, however, much simpler maintanence. Your users no longer need to install Python, centralized upgrades and a shared connection pool to the database.
This solves your database authentication problem. Users authenticate themselves to the web application, not the database.
I use liquibase to manage my database schemas. It has a servlet listener which can automatically upgrade the database when a new version of my app is deployed. (Liquibase also has a command-line interface for use by alternative technologies like Python).
Option 2: encrypt admin password
You encrypt the admin password and make it available as a text file retrievable from a corporate web server, enabling any users application to download it at startup.
There is a security issue with this solution.... In order to decrypt the admin password, a shared secret needs to be built into your python application.... This is security by obscurity.