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

    XMLWordPrintable

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

          Activity

            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.