0

I have a development machine where I play with MySQL (MariaDB 10.3). Every time I need extra privileges I call sudo mysql, which is what I'm trying to change.

I tried to grant myself everything (in a sudo mysql session) like this:

GRANT ALL PRIVILEGES ON *.* TO 'ubuntu'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
CREATE DATABASE test; -- Plain database with no additional grant directives

Now I have access to everything in this installation of MariaDB from the mysql CLI, but other things still fails, e.g. with the Python library mysqlclient or mysqldump utility.

import MySQLdb
db = MySQLdb.connect("localhost", "ubuntu", "", "test")

MySQLdb._exceptions.OperationalError: (1045, "Access denied for user 'ubuntu'@'localhost' (using password: NO)")

mysqldump test > /dev/null

mysqldump: Got error: 1045: "Access denied for user 'ubuntu'@'localhost' (using password: NO)" when trying to connect

All of the above code and command are run directly from a shell under the user ubuntu.

How can I really grant myself super user privileges in my MariaDB, but limit access to unix socket (so ubuntu@127.0.0.1 shouldn't be super user if TCP is used, only when connecting from /var/lib/mysqld/mysqld.sock as ubuntu@localhost)?

I need to do this on MariaDB 10.1 and 10.3.

Update

Output of USE mysql; SELECT * FROM user WHERE Host = 'localhost' OR User = 'ubuntu';

+-----------+--------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-------------+-----------------------+------------------+---------+--------------+--------------------+
| Host      | User   | Password  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | Delete_history_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin      | authentication_string | password_expired | is_role | default_role | max_statement_time |
+-----------+--------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-------------+-----------------------+------------------+---------+--------------+--------------------+
| localhost | root   | *REDACTED | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      | Y                   |          |            |             |              |             0 |           0 |               0 |                    0 | unix_socket |                       | N                | N       |              |           0.000000 |
| localhost | ubuntu | *REDACTED | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      | Y                   |          |            |             |              |             0 |           0 |               0 |                    0 |             |                       | N                | N       |              |           0.000000 |
+-----------+--------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-------------+-----------------------+------------------+---------+--------------+--------------------+

I use the stock /etc/mysql/my.cnf from Ubuntu package and haven't modified it (except a bind-address=0.0.0.0 which I suppose is irrelevant here).

iBug
  • 35,554
  • 7
  • 89
  • 134
  • Please edit your question to include the content of the `mysql.user` table. Also add the statements how you call the `mysqldump` command and the source code which tries to login into the database. Also add the content of the users `my.cnf` file and the configuration file from your mysql server. – Progman Jan 02 '20 at 14:52

1 Answers1

1

I should've Googled harder... Should anyone find this post, here's the solution:

GRANT ALL PRIVILEGES ON *.* TO 'ubuntu'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION;
                                                    ^^^^^^^^^^^^^^^^^^^^^^^^^^

The part IDENTIFIED VIA unix_socket enables passwordless login as long as the connection is done via a unix socket (but not TCP 127.0.0.1).

iBug
  • 35,554
  • 7
  • 89
  • 134
  • `IDENTIFIED VIA unix_socket` seems to be unique to MariaDB and Percona. It is unclear whether it is available before 10.4. See https://mariadb.com/kb/en/authentication-from-mariadb-104/ and https://www.percona.com/blog/2019/11/01/use-mysql-without-a-password/ – Rick James Jan 11 '20 at 04:04
  • 1
    @RickJames Check the revision history and timestamps for [this answer](https://stackoverflow.com/a/53194853/5958455), which suggests `IDENTIFIED VIA unix_socket` is available in Maria even earlier. – iBug Jan 11 '20 at 07:42