WampServer

I've just installed WampServer 3.2.0 and updated it to 3.2.2. My PHPMyAdmin version is 4.9.2. I have not been involved in local website development using WampServer since 2015, so I have some catching up to do. My question is certainly a newbie question, but I can't find the answer after hours of searching and reading.

What are the default MariaDB users created by the WampServer install used for? Here are my questions:

1. I don't understand why the "Any" user accounts were created. What are they used for? Should I delete them?

2. I understand what the 'root'@'localhost' user is used for - it's the superuser account on the local machine. But what are the '127.0.0.1' and '::1' users used for? I know that 127.0.0.1 is the IPv4 IP address for localhost and that '::1' is the IPv6 IP address for localhost. When I set a password on '127.0.0.1' and '::1' and attempted a login, I was logged in because the 'any'@'localhost' user was present. I haven't deleted the 'any'@'localhost' account to see if the '127.0.0.1' and '::1' accounts will login correctly. Should I delete the 'any'@'localhost' user account?

3. What's the machine name account ('machine_name'@'localhost') used for?

Are the '127.0.0.01', ''::1', and 'machine_name' user accounts used for local login or remote login? Sorry for the newb question, but I need to understand what these user accounts are intended to be used for. Thanks in advance for your help!

Hi


1. If you dont want them remove them. But I would check with the mariaDB manual to see if there is a reason for them being there first.

2. A MySQL or mariaDB user account is made up of a username and a domain.
- username is obvious, but the username may only login from the mentioned domain. So we create a root account that can be used from all of the possible places you may login from on your local PC

3. See 2.

---------------------------------------------------------------------------------------------
(Windows 10 Pro 64bit) (Wampserver 3.3.1 32bit & 64bit) Aestan Tray Menu 3.2.5.0
<Apache versions MULTIPE> <PHP versions MULTIPLE> <MySQL Versions MULTIPLE>
<MariaDB versions MULTIPLE> <phpMyAdmin versions MULTIPLE> <MySQL Workbench 8.0.23>
Read The Manuals Apache -- MySQL -- PHP -- phpMyAdmin
Get your Apache/MySQL/mariaDB/PHP ADDONs here from the WAMPServer alternate Repo
-X-X-X- Backup your databases regularly Here is How dont regret it later! Yes even when developing -X-X-X-

Just so that I understand your comment, "the username may only login from the mentioned domain," am I correct in understanding that the '127.0.0.1', '::1", and 'machine_name' domains are relative ONLY to the local machine upon which PHPMyAdmin is installed?

Hi

Yes, because...


127.0.0.1 is the IPV4 loopback address for THIS PC
::1 is the IPV6 loopback address for THIS PC

and `machine_name` well you get the idea.


This is for your security, a `root` account should never be able to connect from a random outside address (IPAddress) so it can never be abused.

If you were to allow a remote connection, it should be a new account and it shoudl NOT be a SuperUser, it should only ever be allowed to connect from a specific address or a specific range of addresses such as `192.168.1` i.e. any ip on your local network

---------------------------------------------------------------------------------------------
(Windows 10 Pro 64bit) (Wampserver 3.3.1 32bit & 64bit) Aestan Tray Menu 3.2.5.0
<Apache versions MULTIPE> <PHP versions MULTIPLE> <MySQL Versions MULTIPLE>
<MariaDB versions MULTIPLE> <phpMyAdmin versions MULTIPLE> <MySQL Workbench 8.0.23>
Read The Manuals Apache -- MySQL -- PHP -- phpMyAdmin
Get your Apache/MySQL/mariaDB/PHP ADDONs here from the WAMPServer alternate Repo
-X-X-X- Backup your databases regularly Here is How dont regret it later! Yes even when developing -X-X-X-

Thanks, Riggs, for the responses. Everything you have told me agrees with what I understand about the 'localhost', '::1', and 'machine_name' elements.

I deleted the three "Any" user accounts that were created during the installation of WampServer 3.2.0 so that I could eliminate them as possible reasons why I can't login with '127.0.0.1' and 'machine_name' (I've disabled IPv6 on my entire network, so the '::1' element isn't relevant to my situation). After deleting the "Any" user accounts I tried to login using 'root'@'127.0.0.1' as the user name. Because there is no password on the 'root'@'127.0.0.1' user account I didn't type a password. I was denied login with the following message:

mysqli_real_connect(): (HY000/1045): Access denied for user 'root'@'127.0.0.1'@'localhost'
(using password: NO)

