Re: Minor Mystery - phpmyadmin and mysql.user
On Mon, 1 Sep 2025 07:04:17 -0500
Kent West <westk@acu.edu> wrote:
> I'm just toying around, learning a lot, with Apache2, phpmyadmin,
> mariadb, SQL in general ... absolutely don't know what I'm doing.
>
> But I've gotten some basics accomplished; I've created a 'pma' user
> in mariadb to tinker with mariadb, and have gotten ppmyadmin to open
> in Firefox and show my databases, and have discovered that I can see
> who my users in mariadb are with a SQL command like 'SELECT user FROM
> mysql.user;'.
>
> I wanted to browse the navigation tree in the left-hand pane of the
> phpmyadmin web page, to see if I could directly see the users in the
> mysql.user table (understanding that "mysql.user" is a "path" to the
> "mysql" database, and the "user" table within that db).
>
> But the "user" table is not there. I spent considerable time last
> night and this morning with two AIs (Gemini, and duck.ai) trying to
> make this table visible (the AI's insist the table is there, but
> simply not visible for some reason), but nothing they suggested made
> the table visible in the navigation tree, although the data in that
> table is accessible via SQL (either through the phpmyadmin GUI, or
> the mariadb cli client, or the mycli client). (The AIs also insist I
> should not manipulate the user table directly, which is why it's
> hidden, but I'm not worried about manipulating that data; I just want
> to see the table there instead of just accepting that it's there;
> besides, if I hork up my mariadb/Apache2/phpmyadmin setup, nothing is
> lost; this is a throw-away setup for my learning.) The AIs seem to
> think this is Debian-specific behavior.
>
> Why can't I see this table in the navigation tree, and how do I force
> the system to let me see it there?
>
The first question is: what user are you? As I recall, mariadb starts
life with a root user, which can only be used from a root terminal
login i.e. as root on the host computer. Note that the two roots are
not the same. This is impractical for my needs, so the first thing I do
is to create an admin user from the terminal while logged in as system
root, using the mariadb root account. I give the admin user all
privileges. I never use the mariadb root account again, since it can
only be invoked from the server's root login (*not* via sudo).
The simplest explanation for what you see is that the table you want
to see, and probably others, is not visible to phpmyadmin through the
user you made for it. mariadb has fine-grained privileges to allow some
users to see (and do other things with) content that others cannot. To
use phpmyadmin for full administration it must login to mariadb as a
user with all privileges. So you need to read about mariadb (or mysql)
privileges to see how to grant all privileges to that user. As I
recall, it's quite simple, but it has been some time since I last
did it, so you need to check for yourself. Make sure at the same time
that it has a strong password, as you are making every part of mariadb
visible over a network connection. If at all possible, restrict the
phpmyadmin URL to https connections, you'll be warned of poor security
if you don't.
--
Joe
Reply to: