为MySQL用户提供最低权限 [英] Providing MySQL users with just the minimum privileges

问题描述

对于Web应用程序，在创建将连接到MySQL数据库的用户时，可以选择特权.假定打算由该用户执行的唯一操作是SELECT/INSERT/UPDATE/DELETE，仅提供这些特权似乎很有意义，但是我从未见过有人建议这样做-支持和反对这样做的原因是什么?方法?

For a web application, when creating the user which will connect to the MySQL database, you have the choice of privileges. Assuming that the only actions intended to be done by that user are SELECT/INSERT/UPDATE/DELETE, it seems to make sense to only provide those privileges, however I've never seen that recommended anywhere - what are the reasons for and against this method?

推荐答案

在普通应用程序中，用户可能还需要其他特权，例如:

There are other privileges that a user might need during an ordinary application, for example:

• 创建临时表
• 执行(存储过程)
• 文件(用于SELECT INTO和LOAD DATA)
• 锁定表

还有 minimum 特权可能意味着在某些表上只有SELECT，而在其他表上只有SELECT和UPDATE等.这可能会在应用程序功能得到增强时随时更改.还有一些奇怪的情况，例如需要对从未查询过的表具有SELECT特权，因为它由UPDATE表中的外键引用.因此，跟踪最小特权是一种痛苦.

There's also the possibility that minimal privileges could mean only SELECT on certain tables, and only SELECT and UPDATE on other tables, etc. This is subject to change any time the application's functionality is enhanced. And there are weird cases, like the need to have SELECT privilege on a table you never query, because it's referenced by the foreign keys in a table you UPDATE. So tracking minimal privileges is a royal pain.

您试图通过使用SQL特权来限制什么?您是编写所有代码的人，因此不需要细粒度地管理SQL特权.坦白说，如果您的用户能够上载并运行未经审核的SQL语句，那么您会遇到更大的问题:

What are you trying to restrict by using SQL privileges? You're the one who wrote all the code, so managing SQL privileges at a fine granularity shouldn't be necessary. Frankly, if your users are able to upload and run SQL statements that you haven't vetted, you have bigger problems:

SELECT * FROM mytable, mytable, mytable, mytable, mytable ORDER BY 1;

您要管理的实际任务不在数据库级别，而是在应用程序业务级别.例如，CMS具有创建页面，编辑页面，管理评论等操作.这些任务的级别比SQL特权高.您可以使用SQL角色(特权组)来模仿它们，但是SQL角色不受广泛支持.

The real tasks you want to govern aren't at the database level, they're at the application business level. For example, a CMS has operations like create a page, edit a page, administer comments, etc. These tasks are higher-level than SQL privileges. You could mimic them with SQL roles (which are groups of privileges), but SQL roles aren't widely supported.

我不知道有人将其应用程序用户映射到不同的MySQL用户.他们是您在应用程序中进行身份验证的用户，在应用程序连接到数据库后 (用户只是数据库中的数据行).

I don't know anyone who maps their application users to distinct MySQL users. They're users you authenticate in your application, after the app has connected to the database (the users are just rows of data in the database).

因此，最好让您的Web应用程序使用具有完全特权的单个MySQL用户.

So you're probably better off having your web app use a single MySQL user with full privileges.

这篇关于为MySQL用户提供最低权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！