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

Easier way to retrieve all users that have privileges on a specific table

Details

    Description

      The purpose of the TABLE_PRIVILEGES table in INFORMATION_SCHEMA seems to be a bit misleading.

      It only shows those privileges that were specifically granted on the table level, but it may be taken as showing all users with privileges on a table, including those that were actually granted at the database or even the global level.

      Right now results from three different tables have to be combined for this, e.g. using:

      SELECT t.TABLE_SCHEMA
      		 
           , t.TABLE_NAME
      		 
           , privs.GRANTEE
      		 
           , GROUP_CONCAT(privs.PRIVILEGE_TYPE) AS PRIVILEGES
      		 
        FROM INFORMATION_SCHEMA.TABLES AS t
      		 
        JOIN ( SELECT NULL AS TABLE_SCHEMA
      		 
                    , NULL AS TABLE_NAME
      		 
                    , GRANTEE, PRIVILEGE_TYPE
      		 
                 FROM INFORMATION_SCHEMA.USER_PRIVILEGES
      		 
       
      		 
                UNION
      		 
       
      		 
               SELECT TABLE_SCHEMA
      		 
                    , NULL AS TABLE_NAME
      		 
                    , GRANTEE
      		 
                    , PRIVILEGE_TYPE
      		 
                 FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
      		 
       
      		 
                UNION
      		 
       
      		 
               SELECT TABLE_SCHEMA
      		 
                    , TABLE_NAME
      		 
                    , GRANTEE
      		 
                    , PRIVILEGE_TYPE
      		 
                 FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
      		 
             ) privs
      		 
          ON (t.TABLE_SCHEMA = privs.TABLE_SCHEMA OR privs.TABLE_SCHEMA IS NULL)
      		 
         AND (t.TABLE_NAME = privs.TABLE_NAME OR privs.TABLE_NAME IS NULL)
      		 
         AND (privs.PRIVILEGE_TYPE <> 'USAGE')
      		 
       WHERE t.TABLE_SCHEMA NOT IN ( 'mysql'
      		 
                                   , 'information_schema'
      		 
                                   , 'performance_schema'
      		 
                                   )
      		 
       GROUP BY t.TABLE_SCHEMA
      		 
              , t.TABLE_NAME
      		 
              , privs.GRANTEE
      		 
      ;

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36030 sysschema.v_privileges_by_table_by_level test fails in network sandbox with hostname set to localhost

          • Major - Major loss of function.
          • Confirmed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32941 I_S.USER_PRIVILEGES has a wrong name and does not show roles

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            hholzgra Hartmut Holzgraefe created issue -
            julien.fritsch Julien Fritsch made changes -
            Field Original Value New Value
            Assignee Ralf Gebhardt [ ralf.gebhardt@mariadb.com ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Labels need_feedback
            julien.fritsch Julien Fritsch made changes -
            Labels need_feedback
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 117352 ] MariaDB v4 [ 131427 ]
            AirFocus AirFocus made changes -
            Description The purpose of the TABLE_PRIVILEGES table in INFORMATION_SCHEMA seems to be a bit misleading.

            It only shows those privileges that were specifically granted on the table level, but it may be taken as showing all users with privileges on a table, including those that were actually granted at the database or even the global level.

            Right now results from three different tables have to be combined for this, e.g. using:

            {code:sql}
            SELECT t.TABLE_SCHEMA
                 , t.TABLE_NAME
                 , privs.GRANTEE
                 , GROUP_CONCAT(privs.PRIVILEGE_TYPE) AS PRIVILEGES
              FROM INFORMATION_SCHEMA.TABLES AS t
              JOIN ( SELECT NULL AS TABLE_SCHEMA
                          , NULL AS TABLE_NAME
                          , GRANTEE, PRIVILEGE_TYPE
                       FROM INFORMATION_SCHEMA.USER_PRIVILEGES

                      UNION

                     SELECT TABLE_SCHEMA
                          , NULL AS TABLE_NAME
                          , GRANTEE
                          , PRIVILEGE_TYPE
                       FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES

                      UNION

                     SELECT TABLE_SCHEMA
                          , TABLE_NAME
                          , GRANTEE
                          , PRIVILEGE_TYPE
                       FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
                   ) privs
                ON (t.TABLE_SCHEMA = privs.TABLE_SCHEMA OR privs.TABLE_SCHEMA IS NULL)
               AND (t.TABLE_NAME = privs.TABLE_NAME OR privs.TABLE_NAME IS NULL)
               AND (privs.PRIVILEGE_TYPE <> 'USAGE')
             WHERE t.TABLE_SCHEMA NOT IN ( 'mysql'
                                         , 'information_schema'
                                         , 'performance_schema'
                                         )
             GROUP BY t.TABLE_SCHEMA
                    , t.TABLE_NAME
                    , privs.GRANTEE
            ;
            {code}

            		The purpose of the TABLE_PRIVILEGES table in INFORMATION_SCHEMA seems to be a bit misleading.

            It only shows those privileges that were specifically granted on the table level, but it may be taken as showing all users with privileges on a table, including those that were actually granted at the database or even the global level.

            Right now results from three different tables have to be combined for this, e.g. using:

            {code:sql}
            SELECT t.TABLE_SCHEMA
                 , t.TABLE_NAME
                 , privs.GRANTEE
                 , GROUP_CONCAT(privs.PRIVILEGE_TYPE) AS PRIVILEGES
              FROM INFORMATION_SCHEMA.TABLES AS t
              JOIN ( SELECT NULL AS TABLE_SCHEMA
                          , NULL AS TABLE_NAME
                          , GRANTEE, PRIVILEGE_TYPE
                       FROM INFORMATION_SCHEMA.USER_PRIVILEGES

                      UNION

                     SELECT TABLE_SCHEMA
                          , NULL AS TABLE_NAME
                          , GRANTEE
                          , PRIVILEGE_TYPE
                       FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES

                      UNION

                     SELECT TABLE_SCHEMA
                          , TABLE_NAME
                          , GRANTEE
                          , PRIVILEGE_TYPE
                       FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
                   ) privs
                ON (t.TABLE_SCHEMA = privs.TABLE_SCHEMA OR privs.TABLE_SCHEMA IS NULL)
               AND (t.TABLE_NAME = privs.TABLE_NAME OR privs.TABLE_NAME IS NULL)
               AND (privs.PRIVILEGE_TYPE <> 'USAGE')
             WHERE t.TABLE_SCHEMA NOT IN ( 'mysql'
                                         , 'information_schema'
                                         , 'performance_schema'
                                         )
             GROUP BY t.TABLE_SCHEMA
                    , t.TABLE_NAME
                    , privs.GRANTEE
            ;
            {code}
            ralf.gebhardt Ralf Gebhardt made changes -
            Assignee Ralf Gebhardt [ ralf.gebhardt@mariadb.com ] Sergei Golubchik [ serg ]
            serg Sergei Golubchik made changes -
            Priority Minor [ 4 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 11.4 [ 29301 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Oleg Smirnov [ JIRAUSER50405 ]
            oleg.smirnov Oleg Smirnov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            oleg.smirnov Oleg Smirnov made changes -
            Attachment MDEV-24486.sql [ 72485 ]
            oleg.smirnov Oleg Smirnov made changes -
            Assignee Oleg Smirnov [ JIRAUSER50405 ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Oleg Smirnov [ JIRAUSER50405 ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            oleg.smirnov Oleg Smirnov made changes -
            Status Stalled [ 10000 ] In Testing [ 10301 ]
            oleg.smirnov Oleg Smirnov made changes -
            Assignee Oleg Smirnov [ JIRAUSER50405 ] Elena Stepanova [ elenst ]
            alice Alice Sherepa made changes -
            Assignee Elena Stepanova [ elenst ] Alice Sherepa [ alice ]
            julien.fritsch Julien Fritsch made changes -
            Issue Type Task [ 3 ] New Feature [ 2 ]
            alice Alice Sherepa made changes -
            Assignee Alice Sherepa [ alice ] Oleg Smirnov [ JIRAUSER50405 ]
            alice Alice Sherepa made changes -
            Status In Testing [ 10301 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            oleg.smirnov Oleg Smirnov made changes -
            Component/s Information Schema [ 14413 ]
            Fix Version/s 11.4.1 [ 29523 ]
            Fix Version/s 11.4 [ 29301 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            oleg.smirnov Oleg Smirnov made changes -
            Resolution Fixed [ 1 ]
            Status Closed [ 6 ] Stalled [ 10000 ]
            oleg.smirnov Oleg Smirnov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Priority Critical [ 2 ] Blocker [ 1 ]
            oleg.smirnov Oleg Smirnov made changes -
            Assignee Oleg Smirnov [ JIRAUSER50405 ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Oleg Smirnov [ JIRAUSER50405 ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            oleg.smirnov Oleg Smirnov made changes -
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Labels Preview_11.4
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 102362
            serg Sergei Golubchik made changes -

            People

              oleg.smirnov Oleg Smirnov
              hholzgra Hartmut Holzgraefe
              Votes:
              0 Vote for this issue
              Watchers:
              10 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.