The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-16. For the latest, interactive version please visit https://mariadb.com/kb/.

impossible to set root user

So far I used xampp, but now I would like to use directly mariadb. I have a problem: how set root user, giving the necessary permissions. I guess that I have to modify the mysql database. But today this happened (with skip-tables-grant): 

MariaDB [mysql]> SHOW GRANTS FOR 'root'@localhost; 
+-------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD 'mypsw' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> exit
Bye

But despite it now root is still without permissions (after commenting skip-tables and restarting mariadb):

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

Where I was wrong?

Answer Answered by Ian Gilfillan in this comment.

In 10.3, the mysql.user table can be updated directly with something like the following:

1) Start the server --skip-grant-tables.

2) Connect and run the following SQL:

UPDATE mysql.user SET Select_priv="Y",Insert_priv="Y", Delete_priv="Y", Create_priv="Y", Drop_priv="Y", Reload_priv="Y", Shutdown_priv="Y", Process_priv="Y", File_priv="Y", Grant_priv="Y", References_priv="Y", Index_priv="Y", Alter_priv="Y", Show_db_priv="Y", Super_priv="Y", Create_tmp_table_priv="Y", Lock_tables_priv="Y", Execute_priv="Y", Repl_slave_priv="Y", Repl_client_priv="Y", Create_view_priv="Y", Show_view_priv="Y", Create_routine_priv="Y", Alter_routine_priv="Y", Create_user_priv="Y", Event_priv="Y", Trigger_priv="Y", Create_tablespace_priv="Y", Delete_history_priv="Y" WHERE Host="localhost" AND User="root";

3) Run FLUSH PRIVILEGES

4) Exit, and start the server normally

The above will not work from MariaDB 10.4, which introduced the mysql.global_priv table.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.