If i create a view teste with a user let´s say rreis and then connect as root user i cannot alter view because it return only the following:
CREATE OR REPLACE VIEW `teste`AS
If i connect again as rreis i can see the following:
CREATE OR REPLACE VIEW `teste`ASselect `listaprodutos`.`IdProduto` AS `IdProduto`,`listaprodutos`.`Ordem` AS......
It looks like root does not have access to the information in information_schema.views. We extract the view definition from information_schema.views like below. You can try that query and see if you get information back using root. Perhaps, you just need to grant access to root.
SELECT table_schema, table_name, view_definition, check_option, is_updatable, definer, security_type FROM information_schema.views WHERE table_name = 'teste'
If i run as root i get this:
If i run as rreis i get:
But in MySql Workbench i can run alter view with both user without problem. I´m not an expert on MySQL because i use a lot MS SQL, but it seems to me that the root user witch act as a superuser should be able to view and alter everything.
Powered by IDERA