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 |
+-------------+-------------+---------------+--------------------------------+