Root Issues with MariaDB Docker
Hawkins1212 opened this issue · 7 comments
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 table
mysql.
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
$ 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.
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"
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.
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 theroot@%
user. Hopefully that has full grants and cangrant all on *.* to root@localhost with grant option
Alternately, start container, use a docker command of
--skip-grant-tables
directly. Then connect with themariadb
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?
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
The
show grants
forroot@%
doesn't includeWITH GRANT OPTION
. Restart with--skip-grant-tables
and give the full grant command, to bothroot'%'
androot@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!!!
You're welcome. Thanks for using MariaDB.