[Maria-discuss] Privilege Question
We are on MariaDB 10.5.18. There is a requirement to send all syslog data to a central syslog server. In the past, we did it using a login called ITS_READ. It has limited privs on purpose, but used to be able to execute the SET GLOBAL statements that we needed. Those statements are: SET GLOBAL server_audit_output_type=SYSLOG; SET GLOBAL server_audit_logging=1; SET GLOBAL server_audit_syslog_facility=LOG_LOCAL2; SET GLOBAL server_audit_events="connect,table,query_ddl,query_dcl"; Now, it doesn't have the privs needed to do that. In looking online, I found that there is a "new" privilege (in MySQL 8), called system_variables_admin that allows the user to execute SET GLOBAL statements. However, when I try to grant that privilege, I get an error: MariaDB [(none)]> grant system_variables_admin to 'ITS_READ'@'localhost'; ERROR 1959 (OP000): Invalid role specification `system_variables_admin` It seems that the only other option is to grant the SUPER privilege, which we don't want to do. How do I get around this, or do I just tell the sysadmins they can't have the syslog data? Scott Canaan '88 Sr Database Administrator Information & Technology Services Finance & Administration Rochester Institute of Technology o: (585) 475-7886 | f: (585) 475-7520 srcdco@rit.edumailto:srcdco@rit.edu | c: (585) 339-8659 CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.
Hi, Scott, In MariaDB it's SUPER privilege. Starting from 10.5 there are fine-grained privilege for various activities, see https://mariadb.com/kb/en/grant/#global-privileges But there is no one "system_variables_admin" privilege. Different system variables are protected by different privileges, for example, @@gtid_binlog_state is protected by the REPLICATION MASTER ADMIN privilege. SUPER is still left to control access to global variables that don't have a dedicated privilege. This includes all plugin variables too. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org On Apr 06, Scott Canaan wrote:
We are on MariaDB 10.5.18. There is a requirement to send all syslog data to a central syslog server. In the past, we did it using a login called ITS_READ. It has limited privs on purpose, but used to be able to execute the SET GLOBAL statements that we needed. Those statements are:
SET GLOBAL server_audit_output_type=SYSLOG; SET GLOBAL server_audit_logging=1; SET GLOBAL server_audit_syslog_facility=LOG_LOCAL2; SET GLOBAL server_audit_events="connect,table,query_ddl,query_dcl";
Now, it doesn't have the privs needed to do that. In looking online, I found that there is a "new" privilege (in MySQL 8), called system_variables_admin that allows the user to execute SET GLOBAL statements. However, when I try to grant that privilege, I get an error:
MariaDB [(none)]> grant system_variables_admin to 'ITS_READ'@'localhost'; ERROR 1959 (OP000): Invalid role specification `system_variables_admin`
It seems that the only other option is to grant the SUPER privilege, which we don't want to do.
How do I get around this, or do I just tell the sysadmins they can't have the syslog data?
Scott Canaan '88
Sergei,
Thank you. I found SUPER, but was trying to avoid using it as it gives too many privileges. I was looking for something more fine-grained.
Scott Canaan '88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco@rit.edu | c: (585) 339-8659
CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.
-----Original Message-----
From: Sergei Golubchik
We are on MariaDB 10.5.18. There is a requirement to send all syslog data to a central syslog server. In the past, we did it using a login called ITS_READ. It has limited privs on purpose, but used to be able to execute the SET GLOBAL statements that we needed. Those statements are:
SET GLOBAL server_audit_output_type=SYSLOG; SET GLOBAL server_audit_logging=1; SET GLOBAL server_audit_syslog_facility=LOG_LOCAL2; SET GLOBAL server_audit_events="connect,table,query_ddl,query_dcl";
Now, it doesn't have the privs needed to do that. In looking online, I found that there is a "new" privilege (in MySQL 8), called system_variables_admin that allows the user to execute SET GLOBAL statements. However, when I try to grant that privilege, I get an error:
MariaDB [(none)]> grant system_variables_admin to 'ITS_READ'@'localhost'; ERROR 1959 (OP000): Invalid role specification `system_variables_admin`
It seems that the only other option is to grant the SUPER privilege, which we don't want to do.
How do I get around this, or do I just tell the sysadmins they can't have the syslog data?
Scott Canaan '88
Scott Canaan
Thank you. I found SUPER, but was trying to avoid using it as it gives too many privileges. I was looking for something more fine-grained.
Maybe you can define a stored procedure with SQL SECURITY DEFINER (and a DEFINER with the SUPER priviledge) that sets the desired syslog global system variables. Then you can grant the ITS_READ account access to the stored procedure, which will give access only to set the syslog configuration. Hope this helps, - Kristian.
On Apr 06, Scott Canaan wrote:
We are on MariaDB 10.5.18. There is a requirement to send all syslog data to a central syslog server. In the past, we did it using a login called ITS_READ. It has limited privs on purpose, but used to be able to execute the SET GLOBAL statements that we needed. Those statements are:
SET GLOBAL server_audit_output_type=SYSLOG; SET GLOBAL server_audit_logging=1; SET GLOBAL server_audit_syslog_facility=LOG_LOCAL2; SET GLOBAL server_audit_events="connect,table,query_ddl,query_dcl";
participants (3)
-
Kristian Nielsen -
Scott Canaan -
Sergei Golubchik