Knowing which privileges a given account has is easy – just issue SHOW GRANTS FOR user@host.  But what about when you need visibility into privileges from the other direction – which accounts can access specific data?  If you’re a DBA – or perform DBA duties, regardless of your title – you may have been asked this question.  It’s an important question to ask in an audit or compliance review – but it can be a difficult question to answer.    This post will walk through how to assess this, but if you’re impatient and need answers to this question immediately, jump to the end – there’s a simple shortcut.

Things to consider

There are a few things you’ll want to consider about the implementation of the MySQL privilege system as you try to sort out who has access to certain data.

Access type

MySQL can restrict privileges based on operations – somebody who has access to read data (with the SELECT privilege) may not have access to manipulate existing data (DELETE or UPDATE privileges required).  For simplicity, this post will focus on SELECT privileges, but the same general principles apply for other privileges.

Access scope

Users can grant privileges at various scope levels to users – users granted SELECT privileges on *.* (all tables/views in all schemata) don’t require explicit privileges on the mysql system schema.  Privileges granted at high levels filter down to low levels implicitly.  Privileges can exist on a global, database, table or column level.

Schema names and wildcards

This can surprise even seasoned MySQL veterans, but the privilege system allows wildcard values in defining which databases users have access to, in certain situations.  For example, the following is entirely valid, and effectively says, “give this user the ability to execute SELECT statements on any table or view that’s part of a schema with a name starting with the letter ‘s'”:

GRANT SELECT ON `s%`.* TO wc@localhost;

Note that wildcards are not allowed when you are defining privileges with scope lower than database level – for example, you cannot grant SELECT privileges to all tables in a given schema where the table name starts with the letter ‘s’.

Proxy users

MySQL has long supported proxy users, even before MySQL Server 5.7 added support for emulated roles via automated mapping of proxy identities.  Proxied identities allow one user to assume the privileges of another, and adds another layer of indirection to figuring out who has access to what.  One needs to know privileges UserA has to evaluate what UserB can do, if UserB has proxy privileges on UserA.

System table structure

Another barrier to comprehensive views into access privileges is the varying structure of the underlying system tables which support ACLs.  For example, database-level privileges are stored in the db table, which has per-privilege columns (e.g., Select_priv, Insert_priv, Update_priv, etc.).  The tables_priv table stores table-level privilege information, and shoves the various privileges into a single column (Table_priv, with possible values including Select, Insert, Update, etc.).  Because these legacy tables store access control data in different ways, creating a unified view across the access levels is cumbersome.

Putting it into practice

The following stored procedure accounts for the issues listed above, and produces a unified list of accounts with specified privileges on a given table:

DROP PROCEDURE IF EXISTS audit_access;
DELIMITER ||
CREATE PROCEDURE audit_access
(
 IN ob_schema TEXT,
 IN ob_name TEXT,
 IN access_priv TEXT 
)
 BEGIN
 
   IF NOT FIND_IN_SET(access_priv, 'SELECT,INSERT,UPDATE,DELETE') THEN
     SIGNAL SQLSTATE '45000'
       SET MESSAGE_TEXT = 
         'Privilege to audit must be one of:  SELECT, INSERT, UPDATE, DELETE.';
   END IF;
 
   DROP TEMPORARY TABLE IF EXISTS accounts_with_access;
   CREATE TEMPORARY TABLE accounts_with_access (
     User CHAR(32) NOT NULL,
     Host CHAR(60) NOT NULL,
     Notes TEXT,
     PRIMARY KEY (Host, User)
   );
   DROP TEMPORARY TABLE IF EXISTS accounts_with_proxy;
   CREATE TEMPORARY TABLE accounts_with_proxy (
     User CHAR(32) NOT NULL,
     Host CHAR(60) NOT NULL,
     PRIMARY KEY (Host, User)
   );
     
   -- Find users with global access:
   INSERT 
     INTO accounts_with_access (User, Host, Notes)
     SELECT User, Host, 'GLOBAL'
       FROM mysql.user
       WHERE IF(access_priv = 'SELECT', Select_priv = 'Y', 0)
         OR IF(access_priv = 'INSERT', Insert_priv = 'Y', 0)
         OR IF(access_priv = 'UPDATE', Update_priv = 'Y', 0)
         OR IF(access_priv = 'DELETE', Delete_priv = 'Y', 0)
     ON DUPLICATE KEY UPDATE Notes = CONCAT(Notes, ', GLOBAL');

