Bug #70929 Protect P_S content from other users.
Submitted: 16 Nov 2013 14:10 Modified: 18 Nov 2013 19:38
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S4 (Feature request)
Version:5.6.14 OS:Any
Assigned to: Marc ALFF CPU Architecture:Any

[16 Nov 2013 14:10] Peter Laursen
Description:
It is possible to DELETE FROM|TRUNCATE P_S tables (at least some of them).

I am looking for a way to protect myself from this happening due to actions of another user while I am working with the tables. I thought I could LOCK TABLE .. WRITE. But access is denied even with all privileges available.

Such protection would be meaningfull also when a monitoring tool or a tool for automated server management use the tables. If some other tool or user DELETES|TRUNCATES this could very well lead to wrong conclusions and actions.

How to repeat:
USE `performance_schema`;

LOCK TABLES `events_statements_history` WRITE;
-- Error Code: 1142
-- SELECT, LOCK TABLES command denied to user 'root'@'localhost' for table 'events_statements_history'

TRUNCATE TABLE `events_statements_history`; 
-- success - and this could have been executed by another user.

Suggested fix:
I think there is the workaround to ensure that only one user has DELETE privilege to P_S.  But this is not always practical. Both tools/applications and human users may need it.

Ideally I would like to 'instantiate' private copies (being a property of the current connection) of the P_S tables, so that I can work with these and not affect other tools and users with what I am doing. But this is not possible. 

What prevents allowing "LOCK TABLES p_s_table .."?

I expect to get the usual reply ("everything in P_S works as designed") to this report.  But it is actually the design I am complaining about! How do I work (as a human user) with the p_s.events_statements* tables without harassing my monitoring tool that also do for instance?

Not sure about the 'severity' I should assign.  But is is not probably used anyway.
[18 Nov 2013 18:47] Sveta Smirnova
Thank you for the report.

Actually I think you should setup privileges properly to prevent such issues and you can also use views and stored procedures to limit other users from deleting your data, but I heard similar requests from other users too, therefore I set this feature request to "Verified".
[18 Nov 2013 19:38] Peter Laursen
I am surprised to see it 'verified', really! :-)

However I don't understand "you can also use views and stored procedures to limit other users from deleting your data".  Even if I access the table through a view it does not prevent other users from DELETES to the table - and my view will change immediately as MySQL does not have 'materialized views'. And how Stored Procedures should help I am simply not able to understand.

The whole P_S design completely neglects multiuser/multiadmin/multitool realities of today.  It is worse than 70'es Unix-thinking in that respect. The philosophy is like : one server + one admin (or rather: one monkey) ==> one system.

The only satisfying solution is that user will access P_S tables (at least those that are writeable) through some kind of a materialized view private for the user. Either it could always be the case or some syntax like "MATERIALIZE P_S_TABLES table[, table] EXPIRE AFTER n SECONDS|MINUTES|HOURS" (EXPIRE just in case user forgets to DEMATERIALIZE before disconnecting) could be implemented.
[19 Nov 2013 0:17] Sveta Smirnova
Peter, you should allow to modify your P_S tables only using procedures and DEFINER clause. Simply give read-only access to all users, then create SPs which are allowed to remove only part of data.