grant create database privilege to mariadb user

Question

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.

RiggsFolly · Accepted Answer · 2020-10-22 14:25:50Z

7

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';

edited Oct 22, 2020 at 14:25

answered Oct 22, 2020 at 14:10

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Line Over a year ago

ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number - what is wrong with my password? using it in this answer was fine

Bill Karwin · Accepted Answer · 2020-10-22 14:33:06Z

7

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?

answered Oct 22, 2020 at 14:33

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

2 Comments

DataHearth Over a year ago

My thought is that I might have forget to flush privileges and running the connected user on another terminal. So the grant table might not have been updated... But I learned, that there's no special commands for server privileges :D. Thanks noticing it to me.

Bill Karwin Over a year ago

In my example, I had to disconnect as testy and reconnect after the GRANT to get those privileges. But that's all. It's not necessary to FLUSH PRIVILEGES after GRANT/REVOKE.

Collectives™ on Stack Overflow

grant create database privilege to mariadb user

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related