Bug #6183 global privileges not showing on servers > 4.1.4
Submitted: 20 Oct 2004 18:12 Modified: 3 Dec 2004 16:51
Reporter: Tod Carrier Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Administrator Severity:S2 (Serious)
Version:1.0.13 OS:Microsoft Windows (Win 2000)
Assigned to: CPU Architecture:Any

[20 Oct 2004 18:12] Tod Carrier
Description:
The global privileges are not visible.  When in User Admin, you can look at privileges on all levels, each in a separate tab.  The schema and table/column privileges dialogs work OK.  In the global tab, neither assigned or available privileges are visible.

A colleague and I did some research and we believe this may be related to the system tables' character set being utf8.  My servers still use default character set of latin1, but the catalog/privilege tables are all created with utf8.  I believe this change occurred in 4.1.4.

ANOTHER RELATED  ISSUE: the 'user_info' table that Administrator creates is now a different character set than the other catalog/privilege tables.  I cannot join 'user' and 'user_info' because of a character set error.  I think you should create the 'user_info' table with the character_set_system character set, NOT the character_set_server character set.

How to repeat:
Open Administrator, we checked 1.0.12 and 1.0.13, against a server > 4.1.4.  We saw the problem on a 4.1.6 server and a 5.0 server.

Suggested fix:
none
[20 Oct 2004 19:45] Matthew Lord
Hi,

I'm testing this on windows 2000 using 1.0.13.  I have no "Global" tab when looking at user
privileges.  Is this what you're seeing as well?  It sounds like you have the tab but it's not 
working.

Best Regards
[20 Oct 2004 20:00] Tod Carrier
When I choose the User Administration task, the dialog have four tabs.  You are first focused on the User Information tab, then you need to choose the Global Privileges tab, which is the second tab to the right.  IF you are not seeing the tab, maybe you are not logged in as a SUPER user??

Here is what I see on a v4.1.6 server: sorry, but I cannot cut and paste here...
[20 Oct 2004 20:10] Tod Carrier
I just noticed that the global privileges appear when you ADD a new login through the New User wizard.  However, when viewing that same user later, the global privileges are not there.
[20 Oct 2004 20:17] Tod Carrier
I am talking to my manager, who has done some additional testing.  She thinks this may be incompatible collations, as a result of the first part of the SELECT statement using some '+' characters.  We pulled the code executed by Administrator and deleted the first UNIONed SELECT, the one with the '+'s and it worked, according to her.
[20 Oct 2004 21:06] Tod Carrier
BTW, I am not connecting to a localhost server.  Maybe this helps...
* MySQL Administrator is running on Win 2000.
* MySQL server on Red Hat Enterprise Linux v3.0.
[26 Oct 2004 18:47] Matthew Lord
Hi Tod,

I was unable to repeat this on my windows 2000 machine.  Hopefully
somebody else will be able to.  I still never get the global tab.

Best Regards
[26 Oct 2004 19:50] Tod Carrier
You SHOULD see the Global Privileges tab.  Maybe you should put in your own bug report?  It is a standard part of tool that you should see.

Good news!  I just loaded MySQL v4.1.7 and connected.  The global privileges are working again!  They appear to be right!  Might be too soon to close the bug report, but it looks promising.
[26 Oct 2004 20:16] Tod Carrier
OOPS, I spoke too quickly in my last comment.  The upgrade to v4.1.7 does NOT fix this bug!  The server I was looking at had been converted to latin1 character set and fixed during my testing on the bug.

Please refer back to the code fix suggestion that worked for us and that I put in an earlier comment.  Thanks.
[29 Oct 2004 19:14] Ralph Loizzo
I am also having the same problem after installing 4.17 as well.

Can anyone walk me through the process of making this work properly?
[29 Oct 2004 21:23] Tod Carrier
I proposed a code fix to the MySQL Administrator (MA) code that pulls the global permissions data.  That would only work for the MA developers.

I also had a workaround that is tactical, not strategic.  Before v4.1.5, if you use the default settings like most people, all tables were created with the latin1 character set.  Starting in v4.1.5, if you use the default settings like most people, the system tables use the utf8 character set, but all other tables are created with the latin1 character set.  SO, a workaround is to change the character set of the system tables in the mysql database to all be latin1.  The downside of this: you are not "standard" anymore, not getting the benefits of utf8 (what are they really?), may cause problems when this bug gets fixed.

We are waiting and not making any changes to the tables.  We assume that MySQL will think through the problems created by the character set change and solve them quickly.
[2 Nov 2004 14:59] Ron Wheeler
In Windows 2000 I do not have the global settings tab.
How does it figure out who is a "super user"? 
I am an "Administrator" for this PC. 
It is the only machine in its workgroup.
[2 Nov 2004 15:18] Tod Carrier
The 'Global Privileges' tab controls global access privileges for all users.  I originally thought that the system might check the mysql.user.super_priv column or something similar to control the display of this tab.

The tab's presence in User Administration is controlled by a MySQL Administrator option.  The exact route to the option changed between v1.0.12 and v1.0.13.  In v1.0.13 choose Tools from the menu bar, then Options from the drop-down menu.  The Options dialog appears.  Choose the Administrator category from the left column.  In the User Administration area, there are two check boxes - one controls the Global tab and another the Table/Column tab.  If you are an administrator, both should be checked.
[3 Nov 2004 16:18] Michael G. Zinner
Could you verify with the latest release 1.0.14? This issue should be fixed now.
[3 Nov 2004 16:51] Tod Carrier
At your request, I installed MA v1.0.14.

I see the listed global privileges for v4.1.7 servers.  I upgraded all my v4.1.5 and v4.1.6 servers to v4.1.7 since I entered the bug report.  I can add global privileges and remove them in the MA GUI and they are accurately displayed.

Looks like the bug is fixed.  Thanks.
[14 Feb 2005 22:54] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".