MySQL Forums
Forum List  »  Views

Re: Granting a view to a view
Posted by: Ed Wiles
Date: September 21, 2010 09:19AM

Hi,

I received some requests for a script in the Newbie form (where I also posted this message by mistake...). To replicate the problem do this:

mysql --host=YOUR_HOST_GOES_HERE --user=root --password=YOUR_ROOT_PASSWORD_GOES_HERE

CREATE DATABASE test1;
CREATE DATABASE test2;
CREATE USER test1 IDENTIFIED BY 'test1';
CREATE USER test2 IDENTIFIED BY 'test2';
GRANT ALL ON test1.* TO test1;
GRANT GRANT OPTION ON test1.* TO test1;
GRANT ALL ON test2.* TO test2;
EXIT

mysql --host=YOUR_HOST_GOES_HERE --user=test1 --password=test1 test1

CREATE VIEW test1_view AS SELECT 123 AS my_number; 
SELECT * FROM test1_view; -- Returns 123
GRANT SELECT ON test1_view TO test2; 
EXIT

mysql --host=YOUR_HOST_GOES_HERE --user=test2 --password=test2 test2

SELECT * FROM test1.test1_view; -- Returns 123
CREATE VIEW test2_view AS SELECT * FROM test1.test1_view; 
SELECT * FROM test2_view; -- Returns ERROR 1356 (HY000) (why?)
EXIT

mysql --host=YOUR_HOST_GOES_HERE --user=test1 --password=test1 test1

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

mysql --host=YOUR_HOST_GOES_HERE --user=test2 --password=test2 test2

SELECT * FROM test2_view; -- Returns 123
EXIT

mysql --host=YOUR_HOST_GOES_HERE --user=test1 --password=test1 test1

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

mysql --host=YOUR_HOST_GOES_HERE --user=test2 --password=test2 test2

SELECT * FROM test2_view; -- Returns 123
SELECT VERSION; -- For me this returns 5.1.37-1ubuntu5.4
EXIT

The question is, why does granting table-level privileges return the error, but granting column-level privileges or privileges on all objects does not?

Ed

Options: ReplyQuote


Subject
Views
Written By
Posted
3360
September 20, 2010 09:04AM
Re: Granting a view to a view
1480
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.