MySQL – Privilege to Kill Processes of Other Users

MySQLpermissions

I have a percona mysql database. The version is 5.5.29-29.4-log.

I have created a user called admin which should be able to kill processes initiated by other users, i.e worker.

GRANT SELECT, INSERT, UPDATE, DELETE, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON *.* TO 'admin'@'ip_' IDENTIFIED BY PASSWORD 'password';

It does had the process privilege, which I thought would do the trick. But, I can see this error

/* SQL Error (1095): You are not owner of thread 7480126 */

Would anyone be able to help?

Best Answer

From here

If you have the PROCESS privilege, you can see all threads. If you have the SUPER privilege, you can kill all threads and statements. Otherwise, you can see and kill only your own threads and statements.

You can also use the mysqladmin processlist and mysqladmin kill commands to examine and kill threads.

See here for privilege granting - search for SUPER.

Related Solutions

Mysql – Problem with View When Restoring a MySQL Backup

The definition of the View must be contained within the dump file.

To see the definitions, run the following command on the dumpfile:

cat backup.sql | grep -A 3 "CREATE ALGORITHM" > backup_views.sql
cat backup_views.sql

You could then drop the offending view(s) and recreate them manually.

If you have another server with the views established already, you should mysqldump the schema only. It will contain the views as well. @DTest and I wrote earlier posts on this.

Views are always processed last (after all tables) in a mysqldump.

This problem may also occur if one view references another, and the order the views are created doesn't take account of that.

Mysql Server Login and Permission Deny Error

Besides not running mysql_upgrade after upgrading MySQL from major subversion to major subversion (E.g 5.1 to 5.5), having the wrong permissions/ownership on the performance_schema folder and files within the data directory can also cause this error.

For example, a fresh install of MySQL 5.5's RPM will set the ownership of that folder and files within it to root:root. To fix this, you will need to change the ownership to mysql:mysql.

E.g.

chown -R mysql:mysql /var/lib/mysql/performance_schema

ls -al /var/lib/mysql

drwx------. 2 mysql mysql     4096 Jun 10 10:29 performance_schema

Best Answer

Related Solutions

Mysql – Problem with View When Restoring a MySQL Backup

Mysql Server Login and Permission Deny Error

Related Question