CODEWITHSUNDEEP
mysqldatabasemariadbprivileges
DataHearth
DataHearth

Reputation: 504

grant create database privilege to mariadb user

I know that in database management, it's a bad behavior to use the root user and I'm setting up a custom user with base privileges (create database, drop db, all access to all newly created dbs, etc).
I've created a user antoine which should be the base user used to login to the mariadb server.

Problem is, I can't figure out how to grant administration privileges like create database xxx, drop database xxx, etc... The documentation explains that to grant a privilege, you run the following command:

GRANT role ON database.table TO user@host

But, when it comes to server privileges, how can I do it ? I've tried the *.* instead of database.table but it's not working.

Upvotes: 7

Views: 12474

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562881

Demo:

I create a user with privilege to connect but nothing else.

mysql> create user 'testy'@'%' identified by 'testy';
Query OK, 0 rows affected (0.02 sec)

I log in as that user and find they have no privilege to create a schema (synonym for database).

$ mysql -utesty -ptesty

mysql> create schema test2;
ERROR 1044 (42000): Access denied for user 'testy'@'%' to database 'test2'

Log in as my admin user and grant them the privilege:

mysql> grant create on *.* to 'testy'@'%';
Query OK, 0 rows affected (0.01 sec)

Now my test user can do it:

$ mysql -utesty -ptesty

mysql> create schema test2;
Query OK, 1 row affected (0.01 sec)

I don't know what you mean by "it didn't work." That's not a good way to describe a problem. What was the command you executed? What happened when you did that command? Did it give an error? If so, what was the error?

Upvotes: 6

RiggsFolly
RiggsFolly

Reputation: 94682

Create a user and then grant that user the privileges you want them to have like this.

CREATE USER 'antoine'@'localhost' IDENTIFIED VIA mysql_native_password USING 'a-password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON *.* TO 'antoine'@'localhost';

Upvotes: 8

Related Questions