MySQL and MariaDB Basics: Users, Privileges and What phpMyAdmin Does

If you have ever set up a WordPress site or any other database-driven application, you have probably had to create a database, a database user, and grant that user privileges on the database, then perhaps poked around in phpMyAdmin to check everything looks right. These pieces fit together in a specific way, and understanding the relationship between them makes managing a database far less confusing. This post covers the basics: how MySQL and MariaDB relate to each other, what a database user actually is, how privileges work, and what phpMyAdmin is really doing under the hood.

MySQL and MariaDB: what’s the relationship?

MariaDB began life in 2009 as a fork of MySQL, created by members of the original MySQL development team amid concerns over Oracle’s pending acquisition of Sun Microsystems (and with it, MySQL), which was announced in April 2009 and would not complete until January 2010. The goal was to keep an open-source, community-driven version of the database available for good. The very first MariaDB release was based directly on MySQL 5.1.38.

Because of that shared history, MariaDB has deliberately maintained a high degree of compatibility with MySQL: the same client tools, connectors, ports, and SQL syntax generally work with either. In practice, this means most guidance written for MySQL, including everything below on users and privileges, applies equally to MariaDB, since the underlying command set is shared between the two.

A database user is not the same thing as a database

It is a common point of confusion for anyone new to databases: a database and a database user are two separate objects, and creating one does not automatically create or grant access to the other.

• A database is where the actual data lives, organised into tables.
• A user is a login account, identified by a username and host combination, that can authenticate to the MySQL or MariaDB server.

A user can exist without being able to touch any database at all. Access is only granted when specific privileges, on a specific database (or even a specific table), are assigned to that user. This separation is intentional: it lets a single server host many databases while giving each application or person access only to what they specifically need.

Common privilege types

Privileges are assigned per user, per database (or per table), using the GRANT statement. A few come up in almost every setup:

A typical example, granting a web application’s database user everyday read and write access to its own database without full administrative rights, looks like this:

GRANT SELECT, INSERT, UPDATE, DELETE ON app_database.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;

This gives app_user the ability to read, add, change, and remove data within app_database only, but not, for example, to create or drop entire tables, or touch any other database on the server. Granting ALL PRIVILEGES instead would hand over full control of that database, which is normally reserved for administrative accounts rather than the application’s day-to-day login.

What phpMyAdmin actually is

phpMyAdmin is not a database and it is not a database engine. It is a free, web-based administration tool, written in PHP, that gives you a graphical interface for managing a MySQL or MariaDB server that is running somewhere else, whether that’s the same machine or a remote one, such as a VPS.

Everything you do by clicking around in phpMyAdmin, browsing tables, running a query, creating a user, granting privileges, is ultimately translated into the same SQL statements you could type directly at a command-line client. phpMyAdmin does not store or process your data itself; it simply connects to the actual MySQL or MariaDB server, sends it commands, and displays the results in a browser-friendly way. This makes it a convenient front end for people who would rather not memorise SQL syntax for routine administrative tasks, but the database engine doing the actual work is always MySQL or MariaDB underneath. If you’d rather not manage users, privileges, and backups yourself, Managed Cloud Hosting handles routine database administration as part of the service.

Summary

MariaDB is a community fork of MySQL that has kept close compatibility with it since 2009, so the concepts here apply to both. A database user is a separate object from a database, and access only exists once specific privileges, such as SELECT, INSERT, UPDATE, DELETE, or the broader ALL PRIVILEGES, are granted to that user on a particular database or table. phpMyAdmin sits on top of all this as a web-based management interface; it is not the database engine itself, just a friendlier way of issuing the same commands MySQL or MariaDB would otherwise expect at the command line.