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

change the structure of mysql.user table

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

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Description The suggestion:

            Create new table
            {code:sql}
            CREATE TABLE mysql.global.priv (
              Host char(60) binary DEFAULT '' NOT NULL,
              User char(80) binary DEFAULT '' NOT NULL,
              data JSON,
              UNIQUE(host,user)
            )
            {code}
            The data might looks like
            {code:javascript}
            {
              "SELECT":"Y",
              "SUPER":"N",
              ...
             "plugin":"mysql_native_password",
              ...
            }
            {code}
            and a view:
            {code:sql}
            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;
            {code}
            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.             		The suggestion:

            Create new table
            {code:sql}
            CREATE TABLE mysql.global_priv (
              Host char(60) binary DEFAULT '' NOT NULL,
              User char(80) binary DEFAULT '' NOT NULL,
              data JSON,
              UNIQUE(host,user)
            )
            {code}
            The data might looks like
            {code:javascript}
            {
              "SELECT":"Y",
              "SUPER":"N",
              ...
             "plugin":"mysql_native_password",
              ...
            }
            {code}
            and a view:
            {code:sql}
            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;
            {code}
            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.
            ralf.gebhardt Ralf Gebhardt made changes -
            Epic Link PT-73 [ 68549 ]
            serg Sergei Golubchik made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Rank Ranked higher
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Description The suggestion:

            Create new table
            {code:sql}
            CREATE TABLE mysql.global_priv (
              Host char(60) binary DEFAULT '' NOT NULL,
              User char(80) binary DEFAULT '' NOT NULL,
              data JSON,
              UNIQUE(host,user)
            )
            {code}
            The data might looks like
            {code:javascript}
            {
              "SELECT":"Y",
              "SUPER":"N",
              ...
             "plugin":"mysql_native_password",
              ...
            }
            {code}
            and a view:
            {code:sql}
            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;
            {code}
            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.             		The suggestion:

            Create new table
            {code:sql}
            CREATE TABLE mysql.global_priv (
              Host char(60) binary DEFAULT '' NOT NULL,
              User char(80) binary DEFAULT '' NOT NULL,
              data JSON,
              UNIQUE(host,user)
            )
            {code}
            The data might looks like
            {code:javascript}
            {
              "SELECT":"Y",
              "SUPER":"N",
              ...
             "plugin":"mysql_native_password",
              ...
            }
            {code}
            and a view:
            {code:sql}
            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;
            {code}
            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}}.
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Alexey Botchkov [ holyfoot ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            holyfoot Alexey Botchkov made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.4.1 [ 23228 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Assignee Alexey Botchkov [ holyfoot ] Sergei Golubchik [ serg ]
            GeoffMontee Geoff Montee (Inactive) made changes -
            GeoffMontee Geoff Montee (Inactive) made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 90528 ] MariaDB v4 [ 133743 ]
            lstartseva Lena Startseva made changes -
            marko Marko Mäkelä made changes -

            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.