Fixed: "ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation" in MySQL

I wanted to see which transactions were open, and MySQL said ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation. The account has full rights on its own database, and that's not enough. Here's why, and the right way to fix it without making your application's account more powerful.

What I Was Doing

An UPDATE was waiting on a lock, and I wanted to find the open transaction behind it. I was connected as the application's account, inventory_app, on MySQL 9.7.4 in Docker:

mysql

SELECT * FROM information_schema.innodb_trx;

What I Expected

A row for each open transaction.

What I Got

Output

ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

The deadlock report gives the same answer:

mysql

SHOW ENGINE INNODB STATUS;

Output

ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

What I Checked

What the account can do.

mysql

SHOW GRANTS;

Output

+--------------------------------------------------------------+
| Grants for inventory_app@%                                   |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `inventory_app`@`%`                    |
| GRANT ALL PRIVILEGES ON `inventory`.* TO `inventory_app`@`%` |
+--------------------------------------------------------------+
2 rows in set (0.000 sec)

ALL PRIVILEGES on inventory.*, and nothing outside it. USAGE means no rights at all.

What the docs ask for. The INNODB_TRX page says: "You must have the PROCESS privilege to query this table." The SHOW ENGINE page says the same thing of that statement: "It requires the PROCESS privilege."

Why It Happens

PROCESS isn't a right on a database. The privileges page says: "The PROCESS privilege controls access to information about threads executing within the server (that is, information about statements being executed by sessions)." That covers every session, and every database, so ALL on one database doesn't include it. An ordinary account can't see what others are running, and that's by design.

The Fix

Don't grant it to the application's account: if the app is ever compromised, that account would be able to read the statements of every other session. Create a separate account for looking, as root. Choose your own password:

mysql: root session

CREATE USER IF NOT EXISTS 'lock_viewer'@'%' IDENTIFIED BY 'choose-a-strong-password';
GRANT PROCESS ON *.* TO 'lock_viewer'@'%';
GRANT SELECT ON performance_schema.* TO 'lock_viewer'@'%';
GRANT SELECT, EXECUTE ON sys.* TO 'lock_viewer'@'%';

PROCESS has to be on *.*: on one database, it's ERROR 1221. Connect as lock_viewer, with no database on the command line. How to Let a MySQL User See Locks and Transactions explains each grant.

After the Fix

mysql: as lock_viewer

SELECT COUNT(*) AS transactions FROM information_schema.innodb_trx;
SELECT COUNT(*) FROM inventory.products;

Output

+--------------+
| transactions |
+--------------+
|            0 |
+--------------+
1 row in set (0.000 sec)

ERROR 1142 (42000): SELECT command denied to user 'lock_viewer'@'localhost' for table 'products'

It can see the transactions, and it can't see the data.

Common Mistakes

Mistake	What happens	Do this instead
GRANT PROCESS to the app's account	The app can read every session's statements	Use a separate account
GRANT PROCESS ON inventory.*	ERROR 1221	It's a global right: *.*
Reaching for root every time	One leaked password opens everything	Keep a narrow account for this

Troubleshooting

Symptom	Likely cause	Fix
SELECT command denied ... 'data_locks'	That's performance_schema, not PROCESS	ERROR 1142
the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s)	A different operation: SET GLOBAL	The fix
It works from one account and not another	Privileges belong to an account and host	SHOW GRANTS on both

FAQ

Does my application need PROCESS?

No. It only needs its own data. These views are for people working out what's wrong.

Conclusion

ERROR 1227 here means the account can't look at other sessions. PROCESS is a server-wide right, and your application doesn't need it. Give it to a small account that's only for reading locks and transactions, and keep your data out of its reach.

Additional Resources

• Privileges Provided by MySQL — what PROCESS allows.
• The INFORMATION_SCHEMA INNODB_TRX Table — the table.

Related

• How to Let a MySQL User See Locks and Transactions
• How to Find Long-Running Transactions in MySQL
• Fixed: "ERROR 1142 (42000): SELECT command denied to user for table 'data_locks'" in MySQL