It looks like the PHPMyAdmin login interface is automatically appending the 'localhost' hostname to anything I type into the Username: box. If I understand the username/hostname login construct correctly, that means that I will never be able to use the '127.0.0.1' or the "machine_name' (and for networks with IPv6 enabled the '::1') username/host name login. Only the root/localhost user account is currently able to login.

What am I missing?

Use

Username = root
Password = [leave blank]

---------------------------------------------------------------------------------------------
(Windows 10 Pro 64bit) (Wampserver 3.3.1 32bit & 64bit) Aestan Tray Menu 3.2.5.0
<Apache versions MULTIPE> <PHP versions MULTIPLE> <MySQL Versions MULTIPLE>
<MariaDB versions MULTIPLE> <phpMyAdmin versions MULTIPLE> <MySQL Workbench 8.0.23>
Read The Manuals Apache -- MySQL -- PHP -- phpMyAdmin
Get your Apache/MySQL/mariaDB/PHP ADDONs here from the WAMPServer alternate Repo
-X-X-X- Backup your databases regularly Here is How dont regret it later! Yes even when developing -X-X-X-

Hi,

Below is a translation of an explanation I wrote on the French forum:

MySQL user management.

The user management allows :
- to set up an access control, i.e. to determine if a user has the right to connect to a MySQL server,
- if the user has the right to log in, determine what they can do there.

The MySQL access control system is specific to the SQL standard. It lacks role management but this control distinguishes users according to where they log in from.
There are two phases of access control:
- the connection to the server,
- privilege check.
All information related to user management is stored in a relational way, in this case in a dedicated database, simply named mysql.

The mysql database is one of the two databases created automatically when MySQL is installed (the other being information_schema). If it is strongly advised not to make any structural modifications, we can add, modify and delete data in two different ways:
- by using MySQL's DCL (Data Control Language) commands, such as: Create User, Rename User, Drop User, Set Password, Grant, Revoke. This is the recommended method in most cases.
- by directly modifying the contents of the tables with the "classic" commands (Insert, Update, Delete). This method, which is very delicate and requires a very good knowledge of the system, is strongly discouraged.

To connect to a MySQL server, you must have a username and, if necessary, the associated password. The list of authorized users, as well as their possible password, corresponds to the contents of the user table of the mysql database.

Notion of user account

