Cant grant mariadb permission to new user. Access denied for user 'root'@'localhost&#-CodePudding

I am attempting to grant permissions to a new user, and getting the access denied error. I have done this many times before without a problem, so I am very confused why this is happening now.

I log in to mysql like this:

sudo mysql -u root -p

I provide the password and get access without issue.

I created the user without problem:

CREATE USER 'root'@'newhost' IDENTIFIED BY 'password';

But, when trying to grant permission, I am getting the access denied error:

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'newhost';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

WHEN I look at the grants for root@localhost, i get the following:

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, SET USER, FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY, SLAVE MONITOR ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*someencodedpassword' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION

I am using MariaDB 10.6:

$ mysql --version
mysql  Ver 15.1 Distrib 10.6.5-MariaDB, for Linux (x86_64) using readline 5.1

I searched and made many attempts without getting past this issue. Any suggestions are appreciated.

-- EDIT --- I was able to successfully grant these permissions:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON *.* TO `root`@`newhost`

But, apparently these permissions are not enough, because my application is still giving me DB permission errors. Also, this doesn't really answer my question on why I"m not able to give ALL PERMISSIONS as I have in the past.

Is there something missing in my root@localhost grant permissions?

-- EDIT --

I was able to bypass my application error by changing the grant to the following:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON *.* TO 'root'@'newhost' IDENTIFIED BY 'password';

notice the IDENTIFIED BY 'password'

But, again, still not sure why I can't do GRANT ALL. Hopefully, someone can answer this question.

CodePudding user response：

The aspect of SHOW GRANTS for root@localhost that enumerates a list rather than showing GRANT ALL PRIVILEGES ON ... indicates a privilege is missing.

By creating a new user, removing an arbitrary privilege, and comparing its SHOW GRANTS to the root@localhost grants you have there is a missing DELETE HISTORY privilege which is why the GRANT ALL didn't work.