You more likely know how to create a database user using PhpMyAdmin or some other administrative tool. But sometimes we need to create a database user and add privileges manually. Here is an example SQL which does just that:
Adding MariaDB database user, managing his privileges and assigning database user to database:
Short version:
— add user
— configure parameters
— grant all privileges to databse
CREATE USER 'someuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('***'); -- replace *** with password
GRANT USAGE ON *.* TO 'someuser'@'localhost' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
GRANT ALL PRIVILEGES ON `somedatabase`.* TO 'someuser'@'localhost';Add / GRANT only specific privileges to DB user
GRANT SELECT, INSERT, UPDATE, CREATE, DELETE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, EVENT, TRIGGER, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EXECUTE ON *.* TO 'someuser'@'localhost' REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;Edit / REVOKE database user privileges in MariaDB SQL example
REVOKE ALL PRIVILEGES ON `somedatabase`.* FROM 'someuser'@'localhost'; GRANT ALL PRIVILEGES ON `somedatabase`.* TO 'someuser'@'localhost' WITH GRANT OPTION;Or to be more specific, we could do the same with the fallowing:
REVOKE ALL PRIVILEGES ON `somedatabase`.* FROM 'someuser'@'localhost'; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, EVENT, TRIGGER, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON `somedatabase`.* TO 'someuser'@'localhost' WITH GRANT OPTION;MariaDB database user require SSL
You can replace REQUIRE NONE directive and force a connection constraint on a database user like SSL, x509. See example code below
... REQUIRE SSL ...Or you could define all parameters manually like so:
... REQUIRE CIPHER 'a' AND ISSUER 'b' AND SUBJECT 'c' ...Remove / DROP MariaDB database user
Remove the database user with the fallowing SQL:
USE somedatabase;
DROP USER 'someuser'@'localhost'; -- DROP USER IF EXISTS ...https://mariadb.com/kb/en/alter-user/
https://mariadb.com/kb/en/drop-user/