微调MariaDB权限：允许开发者与维护者创建数据库但禁止创建用户

Hey there, let's tackle this permission issue you're facing with your MariaDB Docker container. Your goal is clear: let developer and maintainer have full access to create databases/tables and manage data, but lock down their ability to create new users—only admin should handle user management.

What Went Wrong Earlier #

You mentioned that after granting CREATE to those users, they could still run CREATE USER successfully. That's likely either a leftover from a previous permission grant (maybe you accidentally added CREATE USER before) or a test mix-up. Here's the key point: in MariaDB, the CREATE permission (for databases/tables) is completely separate from the CREATE USER permission. The former lets users create databases and tables, while the latter is a standalone global permission that's required to create new users. So as long as you don't grant CREATE USER to non-admin users, they won't be able to create new accounts.

Updated Initialization Script #

Let's adjust your SQL script to get the permissions right. We'll keep admin with full privileges, and restrict developer/maintainer to only the permissions they need (no user management capabilities):

-- Admin gets full control, including user creation and granting permissions
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

-- Set up developer with data and object management permissions (no user control)
CREATE USER IF NOT EXISTS 'developer'@'%' IDENTIFIED BY 'devpassword1';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON *.* TO 'developer'@'%';

-- Maintainer gets the same set of permissions as developer (tweak if needed)
CREATE USER IF NOT EXISTS 'maintainer'@'%' IDENTIFIED BY 'maintainerpw1';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON *.* TO 'maintainer'@'%';

-- Apply the permission changes immediately
FLUSH PRIVILEGES;

How to Verify It Works #

1. Log into the MariaDB container as developer:

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

2. Try creating a new user—this should fail:

CREATE USER dummy1@'%';

You'll see an error like this, confirming the restriction is in place:

ERROR 1044 (42000): Access denied for user 'developer'@'%' to database 'mysql'

3. Now test creating a database to make sure their valid permissions work:

CREATE DATABASE test_dev_db;

This should run successfully, so they can still create databases and tables as needed.

Quick Note for Existing Containers #

Remember that MariaDB only runs the init scripts in /docker-entrypoint-initdb.d when the container is first started (i.e., when the data volume is empty). If you already ran the container before, you'll need to wipe the old volume and restart:

docker-compose down -v
docker-compose up -d

内容的提问来源于stack exchange，提问作者user4851126