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

          Activity

            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 ]
            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?
            serg Sergei Golubchik made changes -
            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 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 ]

            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 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.