每个用户在使用单个应用程序时是否应该获得自己的数据库用户/密码？
我想问的是......
用户只能通过我的应用程序访问数据库。应用程序本身允许管理员用户创建/修改/删除用户并授予他们某些权限。所以，我有一个表，其中包含每个用户的登录名和密码。密码。
我仍在开发该应用程序，因此它当前以 root 身份登录，无需密码。显然，我会改变这一点，但最好的做法是什么？我应该有一个数据库用户吗？应用程序使用的密码，还是每次创建（或修改）应用程序的用户时我应该为数据库创建一个单独的用户？后者似乎需要更多工作。
What I am trying to ask is ...
Users will only ever access the database via my application. The application itself allows an admin user to create/modify/delete users and give them certain privileges. So, I have a table which contains each users log-in name & password.
I am still developing the app, so it currently logs in as root with no password. Obviously, I will change that, but what's the best practise? Should I have a single database user & password which the application uses, or should I create a separate user for the databaase each time a user for the application is created (or modified)? The latter seems like more work.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您的应用程序当然应该为每个用户提供单独的用户 ID 和密码。鉴于此，应用程序在与数据库通信时没有理由拥有多个用户 ID。只要应用程序的安全性得到正确实现，拥有多个数据库用户 ID 就不会带来任何好处。
为每个用户提供自己的数据库用户 ID 肯定会是一个巨大的痛苦，因为它可能涉及各种特殊情况和例外。例如，要登录您的应用程序，应用程序必须验证用户的 ID 和密码。如果用户无权访问密码表，它将如何做到这一点？但如果有什么需要防止未经授权的访问，那就是密码表。因此，您必须使用一个用户 ID 进行登录，然后将其删除并提供另一个用户 ID。很可能存在允许给定用户在一个上下文中访问但在另一上下文中不允许访问的其他表。会计部门可能需要查看当年支付的工资总额，但也许他们看不到个别员工的工资。员工可能能够访问有关自己利益的数据，但无法访问其他员工的利益数据。等等。
我能想到的唯一例外是如果您允许对数据库进行某种通用访问。以最极端的情况为例，如果您有一个屏幕，用户可以在其中输入任意 SQL 查询，然后您将执行该查询。在这种情况下，理论上您可以让应用程序分析查询并尝试应用安全规则，但这需要您的应用程序嵌入大量有关 SQL 的知识。在这种情况下，您最好为每个用户提供自己的数据库用户 ID，并将安全规则放入数据库引擎中。
Your APPLICATION should certainly have separate user ids and passwords for each user. Given that, there's no reason for the application to have multiple user ids when it talks to the database. As long as the application's security is implemented correctly, there's no gain from having multiple DB user ids.
Giving each user his own DB user id would surely be a gigantic pain because it would likely involve all sorts of special cases and exceptions. For example, to log in to your application, the application would have to validate the user's id and password. How will it do that if the user doesn't have access to the password table? But if anything needs to be protected from unauthorized access, it's the password table. So you'd have to use one userid to do the login, then take that away and give a different userid. It's likely that there are other tables that a given user might be allowed to access in one context but not in another. The accounting department likely needs to see total amounts paid in salaries for the year, but maybe they can't see individual employee's salaries. Employees may be able to access data about their own benefit, but not that of other employees. Etc.
The only exception I can think of to this would be if you allowed some sort of generic access to the database. To take the most extreme case, if you had a screen where the user can type in an arbitrary SQL query which you would then execute. In that case, you could theoretically have the application analyze the query and attempt to apply security rules, but that would require your application to embed an awful lot of knowledge about SQL. In that case you'd be better to give each user his own DB user id and putting the security rules into the database engine.
简短的回答：在互联网出现之前，是的。互联网之后：没有人这样做，但它仍然是完全可以接受的。
互联网时代的常见做法是将您的应用程序视为用户，并为该应用程序提供登录权限。唯一实际的好处是连接池带来的一些性能提升。感知到但虚幻的好处是安全。
了解安全角度需要认识到所有安全性最终都归结为谁可以从哪些表、行和列中读取和写入。要了解其工作原理，请考虑一个有权操作高度安全的表的用户，以及另一个甚至无法查看该表的用户。权限较低的用户成功管理了试图清除安全表的 SQL 注入攻击，但它失败了，因为数据库阻止该用户访问该表。
结论是，除非连接池很重要，否则没有任何技术原因需要使用单次登录。许多互联网时代的程序员对数据库知之甚少，因此解释如何使用其内置安全性是一场艰苦的战斗，需要克服许多先入为主的错误想法。
Short Answer: Before the internet, yes. After the internet: nobody does it, but it is still perfectly acceptable.
Common practice in the internet age is to consider your application to be the user, and to give that application a login. The only actual benefit is some performance boost from connection pooling. The perceived but illusory benefit is security.
Understanding the security angle requires the realization that all security in the end resolves down to who can read and write from what tables, rows and columns. To see how this works, consider a user who is authorized to manipulate a highly secure table, and another user who cannot even see that table. The less privileged user successfully manages a SQL injection attack attempting to wipe out the secure table, it fails because the Database prevents access by that user to that table.
The takeaway is that there is no technical reason to use a single login except if connection pooling is important. Databases are very poorly understood by many internet age programmers so explaining how to use their built-in security is an uphill battle against many pre-conceived and incorrect ideas.
没有理由为每个用户创建数据库登录名。实际上是您的应用程序与数据库交互，而不是用户。创建额外的登录名只会使您的数据库安全性大大降低。
There's no reason to create a database login for each user. It's really your app that's interacting with the database, not the user. Creating extra logins just makes your database that much less secure.