MySQL:授予**数据库上所有**特权。

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'

10 个解决方案

#1

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 OPTION creates a MySQL user that can edit the permissions of other users.

虽然这个答案可以解决访问问题，但是GRANT选项创建了一个MySQL用户，可以编辑其他用户的权限。

The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess.

授予选项特权允许您向其他用户或从其他用户删除您自己拥有的特权。

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 privileges for that kind of use.

出于安全考虑，您不应该使用这种类型的用户帐户来处理公众可以访问的任何过程(例如，一个网站)。建议您创建一个只使用数据库特权的用户。

#2

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 localhost is for. WITH GRANT OPTION is only good for the super user, otherwise it is usually a security risk.

%似乎不包括套接字通信，即localhost是for。使用GRANT选项只对超级用户有利，否则通常是安全风险。

Hope this helps.

希望这个有帮助。

#3

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, Navicat or SQLyog

如果您不熟悉命令行，那么可以使用MySQL工作台、Navicat或SQLyog这样的客户机。

#4

 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中，然后执行创建、选择和插入操作等命令。

#5

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';

#6

I could able to make it work only by adding GRANT OPTION, without that always receive permission denied error

我只能通过添加GRANT选项使其工作，而不需要总是允许被拒绝的错误。

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost' WITH GRANT OPTION;

#7

GRANT ALL PRIVILEGES ON mydb.* TO myuser@localhost IDENTIFIED BY 'mypasswd';

Works for privileges on schema :)

为模式特权工作:)

Optional: after mypasswd you can add WITH GRANT OPTION

可选:在mypasswd之后，您可以添加GRANT选项。

#8

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;

#9

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';

#10

To grant all priveleges on the database: mydb to the user: myuser, just execute:

将数据库中的所有priveleges授予给用户:myuser，只需执行:

GRANT ALL ON mydb.* TO 'myuser'@'localhost';

or:

或者:

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';

The PRIVILEGES keyword is not necessary.

特权关键字不是必需的。

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.

另外，我也不知道为什么其他的答案提示，在命令的末尾输入了“密码”。我认为这是不需要的。