how do you give a user localhost and non localhost access in mysql?

[Red Squirrel]

Pulling my hair out trying to get a user to work in mysql, it's a pain. I normally use root for everything and trying to get away from that.. but now I remember why I originally said F it... I want to make a user that can access the DB from a certain host (or multiple hosts), but also be able to login locally through phpmyadmin which I can use to properly test access. I can't seem to get it to work. The user can connect remotely but yet cannot login through phpmyadmin, even if I use a %. I googled and supposedly localhost does not count as the wild card? So how do I give a user access to multiple hosts including localhost?

Also noticed in phpmyadmin every time I go to edit the user it just creates a new user with same name? That's kind of messed up. Why is it doing that? There's an option to delete the old user, but I'm not trying to create a new user I just want to edit a user.

 

[mxnerd]

not familiar with this stuff, but I managed to install WampServer64 on my machine and created a user using phpMyAdmin without problem.

 

[Red Squirrel]

But does it have access both remotely and locally? That's the part I had an issue with. The only way I was able to do it is to create two separate users with same name, which is a hack and a half. I'm surprised it even lets me do that.

The wildcard does not include localhost so had to setup another with localhost.

 

[mxnerd]

you mean this?

Allowing remote access to PHPMyAdmin

PhpMyAdmin is a web tool for administering (and browsing) mysql based databases. Its extremely useful, and we installed it when we put php on (if you didn’t...

community.spiceworks.com

 

[Red Squirrel]

That won't do anything. When logging in to phpmyadmin, the mysql server does not care where you're logging in from, it still treats the connection as local because it is local to the web server. (assuming phpmyadmin is hosted on same server which it is in this case)

When I created a mysql user and used the wild card, it allows a remote SQL application to connect to it fine, but it won't allow localhost so trying to login with phpmyadmin or with mysql command locally on the server does not work. What I'm trying to do is to setup a user that has access locally and remotely. I can't seem to find a way to do that without creating two users with same name and password which is quite dirty.

 

[mxnerd]

So even

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

doesn't wokr?

Mastering MySQL: Granting Database Privileges | Atlassian

Elevate your database operations and ensure optimal security with Chartio's in-depth tutorial on managing privileges within MySQL.

chartio.com

 

[Red Squirrel]

That just seems to create a new user with same name, and with no password at that.

 

[mxnerd]

MySQL :: MySQL 5.7 Reference Manual :: 13.7.1.4 GRANT Statement

dev.mysql.com

GRANT does not create user. You use "Create user":

Normally, a database administrator first uses CREATE USER to create an account and define its nonprivilege characteristics such as its password, whether it uses secure connections, and limits on access to server resources, then uses GRANT to define its privileges.

Ex:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';


Account Names and Passwords
A user value in a GRANT statement indicates a MySQL account to which the statement applies. To accommodate granting rights to users from arbitrary hosts, MySQL supports specifying the user value in the form 'user_name'@'host_name'.

You can specify wildcards in the host name. For example, 'user_name'@'%.example.com' applies to user_name for any host in the example.com domain, and 'user_name'@'198.51.100.%' applies to user_name for any host in the 198.51.100 class C subnet.

The simple form 'user_name' is a synonym for 'user_name'@'%'.

 

[Fallen Kell]

Grant does create the user. Just use the following:
GRANT ALL ON database.* TO ‘username’@‘localhost’ IDENTIFIED BY ‘password’;

This will create a new user account for username on the localhost using the specified password. You can then run the same exact command and change “localhost” to “%” to create a remote access user.

Please note, this assumes you are using command line (like most things in Linux). You will probably want to only add certain privileges depending on what you are doing (I.e. for many things read permission is the only thing needed like an account for use in PHP (or other dynamic) webpages to pull data in from the database).

 

[Red Squirrel]

You still end up with two separate users though. Is that really how it's suppose to be? Seems dirty. What happens if they change their password will it replicate to all the users with same username?

 

[mxnerd]

Specifying Account Names

MySQL :: Security in MySQL :: 4.4 Specifying Account Names

dev.mysql.com

 

[ultimatebob]

It's not really two usernames, though... it's just two different grants. I often need to do a local network admin DB account for someone (think username@'192.168.1.%') along with a local host (think username@localhost) account for local console access.

By the way... I'd recommend using MariaDB over MySQL. MariaDB seems to be better supported now that Oracle has taken over MySQL and alienated a lot of the open source community.

 

[Red Squirrel]

It's still treated as two accounts though, they have their own unique password. It's also possible to grant to one and not the other, making it impossible to properly test access by simply logging in to phpmyadmin. The main reason for wanting to give local access was so if I'm troubleshooting something in the remote app I can test that it's not access related but when logging in locally I'm technically logging in as a separate user. This particular case is an oddball one though, most of my other applications use local access only.

I'd have to look into MariaDB at some point but that means I need to rewrite all my code and make sure that every server I plan to deploy on has it installed and the php, C++, C# etc prerequisites setup etc. Mysql is just so universal it's always there by default. Guessing MariaDB might be getting more and more adopted though so yeah I should probably start looking at switching my stuff over.

 

[mv2devnull]

https://blog.panoply.io/a-comparative-vmariadb-vs-mysql writes:

MariaDB is a fork of MySQL, the database structure and indexes of MariaDB are the same as MySQL. This allows you to switch from MySQL to MariaDB without having to alter your applications

Even the command line tools are still having the original names, allowing MariaDB to be a drop-in replacement.

 

[Red Squirrel]

Hmmm good to know. Might make the transition a bit easier then. I'm looking at a full revamp of my network in the new year as I need to update all my OSes to newer ones, so I will probably make that change at same time.