Getting started with MariaDB Server – Privileges

When talking about MariaDB Server – Privileges, people use grant all and assign a full set of privileges to *.* – to all databases on the database server. This is not very smart so, that is why we assigned a full set of privileges to a single database only.

To understand what we are working with, let’s login to MariaDB Server:

bluegrid-edu:~# mysql

Now, let’s run following command to list out all privileges:

MariaDB [(none)]> show privileges;

Here is the privileges list with context for each and short description:

To exercise the use of privileges let’s create a user called “tester” and allow it to only create tables in the database “testdb”:

Create test user “tester”:

MariaDB [(none)]> create user [email protected] identified by 'tester';

Create test database:

MariaDB [(none)]> create database testdb;

Grant create a privilege to user “tester” on database “testdb”:

MariaDB [(none)]> grant create on testdb.* to [email protected];

Reload privileges:

MariaDB [(none)]> flush privileges;

Let’s test our privileges by creating a database and then deleting it. We should be able to create it but delete command should fail:

• Select database

MariaDB [testdb]> use testdb;

• Create command

MariaDB [testdb]> create table test (id int, name char(255));

• Delete command

MariaDB [testdb]> drop table testdb;

Mission accomplished!

Let’s see how we can assign multiple privileges to a single user:

MariaDB [(none)]> grant create, drop on testdb.* to [email protected];

Now we can test it by creating and deleting table:

• Create command

MariaDB [testdb]> create table test (id int, name char(255));

• Drop command

MariaDB [testdb]> drop table test;

Voila! We have successfully created and deleted the table.

By looking at the table above you can use any privilege list per your use case and apply it as shown in the example.