1

I'm trying to disable root login without password with the new version of MariaDB.

Usually, I set the auth plugin for root to "". But it's not working anymore

ERROR 1356 (HY000): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

I tried to use ALTER USER but I've only succeed to set plugin to unix_socket or mysql_native_password

How can I resolve that ?

Thank's a lot.

Regards.

danblack
  • 12,130
  • 2
  • 22
  • 41
guillaumearnx
  • 142
  • 3
  • 12
  • Does this answer your question? [ERROR 1356 (HY000): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them](https://stackoverflow.com/questions/64841185/error-1356-hy000-view-mysql-user-references-invalid-tables-or-columns-o) – danblack Oct 30 '21 at 02:42
  • 1
    [root unix_socket auth isn't insecure](https://mariadb.com/kb/en/authentication-plugin-unix-socket/#security). It can also break you package upgrades. – danblack Oct 30 '21 at 02:44
  • i've see this issue, but nothing talks about set auth plugin – guillaumearnx Oct 30 '21 at 11:54
  • so is mysql_native_password safe ? because it allow me to login without password anyway – guillaumearnx Oct 30 '21 at 11:55

2 Answers2

0

I solved this problem by following steps:

Using command SHOW GRANTS FOR 'root'@'localhost';

If you see a line similar to this one

GRANT ALL PRIVILEGES ON . TO root@localhost IDENTIFIED VIA mysql_native_password USING 'A_HASH_PASSWORD' OR unix_socket WITH GRANT OPTION

That means your root user can be authenticated by 2 methods password or unix_socket.

Now you can use command

GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY 'YOUR_RAW_PASSWORD' WITH GRANT OPTION;
FLUSH FLUSH PRIVILEGES;

Problem solved!

vaduc
  • 61
  • 2
  • 2
-1

If you look at show create user for root you'll see that both mysql_native_password and unix_socket exist as plugin types.

unix_socket will allow the root unix user login without a password. Insecure? Not really, they can manipulate and access database files already.

mysql_native_password is there, but the password there is invalid. No password exists that is usable so you cannot login with this plugin by default. This is there so that if you really want, you can set a password (using SET PASSWORD) to login to the root user from a non-root unix user. This inherently make it less secure as there is another access mechanism.

danblack
  • 12,130
  • 2
  • 22
  • 41
  • Even after setting a new password with mysql_native_password, it always allows me to login without password (even if linux root and mariadb root passwords are differents) I've installed the ed25519 plugin. Now it's working. I see this plugin is safe. Is it true ? – guillaumearnx Nov 01 '21 at 14:40
  • 1
    I've no idea what you have, or have done (a `show create user` was needed), what you want to achieve, why you consider root passwordless insecure under unix user with unix_socket (did you read [auth plugin unix_socket security](https://mariadb.com/kb/en/authentication-plugin-unix-socket/#security)?), whether this was the cases as non-unix root user, why the assumption that linux root and mariadb root passwords need to be the same (they don't), what is secure/safe for you. or how installing a plugin changed this all (and by implication changed a user). "safe" may have broken your upgrade path. – danblack Nov 04 '21 at 02:34
  • Indeed my definition of secure may not be the right one. I read that this plugin was problematic in the case of other users with sudoers access. – guillaumearnx Nov 05 '21 at 06:58
  • try asking the question again, with details. – danblack Nov 07 '21 at 02:07
  • all i wanted to say is some people at my university advised me not to use unix_socket nor mysql_native_password so i looked for another plugin – guillaumearnx Nov 07 '21 at 16:23