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

            Descending order - Click to sort in ascending order
            dveeden Daniël van Eeden added a comment -

            It would be very helpful to be able to link an account to some external system (e.g. a Request ID or a Ticket ID). This would make some compliance processes much easier. Allowing users to add to this JSON (or part of the JSON) would allow this. Maybe something that abstracts this like ALTER USER myuser COMMENT 'request_id: 1234'.

            dveeden Daniël van Eeden added a comment - It would be very helpful to be able to link an account to some external system (e.g. a Request ID or a Ticket ID). This would make some compliance processes much easier. Allowing users to add to this JSON (or part of the JSON) would allow this. Maybe something that abstracts this like ALTER USER myuser COMMENT 'request_id: 1234' .
            serg Sergei Golubchik added a comment - - edited

            No, the JSON column from this task will only be modified by the server, not by users. There's no need to provide an additional CHECK constraint in this case.

            serg Sergei Golubchik added a comment - - edited No, the JSON column from this task will only be modified by the server, not by users. There's no need to provide an additional CHECK constraint in this case.
            ralf.gebhardt Ralf Gebhardt added a comment - - edited

            serg, can you give a rough effort for this one? As type JSON will be used, wouldn't it make sense to give MDEV-13916 priority critical to assure JSON format by default?

            ralf.gebhardt Ralf Gebhardt added a comment - - edited serg , can you give a rough effort for this one? As type JSON will be used, wouldn't it make sense to give MDEV-13916 priority critical to assure JSON format by default?

            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.