Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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()  |
+-------------------------+-----------------+
| testuser@10.X.X.X      | 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.

share|improve this question
    
This issue seems to be related on version 5.0 of MySQL, tests with MySQL 5.5 works perfectly. –  profy Jun 29 '12 at 16:05
1  
Does the view testuser.v reference tables outside the testuser database ? –  RolandoMySQLDBA Jul 2 '12 at 15:59
    
No, just tables in the testuser databases. –  profy Jul 3 '12 at 10:16
    
Please run this command : SHOW CREATE VIEW testuser.v\G and post this in your question –  RolandoMySQLDBA Jul 3 '12 at 22:40

3 Answers 3

This depends entirely on how you authenticated

You should run the following

SELECT USER(),CURRENT_USER();
  • USER() reports how you attempted to authenticate in MySQL
  • CURRENT_USER() reports how you were allowed to authenticate in MySQL

In all likelihood, testuser@'' is not a user you can explicitly connect with. The privs for testuser@'' would be grant to an anonymous user.

You may need to try manipulating mysql.db. Run this

SELECT COUNT(1) FROM mysql.db WHERE LEFT(db,4)='test' AND user='';;

If you get a nonzero answer, then run SELECT * FROM mysql.db\G then update which ever columns you need. Then, run FLUSH PRIVILEGES;

In any case, it is good to remember that a VIEW is a table in information_schema.tables WHERE the engine column is NULL. So, make sure the proper priv need to access the underlying tables in testuser database are accessible.

You many need to adjust the SECURITY_TYPE level of the the View.

share|improve this answer
    
I completed my question with your commands, but I not understand if the privs for testuser@'' is granted like a anonymous user, why can I do all I want on my database ? (except view working ...) –  profy Jul 3 '12 at 11:06
    
After adding the user did you flush privileges? –  bahamat Jul 4 '12 at 7:48
    
Yes I made flush privilèges –  profy Jul 4 '12 at 13:43

To see the contents of a view is no different to seeing the contents of a regular table - you need to run a select query against it. It follows therefore that you must grant the user the select right before the user can run a select against a view.

share|improve this answer
    
I have all right needed on tables used by the view. My setup is working fine on MySQL 5.5 but not on MySQL 5.0 . I think that MySQL 5.0 need SELECT privileges on *.* for users to allow them to use the view, but it's very bad for shared hosting :( and upgrade is not a good answer in my case :( –  profy Jul 5 '12 at 12:26

Is testuser.t itself a view that references tables in another database to which the user wouldn't have access?

You could have something like this:

testuser.v -> testuser.t -> otherdatabase.table

Show us the result of SHOW CREATE TABLE testuser.t \G

share|improve this answer
    
I made view as simple as possible with sample from official documentation : dev.mysql.com/doc/refman/5.0/en/create-view.html –  profy Jul 9 '12 at 9:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.