1

I am using MariaDB 10.0 multi-source replication for a specific use case.

For security reasons, I would like to prevent ALTER commands on master to replicate (such as CREATE, ALTER, DROP...) whatever user run these commands (even root) but of course let SELECT, INSERT, UPDATE, DELETE commands to replicate....

I do not want to use SET SQL_LOG_BIN=0|1 on client side. In fact, I never want to replicate schema modification.

In practice, I wish I could revoke alter permissions to my replication user (who currently has REPLICATION SLAVE permission)

Is there a way to achieve this?

EDIT 2018-02-19

Since my requirements seems nonsense for some readers, I give some additional information about this use case.

I created one (or more) MariaDB Proxy database(s) with tables using BLACKHOLE Storage Engine. So data is not stored on this proxy server, but binlogs are.

I have other MariaDB servers running the same database schema but with INNODB storage engine that replicates data from proxy server(s) using MariaDB Multi-source Replication.

On the proxy server, I can safely recreate, for example, a table schema with a CREATE OR REPLACE TABLE mytable (id int) ENGINE=BLACKHOLE statement as there is no data stored in it.

But this kind of statement MUST NOT run as is on the "slaves" (which are not real slaves as you noticed) as they must remain in their original storage engine or any other option they may have at the table level.

I can do this by issuing a SET SQL_LOG_BIN=0 before executing my command, but I was looking for a way to make sure that I will not break the slaves in case I forget to do it.

0

APPROACH #1

The easiest thing to do would be to revoke ALTER, DROP, and CREATE from all users except root

UPDATE mysql.user SET alter_priv='N',drop_priv='N',create_priv='N' WHERE user <> 'root';
UPDATE mysql.db   SET alter_priv='N',drop_priv='N',create_priv='N' WHERE user <> 'root';
FLUSH PRIVILEGES;

APPROACH #2

You said that you do not want to issue

SET sql_log_bin = 0;

on the client side. A very adventurous approach would be to use a Stored Procedure to do your ALTER TABLE commands. You could then apply SET sql_log_bin = 0; in the Stored Procedure. In effect, that happens on the server side.

DELIMITER $$

DROP PROCEDURE IF EXISTS `Local_Alter_Table` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Local_Alter_Table`
(
    SQLStatement VARCHAR(2048)
)
BEGIN

    SET sql_log_bin = 0;

    SET @sql = SQLStatement;
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;

    SET sql_log_bin = 1;

END $$

DELIMITER ;

Then, require users to run

CALL Local_Alter_Table('ALTER TABLE ...');

GIVE IT A TRY !!!

  • Thank you for your answer, but that is not a solution for my concern. What I want is to cover the case when I alter schema (with any user, even root!) on the master but I omitted to disable replication (with SQL_LOG_BIN=0). When this arrive, I would like replication to ignore commands (or even break!) to make sure alters are never replicated... – Nicolas Payart Oct 1 '15 at 9:30
  • 4
    But... If you change the schema on the Master but not the Slave, in most cases subsequent actions on the table will break replication. You may be preventing a security problem, but causing a DBA nightmare. – Rick James Oct 24 '15 at 18:46
  • 3
    @RickJames your comment should be an answer. The requirements of this old question (which I just noticed) is pure nonsense. – ypercubeᵀᴹ Feb 18 '18 at 23:25
  • Ok I understand my requirements seems pretty foolish, so I just edited my question to give a bit more explanation of this very specific use case. – Nicolas Payart Feb 19 '18 at 10:39
0

Instead, force users to use a Stored Procedure to do any CREATE/DROP commands. Start by not GRANTing CREATE/DROP permission to users. Have different versions of the SP on the Master vs Blackhole vs Slave. The diffs include a suitable combination of

  • 0 or 1 for SQL_LOG_BIN (Maybe this is unnecessary)
  • Doing, or skipping, the CREATE/DROP.

Create the SP as root and have SECURITY DEFINER so that the SP can actually perform the DDL even though the user cannot.

0

Just to add another use-case for this: Live & archive databases.

Think of a setup where you have a live database that holds e.g. the current year of data in a partitioned table with lots of indexes on a fast storage. Storage space is limited (it's expensive SSDs for example), so after a year the old partitions are dropped. Replicated from this is an archive database on slower storage with less indexes where the partitions should be kept indefinitely. Maybe even using a different storage engine (e.g. MyISAM typically uses less disk space than Innodb).

In this setup you obviously do not want to replicate ALTER TABLE statements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.