[MDEV-10131]  BINLOG FORMAT privilege Created: 2016-05-26  Updated: 2017-05-29

Status: Open
Project: MariaDB Server
Component/s: Authentication and Privilege System
Fix Version/s: None

Type: Task Priority: Minor
Reporter: VAROQUI Stephane Assignee: VAROQUI Stephane
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Choosing from the application layer the type of binlog format can be a requirement for the DBA that would like to enforce a specific default format , but wan't to act differently in some batch or a set of tables used by an application.

Unfortunately the only way to do this is by giving the application the SUPER privilege

set session binlog_format=ROW; break without the super priv .

Giving that the super privilege can lead to data corruption by simply changing the replication position , this is a major security concern that would be address by introducing a BINLOG FORMAT privilege that will only affect the session binlog_format change.
This privilege can be split again in BINLOG FORMAT ROW or BINLOG FORMAT STATEMENT to enable one or the other move , script that specifically need to run statement based like online alter can than be restricted to what format they need without being able to impacting the default by mistake.



 Comments   
Comment by Geoff Montee (Inactive) [ 2016-05-26 ]

You could do this with a stored procedure that is configured with SQL SECURITY DEFINER. e.g.:

DELIMITER //
 
 
 
CREATE DEFINER='root'@'127.0.0.1'
 
PROCEDURE db1.set_binlog_format_row ()
 
SQL SECURITY DEFINER
 
BEGIN
 
SET SESSION binlog_format=ROW;
 
END//
 
 
 
CREATE DEFINER='root'@'127.0.0.1'
 
PROCEDURE db1.set_binlog_format_statement ()
 
SQL SECURITY DEFINER
 
BEGIN
 
SET SESSION binlog_format=STATEMENT;
 
END//
 
 
 
DELIMITER ;
 
 
 
CREATE USER 'non_priveleged_user'@'localhost';
 
GRANT EXECUTE ON PROCEDURE db1.set_binlog_format_row TO 'non_priveleged_user'@'localhost';
 
GRANT EXECUTE ON PROCEDURE db1.set_binlog_format_statement TO 'non_priveleged_user'@'localhost';

Example:

$ mysql -u non_priveleged_user
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 6
 
Server version: 10.1.14-MariaDB MariaDB Server
 
 
 
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> select @@session.binlog_format;
 
+-------------------------+
 
| @@session.binlog_format |
 
+-------------------------+
 
| ROW                     |
 
+-------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [(none)]> SET SESSION binlog_format=STATEMENT;
 
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
 
MariaDB [(none)]> CALL db1.set_binlog_format_statement();
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [(none)]> select @@session.binlog_format;
 
+-------------------------+
 
| @@session.binlog_format |
 
+-------------------------+
 
| STATEMENT               |
 
+-------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [(none)]> CALL db1.set_binlog_format_row();
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> select @@session.binlog_format;
 
+-------------------------+
 
| @@session.binlog_format |
 
+-------------------------+
 
| ROW                     |
 
+-------------------------+
 
1 row in set (0.00 sec)

Comment by VAROQUI Stephane [ 2016-05-26 ]

Thanks for the suggestion , We don't control the java app that have to be deployed on our databases, we just change the JDBC connection string to enable ROW based, the architecture is active active and can not use auto increment offset. Some row event ere needed to replace auto increment with uuid_short . We have a trigger doing this and it work transparently to the application but stay dangerous as the application developers will have access to replication command

Generated at Thu Feb 08 07:39:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.