GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, SHOW DATABASES, CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE ON *.* TO 'yar'@'%' IDENTIFIED BY PASSWORD <secret>

REVOKE DROP ON *.* FROM 'yar'@'%';

-- подключился как root
> CREATE DATABASE `test_drop` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
> CREATE USER 'yar'@'%' IDENTIFIED BY '1234';
> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, SHOW DATABASES, CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE ON *.* TO 'yar'@'%' IDENTIFIED BY '1234';
> FLUSH PRIVILEGES;
-- переподключился как yar
> drop database test_drop;
1044 - Access denied for user 'yar'@'%' to database 'test_drop'