While most DBMS characterize an account by a username (often called login), MySQL takes into account an additional parameter: the name (or IP address) of the machine *from* which the user tries to connect (called "host". A user account is therefore the association between a user name and the host of this user.
So, for example, we can let root@localhost (the super administrator working directly on the server) connect, and refuse root@provider.com (the super administrator working from home, with his Internet connection... or a malicious impostor), or downgrade it to a standard user.

Create an account named

Note: the commands below are made from the MySql "console" also called MySQL command line. (Under Wampserver, Icon, Left Click, MySql, MySql Console where you will be asked to enter the password possibly defined for root@localhost.
Note: Users can also be added, modified, deleted via PhpMyAdmin, through the Privileges option.

Start by creating an account to access the server locally :
Create an account without a password : CREATE USER 'foo'@'localhost';
Or in a less formal way if the names do not contain spaces, special characters or wildcards: CREATE USER foo@localhost ;

If you want to access MySQL from another computer, you need to create another account. If you are wandering from one computer to another, this could become a lot of work! Luckily, you can use the wildcard "%" depending on how open you think you are:

Examples of hosts (note the obligatory apostrophes to use the joker %)

Account creation instruction ; The user foo will be able to log in from...

Create User foo@localhost; The MySQL server itself
Create User foo@mycomputer; The computer called mycomputer
Create User foo@192.168.1.123; The computer whose IP is 192.168.1.123
Create User foo@'l92.l68.%'; Any computer whose IP is class 192.168
Create User foo@'%.microapp.com'; Any computer in the microapp.com domain
Create User foo@'%'; Any computer

Note that the user table is the list of user accounts allowed to log in, and that there is no "black list" of forbidden accounts. Similarly, accounts cannot be suspended, disabled or locked, but only destroyed (which does not prevent their re-creation).

Vocabulary traps

CAUTION Access control vocabulary is not always clear. Here are some useful clarifications:
-- The keyword user must not mislead you: what is created is not a user identified by his simple name, but an account characterised by the name/host pair. In the same way, the MySQL documentation most often speaks of a user where it would be less ambiguous to use the term account.
-- The term "host", combined with the username, refers to the user's computer, whereas in another context it would rather refer to the computer that hosts the MySQL server. Suppose user foo wants to connect with the text client, from a Windows computer named client, to a MySQL server named server. He will use the following command :
C:\> mysql --user=foo --host=server. It will be connected as foo@client.
-- Finally, do not confuse the account with the session. The user opens a session by logging in, and closes it using the Quit command. All session-specific information (such as session variables or temporary tables) is then deleted, while account information (such as password or privileges) remains.

Anonymous accounts

For the username, you cannot use a wildcard. However, you can create anonymous user accounts by entering an empty string as username :
Create two anonymous user accounts, one locally, the other from any computer.
CREATE USER ''@localhost, ''%'',
An anonymous account allows you to log in without a username, but also with an unknown name. Thus, with the accounts created above, the following two logins become possible :
Launching the text client under Windows, with a local anonymous connection
C:\n- Mysql --host=localhost
Ditto, with a non-existent username, which will be considered as an anonymous user.
C:\> mysql --user=anyone --host=localhost
Note that if no username is provided, MySQL will automatically assign one: under Linux, it will be the current Linux user name, and under Windows, it will always be ODBC.

Limitations on user names

WARNING The user column of the user table is a Char (16)*, that is to say that the user name is limited to 16 characters and that final spaces do not count: 'foo'@localhost and 'foo '@localhost represent the same account ; on the other hand, ' foo'@localhost is a different user.
In standard installation, this column is case sensitive: foo and Foo are two different users.
* In recent MySQL or MariaDB versions, this can be Char(32) or Char(80).

The passwords

The accounts created have a gaping security hole: they are not protected by any password. Anyone can try to log in to the server under any name, and it will be possible to log in if a username has no password.

Assigning or deleting a password

To assign a password to an existing account (or change the password of an account), use the Set Password* instruction:
Assigning a password with SET PASSWORD and the encryption function PASSWORD()
SET PASSWORD FOR foo@localhost = PASSWORD('secret') ;
It is preferable to assign the password as soon as the account is created:
Creating a password-protected account
CREATE USER tartempion@'%' IDENTIFIED BY 'sesame' ;
If you wish to remove a password to remove account protection, simply use Set Password by providing an empty string instead of the password :
Assigning then deleting a password for the local anonymous account
SET PASSWORD PASSWORD FOR ''@localhost = PASSWORD('shh') ; SET PASSWORD PASSWORD FOR ''@localhost = '' ;
Any named user can change or delete their own password, in which case the For clause is optional:

Changing and then deleting the password of the account you are logged in with (probably root@localhost)
SET PASSWORD = PASSWORD('rosebud') , SET PASSWORD = '' ;
Conversely, an anonymous user cannot change the password of his account.
To change the password of an account other than the one you are logged in with, you must have the Update privilege on the mysql base. As root@localhost has all the privileges, you have no problem assigning passwords.
*WARNING: Depending on the MySQL or MariaDB version, the way to assign a password may differ.

Password encryption

MySQL does not store passwords in clear text, but in encrypted form, using its own encryption algorithm. The encryption function is deterministic but irreversible. In other words, the same clear password will always give the same encrypted password, but it is impossible to recover the clear password from the encrypted password.

In fact, MySQL does not know the users password, only its encrypted version!
Encrypted passwords are, since MySQL 4.1, composed of an asterisk followed by 40 hexadecimal digits. Thus, for MySQL the password of foo@localhost is not secret but *14E65567ABDB5135DOCFD9A7083032C179A49EE7. When the user tries to log in, MySQL encrypts the provided password and compares the result with the encrypted password stored in the User table. If the user provided secret, the encryption gives *14E65567ABDB5135DOCFD9A7083032C179A49EE7 and the connection is accepted. Otherwise, it is denied.
If the user loses the password, there is no way to retrieve it. However, it is easy to assign a new one.

To assign a password, you can always either encrypt a clear password or provide the encrypted version directly, but with different syntaxes depending on whether you are using Set Password or Create User. Thus, the following four instructions give the secret password to the user he@localhost :
With the PASSWORD() encryption function
SET PASSWORD FOR lui@localhost = PASSWORD('secret')
By providing the encrypted version
SET PASSWORD PASSWORD FOR lui@localhost = '*14E65567ABDB5135DOCFD9A70B3032C179A49EE7';
The PASSWORD() function is implicit in the clause IDENTIFIED BY CREATE USER lui@localhost IDENTIFIED BY 'secret';
The PASSWORD keyword in the IDENTIFIED BY clause allows the encrypted version to be provided directly.
CREATE USER lui@localhost IDENTIFIED BY PASSWORD '*14E65567ABDB5135DOCFD9A7OB3032C179A49EE7' ;
These syntaxes can be misleading, because the Password( ) function encrypts, while the Password keyword indicates that there is no need to encrypt.
While the administrator can assign an already encrypted password to a user, the user attempting to log in can never indicate that he or she is providing an already encrypted password. If the user still tries to log in with the encrypted password, the password will be encrypted again, and will no longer match the password stored in the User table. Knowing an encrypted password therefore does not allow you to log in instead of the user.

Ambiguity of accounts

The different accounts created coexist, possibly with different passwords or privileges. Due to wildcards and anonymous logins, it is common for a login attempt to match multiple accounts.

Preference by specificity

See for example the accounts created to date :
Example of reading the User table via the MySQL console
SELECT User, Host, Password FROM mysql.User ;

Result (you may have some additional accounts depending on the options chosen during configuration)
User Host Password
root localhost
tartempion % *EE33294C97C42FFF14EA73AB81E428A6FC7B9A8B
him localhost *14E65567ABD85135DOCFD9A7083032C179A49EE7
%
foo localhost *14E65567ABDB5135DOCFD9A70B3032C179A49EE7
foo mycomputer
foo 192.168.1.123
foo 192.168.%
foo %.microapp.com
foo %
localhost

If foo tries to connect locally, this corresponds not only to foo@localhost, but also to foo@'%', ''@localhost, ''@'%'. However, some of these accounts are protected by a password and others are not. How does MySQL choose?
-- MySQL first chooses the host expressed in the most specific way (so localhost in preference to %).
-- It then chooses the most specific username (so toto rather than an anonymous account).
In this case, it will be foo@localhost which will win; foo will have to use his password.

What happens if tartempion tries to connect locally with his password?
C:\>mysql --user=tartempion --password=sesame --host=localhost
ERROR 1045 (28000): Access denied for user 'tartempion'8'localhost' (using password : YES)
Indeed, by applying the above rules, MySQL prefers an anonymous account on a specific host to a named account on a generic host. It is therefore ''@localhost, without password, which prevails over tartempion@'%'.

Privileges" granted to users.

These are privileges and not rights, granted (GRANT) by an administrator, which can be revoked (REVOKE) at any time. This administrator can only perform this role of granting privileges because he himself has the appropriate privileges granted to him by the "superadministrator" root@localhost.

Global privileges

Global privileges are associated with an account, regardless of database or table.
Privilege; Description
Create user; Allows you to create, delete, and rename users and also to revoke all privileges of a user (Revoke All), but not to restore them.
File ; Allows you to use the Select commands... Into Outfile and Load Data Infile commands
and so on

Granting of the CREATE USER privilege at the global level
GRANT CREATE USER ON *.* TO tartempion@'%' ;

Privileges related to objects

Other privileges are linked to objects: databases, tables, columns, routines, views.

Privilege; Description Privileged objects
Select ; Allows to read data from the Base, table, view, columns of a table with a column query
Select Update ; Allows you to modify the data in the columns of a table (Update query)
Insert ; Allows you to add rows to a table (Insert query)
Delete ; Allows you to delete rows from a Base, table, table view (Delete, Truncate)
and so on

The authorization hierarchy

A privilege changes in scope depending on the level at which it is granted. MySQL defines four levels of privilege:
- global level (privileges entered in the user table of the mysql database);
- database level (db table);
- table level (table tables_priv) or routine level (procs_priv)
- column level (table columns_priv)

Let's take the Select privilege as an example:
Syntax and scope of GRANT according to privilege level
Level; Example; Scope
- Global; Grant Select On *.* To tartempion@'%'; All columns of all tables of all bases
- Base; Grant Select On Bibli.* To tartempion@'%'; All columns of all tables in the Bibli base
- Table; Grant Select On Bibli.Lecteurs To tartempion@'%'; All columns of the table Lecteurs de la base Bibli
- Column; Grant Select (Last Name, First Name) On Bibli.Lecteurs To tartempion@'%'; The Last Name and First Name columns of the table Lecteurs de la base Bibli

The different levels of privilege are likely to conflict. What happens if, for example, tartempion@'%' has the Select privilege on the Players table, but not on the Name and First Name columns of that table? MySQL always gives preference to authorization. In other words, you only need to have a privilege at any level to be able to exercise it on that level and all levels below.

We have just seen the creation of user accounts and the granting of privileges via the MySql console. Adding, deleting, modifying user accounts and granting, revoking or modifying privileges can be done by PhpMyadmin via the Privileges option(2).

In most cases and until now, you have, for the most part, implicitly used the "super root administrator" account (root@localhost), with or without a password depending on the options chosen during configuration. This "super administrator" has all possible privileges on the databases, even the total deletion of tables and databases whatever they are, this is why you should avoid, even in local use and except in very special cases, to connect to MySql as root@localhost. You must create one or more user accounts, with password, and with only the strictly necessary privileges, for example only: SELECT, INSERT, UPDATE, DELETE

You can't create a user and give him privileges on a database all at once. You must first create the user (PhpMyadmin home page then link Privileges, then Add a user) by not giving him any global privileges. Then, one will be able to choose on which basis and which privileges or only one or several tables or even on one or several fields of one or several tables.

When a user logs into phpMyAdmin, his username and password are passed directly to MySQL. phpMyAdmin does not do any user management by itself (other than allowing manipulation of MySQL user account information); all users must be valid MySQL users.

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons



Edited 1 time(s). Last edit at 01/11/2021 10:12AM by Otomatic.

Wow! Thanks, Otomatic, for the GREAT explanation about SQL user accounts. I followed everything in your post - I think. What I'm still confused by is why PHPMyAdmin appends"@localhost" to any Username I enter. I can successfully login using root as the Username and a blank password. That's never been an issue for me. What I'm trying to do is login as root using either 127.0.0.1 or machine_name as the host name. Every time I use either of these host names in conjunction with root as the Username (e.g., 'root'@'127.0.0.0.1') PHPMyAdmin appends "@localhost" to the user/hostname combination so that 'root'@'127.0.0.0.1' entered in the Username box is processed by PHPMyAdmin as 'root'@'127.0.0.0.1'@localhost. This is the issue I'm trying to understand.



Edited 1 time(s). Last edit at 05/13/2020 09:14PM by bill@wgvallance.com.

I haven't heard anything back on my last post. Why is PHPMyAdmin appending "@ localhost" to every Username I enter in PHPMyAdmin login. Is it because the web page I loaded PHPMyAdmin with is localhost?

Hi,

In the case of PhpMyAdmin, i.e. MySQL or MariaDB - PhpMyAdmin does not manage any users on its own - the declared users come from the local server, i.e. from localhost.

You have to remember what I wrote in the explanation of MySQL user management and especially in Vocabulary traps. Although you simply enter a username, MySQL will necessarily look for which client this user comes from and in the case you are concerned about, there is only localhost as the client, so it will be user@localhost, but it could also be user@127.0.0.1.

The concept can be difficult to grasp, but a MySQL user is always a 'name'@'client' couple.

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons

@Ottomatic, just to confirm your comment "MySQL will necessarily look for which client this user comes from ..." - this means that if you are using a web page to login to PHYMyAdmin, the '@????' portion of the MySQL user name will be whatever the URL of the web page that you are using to login to PHYMyAdmin, correct? If the webpage URL is "localhost" that is what is picked up by PHPMyAdmin and passed as the '@????' portion of the MySQL user name, correct?



Edited 1 time(s). Last edit at 05/23/2020 02:06AM by bill@wgvallance.com.

Hi,

I'm assuming that it's not PhpMyAdmin that manages the users, but MySQL or MariaDB; PhpMyAdmin is, in a way, a graphical user interface (GUI) between the user and MySQL. It just sends the right queries to MySQL.

And it's not the source URL that determines the right part of the user@client pair, but the IP or the client's name.

On one of my local sites, whose base url is aviatechno ('http://aviatechno/'), I connect to PhpMyAdmin or directly to MySQL by user@localhost or user@127.0.0.1 or user@::1 because the client name is localhost or 127.0.0.1 or ::1. And I can connect because MySQL has these three users in the mysql.user table and the right part of the user@client pair corresponds to the client whose origin has been detected by MySQL.

Suppose I try to connect to MySQL from a workstation on the LAN whose IP is 198.168.0.25, always by user@localhost, the connection will be rejected by MySQL because none of the right parts of the known user@client pairs have the IP of origin 198.168.0.25.

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons

Thanks, @Otomatic, for the response to my last question. Having never connected remotely to a SQL database, I didn't understand how that was done. The following Rackspace article explained that for me:

[support.rackspace.com]

With that information I now understand how the "@client" portion of the MySQL login credential is determined. Thanks for sticking with me until I figured this out! It was your last sentence that helped me figure this out.