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

change the structure of mysql.user table

    XMLWordPrintable

Details

    Description

      The suggestion:

      Create new table

      CREATE TABLE mysql.global_priv (
      		 
        Host char(60) binary DEFAULT '' NOT NULL,
      		 
        User char(80) binary DEFAULT '' NOT NULL,
      		 
        data JSON,
      		 
        UNIQUE(host,user)
      		 
      )

      The data might looks like

      {
      		 
        "SELECT":"Y",
      		 
        "SUPER":"N",
      		 
        ...
      		 
       "plugin":"mysql_native_password",
      		 
        ...
      		 
      }

      and a view:

      CREATE VIEW mysql.user AS SELECT
      		 
        Host, User,
      		 
        IF(JSON_VALUE(data,"plugin") IN ("mysql_native_password", "mysql_old_password"),
      		 
           JSON_VALUE(data, "authentication_string"),"") as Password
      		 
        ...
      		 
        FROM mysql.global_priv;

      the view will show the same data that old mysql.user table had.

      The script mysql_fix_system_tables.sql should create new table and move data over.
      Also it should convert grants on mysql.user to grants on mysql.global_priv.

      Attachments

        Issue Links

          blocks

          Task - A task that needs to be done. MDEV-7597 Expiration of user passwords

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-11340 Allow multiple alternative authentication methods for the same user

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-13095 Implement user account locking

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21486 Implement option for mysql_install_db that allows root@localhost to be replaced

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21487 Implement option for mysql_upgrade that allows root@localhost to be replaced

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29542 The first character in column name has lowercase instead of uppercase in view created on table " mysql.user"

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32209 mariadb cannot import 10.3 or older mysqldump-produced dumps anymore

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open
          includes

          Task - A task that needs to be done. MDEV-12715 remove mysql.user.password column

          • Major - Major loss of function.
          • Closed

          Activity

            People

              serg Sergei Golubchik
              serg Sergei Golubchik
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.