Guiden skapa användare 'user'@'%' men går inte att logga in

[mosbth]

I guiden i kmom01 skapar man en databasanvändare 'user'@'%' och sedan skall man använda den användaren för att logga in till databasen - men det fungerar inte. https://dbwebb.se/guide/kom-igang-med-sql-i-mysql/skapa-en-anvandare

(Om du använder mysql så byter du ut mariadb mot mysql)

$ mariadb -uuser -p
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

Det behöver inte bero på att din användare är felaktigt skapad utan det kan bero på hur din klient kopplar upp sig mot databasen. Pröva följande för att tvinga en anslutning till databasservern via TCP/IP (istället för sockets) och mot en specifik host.

$ mariadb -uuser -p -h127.0.0.1

Om det fungerar så är allt väl. Då har du en workaround.

I manualen kan man läsa mer om hur % fungerar när man skapar användare. https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/account-names.html https://mariadb.com/kb/en/create-user/#host-name-component

Följande textstycke är jag osäker på om det verkligen stämmer som förklaring, det bör kollas upp. Det är så de pratar i olika StackOverflow svar på liknande frågor.

Konstruktionen % omfattar hostnamnet 127.0.0.1 men inte hostnamnet "localhost". När du ansluter till databasen via sockets så får du hostnamnet "localhost". När du explicit ansluter till databasservern via TCP/IP och en specifik host så får du istället namnet/ipadressen från den host du kopplar upp dig från, i detta fallet 127.0.0.1.

UPPDATERING

Se inlägg https://github.com/dbwebb-se/databas/issues/12#issuecomment-761548915 för detaljer som rör MacOS och installation av Mariadb med brew. Man behöver skapa den användaren som 'user'@'localhost' istället för 'user'@'%' som guiden skriver.

[mosbth]

Jag prövade på min debian. Där kan jag inte återskapa problemet ovan utan här omfattas "localhost" av "%", så som det också står i manualen.

Här är testfallet som visar att jag har en användare "mos@%" i databasen, jag är inloggad som "mos@localhost" och jag är authenticerad som användaren "mos@%".

$ mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.                    
Your MariaDB connection id is 88
Server version: 10.3.27-MariaDB-0+deb10u1 Debian 10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.           

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 

MariaDB [(none)]> select user, host, password from mysql.user;                 
+------+-----------+-------------------------------------------+               
| user | host      | password                                  |               
+------+-----------+-------------------------------------------+               
| root | localhost | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |               
| mos  | %         | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |               
+------+-----------+-------------------------------------------+               
2 rows in set (0.000 sec)

MariaDB [(none)]> select CURRENT_USER();                                       
+----------------+
| CURRENT_USER() |
+----------------+
| mos@%          |
+----------------+
1 row in set (0.000 sec)

MariaDB [(none)]> select user();
+---------------+
| user()        |
+---------------+
| mos@localhost |
+---------------+
1 row in set (0.000 sec)

[pereriksson]

MariaDB 10.5.8 på Mac OS X 10.15.7 (senaste maridb-versionen)

Med %-tecken

$ mariadb # logga in som OS-användare per
MariaDB [(none)]> CREATE USER 'user'@'%' IDENTIFIED BY 'pass';
Query OK
$ mariadb -uuser -ppass
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)
$ mariadb -uuser -ppass --host 127.0.0.1
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)
$ mariadb -uuser -ppass --host localhost
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)
$ mariadb -uuser -ppass --host 192.168.0.10
Welcome to the MariaDB monitor.

Med localhost

$ mariadb # logga in som OS-användare per
MariaDB [(none)]> CREATE USER 'user'@'localhost' IDENTIFIED BY 'pass';
Query OK
$ mariadb -uuser -ppass
Welcome to the MariaDB monitor.
$ mariadb -uuser -ppass --host 127.0.0.1
Welcome to the MariaDB monitor.
$ mariadb -uuser -ppass --host localhost
Welcome to the MariaDB monitor.
$ mariadb -uuser -ppass --host 192.168.0.10
ERROR 1698 (28000): Access denied for user 'user'@'192.168.0.10'

Slutsats Det verkar som att % och localhost är varandras motsatser med den senaste versionen av Mariadb.

[mosbth]

Manualen för mariadb verkar lite mer oklar och under vissa förutsättningar är "%" inte samma som "localhost". https://mariadb.com/kb/en/create-user/#host-name-component

If the host name is not provided, it is assumed to be '%'.

Use 'localhost' as the host name to allow only local client connections.

Note that the credentials added when creating a user with the '%' wildcard host will not grant access in all cases. For example, some systems come with an anonymous localhost user, and when connecting from localhost this will take precedence.

För att få mer koll, vill du @pereriksson logga in och köra följande SQL-kommandon.

select user, host from mysql.user;
select user();
select CURRENT_USER();

Jag vill gärna se vilka användare du har. Har du många användare så kan du köra WHERE user = 'user' i första select-satsen.

[pereriksson]

@mosbth Innan experimentet fanns följande användare:

