MySQL – Access Denied When Updating Trigger via information_schema

MySQLschematrigger

I have some wrong "definer"s for some triggers in MySQL (actually MariaDB). I figured the easiest way of correcting them would be some version of:

use information_schema;
update triggers set definer=current_user() where trigger_schema='foobar';

It produces:

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

In the same session, I can drop and re-create the triggers with "drop trigger" and "create trigger", so I'm not understanding the permissions issue. Is there some privilege that my user needs that it doesn't have?

Best Answer

What you are doing is impossible because the INFORMATION_SCHEMA database is a database made up of in-memory read-only temporary tables (See my post How is INFORMATION_SCHEMA implemented in MySQL?)

You are going to have to drop the triggers and recreate them

I wrote a post on that before (Oct 02, 2011 : Can mysqldump dump triggers and procedures?)

Here is what you can do for all triggers in the foo database

STEP 01 : mysqldump the triggers

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump -${MYSQL_CONN} -d -t --skip-routines --triggers foo > foo_make_triggers.sql

For clarification

STEP 02 : Create a script to drop all triggers

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('DROP TRIGGER ',trigger_name,';')"
SQL="${SQL} FROM information_schema.triggers"
SQL="${SQL} WHERE trigger_schema = 'foo'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > foo_drop_triggers.sql

STEP 03 : Verify Contents of Each File

vi -R foo_drop_triggers.sql
vi -R foo_make_triggers.sql

STEP 04 : Edit all the definers in `foo_make_triggers.sql` as you know how

STEP 05 : Login to mysql and run this

use foo
source foo_drop_triggers.sql
source foo_make_triggers.sql

Test this on a staging server please

GIVE IT A TRY !!!

Related Solutions

MySQL – Access Denied to User@localhost

As @Rick suggested in the question comments. We need to grant all the permissions, procName expects. In my case, procName Creates and Deletes temporary tables. So, granting CREATE TEMPORARY TABLES permissions on dbName.* did the trick

MySQL – How to Give root@localhost Permission to Grant Privileges

I have dealt with this issue before.

When you ran

select count(1) UserTableColumnCount from information_schema.columns
where table_schema='mysql' and table_name='user';

you should have gotten 42. That's how many columns MySQL 5.5 has for mysql.user. Since you got 39, that means you must have upgraded from MySQL 5.1. That has 39 columns.

I wrote an earlier post about the number of columns in mysql.user in different versions : MySQL service stops after trying to grant privileges to a user

Here is the post where I dealt with this : mysql: Restore All privileges to admin user

Hopefully you could run

# mysql_upgrade --upgrade-system-tables

to realign mysql.user and have it autofill missing permissions with Y.

Give it a Try !!!

If you want to try to fix the mysql.user manually, here are the steps:

#
# Backup the mysql.user table
#
CREATE TABLE mysql.user_backup LIKE mysql.user;
INSERT INTO mysql.user_backup SELECT * FROM mysql.user;
#
# Add Missing Columns
#
ALTER TABLE mysql.user 
    ADD COLUMN Create_tablespace_priv enum('N','Y') NOT NULL DEFAULT 'N'
    AFTER Trigger_Priv
;
ALTER TABLE mysql.user ADD COLUMN plugin char(64);
ALTER TABLE mysql.user ADD COLUMN authentication_string text DEFAULT NULL;
#
# Give root user all privileges
#
UPDATE mysql.user SET
Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',
Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',
Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',
Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',
Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',
Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',
Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',
Create_user_priv='Y',Event_priv='Y',Trigger_priv='Y',
Create_tablespace_priv='Y'
WHERE user='root';
FLUSH PRIVILEGES;

That's it !!!