MySQL:授予数据库的**所有**权限

发布于 2024-10-17 16:02:05 字数 550 浏览 8 评论 0原文

我已经创建了数据库,例如“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;

现在我可以从任何地方登录数据库,但无法创建表。

如何授予该数据库和(将来)表的所有权限。我无法在“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'

I've created database, for example '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:

CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
ERROR 1142 (42000): CREATE command denied to user 'myuser'@'...' for table 't'

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(11

像极了他 2024-10-24 16:02:05
GRANT ALL PRIVILEGES
ON mydb.*
TO 'myuser'@'%'
WITH GRANT OPTION;

这就是我创建“超级用户”权限的方式(尽管我通常会指定主机)。

重要提示

虽然这个答案可以解决访问问题,但 WITH GRANT OPTION 创建一个 MySQL 用户,可以 编辑其他用户的权限

GRANT OPTION 权限使您能够将您自己拥有的权限授予其他用户或从其他用户中删除。

出于安全原因,您不应将此类用户帐户用于公众可以访问的任何流程(即网站)。建议您为此类用途创建一个仅具有数据库权限的用户

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.

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.

稍尽春風 2024-10-24 16:02:05

这是老问题,但接受的答案是不安全。这对于创建超级用户很有用,但如果您想授予单个数据库的权限,则不太好。

MySQL 8+ 不允许使用 GRANT 创建用户,这也是在 MySQL 5.7 上推荐的方法:

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

% 不涵盖 localhost 所用的 Unix 套接字通信。 WITH GRANT OPTION 仅对超级用户有利,否则在大多数情况下会存在安全风险。

更改密码(感谢 @scary-wombat 的评论):

ALTER USER 'myuser'@'%' IDENTIFIED BY 'mypassword';
ALTER USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

有关更多信息,您可以查看 MySQL 8 权限博客文章授予文档。如果您使用的是该版本,您还可以查看 MariaDB 文档

MySQL 5.5 原始答案:

grant all privileges on mydb.* to myuser@'%' identified by 'mypasswd';
grant all privileges on mydb.* to myuser@localhost identified by 'mypasswd';

仅供参考 MySQL 5.7+ 警告使用 GRANT 更改密码:

使用 GRANT 语句修改现有用户的除
特权已被弃用,并将在未来版本中删除。使用
此操作的 ALTER USER 语句。

This is old question but the accepted answer is unsafe. It's good for creating a super user but not good if you want to grant privileges on a single database.

MySQL 8+ does not allow creating a user with GRANT, also this would be the recommended way to do on MySQL 5.7:

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

% does not cover Unix socket communications, that the localhost is for. WITH GRANT OPTION is only good for the super user, otherwise it is in most cases a security risk.

Changing password (thanks to comment from @scary-wombat):

ALTER USER 'myuser'@'%' IDENTIFIED BY 'mypassword';
ALTER USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

For some more information, you can check a MySQL 8 privileges blog post and the GRANT documentation. You can also check MariaDB documentation if you are using that version.

MySQL 5.5 original answer:

grant all privileges on mydb.* to myuser@'%' identified by 'mypasswd';
grant all privileges on mydb.* to myuser@localhost identified by 'mypasswd';

FYI MySQL 5.7+ warns about using GRANT to change password:

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.

离鸿 2024-10-24 16:02:05

这对某些人会有帮助:

从 MySQL 命令行:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

遗憾的是,此时 newuser 无权对数据库执行任何操作。事实上,如果 newuser 甚至尝试登录(使用密码、密码),他们将无法访问 MySQL shell。

因此,首先要做的就是为用户提供对他们所需信息的访问。

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

此命令中的星号分别指的是他们可以访问的数据库和表 - 此特定命令允许用户跨所有数据库和表读取、编辑、执行和执行所有任务。

一旦您完成了要为新用户设置的权限,请务必重新加载所有权限。

FLUSH PRIVILEGES;

您的更改现在将生效。

有关更多信息:http://dev.mysql.com/doc/refman/5.6/en/ grant.html

如果您不熟悉命令行,那么您可以使用 MySQL Workbench 等客户端、NavicatSQLyog

This will be helpful for some people:

From MySQL command line:

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.

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

If you are not comfortable with the command line then you can use a client like MySQL workbench, Navicat or SQLyog

⒈起吃苦の倖褔 2024-10-24 16:02:05

 1.创建数据库

CREATE DATABASE db_name;

2.创建数据库的用户名 db_name

GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'localhost' IDENTIFIED BY 'password';

 3.使用数据库

USE db_name;

4.最后,您进入数据库 db_name,然后执行 create 、 select 和 insert 操作等命令。

 1. Create the database

CREATE DATABASE db_name;

 2. Create the username for the database db_name

GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'localhost' IDENTIFIED BY 'password';

 3. Use the database

USE db_name;

 4. Finally you are in database db_name and then execute the commands like create , select and insert operations.

雨巷深深 2024-10-24 16:02:05

此 SQL 授予所有数据库但仅授予基本权限。它们对于 Drupal 或 Wordpress 来说已经足够了,而且还允许一个开发者帐户用于本地项目。

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, 
    INDEX, ALTER, CREATE TEMPORARY TABLES 
ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

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.

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, 
    INDEX, ALTER, CREATE TEMPORARY TABLES 
ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
北凤男飞 2024-10-24 16:02:05
GRANT ALL PRIVILEGES ON mydb.* TO myuser@localhost IDENTIFIED BY 'mypasswd';

适用于模式权限:)

