Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-34851

privileges don't work with grant all privileges

Details

    Description

      create user test identified by 'test';
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [(none)]> grant all privileges on *.* to test;
      ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
      

      I am 'root@localhost', and if I restart the server with skip-grant-tables, I cannot assign privileges. I need to assign all privileges to a user because for backup I need to dump the mysql.user table, and this fails without full privileges, I mean, if you try then you get nothing, mariadb-dump cannot dump that critical table. So I am in a circle. Is there a solution to this nightmare?

      Attachments

        Issue Links

          Activity

            I tried to do this, at another user-database
            *--------------
            CREATE TABLE temp.user LIKE mysql.user
            --------------

            ERROR 1347 (HY000) at line 1: 'mysql.user' is not of type 'BASE TABLE'*

            the idea was to duplicate the user table and backup the copy, but it did not work. Any idea?

            philip_38 Philip orleans added a comment - I tried to do this, at another user-database *-------------- CREATE TABLE temp.user LIKE mysql.user -------------- ERROR 1347 (HY000) at line 1: 'mysql.user' is not of type 'BASE TABLE'* the idea was to duplicate the user table and backup the copy, but it did not work. Any idea?

            you need to duplicate mysql.global_priv table, mysql.user is just a view over mysql.global_priv

            serg Sergei Golubchik added a comment - you need to duplicate mysql.global_priv table, mysql.user is just a view over mysql.global_priv

            THIS WORKS
            mariadb-dump --no-create-info --skip-triggers --compact mysql global_priv > user.sql

            but the question is if I execute the output

            mariadb mysql < user.sql
            will it work or not?
            In general, how does anybody backup and restore the users for a mariadb system, so the company may quickly come back in case of a total catastrophe?

            philip_38 Philip orleans added a comment - THIS WORKS mariadb-dump --no-create-info --skip-triggers --compact mysql global_priv > user.sql but the question is if I execute the output mariadb mysql < user.sql will it work or not? In general, how does anybody backup and restore the users for a mariadb system, so the company may quickly come back in case of a total catastrophe?
            philip_38 Philip orleans added a comment - - edited

            The backup and restore from table global_priv works, but I also need to backup all the Grants, otherwise I will have useless users. The grants for users are stored in what table?
            The fact that I need to backup private tables is unusual. Mariuadb must have published and legal access to user data and grants.

            philip_38 Philip orleans added a comment - - edited The backup and restore from table global_priv works, but I also need to backup all the Grants, otherwise I will have useless users. The grants for users are stored in what table? The fact that I need to backup private tables is unusual. Mariuadb must have published and legal access to user data and grants.

            if you execute

            mariadb mysql < user.sql

            it will work, in the sense, it'll restore the table as it was when you backed it up.

            to backup and restore the users for a mariadb system you can either back up the system database,

            mariadb-dump mysql

            or

            mariadb-dump --system=users

            System tables are documented in the knowledge base.

            The legal way to back up user accounts and grants without directly accessing system tables is

            mariadb-dump --system=users

            serg Sergei Golubchik added a comment - if you execute mariadb mysql < user.sql it will work, in the sense, it'll restore the table as it was when you backed it up. to backup and restore the users for a mariadb system you can either back up the system database, mariadb-dump mysql or mariadb-dump --system=users System tables are documented in the knowledge base . The legal way to back up user accounts and grants without directly accessing system tables is mariadb-dump --system=users

            People

              serg Sergei Golubchik
              philip_38 Philip orleans
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.