distal-attribute
distal-attribute
distal-attribute
distal-attribute

user manager add rights to view please repair

User, date Message
Written by alfiqmiq
7 months ago
Category: General
30 posts since Sun, 02 Mar 14
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
Written by ansgar
7 months ago
5023 posts since Fri, 07 Apr 06
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.
Written by alfiqmiq
7 months ago
30 posts since Sun, 02 Mar 14
"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
Written by ansgar
7 months ago
5023 posts since Fri, 07 Apr 06
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.
Written by ansgar
7 months ago
5023 posts since Fri, 07 Apr 06
Done. See issue #2732 for an earlier discussion about that, and r4717 for modifications.
Written by alfiqmiq
7 months ago
30 posts since Sun, 02 Mar 14
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
Written by alfiqmiq
7 months ago
30 posts since Sun, 02 Mar 14
also ALTER is possible
Written by ansgar
7 months ago
5023 posts since Fri, 07 Apr 06
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?
Written by ansgar
7 months ago
5023 posts since Fri, 07 Apr 06
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?
Written by alfiqmiq
7 months ago
30 posts since Sun, 02 Mar 14
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)).
Written by alfiqmiq
7 months ago
30 posts since Sun, 02 Mar 14
I think view is treated as table for privileges in MySQL database
Written by alfiqmiq
7 months ago
30 posts since Sun, 02 Mar 14
Yep, for example that I wrote before (about SELECT privilege on view), corresponding record is available in information_schema.table_privileges
Written by ansgar
7 months ago
5023 posts since Fri, 07 Apr 06
Please point me to the documentation to which you are referring.
Written by alfiqmiq
7 months ago
30 posts since Sun, 02 Mar 14
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
Written by alfiqmiq
7 months ago
30 posts since Sun, 02 Mar 14
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
Written by ansgar
7 months ago
5023 posts since Fri, 07 Apr 06

alfiqmiq wrote: 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?
Written by alfiqmiq
7 months ago
30 posts since Sun, 02 Mar 14
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)
Written by alfiqmiq
7 months ago
30 posts since Sun, 02 Mar 14
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.


Written by ansgar
7 months ago
5023 posts since Fri, 07 Apr 06
No word about views.
Written by ansgar
7 months ago
5023 posts since Fri, 07 Apr 06
Oh ok didn't See your second post. Well that should be stated in the conjunction with privileges.
Written by alfiqmiq
7 months ago
30 posts since Sun, 02 Mar 14
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.
Written by ansgar
7 months ago
5023 posts since Fri, 07 Apr 06
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.
Written by ansgar
7 months ago
5023 posts since Fri, 07 Apr 06
Done in r4719
Written by alfiqmiq
7 months ago
30 posts since Sun, 02 Mar 14
Thanks al lot, it is fine for me nowsmilehave a nice day
 

Please login to leave a reply, or register at first.