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
