让 MySQL 用户创建数据库，但只允许访问他们自己的数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2950054/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Let MySQL users create databases, but allow access to only their own databases
提问by eikes
I want to have multiplea MySQL users to be able to issue commands like
我希望有多个MySQL 用户能够发出类似的命令
CREATE DATABASE dbTest;
But I also want each of these users to be able to see and access only their owndatabases.
但我也希望这些用户中的每一个都只能查看和访问他们自己的数据库。
All I could find was how to either create the databases by a DBA and grant the privileges on this database to the specific user:
我所能找到的只是如何由 DBA 创建数据库并将此数据库的权限授予特定用户：
GRANT ALL PRIVILEGES ON dbTest.* TO 'user';
or grant privileges on all databases to a user:
或将所有数据库的权限授予用户：
GRANT ALL PRIVILEGES ON *.* TO 'user';
But neither is what I want, because it needs to scale and be secure.
但两者都不是我想要的，因为它需要扩展和安全。
回答by rael_kid
You can use
您可以使用
GRANT ALL PRIVILEGES ON `testuser\_%` . * TO 'testuser'@'%';
to grant the user testuserprivileges on all databases with names beginning with testuser_.
授予用户testuser对所有名称以 开头的数据库的权限testuser_。
This allows the testuser to create databases limited to names starting with testuser_
这允许 testuser 创建仅限于以 testuser_ 开头的名称的数据库
回答by Xycris
You can use
您可以使用
GRANT ALL PRIVILEGES ON `testuser_%` . * TO 'testuser'@'%';
to grant the user testuserprivileges on all databases with names beginning with testuser_.
授予用户testuser对所有名称以 开头的数据库的权限testuser_。
EDIT: I'm not sure if this user is now also allowed to create databases.
编辑：我不确定现在是否还允许此用户创建数据库。
Yes, this allows the testuserto create databases limited to names starting with testuser_
是的，这允许testuser创建仅限于以开头的名称的数据库testuser_
回答by Janek Bogucki
Create a stored procedure that is defined by the admin user and invokes with the admin user privileges by using SQL SECURITY DEFINER. In the stored procedure,
创建一个由 admin 用户定义并使用 SQL SECURITY DEFINER 以 admin 用户权限调用的存储过程。在存储过程中，
- Create the database.
- Set the privileges on the database so only the current user has access.
- Execute FLUSH PRIVILEGES to reload the privileges from the grant tables.
- 创建数据库。
- 设置对数据库的权限，以便只有当前用户可以访问。
- 执行 FLUSH PRIVILEGES 从授权表重新加载权限。
Use USER()to get the current user login details.
使用USER()获取当前用户登录详细信息。
回答by Ada Lovelace
It is impossible to do this using permissions only.
仅使用权限是不可能做到这一点的。
The workaround as suggested in another answer:
GRANT ALL PRIVILEGES ONtestuser_%. * TO 'testuser'@'%';has the problem that the users must then be very careful in naming their databases.
另一个答案中建议的解决方法：
GRANT ALL PRIVILEGES ONtestuser_%. * TO 'testuser'@'%';有一个问题，即用户在命名他们的数据库时必须非常小心。
For example if user aaacreates database bbb_xyz, it can then be accessed exclusively by user bbbbut not by user aaa.
例如，如果用户aaa创建数据库bbb_xyz，那么它可以被用户独占访问，bbb但不能被用户访问aaa。