MariaDB [(none)]> select user, host from mysql.user;
+-------------+------------------------+
| User        | Host                   |
+-------------+------------------------+
|             | localhost              |
| mariadb.sys | localhost              |
| per         | localhost              |
| root        | localhost              |
|             | pers-macbook-pro.local |
+-------------+------------------------+
6 rows in set (0.001 sec)

MariaDB [(none)]> select user();
+---------------+
| user()        |
+---------------+
| per@localhost |
+---------------+
1 row in set (0.000 sec)

MariaDB [(none)]> select CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| per@localhost  |
+----------------+
1 row in set (0.000 sec)

[pereriksson]

Min MariaDB är en ren installation med homebrew som gjordes igår:

$ brew install mariadb
/.../
$ mysql.server start
Starting MariaDB
210113 14:20:33 mysqld_safe Starting mariadbd daemon with databases from /usr/local/var/mysql

[mosbth]

Ok, vi tar det en gång till. Återupprepa detta testfallet.

Skapa en användare och se att den finns. Visa utskriften från följande kommandon.

CREATE USER 'user'@'%' IDENTIFIED BY 'pass';
SELECT user, host FROM mysql.user WHERE user='user';

Det bör se ut så här:

MariaDB [(none)]> CREATE USER 'user'@'%' IDENTIFIED BY 'pass';             
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SELECT user, host FROM mysql.user WHERE user='user';     
+------+------+
| user | host |
+------+------+
| user | %    |
+------+------+
1 row in set (0.000 sec)

Pröva att logga in med den användaren, med eller utan -h.

mariadb -uuser -ppass
mariadb -uuser -ppass -h127.0.0.1

Kör sedan

SELECT USER(), CURRENT_USER(); 

Det bör se ut så här.

MariaDB [(none)]> SELECT USER(), CURRENT_USER();
+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| user@localhost | user@%         |
+----------------+----------------+
1 row in set (0.000 sec)

Förhoppningsvis ser vi något spännande i din utskrift som inte stämmer med min utskrift.

[pereriksson]

@mosbth

$ mariadb

MariaDB [(none)]> CREATE USER 'user'@'%' IDENTIFIED BY 'pass';

MariaDB [(none)]> SELECT user, host FROM mysql.user WHERE user='user';
+------+------+
| User | Host |
+------+------+
| user | %    |
+------+------+

$ mariadb -uuser -ppass
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

$ mariadb -uuser -ppass -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

Jag kan alltså inte logga in utan host eller via lo-interfacet. Om jag däremot loggar in med IP så ser det ut såhär:

$ mariadb -uuser -ppass -h 192.168.0.10

MariaDB [(none)]> SELECT USER(), CURRENT_USER();
+-------------------+----------------+
| USER()            | CURRENT_USER() |
+-------------------+----------------+
| user@192.168.0.10 | user@%         |
+-------------------+----------------+

[mosbth]

Fix att skapa användare 'user'@'localhost' på Mac med brew och Mariadb

Jag prövade att göra en installation på Mac med brew install mariadb. Det sker en anpassad installation och det skapas en extra användare som "krockar" med vår användare. Här följer en work around som löser det genom att skapa användaren mot localhost istället för %.

Normalt skapas användaren så här, med '%'.

CREATE USER 'user'@'%' IDENTIFIED BY 'pass';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%';

I mitt fall kan jag inte logga in med den användare (Mac, brew, mariadb).

$ mariadb -uuser -ppass
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

Anledningen är alltså att det redan ligger en annan användare i databasen som krockar med mitt försök till inloggning.

Vi väljer nu istället att skapa användaren med 'localhost', så fungerar det med den miljö som läggs upp vid installationen av brew och mariadb.

CREATE USER 'user'@'localhost' IDENTIFIED BY 'pass';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';

Nu fungerar det bra att logga in som vi tänkt oss.

mariadb -uuser -ppass

Jag kan kontrollera hur mariadb uppfattar min inloggning, dels genom att se hur jag är identifierad och dels mot vilken databasanvändare som jag är inloggad.

MariaDB [(none)]> SELECT user() AS 'Identified as', current_user() 'Logged in as';
+----------------+----------------+
| Identified as  | Logged in as   |
+----------------+----------------+
| user@localhost | user@localhost |
+----------------+----------------+

När jag testade så skapade jag båda användaren på min maskin. Jag kan se vilka användare som finns skapade så här.

MariaDB [(none)]> SELECT user, host FROM mysql.user WHERE user='user';
+------+-----------+
| User | Host      |
+------+-----------+
| user | %         |
| user | localhost |
+------+-----------+

[mosbth]

Ett liknande problem kan inträffa för användare root på Linux-maskiner. Även i detta fallet kan det bero på hur de befintliga användarna är skapade.

Här är en lösning på det problemet, där man skapar om root användaren.

https://askubuntu.com/questions/766334/cant-login-as-mysql-user-root-from-normal-user-account-in-ubuntu-16-04

[mosbth]

Jag kommer gå över till MariaDB enbart, för att undvika trubbel. Det står en del om hur defaultanvändarna är skapade här: https://mariadb.com/kb/en/authentication-from-mariadb-104/

[pereriksson]

Ett bra val 😉