1045，“拒绝访问用户'username'@'NOT-local'(使用密码:是)" [英] 1045, "Access denied for user 'username'@'NOT-local' (using password: YES)"

问题描述

我在mySQL数据库中有一个这样的用户

I have a user in mySQL database like this

我正在尝试使用python从服务器B登录到服务器A上的mySQL，

And I am trying to login to mySQL on serverA from server B, in python, here is what I am currently doing,

db  = MySQLdb.connect(host='IP-address-server-A', user='username', passwd='my-password', db='my-database')

我收到此错误

(1045, "Access denied for user 'username'@'serverB' (using password: YES)")

这是怎么回事，为什么我不能连接?

What is going on, and why can't I connect?

感谢您的帮助

推荐答案

显示登录到服务器(请注意，%表示任何主机或通配符)

show logins to the server (note that % means anyhost or wildcard)

select user,host from mysql.user;

+-----------+------------+
| user      | host       |
+-----------+------------+
| ajax_guy  | %          |
| joe7      | %          |
| joe8      | %          |
+-----------+------------+

显示为特定用户存在哪些授权.

show what grants exist for a certain user.

show grants for 'ajax_guy'@'%';

+----------------------------------------------------------------------
| Grants for ajax_guy@%                                              
+----------------------------------------------------------------------
| GRANT USAGE ON *.* TO 'ajax_guy'@'%' IDENTIFIED BY PASSWORD ...
| GRANT ALL PRIVILEGES ON `ajax_stuff`.* TO 'ajax_guy'@'%'           
| GRANT ALL PRIVILEGES ON `ajax_stuff`.`ajax_stuff` TO 'ajax_guy'@'%'
+----------------------------------------------------------------------

如何将对特定数据库的访问权限授予特定登录名. 下面，我们授予用户对so_gibberish 数据库的所有权限.

How to grant access to a certain db to a certain login. Below we are granting all rights to the user to the so_gibberish database.

grant ALL on so_gibberish.* to 'ajax_guy'@'%';

查看该登录有效的 now

+----------------------------------------------------------------------
| Grants for ajax_guy@%                                              
+----------------------------------------------------------------------
| GRANT USAGE ON *.* TO 'ajax_guy'@'%' IDENTIFIED BY PASSWORD ...
| GRANT ALL PRIVILEGES ON `ajax_stuff`.* TO 'ajax_guy'@'%'           
| GRANT ALL PRIVILEGES ON `so_gibberish`.* TO 'ajax_guy'@'%'         
| GRANT ALL PRIVILEGES ON `ajax_stuff`.`ajax_stuff` TO 'ajax_guy'@'%'
+----------------------------------------------------------------------

使用密码friday987创建一个新的登录名drew_saturday. 他具有数据库 so_gibberish的所有特权，并且可以从任何主机(%)

Create a new login drew_saturday with a password friday987. He has all privileges on database so_gibberish and can login from any host (%)

grant ALL on so_gibberish.* to 'drew_saturday'@'%' IDENTIFIED BY 'friday987';

select user,host,password from mysql.user where user='drew_saturday';

+---------------+------+-------------------------------------------+
| user          | host | password                                  |
+---------------+------+-------------------------------------------+
| drew_saturday | %    | *4600ED0F377308959665877BD327D4788DC2071F |
+---------------+------+-------------------------------------------+

顺便说一句，上面的密码是哈希密码.

That password above is the hashed password by the way.

注意:对于MySQL 5.7，上面的命令将是:

Note: for MySQL 5.7 the command above would be:

select user,host,authentication_string from mysql.user where user='drew_saturday';

Grant 上的Mysql手册页.不要向使用grant ALL on *. ...的用户授予过多的权限.那将是系统中的 all 数据库.只需阅读手册，少即是多.

Mysql manual page on Grant. Do not grant excessive rights to users using grant ALL on *. .... That would be for all database in the system. Just read the manual and less is more.

有时，管理员希望将对数据库中的少数几个表(而不是数据库中的所有表)的访问权限授予登录名.手册是必读的内容.

Sometimes, admins want to grant access to just a handful of tables in a database (not all tables in it) to a login. The manual is a must read on this.

最后一件事. 'drew_saturday'@'％'是与'drew_saturday'@'NOT-local'不同的登录名(从您的标题中借用).它们是具有不同权限的不同登录名.这就是我在那里写的第一件事的重点.

And one last thing. 'drew_saturday'@'%' is a different login than 'drew_saturday'@'NOT-local' (borrowing from your title). They are different logins with different rights. That is the point of the first thing I wrote way up there.

这篇关于1045，“拒绝访问用户'username'@'NOT-local'(使用密码:是)"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！