Bug #28313 Cluster needs to handle management of user accounts across nodes
Submitted: 8 May 2007 19:04 Modified: 9 May 2007 13:02
Reporter: Gerry Reno Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S4 (Feature request)
Version:5.1.17-beta OS:Any
Assigned to: CPU Architecture:Any

[8 May 2007 19:04] Gerry Reno
Description:
It's difficult to keep up with all the user account creations and password changes on the different SQL Nodes.  Cluster needs to manage user accounts across nodes.

How to repeat:
See description.

Suggested fix:
Put user table under control of the cluster.
[8 May 2007 19:12] Gerry Reno
Our perspective here is from the standpoint of webapps.  Our SQL Nodes run on each webserver in the webserver farm.  When a user accesses their webapp the loadbalancer will direct their request to one of the webservers and its SQL Node.  Since the user needs to authenticate, each SQL Node needs to be in sync as far as user accounts and passwords are concerned.  That is why Cluster needs to be handling all the user accounts in a centralized manner.
[9 May 2007 11:36] Hartmut Holzgraefe
changed category to Server:Privileges as this would require a general "allow other storage engines besides MyISAM for system tables" feature which would not be cluster specific

this won't happen any time soon though, definetly not in 5.1 which is already in feature freeze, so for now user accounts, GRANT information, PROCEDURE definitions etc. need to be replicated seperately, e.g. by using MySQL replication instead (which is not synchronous but when only used for changes on the mysql.* tables slaves should be able to keep up with a sub-second lag)
[9 May 2007 13:02] Gerry Reno
>this won't happen any time soon though, definetly not in 5.1 which is already in
>feature freeze, 

Definitely did not want to hear that.  :-(

>so for now user accounts, GRANT information, PROCEDURE
>definitions etc. need to be replicated seperately, e.g. by using MySQL
>replication instead (which is not synchronous but when only used for changes on
>the mysql.* tables slaves should be able to keep up with a sub-second lag)

So for this workaround using replication, can the whole system tables be replicated?  That is, 'mysql' and 'information_schema'?

As another possible workaround, could the SQL Nodes be run from shared filesystem sharing the same database but using separate pid files?