为什么不使用内置的MySQL用户和网站的权限？ [英] Why not use the built-in MySQL users and permissions for a website?

问题描述

我搜索了很多关于加密，存储密码，设计安全的PHP脚本等主题。

似乎有几个共同的主题： / p>

• 不要尝试编写自己的加密脚本，使用现有库（例如PHPass）。


• 不要为每个用户创建MySQL数据库，创建一个大数据库，并编写一个好的PHP脚本来管理用户，密码及其数据。

现在，是我，还是这两个似乎没有冲突？

让我们举个例子。我想创建一个网站，用户可以创建一个帐户。他们可以添加敏感信息（例如，他们的家庭地址），然后他们可以在网站上查看或编辑。此敏感信息不应公开，不能供网站的任何其他用户使用。它应该是只为用户的眼睛。

有一个单一的管理员谁应该能够读取敏感信息（例如发送一封信给每个客户），但当然不能读取密码。如果认为必要，这可以在本地完成，即。不允许通过互联网进行管理访问。

可以采取各种预防措施以使用最新版本，最佳做法等。网站可以从专用机器运行安全，不与任何其他任务共享。

那么，为什么不为网站的每个用户创建MySQL用户？为什么要自己的PHP脚本创建用户，然后将这个信息存储在数据库中的表中，MySQL已经提供了这个功能？实际原因是什么？我们认为使用PHPass（或另一种）提供比MySQL内置的更安全的密码存储？

MySQL数据库中的存储被认为是不安全的。如果你有本地访问我的机器，但没有管理员或root密码或其他用户/通过组合我的MySQL数据库，那么你仍然能够获得所有的数据吗？

如果为网站的每个用户创建MySQL用户被认为是可接受的，那么为什么不为每个用户创建一个新的数据库或表，并在MySQL中设置权限，因此每个用户只能访问他们的数据，其他？当然，具有本地访问权限和root密码的管理员可以读取所有信息。

因此，似乎通过设计创建用户和分配权限的功能已经内置到MySQL ，为什么要写一个PHP脚本来做同样的事情？

///

如果这是工作，那么需要一个MySQL用户的PHP脚本来创建新用户。这将有它的用户/传递存储在纯文本，没有办法绕过这？

现在，理想情况下，这个MySQL用户将无法读/写或做任何事情

这是否有可能？

解决方案

问题是MySQL访问控制不够细致：它只能在数据库和表级别操作。如果您赋予用户 UPDATE 权限，他们可以更新该表中的任何行，包括包含应用程序其他用户信息的行。

你可以给每个用户自己的数据库，但这使得编写通用应用程序非常困难。如果网站的管理员想要搜索用户，他们必须编写一个搜索数千个表的查询。在这样的环境中写入连接将是完全不可行的。

或考虑用于在用户之间进行通信的应用程序。如果用户A想向用户B发送消息，他们将需要在用户B可以读取的表中写入内容。但是如果每个用户只能访问自己的表，没有这样的表。你要做什么，为每对用户创建一个数据库？那么，多方通信，如论坛系统呢？

I have searched quite a bit on the subjects of encryption, storing passwords, designing secure PHP scripts etc.

There seems to be a couple of common themes:

• "Don't try and write your own encryptions scripts, use an existing library (eg. PHPass)."
• "Don't create MySQL databases for each user, make one big database and write a good PHP script to manage your users, passwords and their data."

Now, is it me, or do these two not seem a bit conflicting?

Let's take this example. I want to create a website where users can create an account. They can add sensitive info (eg. their home address) which they can then view on the website or edit. This sensitive info should not be public and not be available to any other user of the website. It should be "for the user's eyes only".

There is one single admin who should be able to read the sensitive info (for example to send a letter out to every customer) but of course not be able to read passwords. If deemed necessary this could be done locally, ie. without allowing admin access over the internet.

Every precaution could be taken to use the latest versions, best practices etc. The website could run from a dedicated machine which can be physically safe and not shared with any other task.

So, why not create MySQL users for each user of the site? Why roll your own PHP script to create users and then store this info in a table in a database when MySQL already offers this functionality? What are the actual reasons? Are we thinking that using PHPass (or an alternative) provides "safer" password storage than that built into MySQL?

Is storage inside a MySQL database considered "unsafe". If you had local access to my machine but no admin or root passwords or other user/pass combos for my MySQL database then you're still going to be able to get all the data anyway?

If creating a MySQL user for each user of the website is considered "acceptable", then why not create a new database or table for each user and set the permissions in MySQL so each user can access only their data and nothing else? Of course the admin with local access and root password can read all the info.

So it seems that by design the functionality of creating users and assigning permissions is already built into MySQL, why write a PHP script to do the same thing?

///

A follow up question.

If this is to work then there would need to be a MySQL user for the PHP script to use to create new users. This would have it's user/pass stored in plaintext, there's no way round this?

Now, ideally this MySQL user would not be able to read/write or do anything to any existing database, but would be able to create a new user, create a new database/table and assign permissions so this new user.

Is this possible?

解决方案

The problem is that MySQL access control is not fine-grained enough: it only operates at the database and table level. If you give a user UPDATE permission to a table, they can update any row in that table, including rows that contain information about other users of the application.

You could potentially give every user their own database, but that makes writing general purpose applications very difficult. If the administrator of the site wants to search users, they have to write a query that searches thousands of tables. Writing joins in such an environment will be totally unworkable.

Or consider applications that are used to communicate between users. If user A wants to send a message to user B, they'll need to write something into a table that user B can read. But if each user only has access to their own tables, there's no such table. What are you going to do, create a database for each pair of users? And then what about multi-way communications, like a forum system?

这篇关于为什么不使用内置的MySQL用户和网站的权限？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！