[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Possible issues with replication filters
From:       "Radoulov, Dimitre" <cichomitiko () gmail ! com>
Date:       2013-03-26 6:36:24
Message-ID: 515141E8.8030906 () gmail ! com
[Download RAW message or body]

Hi Shawn,
thanks for replying!
Comments in-line.

On 25/03/2013 21:51, shawn green wrote:
> Hello Dimitre,
>
> On 3/24/2013 5:23 PM, Radoulov, Dimitre wrote:
>> [...]
>> The documentation states the following about the replicate-wild-do-table
>> option:
>>
>> ==
>> This option applies to tables, views, and triggers. It does not apply to
>> stored procedures and functions,
>> or events. To filter statements operating on the latter objects, use one
>> or more of the |--replicate-*-db| options.
>> ==
>>
>> Does anybody know how exactly this option doesn't apply for stored
>> procedures, functions or events?
>> Is the creation DDL "skipped" or their execution isn't replicated?
>> [...]
>
> Stored procedures and Functions do not exist at the table level. They 
> only exist at the global (system) level. Therefore there is no way to 
> filter statements like CREATE PROCEDURE or DROP FUNCTION based on 
> table-level substring matches.

I run a few tests (replicating from 5.5 to 5.6) and it seems that even 
only with Replicate_Wild_Do_Table = db_name.%
functions and procedures are replicated. I suppose that it's because 
we're forced to execute "use dbname"
before creating them (otherwise we get ERROR 1046 (3D000): No database 
selected) and because
they are associated to a schema 
(information_schema.routines.routine_schema).
It gets logged like this:

use `sakila`/*!*/;
[...]
DROP FUNCTION IF EXISTS `sampleFunc1`
/*!*/;
# at 209
#130326  7:25:10 server id 2  end_log_pos 524   Query thread_id=2     
exec_time=0     error_code=0
SET TIMESTAMP=1364279110/*!*/;
CREATE DEFINER=`root`@`localhost` FUNCTION `sampleFunc1`(a INT, b INT) 
RETURNS tinyint(1)
[...]

At this point, I don't understand why the documentation states that 
Replicate_Wild_Do_Table doesn't apply
to procedures and functions. Am I missing something?

>
> The key to notice is 'operating on' in the text you quoted. If you 
> limit execution of those DDL statements to just a few databases, then 
> any user with enough privileges that start the DDL command from the 
> context of the permitted database will be able to affect those objects 
> on the slave via replication. If you change one of them on the master 
> and you do it from the context of a database that is not on the 'do 
> list', then that change will not be applied to the slave via replication.
>
> Based on your example, a DBA starting in the db_name1 database or 
> db_name2 database would be able to affect a PROCEDURE or FUNCTION on 
> the master and through replication, the same command would execute on 
> the slave.

Yes, thanks for pointing this out.
The same applies for other type of SQL that spans multiple databases 
(and Replicate_Wild_Do_Table is enabled for those databases).

> Controlling this behavior is one of the uses of the 'principle of 
> least privileges'. In short, it means you give each user just enough 
> rights to do what it is they are supposed to do. For example, you want 
> very few users to have the 'super' privilege or the 'with create 
> option' option on their accounts. In my position I see many servers 
> operating where every user (including applications) are operating with 
> root privileges.  This is as bad for databases as it is for operating 
> systems.

Completely agreed!


Best regards
Dimitre


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql

[prev in list] [next in list] [prev in thread] [next in thread]