I have a problem using view with MySQL server 5.0 (5.0.92)
I cannot use view with a user granted like that :
GRANT USAGE ON *.* TO 'testuser'@'' IDENTIFIED BY PASSWORD '**********';
GRANT ALL PRIVILEGES ON `testuser`.* TO 'testuser'@'';
Record in mysql db table :
mysql> select * from db where user='testuser'\G;
*************************** 1. row ***************************
Host:
Db: testuser
User: testuser
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Execute_priv: Y
max_size: 1024
max_size_status: 0
I'm connected like this :
mysql> SELECT USER(),CURRENT_USER();
+-------------------------+-----------------+
| USER() | CURRENT_USER() |
+-------------------------+-----------------+
| [email protected] | testuser@ |
+-------------------------+-----------------+
I can create view, but when I try to select in, I have this messages :
ERROR 1356 (HY000): View 'testuser.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
I can show the view :
mysql> SHOW CREATE VIEW testuser.v;
| View | Create View
| v | CREATE ALGORITHM=UNDEFINED DEFINER=`testuser`@`` SQL SECURITY DEFINER VIEW `testuser`.`v` AS select `testuser`.`t`.`qty` AS `qty`,`testuser`.`t`.`price` AS `price`,(`testuser`.`t`.`qty` * `testuser`.`t`.`price`) AS `value` from `testuser`.`t`
View refer only to table named "t" which is in the same database :
show create table testuser.t;
| t | CREATE TABLE `t` (
`qty` int(11) default NULL,
`price` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
I need to GRANT SELECT ON *.* TO 'testuser'@''
to make select working on the view.
Why ?
Do you know a solution to use VIEW's without the select privileges on . ?
Thanks a lots for your answers.
SHOW CREATE VIEW testuser.v\G
and post this in your question