So, you want to have only system root access to mariadb root, and you configure /etc/sudoers so that other users will be able to run mysql client as root, but not any other commands?

Yes, in this case chroot approach will work and will do what you need.

A more database centric approach would be to create a role, say, "admin" and grant it to those users that you want to be root-like. Say,

CREATE ROLE admin;

GRANT ALL PRIVILEGES ON *.* TO admin;

GRANT admin TO tom, dick, harry;

After that, say, Tom will be able to login without a password as tom, and then he'll be able to set the admin role to do root stuff:

tom ~ $ mysql

MariaDB> SHOW BINARY LOGS;

ERROR ... permission denied to user `tom`@`localhost`

MariaDB> SET ROLE admin;

MariaDB> SHOW BINARY LOGS

+--------------------------+

...

That is, a user can login without a password as himself — just as you like. And then the user can assume the superuser role if needed, but can also work as non-superuser normally, so it doesn't mean that Tom becomes a superuser. Pretty much like you'd have with sudo and /etc/sudoers approach. But without a dangerous system root access.

Less invasive than chroot that doesn't require elevated privs, set the process rlimit to 1

diff --git a/client/mysql.cc b/client/mysql.cc

index 15325aec69d..c161d8b0f8b 100644

--- a/client/mysql.cc

+++ b/client/mysql.cc

@@ -45,6 +45,7 @@

 #if defined(USE_LIBEDIT_INTERFACE) && defined(HAVE_LOCALE_H)

 #include <locale.h>

 #endif

+#include <sys/resource.h>

 const char *VER= "15.1";

@@ -1121,6 +1122,11 @@ int main(int argc,char *argv[])

   DBUG_ENTER("main");

   DBUG_PROCESS(argv[0]);

+  struct rlimit rlimit;

+

+  rlimit.rlim_cur= rlimit.rlim_max= 1;

+  setrlimit(RLIMIT_NPROC, &rlimit);

MariaDB [(none)]> \! ls

MariaDB [(none)]> \! ls

MariaDB [(none)]> \! echo ya

Sure some error handling is needed here.

clone3({flags=CLONE_VM|CLONE_VFORK, exit_signal=SIGCHLD, stack=0x7fd064f46000, stack_size=0x9000}, 88) = -1 EAGAIN (Resource temporarily unavailable)

...

Other non-code solutions:

• Custom selinux/apparmor profile for the mariadb client
• run under restricted shell (if the remaining privs are acceptable)
• firejail
• run mariadb client in container podman run -v /var/run/mysqld:/run/mysqld --rm mariadb:10.5 mariadb

• nice trick with rlimit. Doesn't protect against \T overwriting system files. And against load data local reading them.
• I don't see how restricted shell can help
• custom selinux profile might work
• haven't used firejail or podman, but likely will have the same issue as using chroot for a non-code solution (that is, running chroot /restricted mysql) — it needs a complete execution environment in /restricted with all needed libraries and config files. So, awkward to use.

Implemented

• new mariadb client command line option: --sandbox which enables the sandbox mode
• new mariadb command sandbox (short form \-) which enables the sandbox mode

Sandbox mode

In the sandbox mode, all mariadb commands that reach out into the file system are not allowed. These commands are: system (short form \!), tee (\T), pager with an argument(\P foo), source (\.), an attempt to use them causes an error. This error can be ignored with --force.

Note that commands pager without an argument (\P) and edit (\e) are still allowed. To prevent the break-out from the sandbox they should be configured to use a safe pager (e.g. with PAGER=less LESSSECURE=1) and a safe editor (e.g. with EDITOR=rnano) or disabled whatsoever with PAGES=/bin/false EDITOR=/bin/false.

There is no command to disable the sandbox mode. If it's enabled in the sql script, it'll be in force until the end of the script, then it'll be automatically disabled.