MySQL Forums
Forum List  »  Views

Granting a view to a view
Posted by: Ed Wiles
Date: September 20, 2010 09:04AM

Hello,

This is about granting privileges on a view. I've created 2 databases with associated users called test1 and test2. In test1 I create a simple view and grant it to test2 as follows:

CREATE VIEW test1_view AS SELECT 123 AS MY_NUMBER;
GRANT SELECT ON test1_view TO test2;

Then in test2 I type SELECT * FROM test1.test1_view and this works fine (returns 123). However, still in test2 I create a view of that select statement and try selecting from it:

CREATE VIEW test2_view AS SELECT * FROM test1.test1_view;
SELECT * FROM test2_view;

This fails with the error: ERROR 1356 (HY000): View 'test2.test2_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them.

Back in test1 I try granting the column explicitly:

REVOKE SELECT ON test1_view FROM test2;
GRANT SELECT(my_number) ON test1_view TO test2;

And in test2 I can now select successfully i.e. SELECT * from test2_view returns 123. The other thing that works is granting select on all tables, i.e. back in test1:

REVOKE SELECT(my_number) ON test1_view FROM test2;
GRANT SELECT ON test1.* TO test2;

And in test2 I can now select successfully.

So, what have I misunderstood about table privileges? I don't really want to use column privileges (requires new grants if the columns change) and obviously I don't want to grant the entire contents of test1 to test2. The same effect occurs if I specify columns explicitly in the CREATE VIEW statements and the SELECT statements.

Version 5.1.37-1ubuntu5.4, same effect with both MyISAM and InnoDB.

Thanks very much

Ed

Options: ReplyQuote


Subject
Views
Written By
Posted
Granting a view to a view
3360
September 20, 2010 09:04AM
1481
September 21, 2010 09:19AM
1537
September 22, 2010 01:33AM
1463
September 28, 2010 04:30PM
2481
September 29, 2010 02:40AM
1638
September 29, 2010 02:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.