在 MySQL 中创建新用户并授予它对一个数据库的完全访问权限

Create new user in MySQL and give it full access to one database

提问by jimgh

I want to create a new user in MySQL and give it full access only to one database, say dbTest, that I create with a command like create database dbTest;. What would be the MySQL commands to do that?

我想在 MySQL 中创建一个新用户，并仅授予它对一个数据库的完全访问权限，例如dbTest，我使用类似create database dbTest;. 什么是 MySQL 命令来做到这一点？

回答by Dan McGrath

Try this to create the user:

试试这个来创建用户：

CREATE USER 'user'@'hostname';

Try this to give it access to the database dbTest:

试试这个让它访问数据库dbTest：

GRANT ALL PRIVILEGES ON dbTest.* To 'user'@'hostname' IDENTIFIED BY 'password';

If you are running the code/site accessing MySQL on the same machine, hostname would be localhost.

如果您在同一台机器上运行访问 MySQL 的代码/站点，则主机名将是 localhost。

Now, the break down.

现在，崩溃了。

GRANT- This is the command used to create users and grant rights to databases, tables, etc.

GRANT- 这是用于创建用户和授予数据库、表等权限的命令。

ALL PRIVILEGES- This tells it the user will have all standard privileges. This does not include the privilege to use the GRANT command however.

ALL PRIVILEGES- 这告诉它用户将拥有所有标准权限。但是，这不包括使用 GRANT 命令的权限。

dbtest.*- This instructions MySQL to apply these rights for use in the entire dbtest database. You can replace the * with specific table names or store routines if you wish.

dbtest.*- 这指示 MySQL 应用这些权限以在整个 dbtest 数据库中使用。如果您愿意，您可以将 * 替换为特定的表名或存储例程。

TO 'user'@'hostname'- 'user' is the username of the user account you are creating. Note: You must have the single quotes in there. 'hostname' tells MySQL what hosts the user can connect from. If you only want it from the same machine, use localhost

TO 'user'@'hostname'- 'user' 是您正在创建的用户帐户的用户名。注意：你必须在那里有单引号。'hostname' 告诉 MySQL 用户可以连接哪些主机。如果您只想从同一台机器上使用它，请使用localhost

IDENTIFIED BY 'password'- As you would have guessed, this sets the password for that user.

IDENTIFIED BY 'password'- 正如您所猜到的，这将为该用户设置密码。

回答by kenorb

Syntax

句法

To create user in MySQL/MariaDB 5.7.6 and higher, use CREATE USERsyntax:

要在 MySQL/MariaDB 5.7.6 及更高版本中创建用户，请使用CREATE USER语法：

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'new_password';

then to grant all access to the database (e.g. my_db), use GRANTSyntax, e.g.

然后授予对数据库的所有访问权限（例如my_db），使用GRANT语法，例如

GRANT ALL ON my_db.* TO 'new_user'@'localhost';

^{Where ALL(priv_type) can be replaced with specific privilege such as SELECT, INSERT, UPDATE, ALTER, etc.}

^{其中ALL（priv_type）可与特定的权限，如替换SELECT，INSERT，UPDATE，ALTER，等。}

Then to reload newly assigned permissions run:

然后重新加载新分配的权限运行：

FLUSH PRIVILEGES;

---

---

Executing

执行

To run above commands, you need to run mysqlcommand and type them into prompt, then logout by quitcommand or Ctrl-D.

要运行上述命令，您需要运行mysqlcommand 并将它们输入到提示符中，然后通过quitcommand 或Ctrl-注销D。

To run from shell, use -eparameter (replace SELECT 1with one of above commands):

要从 shell 运行，请使用-e参数（替换SELECT 1为上述命令之一）：

$ mysql -e "SELECT 1"

or print statement from the standard input:

或从标准输入打印语句：

$ echo "FOO STATEMENT" | mysql

If you've got Access deniedwith above, specify -u(for user) and -p(for password) parameters, or for long-term access set your credentials in ~/.my.cnf, e.g.

如果您的访问被拒绝，请指定-u（对于用户）和-p（对于密码）参数，或者对于长期访问，请在 中设置您的凭据~/.my.cnf，例如

[client]
user=root
password=root

---

---

Shell integration

外壳集成

For people not familiar with MySQL syntax, here are handy shell functions which are easy to remember and use (to use them, you need to load the shell functions included further down).

对于不熟悉 MySQL 语法的人，这里有一些方便的 shell 函数，它们易于记忆和使用（要使用它们，您需要进一步加载包含的 shell 函数）。

Here is example:

这是示例：

$ mysql-create-user admin mypass
| CREATE USER 'admin'@'localhost' IDENTIFIED BY 'mypass'

$ mysql-create-db foo
| CREATE DATABASE IF NOT EXISTS foo

$ mysql-grant-db admin foo
| GRANT ALL ON foo.* TO 'admin'@'localhost'
| FLUSH PRIVILEGES

$ mysql-show-grants admin
| SHOW GRANTS FOR 'admin'@'localhost'
| Grants for admin@localhost                                                                                   
| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4' |
| GRANT ALL PRIVILEGES ON `foo`.* TO 'admin'@'localhost'

$ mysql-drop-user admin
| DROP USER 'admin'@'localhost'

$ mysql-drop-db foo
| DROP DATABASE IF EXISTS foo

To use above commands, you need to copy&paste the following functions into your rcfile (e.g. .bash_profile) and reload your shell or source the file. In this case just type source .bash_profile:

