0

I've created a custom user by executing:

mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypwd';
mysql> GRANT select, update, alter ON mydb.* TO myuser@localhost;

My user only needs to read data from the database, update entries and change a few table's structure using the ALTER command.

Now I want to execute the following script:

import mysql.connector

db = mysql.connector.connect(
        host="localhost",
        user="myuser",
        passwd="mypwd",
        database="mydb")

print(db);

I don't fully understand why, but following error occurrs:

Traceback (most recent call last):
  File "conneciton.py", line 3, in <module>
    weewxDb = mysql.connector.connect(
  File "/usr/lib/python3/dist-packages/mysql/connector/__init__.py", line 173, in connect
    return MySQLConnection(*args, **kwargs)
  File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 102, in __init__
    self.connect(**kwargs)
  File "/usr/lib/python3/dist-packages/mysql/connector/abstracts.py", line 735, in connect
    self._open_connection()
  File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 250, in _open_connection
    self._do_auth(self._user, self._password,
  File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 172, in _do_auth
    self._auth_switch_request(username, password)
  File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 216, in _auth_switch_request
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1044 (42000): Access denied for user 'myuser'@'localhost' to database 'mydb'

I think my user is missing some privileges. But which? I want to give him as few permissions as possible.

MeineHTMLCodes
  • 329
  • 3
  • 15
  • Did you do [`FLUSH PRIVILEGES;`](https://dev.mysql.com/doc/refman/8.0/en/flush.html#flush-privileges) after `CREATE USER` and `GRANT`? MySQL couldn't find your username / password combination. – O. Jones Feb 25 '21 at 12:02
  • access denied means you could not even connect to the database as either your username and/or password was incorrect. – Shadow Feb 25 '21 at 12:05
  • Grant all privileges. Then remove them one-by-one and test does each privilege remove causes problems, if true then grant it back and test the next. Finally, after all privileges tested, you will obtain minimal privileges set needed for notmal application execution. – Akina Feb 25 '21 at 12:06
  • @O.Jones Yes, I did! – MeineHTMLCodes Feb 25 '21 at 12:08
  • @Shadow Username and password are correct. I copy and pasted it and checked it twice ;) – MeineHTMLCodes Feb 25 '21 at 12:09
  • @Akina Seems to be the only way to go :/ – MeineHTMLCodes Feb 25 '21 at 12:10
  • I think mysql uses this dual host@username way of specifying users and from memory, local connections are not always reliably attributed to `localhost` - instead being represented by some ip-address, or other way of talking about the local machine - sometimes just a blank `''@'username'` format. See here: https://stackoverflow.com/questions/1412339/cannot-log-in-with-created-user-in-mysql – Thomas Kimber Feb 25 '21 at 12:11
  • 1
    @MeineHTMLCodes sorry, the error message tells otherwise. mysql error 1142 (xxx command denied to user yyy) indicates issues with privileges, not 1044. – Shadow Feb 25 '21 at 12:11
  • @ThomasKimber I am able to login using *mysql -u myuser -pmypwd*. So the error has to do sth. with the required privileges. – MeineHTMLCodes Feb 25 '21 at 12:14
  • @MeineHTMLCodes or with your hostname part of your username... – Shadow Feb 25 '21 at 12:19
  • @Shadow Hostpart cannot be specified in the command line. If do so then everything, including `@` char and hostname, will be treated as username part of complete name. And CLI will try to authenticate as `'name@host'@'host'` – Akina Feb 25 '21 at 12:22
  • @Shadow But it works if I grant all privileges. – MeineHTMLCodes Feb 25 '21 at 12:24
  • @Akina you cannot provide the hostname of the user name in any of the connection strings, this is automatically assigned by the mysql API. That's the whole point. The same username / password combination works with cli client that fails with the python code. Either the hostname part of the user name does not match or the password has some kind of a special character that causes it to fail. – Shadow Feb 25 '21 at 12:27
  • Hey guys, I solved it. Sadly I don't know how. After doing some other stuff it is suddenly working. This is very weird. Could it be that the python program was cached or sth. like this? – MeineHTMLCodes Feb 25 '21 at 12:59

0 Answers0