Bug #36799 Expanding user_info table to support hosts
Submitted: 19 May 2008 13:32 Modified: 31 Mar 2009 12:25
Reporter: Marius Petravicius Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Workbench Severity:S4 (Feature request)
Version:6.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: host, hosts, user, user info, user info hosts

[19 May 2008 13:32] Marius Petravicius
Description:
It would be nice if Administrator had the ability to save user_info not only 
for certain user but also for certain host.

This is needed because there are situation when some service connects 
to MySQL from several hosts and it would be useful to save the host information.

How to repeat:
Let's add some user `service_stats` with some information.
After this we get:

SELECT `User`, `Full_name`, `Description` FROM `user_info` WHERE User='service_st';

+-------------+---------------+-------------------------------+
| User        | Full_name     | Description                   |
+-------------+---------------+-------------------------------+
| service_st  | Stats Service | Service for generating stats. |
+-------------+---------------+-------------------------------+
1 row in set (0.00 sec)

If we'll try to edit user info for service_st@192.168.0.1 and service_st@192.168.0.2 
we'll end up having the same information in `user_info` table.

Sure, we could try adding everything to `Description` like this:
service_st@192.168.0.1 - Front end server for Stats;  
service_st@192.168.0.2 - Backend service for Stats.

But this workaround is not so atomic and breaks First NF.

Suggested fix:
Creating new table `user_info_hosts` for possible couple hosts:

CREATE TABLE IF NOT EXISTS `user_info_host` (
  `Host` char(60) collate utf8_bin NOT NULL,
  `User` varchar(16) collate utf8_bin NOT NULL,
  `Full_name` varchar(60) collate utf8_bin default NULL,
  `Description` varchar(255) collate utf8_bin default NULL,
  `Email` varchar(80) collate utf8_bin default NULL,
  `Contact_information` text collate utf8_bin,
  `Icon` blob,
  PRIMARY KEY  (`Host`,`User`),
  KEY `user_info_Full_name` (`Full_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Stores additional user host information';

Suggested fix example:

SELECT `User`, `Full_name`, `Description` FROM `user_info` WHERE User='service_st';

+-------------+---------------+-------------------------------+
| User        | Full_name     | Description                   |
+-------------+---------------+-------------------------------+
| service_st  | Stats Service | Service for generating stats. |
+-------------+---------------+-------------------------------+

SELECT `Host`, `User`, `Full_name`, `Description` FROM `user_info_host` WHERE User='service_st';

+-------------+-------------+---------------+--------------------------------+
| Host        | User        | Full_name     | Description                    |
+-------------+-------------+---------------+--------------------------------+
| 192.168.0.1 | service_st  |               | Front end server for Stats     |
+-------------+-------------+---------------+--------------------------------+
| 192.168.0.2 | service_st  |               | Backend service for Stats      |
+-------------+-------------+---------------+--------------------------------+
| 192.168.2.% | service_st  |               | Second Network users for Stats |
+-------------+-------------+---------------+--------------------------------+
[12 Jan 2009 13:58] Susanne Ebrecht
Many thanks for writing a feature request. The full functionality from MySQL Administrator will be implemented into Workbench. Therefor this is a good hint and we will discuss this during implementation into Workbench.
[31 Mar 2009 12:25] Susanne Ebrecht
Many thanks for writing a feature request.

We discussed this feature request and decided that we won't implement this.