thanks for replying!
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
>> 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
It gets logged like this:
DROP FUNCTION IF EXISTS `sampleFunc1`
# at 209
#130326 7:25:10 server id 2 end_log_pos 524 Query thread_id=2
CREATE DEFINER=`root`@`localhost` FUNCTION `sampleFunc1`(a INT, b INT)
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