Home / server / Questions / 403438
In Process
profy
Asked: 2012-06-30 07:11:31 +0800 CST

why need select privileges on *.* to use view?

  • 772

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.

mysql

3 Answers

  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.

    • 1

  2. 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

  3. 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

    • 0