Fine tuning MariaDB grants to allow user to create database but not new users

Question

I have the following initialization script:

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

CREATE USER IF NOT EXISTS 'developer'@'%' IDENTIFIED BY 'devpassword1';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON *.* TO 'developer'@'%'; 
CREATE USER IF NOT EXISTS 'maintainer'@'%' IDENTIFIED BY 'maintainerpw1';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON *.* TO 'maintainer'@'%';
FLUSH PRIVILEGES;

which I mount to a docker container running mariadb using the volume mounts in a docker-compose.yml file

services:
  mariadb:
    image: mariadb:10.7.3
    container_name: sql-db
    volumes:
      - ./mariadb/init-scripts:/docker-entrypoint-initdb.d

Requirements

I am looking to grant developer and maintainer most of R/W grants but they MAY NOT create new users. That should be possible only via admin user.

Trials

I tried adding CREATE USER to both the maintainer/developer users and it works which what I do not wish to have.

This was possible by doing:

docker exec -it sql-db mariadb -u developer -p

docker exec -it sql-db mariadb -u maintainer -p

and in both cases CREATE USER dummy1@'%' actually creates the user. Is there a fine tuning possible for mariadb Grants on user to let them create databases but not users?

danblack · Accepted Answer · 2022-06-03 05:02:54Z

The creation of users requires insert privileges on mysql.global_priv. The restriction to avoid this privilege is hard without negative grants (coming sometime in the near future with MDEV-14443). This is because a *.* privileged is needed to create arbitrary databases (unless you want to namespace restrict the database to a non-mysql prefix grant all on u_*.* to maintainer).

What you can do is create a procedure like mysql.create_db and

MariaDB [mysql]> create or replace procedure
    mysql.create_db(IN dbname VARCHAR(30))
    SQL SECURITY DEFINER
    execute immediate concat('create database ',dbname);
Query OK, 0 rows affected (0.013 sec)

MariaDB [mysql]> call mysql.create_db('catdog');
Query OK, 1 row affected (0.001 sec)

MariaDB [mysql]> create role maintainer;
Query OK, 0 rows affected (0.005 sec)

MariaDB [mysql]> grant execute on procedure mysql.create_db to maintainer;
Query OK, 0 rows affected (0.003 sec)

Though with containers you might be just able to create a database instance per DB and ignore the complexities of multiple databases together.

Collectives™ on Stack Overflow

Fine tuning MariaDB grants to allow user to create database but not new users

Requirements

Trials

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Requirements

Trials

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related