root can connect to database other users can't

Problems with the Mac OS X version of XAMPP, questions, comments, and anything related.

root can connect to database other users can't

Postby xampp_wanna_use » 04. May 2021 03:25

Mac mini OS 11.3, XAMPP 8.03

Trying to install Wordpress. Followed the directions here:
https://wordpress.org/support/article/how-to-install-wordpress/

I can't connect to the database with anything but root.

define( 'DB_NAME', 'mcsga' );
define( 'DB_USER', 'mcsga_user' );
define( 'DB_PASSWORD', 'XXX' );
//works fine
//define( 'DB_USER', 'rootr' );
//define( 'DB_PASSWORD', '' );

I tried this and can't connect with anything but root.

<?php
$servername = "localhost";
$username = "mcsga_user";
$password = "XXX";

//root works fine
//$username = "root";
// $password = "";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

I'm out of ideas.
xampp_wanna_use
 
Posts: 6
Joined: 02. May 2021 21:05
XAMPP version: xampp-osx-8.0.3-0-vm
Operating System: 11.3

Re: root can connect to database other users can't

Postby Altrea » 04. May 2021 08:01

Did you create the user and which permissions did you set?
What specific error message do you get?
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64

Re: root can connect to database other users can't

Postby xampp_wanna_use » 04. May 2021 12:31

Yes, the user is created.

GRANT USAGE ON *.* TO `mcsga_user`@`%` IDENTIFIED BY PASSWORD '*87AE701A070C45E7172C5411D7ED3EFB9276CC8A';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mcsga`.* TO `mcsga_user`@`%`;

The error I get is: Error establishing a database connection
xampp_wanna_use
 
Posts: 6
Joined: 02. May 2021 21:05
XAMPP version: xampp-osx-8.0.3-0-vm
Operating System: 11.3

Re: root can connect to database other users can't

Postby Altrea » 04. May 2021 13:31

Ok, so you used the wildcard for the host part of the account.

MariaDB identifies a user by a priority list sorted from most specific to least specific (where a host is more specific than a username).
Means in order:

  • 1. Priority (full specific host, specific user)
    'username'@'localhost' , 'username'@'example.com' , etc.

  • 2. Priority (full specific host, unspecific user)
    ''@'localhost' , ''@'example.com' , etc.

  • 3. Priority (partly unspecific/wildcarded host, specific user)
    'username'@'%.example.com'

  • 4. Priority (partly unspecific/wildcarded host, unspecific user
    ''@'%.example.com'

  • 5. Priority (fully wildcarded host, specific user)
    'username'@'%'

  • 6. Priority (no given host, specific user)
    'username'@''

  • 7. Priority (fully wildcarded host, unspecific user)
    ''@'%'

  • 8. Priority (no given host, unspecific user)
    ''@''

So you Created a User of Priority 5. Are there any other users present in your MariaDB Instance that can also match but has a higher priority (like ''@'localhost' for example)?
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64

Re: root can connect to database other users can't

Postby xampp_wanna_use » 04. May 2021 17:42

Thank you, that did the trick.

On the User accounts overview of the User Account tab, now I notice "A user account allowing any user from localhost to connect is present. This will prevent other users from connecting if the host part of their account allows a connection from any (%) host." By default MariaDB adds an ANY user with host name % Is that what was working against me?

I appreciate the help Altrea. I don't even know what I don't know. Now I know a little more.
xampp_wanna_use
 
Posts: 6
Joined: 02. May 2021 21:05
XAMPP version: xampp-osx-8.0.3-0-vm
Operating System: 11.3

Re: root can connect to database other users can't

Postby Altrea » 04. May 2021 18:03

xampp_wanna_use wrote:By default MariaDB adds an ANY user with host name % Is that what was working against me?

The Any@'%' user (or also written ''@'%') may produce problems, but only if no other user account matches. It is just a Priority 7 user account.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64


Return to XAMPP for macOS

Who is online

Users browsing this forum: No registered users and 20 guests