-- Find users with database-specific access:
   INSERT 
     INTO accounts_with_access (User, Host, Notes)
     SELECT User, Host, 'DATABASE'
       FROM mysql.db
       WHERE ob_schema LIKE DB
         AND (IF(access_priv = 'SELECT', Select_priv = 'Y', 0)
         OR IF(access_priv = 'INSERT', Insert_priv = 'Y', 0)
         OR IF(access_priv = 'UPDATE', Update_priv = 'Y', 0)
         OR IF(access_priv = 'DELETE', Delete_priv = 'Y', 0))
     ON DUPLICATE KEY UPDATE Notes = CONCAT(Notes, ', DATABASE');

-- Find users with table-specific access:
   INSERT
     INTO accounts_with_access (User, Host, Notes)
     SELECT DISTINCT User, Host, 'TABLE'
       FROM mysql.tables_priv
       WHERE ob_schema = Db
         AND ob_name = Table_name
         AND FIND_IN_SET(access_priv, Table_priv)
     ON DUPLICATE KEY UPDATE Notes = CONCAT(Notes, ', TABLE');

-- Find users with column-specific access:
   INSERT
     INTO accounts_with_access (User, Host, Notes)
     SELECT DISTINCT User, Host, 'COLUMN'
       FROM mysql.columns_priv
       WHERE Db = ob_schema
         AND Table_name = ob_name
         AND FIND_IN_SET(access_priv, Column_priv)
     ON DUPLICATE KEY UPDATE Notes = CONCAT(Notes, ', COLUMN');

-- Find users with proxy-based access.
-- First, find the users who can proxy users
-- we know have access:
   INSERT
     INTO accounts_with_proxy (User, Host)
     SELECT DISTINCT pp.User, pp.Host
       FROM mysql.proxies_priv pp
       JOIN accounts_with_access awa
         ON (awa.Host = pp.Proxied_Host AND awa.User = pp.Proxied_User);
 
-- Then add these users to the list:
   INSERT
     INTO accounts_with_access (User, Host, Notes)
     SELECT DISTINCT awp.User, awp.Host, 'PROXY'
       FROM accounts_with_proxy awp
     ON DUPLICATE KEY UPDATE Notes = CONCAT(Notes, ', PROXY');
     
   SELECT * FROM accounts_with_access;
   DROP TEMPORARY TABLE IF EXISTS accounts_with_access;
   DROP TEMPORARY TABLE IF EXISTS accounts_with_proxy;
 END||
 DELIMITER ;

Here’s a quick demonstration of how this stored procedure in action:

mysql>  GRANT SELECT ON *.* TO at1@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>  GRANT SELECT ON access_audit_test.* TO at2@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>  GRANT SELECT ON access_audit_test.t1 TO at3@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>  GRANT SELECT (a) ON access_audit_test.t1 TO at4@localhos
Query OK, 0 rows affected (0.00 sec)

mysql>  GRANT PROXY ON at1@localhost TO at5@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>  GRANT SELECT ON `a%`.* TO at6@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>  GRANT SELECT ON *.* TO at6@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>  CALL audit_access('access_audit_test', 't1', 'SELECT');
+------+-----------+------------------+
| User | Host      | Notes            |
+------+-----------+------------------+
| at1  | localhost | GLOBAL           |
| at2  | localhost | DATABASE         |
| at3  | localhost | TABLE            |
| at4  | localhost | COLUMN           |
| at5  | localhost | PROXY            |
| at6  | localhost | GLOBAL, DATABASE |
| root | localhost | GLOBAL           |
+------+-----------+------------------+
7 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

The source for this stored procedure – and the test script above – can be downloaded here.

Additional note

There are a few other ways in which data can be accessed that are not reflected in the stored procedure provided.  For example, a stored procedure itself may execute using the security context of the definer, rather than the invoking user.  VIEWs behave similarly.  A user may have limited access to data via such objects, and would not be reflected in the output of the stored procedure above.

Conclusion

Identifying which accounts have access to specific data requires a bit of work – hopefully the stored procedure and information provided makes this task easier for you.