content_image/863705.jpg
• CREATE USER  fails with an error if any account to be created is named as the DEFINER  attribute for any stored object. (That is, the statement fails if creating an account would cause the account to adopt a currently orphaned stored object.) 

In certain situations, it may be necessary to deliberately execute those account-management statements even when they would otherwise fail. To make this possible, if a user has the SET_USER_ID  privilege, that privilege overrides the orphan object security checks and the statements succeed with a warning rather than failing with an error. 

To obtain information about the accounts used as stored object definers in a MySQL installation, query the INFORMATION_SCHEMA . 

This query identifies which INFORMATION_SCHEMA  tables describe objects that have a DEFINER  attribute: 

mysql> SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ’DEFINER’; 

<img src='content_image/863705.jpg'>

The result tells you which tables to query to discover which stored object DEFINER  values exist and which objects have a particular DEFINER  value: 

•To identify which DEFINER  values exist in each table, use these queries: 

SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.EVENTS; SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.ROUTINES; SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.TRIGGERS; SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.VIEWS; 

The query results are significant for any account displayed as follows: 

•If the account exists, dropping or renaming it causes stored objects to become orphaned. If you plan to drop or rename the account, consider first dropping its associated stored objects or redefining them to have a different definer. 

•If the account does not exist, creating it causes it to adopt currently orphaned stored objects. If you plan to create the account, consider whether the orphaned objects should be associated with it. If not, redefine them to have a different definer. 

To redefine an object with a different definer, you can use ALTER EVENT  or ALTER VIEW  to directly modify the DEFINER  account of events and views. For stored procedures and functions and for triggers, you must drop the object and re-create it to assign a different DEFINER  account 

•To identify which objects have a given DEFINER  account, use these queries, substituting the account of interest for user_name @ host_name : 

SELECT EVENT_SCHEMA, EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS WHERE DEFINER = ’ user_name @ host_name ’; SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE DEFINER = ’ user_name @ host_name ’; SELECT TRIGGER_SCHEMA, TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS WHERE DEFINER = ’ user_name @ host_name ’; SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE DEFINER = ’ user_name @ host_name ’; 

