user manager add rights to view please repair

If I try to add for example select right for certain user to view i've got "Objects of type View cannot be part of privileges" but this is false,

when I add this with SQL query it is visible in "object" part of user manager

Please read issue #2732. You can grant privileges on a view, but MySQL then assumes this is a table, so that does not have an effect.

"VIEWS are not supported in the GRANT syntax"

maybe this explain better what i observed (and using to get access to some data for some users)

1. CREATE DATABASE mydb;
2. USE mydb;
3. CREATE TABLE mytable ( id INT(10), name CHAR(50) );
4. CREATE VIEW viewMytable AS SELECT name FROM mytable;

5. When I add viewMytable in "User manager" to certain (existing) user - Error "Objects of type View cannot be part of privileges" occur

so...

6. I use SQL query: gRANT SELECT ON mydb.viewMytable TO 'someuser'@'loclahost';
7. FLUSH PRIVILEGES;

8. Now open "User manager" and what I see...? my view (viewMytable) is assigned to "someuser" as table, and from now I can do any modyfications of privileges on this in "User manager".

and then

When I connect to MySQL as someuser I can do queries only on viewMytable.

So my suggestion is to add option to grant privileges on view as I described above.
This is a cosmetic change, but increasing usability of the "user manager" window

I understood you quite right. I was convinced there are no privilege names like SELECT, INSERT, DROP... for views. But there are 3 privs: CREATE VIEW, SHOW VIEW and DROP, since MySQL 5.0.1, stated here: http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html

I wonder why the heck there is no syntax for "GRANT ... ON *VIEW* xyz...". That way, HeidiSQL won't be able to distinct between table and view privileges. Instead, they will be displayed as table objects, unless I'm examining existing objects in the given database.

Done. See issue #2732 for an earlier discussion about that, and r4717 for modifications.

first of all when You add view (r4718) You can't remove it from certain user in "user manager"

second thing referring to previous message ...
(I'm using MySQL 5.1.30 and 5.5.29)

show grants for 'someuser'@'loclahost';

i see:

GRANT SELECT ON `mydb`.`viewMytable` TO 'someuser'@'loclahost'

because the view is treated as a table (obviously with some limitations) except permissions those mentioned by you also can be given SELECT, INSERT, DELETE, UPDATE

also ALTER is possible

This is highly confusing. Please read the documentation: http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html
You will find the 3 privileges which Heidi now provides for the context "views". If there is more, why isn't that stated somewhere? Is it just an incomplete documentation or what?

Also, there is a table called "table_privs" in the "mysql" database, but no "view_privs". So I thought I follow the docs and implement the 3 privileges mentioned there with the context "views". Now you say there is also a SELECT privilege, and this seems true, but this is nowhere stated.

As there is no "view_privs" table, a GRANT to a view stores in the "table_privs" instead. But naturally, those views can have *all* privileges a table can have, such as DELETE and INSERT. If you grant INSERT to a user, but disallow INSERT on the referenced table, then what has precedence?

Understand why this is confusing?

From documentation:

SELECT Select_priv tables or columns (and what have view, isn't it columns?)

I suppose that privileges described "view" is only for "view" but You can do SELECT on tables or on views am I right? In this case SELECT privileges fits to table and view. Also other privileges like that I described before. You can check this empirically.

Mostly I use views as a separate data space where you can perform only SELECT query (when I want to present only few columns from table or if I want do view as complicated query that joins few tables). I give SELECT permission on the view but not on the table(s), and MySQL handles this well. Also this works for INSERT (in simple views)

And i checked that if INSERT on view isn't possible MySQL reports an ERROR so it is safe as I think (this happens if viewe is complicated query that joins few tables or in view isn't visible indexed (key) column)).

I think view is treated as table for privileges in MySQL database

Yep, for example that I wrote before (about SELECT privilege on view), corresponding record is available in information_schema.table_privileges

Please point me to the documentation to which you are referring.

http://dev.mysql.com/doc/refman/5.1/en/grant.html

SELECT Enable use of SELECT. Levels: Global, database, table, column.

INSERT Enable use of INSERT. Levels: Global, database, table, column.


so ... if view is treated as some kind of table object U can grant privileges as select, insert, update... to use of this view for certain user

please try do what I described above (create simple table, then create view and grant privileges on this view to user SELECT for example), you'll see how it looks like in practice

so ... if view is treated as some kind of table object U can ...

You are just speculating about that. Surely I can test it and see how it looks in practice. But I want to have a documentation on that if I shall put something into HeidiSQL, instead of blindly assuming things that are probably a misunderstanding. Every bit and piece in MySQL is documented, so where is the documentation for granting view privileges?

so read http://dev.mysql.com/doc/refman/5.1/en/grant.html

there is answer as i think,

SELECT	Enable use of SELECT. Levels: Global, database, table, column.

enable __USE OF SELECT__ (for example)

http://dev.mysql.com/doc/refman/5.0/en/views.html

Views are stored queries that when invoked produce a result set. A view acts as a virtual table.

No word about views.

Oh ok didn't See your second post. Well that should be stated in the conjunction with privileges.

I reported privileges for views because I think you've done a good job with heidi, from time to time I allow myself to report something that will improve the functionality of the program.

However, if you don't want to add permissions like SELECT, INSERT, UPDATE, DELETE to view in "User manager", please, restore the old service for User manager.

I will grant permissions using SQL query and then I will change them freely in the user manager. This what is now is decreasing usability of User manager.

I'm willing to do that, believe me. It's only so weird that privileges for views are not documented. The mysql documentation is huge and perfectly written, I can't believe they just forgot to say a word about views when granting stuff.

I think it's the best to revert my previous commit and just remove the warning you meant initially, so you can use the user manager to add views just like tables.

Done in r4719

Thanks al lot, it is fine for me nowhave a nice day