要使用上述命令，您需要将以下函数复制并粘贴到您的rc文件中（例如.bash_profile）并重新加载您的 shell 或源文件。在这种情况下，只需键入source .bash_profile：

# Create user in MySQL/MariaDB.
mysql-create-user() {
  [ -z "" ] && { echo "Usage: mysql-create-user (user) (password)"; return; }
  mysql -ve "CREATE USER ''@'localhost' IDENTIFIED BY ''"
}

# Delete user from MySQL/MariaDB
mysql-drop-user() {
  [ -z "" ] && { echo "Usage: mysql-drop-user (user)"; return; }
  mysql -ve "DROP USER ''@'localhost';"
}

# Create new database in MySQL/MariaDB.
mysql-create-db() {
  [ -z "" ] && { echo "Usage: mysql-create-db (db_name)"; return; }
  mysql -ve "CREATE DATABASE IF NOT EXISTS "
}

# Drop database in MySQL/MariaDB.
mysql-drop-db() {
  [ -z "" ] && { echo "Usage: mysql-drop-db (db_name)"; return; }
  mysql -ve "DROP DATABASE IF EXISTS "
}

# Grant all permissions for user for given database.
mysql-grant-db() {
  [ -z "" ] && { echo "Usage: mysql-grand-db (user) (database)"; return; }
  mysql -ve "GRANT ALL ON .* TO ''@'localhost'"
  mysql -ve "FLUSH PRIVILEGES"
}

# Show current user permissions.
mysql-show-grants() {
  [ -z "" ] && { echo "Usage: mysql-show-grants (user)"; return; }
  mysql -ve "SHOW GRANTS FOR ''@'localhost'"
}

Note: If you prefer to not leave trace (such as passwords) in your Bash history, check: How to prevent commands to show up in bash history?

注意：如果您不想在 Bash 历史记录中留下痕迹（例如密码），请检查：如何防止命令出现在 bash 历史记录中？

回答by superluminary

To create a user and grant all privileges on a database.

创建用户并授予对数据库的所有权限。

Log in to MySQL:

登录到 MySQL：

mysql -u root

Now create and grant

现在创建并授予

GRANT ALL PRIVILEGES ON dbTest.* To 'user'@'hostname' IDENTIFIED BY 'password';

Anonymous user (for local testing only)

匿名用户（仅用于本地测试）

Alternately, if you just want to grant full unrestricted access to a database (e.g. on your local machine for a test instance, you can grant access to the anonymous user, like so:

或者，如果您只想授予对数据库的完全无限制访问权限（例如，在您的本地计算机上进行测试实例，您可以授予匿名用户访问权限，如下所示：

GRANT ALL PRIVILEGES ON dbTest.* To ''@'hostname'

Be aware

意识到

This is fine for junk data in development. Don't do this with anything you care about.

这对于开发中的垃圾数据很好。不要用你关心的任何东西来做这件事。

回答by S.K. Venkat

$ mysql -u root -p -e "grant all privileges on dbTest.* to
`{user}`@`{host}` identified by '{long-password}'; flush privileges;"

ignore -poption, if mysql user has no password orjust press "[Enter]" button to by-pass. strings surrounded with curly braces need to replaced with actual values.

忽略-p选项，如果 mysql 用户没有密码或只需按“[Enter]”按钮绕过。花括号包围的字符串需要替换为实际值。

回答by candiru

You can create new users using the CREATE USERstatement, and give rights to them using GRANT.

您可以使用CREATE USER语句创建新用户，并使用GRANT授予他们权限。

回答by Park JongBum

To me this worked.

对我来说这有效。

CREATE USER 'spowner'@'localhost' IDENTIFIED BY '1234'; 
GRANT ALL PRIVILEGES ON test.* To 'spowner'@'localhost'; 
FLUSH PRIVILEGES;

where

在哪里

• spowner : user name
• 1234 : password of spowner
• test : database 'spowner' has access right to

• spowner : 用户名
• 1234 : spowner 的密码
• 测试：数据库“spowner”有权访问

回答by Prabhakar Undurthi

The below command will work if you want create a new user give him all the access to a specific database(not all databases in your Mysql) on your localhost.

如果您想创建一个新用户，让他拥有对本地主机上特定数据库（不是 Mysql 中的所有数据库）的所有访问权限，则以下命令将起作用。

GRANT ALL PRIVILEGES ON test_database.* TO 'user'@'localhost' IDENTIFIED BY 'password';

This will grant all privileges to one database test_database(in your case dbTest) to that user on localhost.

这将向本地主机上的该用户授予一个数据库test_database（在您的情况下dbTest）的所有权限。

Check what permissions that above command issued to that user by running the below command.

通过运行以下命令检查上述命令向该用户发出的权限。

SHOW GRANTS FOR 'user'@'localhost'

Just in case, if you want to limit the user access to only one single table

以防万一，如果您想限制用户只能访问一张表

GRANT ALL ON mydb.table_name TO 'someuser'@'host';

回答by dummyDev

In case the hostpart is omitted it defaults to the wildcard symbol %, allowing all hosts.

如果host省略该部分，则默认为通配符%，允许所有主机。

CREATE USER 'service-api';

CREATE USER 'service-api';

GRANT ALL PRIVILEGES ON the_db.* TO 'service-api' IDENTIFIED BY 'the_password'

GRANT ALL PRIVILEGES ON the_db.* TO 'service-api' IDENTIFIED BY 'the_password'

SELECT * FROM mysql.user;
SHOW GRANTS FOR 'service-api'