• MySQL知识库 :: security
  • Security issues with Federated Tables

  • Discussion

    If the table requester is created in the same schema as the other tables we have a documented security issue: Any user who is allowed to call the SHOW CREATE TABLE command can see the password from the remote database. This might be not wanted in any case:

     
    SHOW CREATE TABLE requester \G

*************************** 1. row ***************************
       Table: requester
Create Table: CREATE TABLE `requester` (
  `a` int(10) unsigned NOT NULL auto_increment,
  `b` varchar(32) default NULL,
  PRIMARY KEY  (`a`),
  KEY `b_i` (`b`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1
CONNECTION='mysql://root:*secret*@master:3320/test/provider'
1 row in set (0.00 sec)

    To avoid this you can simply move this table into a schema where the users do not have access to and create some VIEWS into the users schema:

    CREATE SCHEMA users;

USE users;
    CREATE VIEW requester_v AS
SELECT * FROM xyz.requester;

CREATE USER dummy@'localhost' 
IDENTIFIED BY 'dummy';

GRANT SELECT ON users.requester_v 
TO dummy@localhost;

    Switch to user dummy

    USE xyz;
ERROR 1044 (42000): 
Access denied for user 
'dummy'@'localhost' to database 'xyz'

SHOW CREATE TABLE xyz.requester;
ERROR 1142 (42000): SELECT command denied 
to user 'dummy'@'localhost' for table 'requester'

SELECT * 
FROM requester_v 
LIMIT 3;

+----+---------+
| a  | b       |
+----+---------+
|  1 | Apfel   |
|  2 | Birne   |
|  3 | Pflaume |
+----+---------+
3 rows in set (1.92 sec)