可选:在 mypasswd 之后,您可以添加 WITH GRANT OPTION

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

远山浅 2024-10-24 16:02:05

我只能通过添加 GRANT OPTION 才能使其工作,而不会总是收到权限被拒绝的错误

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

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

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost' WITH GRANT OPTION;
篱下浅笙歌 2024-10-24 16:02:05

你好,我使用这段代码在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;

,然后

FLUSH PRIVILEGES;

Hello I used this code to have the super user in 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;
岁月静好 2024-10-24 16:02:05

我在使用 MySQL Ver 8.0.21 时遇到了这个挑战,

我想将名为 my_app_db 的数据库的权限授予在 root 上运行的用户。代码>本地主机主机。

但是当我运行命令时:

use my_app_db;

GRANT ALL PRIVILEGES ON my_app_db.* TO 'root'@'localhost';

我收到错误:

错误 1064 (42000):您的 SQL 语法有错误;检查与您的 MySQL 服务器版本相对应的手册,了解在第 1 行 'my_app_db.* TO 'root'@'localhost'' 附近使用的正确语法>

这是我修复的方法

登录到您的 MySQL 控制台。您可以将 root 更改为您想要登录的用户

mysql -u root -p

输入您的mysql root 密码

接下来,列出所有用户及其主机MySQL 服务器。与 PostgreSQL 不同,它通常存储在 mysql 数据库中。所以我们需要先选择mysql数据库:

use mysql;
SELECT user, host FROM user;

注意:如果你不运行use mysql,你会得到no数据库选择错误。

这应该会给您这样的输出:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

接下来,根据从列表中获得的信息,向您想要的用户授予权限。我们需要先选择数据库,然后再授予其权限。对于我来说,我使用的是在 localhost 主机上运行的 root 用户:

use my_app_db;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';

注意GRANT ALL PRIVILEGES ON database_name.* TO 'root'@'localhost'; 命令可能不适用于现代版本的 MySQL。选择要使用的数据库后,大多数现代版本的 MySQL 将授予权限命令中的database_name替换为*

然后您可以退出 MySQL 控制台:

exit

就是这样。

我希望这有帮助

I had this challenge when working on MySQL Ver 8.0.21

I wanted to grant permissions of a database named my_app_db to the root user running on localhost host.

But when I run the command:

use my_app_db;

GRANT ALL PRIVILEGES ON my_app_db.* TO 'root'@'localhost';

I get the error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'my_app_db.* TO 'root'@'localhost'' at line 1>

Here's how I fixed:

Login to your MySQL console. You can change root to the user you want to login with:

mysql -u root -p

Enter your mysql root password

Next, list out all the users and their host on the MySQL server. Unlike PostgreSQL this is often stored in the mysql database. So we need to select the mysql database first:

use mysql;
SELECT user, host FROM user;

Note: if you don't run the use mysql, you get the no database selected error.

This should give you an output of this sort:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

Next, based on the information gotten from the list, grant privileges to the user that you want. We will need to first select the database before granting permission to it. For me, I am using the root user that runs on the localhost host:

use my_app_db;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';

Note: The GRANT ALL PRIVILEGES ON database_name.* TO 'root'@'localhost'; command may not work for modern versions of MySQL. Most modern versions of MyQL replace the database_name with * in the grant privileges command after you select the database that you want to use.

You can then exit the MySQL console:

exit

That's it.

I hope this helps

沉睡月亮 2024-10-24 16:02:05

仅从远程服务器访问 mydb 数据库

GRANT ALL PRIVILEGES ON mydb.* TO 'root'@'192.168.2.21';

从远程服务器访问所有数据库。

GRANT ALL PRIVILEGES ON * . * TO 'root'@'192.168.2.21';

To access from remote server to mydb database only

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';
耳根太软 2024-10-24 16:02:05

要将数据库 mydb 上的所有权限授予用户:myuser,只需执行:

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

或:

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

PRIVILEGES 关键字不是必需的。

另外,我不知道为什么其他答案建议将 IDENTIFIED BY 'password' 放在命令末尾。我相信这不是必需的。

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

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文