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......
Which database are you using?
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.
Your last scenario makes a little more sense since it looks like root is the definer of the view. User rreis will not be able to view the definition column because he is not the definer. I think this is an old MySQL bug that goes back many years and looks like it is still not fixed. I don't know how Workbench has access other then it might be using "show create view" to get the view definition. Something like "show create view loja_abola.teste" This is currently not available in Aqua Data Studio. I opened issue #15766 in our tracking system to investigate this as a bug to be fixed in a future release.
Issue #15766 is here... https://www.aquaclusters.com/app/home/project/public/aquadatastudio/issue/15766
Thanks a lot Tom for the support.