Root Issues with MariaDB Docker

[Hawkins1212]

I am currently running the latest version of the MariaDB-docker via Unraid. I am having significant issues with my root permissions. See below for steps and the errors I am getting.

The first action is running: -u root -p Using my password set, I am able to log in as the root user.

Then I look at my users SELECT user, host FROM mysql.user; which returns: ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for tablemysql.user``

I also have "Adminer" set up via Docker and am able to login to 'root" with my password. Below are the results: Note that no privelages are defined for root:localhost; however root:% has ALL Privelages. I was able to log in to the root:% and view users via console; however, now I seem unable to. What I want to do is be able to create new users/databases via Adminer however I kept getting errors that I don't have the ability to do that.

What has gone wrong with my setup? I presently use MariaDB for Nextcloud; however, looking to expand usage to other dockers without having to create secondary dockers.

Also, I've tried doing resets in safe mode; however, I cannot even get MariaDB to run in safe mode. I think this may be Unraid and I tried mysqld_safe --skip-grant-tables which yielded /usr/local/bin/docker-entrypoint.sh: line 658: exec: mysqld_safe: not found in the logs

I can get into safe mode using mariadbd-safe --skip-grant-tables but kind of lost on what I need to do within safemode to be able to grant my root users all the permissions/privileges they should have

[grooverdan]

$ podman run --rm --name mlatest --env MARIADB_ROOT_PASSWORD=bob --env MARIADB_USER=nextcloud --env MARIADB_PASSWORD=julie --env MARIADB_DATABASE=nc -d  mariadb:latest
9ca47aa025ad94dcb4c4f53ecc5a524f1651662c1e9f4067968a0af86abbea29

$ podman exec -ti mlatest mariadb -pbob
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 11.2.2-MariaDB-1:11.2.2+maria~ubu2204 mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*61584B76F6ECE8FB9A328E7CF198094B2FAC55C7' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> show grants for root;
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*61584B76F6ECE8FB9A328E7CF198094B2FAC55C7' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> SELECT user, host FROM mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| nextcloud   | %         |
| root        | %         |
| healthcheck | 127.0.0.1 |
| healthcheck | ::1       |
| healthcheck | localhost |
| mariadb.sys | localhost |
| root        | localhost |
+-------------+-----------+
7 rows in set (0.001 sec)

tested with Adminer 4.8.1:

$ cat compose-adminer.yml 
services:
  mariadb:
    image: mariadb
    volumes:
      - mariadbdata:/var/lib/mysql
    environment:
      - MARIADB_ROOT_PASSWORD=bob
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080
volumes:
  mariadbdata: {}

Looks like the Adminer parsing of these grants needs to be improved, the code base appears untouched in the last few years.

If you look at information_schema.user_privileges the privileges are there.

[Hawkins1212]

I apologize in advance but I am not the most "programmer"/"coder" savvy. I believe my issues are with permissions in MariaDB but trying to follow what you are saying above. I ran the following:

$ mariadb -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 11.2.2-MariaDB-1:11.2.2+maria~ubu2204 mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show grants;
+-------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*01F2DCB2F68614046CD65A73EB69CA7447104286' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show grants for root;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mysql'`

It seems like now when I log in to "root" user its defaulting to the local host which is likely part of the issue. I know I have a root user defined to '%'. It looks like i just have "Grant Usage" vs. "All Privileges" to my root which is likely causing the issue; however, I have no idea how to get privileges to my root user. Every time I try, it does not allow me to and gives me an error. I am assuming if I log in with my root @ % user, I'll be able to see the privileges as per adminer, it has all privileges. Also, apologies for my formatting above, struggling to insert the text as "code"

[grooverdan]

Ok. I can't quite work out hour it initialized to this state.

For resetting permissions:

Try mariadb --protocol tcp -u root -p to get the root@% user. Hopefully that has full grants and can grant all on *.* to root@localhost with grant option

Alternately, start container, use a docker command of --skip-grant-tables directly. Then connect with the mariadb and in one session do:

FLUSH PRIVILEGES;
GRANT ALL ON *.* TO root WITH GRANT OPTION;
GRANT PROXY ON ''@'%' TO'root WITH GRANT OPTION   
GRANT ALL ON *.* TO root@localhost WITH GRANT OPTION;
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION   

After that --skip-grant-tables isn't needed.

[Hawkins1212]

Ok. I can't quite work out hour it initialized to this state.

For resetting permissions:

Try mariadb --protocol tcp -u root -p to get the root@% user. Hopefully that has full grants and can grant all on *.* to root@localhost with grant option

Alternately, start container, use a docker command of --skip-grant-tables directly. Then connect with the mariadb and in one session do:

FLUSH PRIVILEGES;
GRANT ALL ON *.* TO root WITH GRANT OPTION;
GRANT PROXY ON ''@'%' TO'root WITH GRANT OPTION   
GRANT ALL ON *.* TO root@localhost WITH GRANT OPTION;
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION   

After that --skip-grant-tables isn't needed.

Thanks for taking the time to assist. Here is where I am confused. I am able to log in to root@% and I see the following:

MariaDB [(none)]> show grants;
+--------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*1675A5CB3ED3617719166B312677C0787CE7A946' |`

Then when I go to create a user using this: MariaDB [(none)]> CREATE USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD'; this also seems to work.

Fianlly when I am still logged in to my root@% I run the command MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)

and the Error spits out. What could be causing this when my root@% clearly has full permissions?

[grooverdan]

The show grants for root@% doesn't include WITH GRANT OPTION. Restart with --skip-grant-tables and give the full grant command, to both root'%' and root@localhost

[Hawkins1212]

The show grants for root@% doesn't include WITH GRANT OPTION. Restart with --skip-grant-tables and give the full grant command, to both root'%' and root@localhost

Thank you for your assistance. I think I figured out some of the issue -- when I was logging on to adminer, I think it was logging in on my root@localhost vs. my root @%. To me, I thought both had all permissions but I think that caused some of the permission issues. Also, I think adminer had some syntax errors when trying to run some of the "Grant Privileges" commands which was tripping me up. I got it all figured out thanks to your help. Much appreciated!!!

[grooverdan]

You're welcome. Thanks for using MariaDB.