MySQL 授予**所有**数据库权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5016505/
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
Grant **all** privileges on database
提问by marioosh
I've created database, for example 'mydb'.
我已经创建了数据库，例如“mydb”。
CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_bin;
CREATE USER 'myuser'@'%' IDENTIFIED BY PASSWORD '*HASH';
GRANT ALL ON mydb.* TO 'myuser'@'%';
GRANT ALL ON mydb TO 'myuser'@'%';
GRANT CREATE ON mydb TO 'myuser'@'%';
FLUSH PRIVILEGES;
Now i can login to database from everywhere, but can't create tables.
现在我可以从任何地方登录到数据库，但不能创建表。
How to grant all privileges on that database and (in the future) tables. I can't create tables in 'mydb' database. I always get:
如何授予对该数据库和（将来）表的所有权限。我无法在“mydb”数据库中创建表。我总是得到：
CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
ERROR 1142 (42000): CREATE command denied to user 'myuser'@'...' for table 't'
回答by diagonalbatman
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%' WITH GRANT OPTION;
This is how I create my "Super User" privileges (although I would normally specify a host).
这就是我创建“超级用户”权限的方式（尽管我通常会指定一个主机）。
IMPORTANT NOTE
重要的提示
While this answer can solve the problem of access, WITH GRANT OPTIONcreates a MySQL user that can edit the permissions of other users.
虽然这个答案可以解决访问问题，但WITH GRANT OPTION创建了一个可以编辑其他用户权限的MySQL用户。
The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess.
GRANT OPTION 权限使您能够将您自己拥有的权限授予其他用户或从其他用户中删除。
For security reasons, you should not use this type of user account for any process that the public will have access to (i.e. a website). It is recommended that you create a user with only database privilegesfor that kind of use.
出于安全原因，您不应将此类用户帐户用于公众有权访问的任何流程（即网站）。建议您创建一个仅具有此类用途的数据库权限的用户。
回答by akostadinov
This is old question but I don't think the accepted answer is safe. It's good for creating a super user but not good if you want to grant privileges on a single database.
这是一个老问题，但我认为接受的答案并不安全。这对创建超级用户很有用，但如果您想授予对单个数据库的权限，则不太好。
grant all privileges on mydb.* to myuser@'%' identified by 'mypasswd';
grant all privileges on mydb.* to myuser@localhost identified by 'mypasswd';
%seems to not cover socket communications, that the localhostis for. WITH GRANT OPTIONis only good for the super user, otherwise it is usually a security risk.
%似乎不包括套接字通信，那localhost是为了。WITH GRANT OPTION只对超级用户有益，否则通常存在安全隐患。
Updatefor MySQL 5.7+ seems like this warns about:
MySQL 5.7+ 的更新似乎是这样警告的：
Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation.
不推荐使用 GRANT 语句来修改现有用户的权限以外的属性，并将在未来版本中删除。使用 ALTER USER 语句进行此操作。
So setting password should be with separate commands. Thanks to comment from @scary-wombat.
所以设置密码应该使用单独的命令。感谢@scary-wombat 的评论。
ALTER USER 'myuser'@'%' IDENTIFIED BY 'mypassword';
ALTER USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
Hope this helps.
希望这可以帮助。
回答by akostadinov
This will be helpful for some people:
这对某些人会有所帮助：
From MySQL command line:
从 MySQL 命令行：
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
Sadly, at this point newuser has no permissions to do anything with the databases. In fact, if newuser even tries to login (with the password, password), they will not be able to reach the MySQL shell.
遗憾的是，此时 newuser 没有权限对数据库执行任何操作。事实上，如果 newuser 甚至尝试登录（使用密码，密码），他们将无法访问 MySQL shell。
Therefore, the first thing to do is to provide the user with access to the information they will need.
因此，首先要做的是让用户能够访问他们需要的信息。
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
The asterisks in this command refer to the database and table (respectively) that they can access—this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables.
该命令中的星号分别表示它们可以访问的数据库和表——该特定命令允许用户读取、编辑、执行和执行跨所有数据库和表的所有任务。
Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.
确定要为新用户设置的权限后，请务必重新加载所有权限。
FLUSH PRIVILEGES;
Your changes will now be in effect.
您的更改现在将生效。
For more information: http://dev.mysql.com/doc/refman/5.6/en/grant.html
更多信息：http: //dev.mysql.com/doc/refman/5.6/en/grant.html
If you are not comfortable with the command line then you can use a client like MySQL workbench, Navicator SQLyog
如果你对命令行不满意，那么你可以使用像MySQL workbench、Navicat或SQLyog这样的客户端
回答by Bhavnesh
1. Create the database
1.创建数据库
CREATE DATABASE db_name;
2. Create the username for the database db_name
2.为数据库db_name创建用户名
GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'localhost' IDENTIFIED BY 'password';
3. Use the database
3. 使用数据库
USE db_name;
4. Finally you are in database db_name and then execute the commands like create , select and insert operations.
4. 最后你在数据库 db_name 中，然后执行像 create ， select 和 insert 操作的命令。
回答by s6712
This SQL grants on all databases but just basic privileges. They're enough for Drupal or Wordpress and as a nicety, allows one developer account for local projects.
此 SQL 授予所有数据库的权限，但仅授予基本权限。它们对于 Drupal 或 Wordpress 来说已经足够了，而且还允许一个开发人员帐户用于本地项目。
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
INDEX, ALTER, CREATE TEMPORARY TABLES
ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
回答by Dutch Glory
GRANT ALL PRIVILEGES ON mydb.* TO myuser@localhost IDENTIFIED BY 'mypasswd';
Works for privileges on schema :)
适用于架构特权:)
Optional: after mypasswdyou can add WITH GRANT OPTION
可选：之后mypasswd可以添加WITH GRANT OPTION
回答by GnanaPrakash
I could able to make it work only by adding GRANT OPTION, without that always receive permission denied error
我只能通过添加来使其工作GRANT OPTION，而不会总是收到权限被拒绝的错误
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost' WITH GRANT OPTION;
回答by gastonnina
Hello I used this code to have the super user in mysql
您好，我使用此代码在 mysql 中拥有超级用户
GRANT EXECUTE, PROCESS, SELECT, SHOW DATABASES, SHOW VIEW, ALTER, ALTER ROUTINE,
CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP,
EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, CREATE USER, FILE,
LOCK TABLES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHUTDOWN,
SUPER
ON *.* TO mysql@'%'
WITH GRANT OPTION;
and then
进而
FLUSH PRIVILEGES;
回答by Developer
To access from remote server to mydb database only
仅从远程服务器访问 mydb 数据库
GRANT ALL PRIVILEGES ON mydb.* TO 'root'@'192.168.2.21';
To access from remote server to all databases.
从远程服务器访问所有数据库。
GRANT ALL PRIVILEGES ON * . * TO 'root'@'192.168.2.21';
回答by pgmank
To grant all priveleges on the database: mydbto the user: myuser, just execute:
要授予数据库的所有特权：mydb给用户：myuser，只需执行：
GRANT ALL ON mydb.* TO 'myuser'@'localhost';
or:
或者：
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';
The PRIVILEGESkeyword is not necessary.
该PRIVILEGES关键字是没有必要的。
Also I do not know why the other answers suggest that the IDENTIFIED BY 'password'be put on the end of the command. I believe that it is not required.
另外我不知道为什么其他答案建议将IDENTIFIED BY 'password'其放在命令的末尾。我相信这不是必需的。
