MySQL：错误 1142 (42000) 和错误 1064 (42000)

发布于 2024-12-09 05:53:15 字数 1158 浏览 0 评论 0原文

我使用的是 Windows 7。我已下载并安装了 mysql-5.5.16-win32.zip。我成功启动了 MySQL 服务器，但出现此错误：

C:\Program Files\Mysql\bin>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.16 MySQL Community Server (GPL)

mysql> select user, host, password from mysql.user;
ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user
'
mysql> mysql -u root -p
    -> select user, host, password from mysql.user;
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 'mysql
 -u root -p
select user, host, password from mysql.user' at line 1
mysql> mysql -u root -p root
    -> select user, host, password from mysql.user;
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 'mysql
 -u root -p root
select user, host, password from mysql.user' at line 1
mysql>

How can set the required privilege, from Windows, to the mysql table users?

原文

I'm using windows 7. I've downloaded mysql-5.5.16-win32.zip and installed it. I started MySQL server successfully, but I get this error:

C:\Program Files\Mysql\bin>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.16 MySQL Community Server (GPL)

mysql> select user, host, password from mysql.user;
ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user
'
mysql> mysql -u root -p
    -> select user, host, password from mysql.user;
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 'mysql
 -u root -p
select user, host, password from mysql.user' at line 1
mysql> mysql -u root -p root
    -> select user, host, password from mysql.user;
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 'mysql
 -u root -p root
select user, host, password from mysql.user' at line 1
mysql>

How can set the required privileges, from Windows, to the mysql table users?

分享到QQ

分享到微博

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

发布评论

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

宫墨修音 2024-12-16 05:53:15

mysql命令行错误消息：

ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user'

意味着您使用默认的空用户登录mysql，权限几乎为零。

从命令行，像以前一样运行 mysql：
```
C:\Users\Charity>mysql
```
这将带您进入 mysql 提示符，运行命令 select user();
```
mysql>选择用户（）；
+----------------+
|用户() |
+----------------+
| ODBC@localhost |
+----------------+
一组 1 行（0.00 秒）
```
该输出意味着您不是作为用户进行连接，而是作为某种非用户 API 连接器进行连接。 ODBC 不在 mysql.users 表中。当不指定用户时，就是连接MySQL的用户。

运行另一个命令：

mysql>从 mysql.user 中选择用户；
错误 1142 (42000)：拒绝用户 ''@'localhost' 的 SELECT 命令 
表“用户”
mysql>

我们没有以任何用户身份连接，因此它告诉我们访问被拒绝。

运行这些命令：

C:\Users\Charity>mysql -u this_is_not_a_user
mysql>选择用户（）；
+------------------------------------------+
|用户() |
+------------------------------------------+
| this_is_not_a_user@localhost |
+------------------------------------------+
一组 1 行（0.00 秒）

mysql>

因此，我们已以我们定义的另一个非用户身份登录。您是否希望我们随机抽取的这个用户拥有任何特权？否。

别再胡闹了，以 root 身份登录：

C:\Users\Charity>mysql -u root -p
输入密码：************
mysql>选择用户（）；
+----------------+
|用户() |
+----------------+
|根@本地主机 |
+----------------+
一组 1 行（0.00 秒）
mysql>

现在你是 root，你可以看到一切。

查看mysql.user表：

mysql>从mysql.user中选择用户、主机；
+------+------------+
|用户 |主持人|
+------+------------+
| | linux |
|根 | linux |
| |本地主机 |
|项目经理 |本地主机 |
|根 |本地主机 |
+------+------------+
5 行一组（0.00 秒）

您可能想要创建更多用户，以便让人们使用数据库，而无需授予他们对您的数据库执行任何操作的权限。将野蛮人挡在护城河之外。

您不想一直以 root 身份登录。所以创建一个新用户。转到 phpmyadmin，以 root 身份登录 phpadmin。单击“用户”选项卡并创建一个新用户。将显示一个新对话框，填写用户名、密码和权限。然后您可以以该用户身份登录：
```
C:\Users\Charity>mysql -u el -p
输入密码：************
mysql>选择用户（）；
+----------------+
|用户() |
+----------------+
| el@localhost |
+----------------+
1 行一组（0.00 秒）
mysql>
```
此用户可以执行您在步骤 7 中定义的权限中授予的所有操作。

The mysql commandline error message:

ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user'

Means you are logged into mysql with a default null user with just about zero privileges.

From the command line, run mysql as you did before:
```
C:\Users\Charity>mysql
```
Which brings you to a mysql prompt, run the command select user();
```
mysql> select user();
+----------------+
| user()         |
+----------------+
| ODBC@localhost |
+----------------+
1 row in set (0.00 sec)
```
That output means you are connecting not as a user, but as some kind of non-user API connector. ODBC is not in the mysql.users table. It is the user that connects to MySQL when you don't specify a user.

Run another command:

mysql> select user from mysql.user;
ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for 
table 'user'
mysql>

We didn't connect as any user so it's telling us access denied.

Run these commands:

C:\Users\Charity>mysql -u this_is_not_a_user
mysql> select user();
+------------------------------+
| user()                       |
+------------------------------+
| this_is_not_a_user@localhost |
+------------------------------+
1 row in set (0.00 sec)

mysql>

So we've logged in as another non-user of our definition. Would you expect this user we pulled out of a hat to have any privileges at all? No.

Stop fooling around and login as root:

C:\Users\Charity>mysql -u root -p
Enter password: **********
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql>

Now you are root, you can see everything.

Look at the mysql.user table:

mysql> select user, host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
|      | linux     |
| root | linux     |
|      | localhost |
| pma  | localhost |
| root | localhost |
+------+-----------+
5 rows in set (0.00 sec)

You might want to create more users so you can let people use the database without giving them the rights to do anything to your database. Keep the barbarians out at the moat.

You don't want to be logging in as root all the time. So create a new user. Go to phpmyadmin, login to phpadmin as root. Click the "users" tab and create a new user. A new dialog shows, fill in the username, password and permissions. Then you can login as that user:
```
C:\Users\Charity>mysql -u el -p
Enter password: **********
mysql> select user();
+----------------+
| user()         |
+----------------+
| el@localhost   |
+----------------+
1 row in set (0.00 sec)
mysql>
```
This user can do everything that you granted in the permissions defined in step 7.