How to Change Definer in MySQL Views

Posted on

In this article, as shown in the title of the article which is how to change definer inMySQL views, this article is written to show how to change definer in MySQL views. At first, definer of a file is actually specified in the MySQL Database dump file  where it can actually be changed.  It is quite simple to change it if the prerequisite on accessing MySQL Database Server can be fulfilled.

The prerequisite can be specified as follows :

1. The most important is the existance of MySQL Database Server. So, in order to have MySQL Database Server it must be installed first.

2. Check MySQL Database Server which can be inferred from an article titled ‘Check MySQL Service Status’ in this link .

3. The next step is the ability to access MySQL Database Server. The article titled ‘MySQL Database Access from Command Line in Linux’ in this link can be used as a reference.

After fulfilling all of the prerequisite needed to change the definer, the specific query for achieving it can be executed. Being able to connect to MySQL Database via command line specifically to the MySQL Command Line or MySQL Console Terminal is one way to change the definer of a view in MySQL Database Server using the specific query.

So, after accessing it, the following are the specific query which can be used to be executed in MySQL Command Line or MySQL Console Terminal to change the definer of a view :

SELECT CONCAT("ALTER DEFINER=`username@ip_address` VIEW ", table_name," AS ", view_definition,";") FROM  information_schema.views WHERE table_schema='database_schema_name';

username : It is the user which is used to be the new definer of the view, for an example, root.
ip_address : It is the host or the source of the new definer which is used to connect to the view, for an example, localhost. 
database_schema_name : It is the name of the database which is containing  the view where the definer is going to be changed. 


Leave a Reply