2

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.

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

3 Answers 3

1

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.

3
  • 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, 2012 at 11:06
  • After adding the user did you flush privileges?
    – bahamat
    Jul 4, 2012 at 7:48
  • Yes I made flush privilèges
    – profy
    Jul 4, 2012 at 13:43
1

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.

1
  • 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, 2012 at 12:26
0

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

1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .