I thought I understood the basics of PHPMyadmin, MySQL, PHP and databases and have been using them for a long while but I realise I don’t understand as much as I thought.
I have always used a connection script that defines database name, host, user name and password but various resources suggest you connect to the server first defining only host, user name and password.
I thought all four parameters database name, host, user name and password were specific to the database.
On further investigation I see that PHPMyadmin has user accounts and databases have privileges where I have always created users for my databases. It seems when I do this a user is also created in the PHPMyAdmin users section. I am now completely confused about server logins and database logins and also the fact that some users in the PHPMyadmin user section have multiple entries with hosts localhost, 127.0.0.1 and %
I also have a warning ‘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’
I am completely confused about the correct way to define users, privileges and connecting to server or database.
Can anybody throw some light on this for me - thanks guys
Well, it is all really database stuff. PHP is its own thing. PHPMyAdmin is a tool written using PHP that helps you connect and visualize how the database looks like. The database itself is a collection of files that are then stored onto the host device. These files can only be read by the server if I understand correctly. I’ve tried looking through them once and I think they’re encoded.
For the user portion that you’re confused with, that’s actually all visual stuff. If we were to do this in command line it would be something like
// Logging into mysql using an account and supplying a password
mysql -u USER_ACOUNT -p
// Create a new user under the host of “localhost”
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';
Notice the @'localhost'. That’s where you can specify the host you’re using. This can be the wild card (%) you’ve mentioned as well. The address 127.0.0.1 is just the address for localhost. As you’ve noticed, you can also specify IP addresses for your host. It’s all the same. Host names like localhost are just human friendly host names that you and I can read. Honestly, it could be sitepoint.com if SitePoint’s main domain is pointing to a database. The point here is, your host has to actually be running a database server for it to connect properly.
Lastly, PHP doesn’t really have much to do with this at all. It’s all database related stuff. PHP has APIs that can connect and interact with the database, that much is true. However, up to this point, we’ve been dealing with actual database stuff. Creating a user in PHPMyAdmin is just visually creating an actual user account in the database. PHPMyAdmin really doesn’t have much to do with the database as some may think. Yes, it does interact with the database for you, but the configurations you set in the config.inc.php file has little to do with how you access the database, but rather to do with PHPMyAdmin itself. There is an option that allows you to bypass the login screen and have a global user account that’s not stored in the database, but that isn’t ideal or a smart move. The only reason to use that is if you have absolutely no way to log in anymore and you want to change or add a user account in your database.
You can do either, as far as I am aware. The connection code allows an optional database name, so to me it seems simpler to specify it rather than leaving it out and then calling a separate function to select the database.
OK thanks to everybody for their help so far. Piecing together what I have been told and my further investigations. Maybe this will help clarify for others as well
1 - I am still confused between server and host and hostname = @ or % or particularly ::1
Using PHPMyAdmin
1 - I can create a user using the PHPMyAdmin users tab - this is a server/host user ? and credentials used can be used to log into the server no association with any database exists at this point
2 - Any database I then create is automatically assigned all users on the server/host ? that have all privileges
3 - If I create a user for a database using the PHPMyAdmin privileges tab that user is also automatically assigned to the server / host ? also and can be now viewed under the user tab
This is where my confusion arose since -
1 - I could log into the server / host ? with multiple user/pass combinations because they had been automatically created by adding a user for any database with global privileges.
2 - I could also ‘login’ without specifying the database name.
I now understand that the server/host ? and database both have users/passwords and with PHPMyAdmin all databases created, then automatically get all existing global host/server ? users/passwords and when I assign any user to a database using the privileges tab this also gets automatically added to the server/host ? users - this is where most of my confusion arose.
I also understand I can login to server/host ? and database with one script or just server/host ?
But I am still confused, forgetting PHPMyAdmin
1 - between server and host and relevant login
2 - about % and @ and ::1
3 - Can I log into server/host ? with one user/pass and database with another different user/pass
Thanks again, sorry for seeming dumb but serious mental blockage here
No, just tested again, with PHPMyAdmin when you create a new database it automatically allocates all users on system that have global rights/all privileges. You can then edit the privileges. Under edit privileges you can then also add new users and if you add a new user with global/all privileges that user will also be allocated to any new databases you create - this is what was confusing me - I could create a new database and without adding any users I could access it by using credentials from another database - confusing!
Oh yes, I can see that it would allocate all users that have global rights, because that’s what “global” means. Otherwise it would only be global rights for the database created prior to the user. Normally though, you would have users configured with global rights, other than perhaps on a development machine.
I’m going to stop commenting here, it’s such a long time since I did this that I don’t want to send you the wrong way, which it seems I’ve already done a little.
Username is the username. Host is the host they are allowed to log in from. % is a wildcard (as it is in SQL queries for LIKE “%stuff%”), so giving a host of just “%” means “they can log in from anywhere”. ::1 is the IPv6 way of writing localhost, as 127.0.0.1 is in IPv4.
Correct, unless you tell PHPMyAdmin to grant the user permissions when creating it.
If, as droop says, you’ve granted GLOBAL permissions to a user, then yes. By default, a user has permissions to none of the databases in the server.
The user is created in the server, and given those permissions to “thatdb.*” (Read as “all tables in the database thatdb”).
Users are created on the server, as such, they can log on to the server, regardless of their database permissions. What they can DO once they are connected, depends on their permissions.
See above. You log into the server, and then USE the database you want to work with; and operate under the limitations of the permissions your user, from that host, has to that database.
No, your login is to the server. If you want to have multiple users for multiple databases, that’s fine, but their logins all belong to the server.
