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.


OS Version: Ubuntu Os 18.04.3 (LTS) x64
MariaDB Version: 10.1.44

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:

AlterTablesTo alter the table 
Alter routineFunctions, ProceduresTo alter or drop stored functions/procedures 
CreateDatabases, Tables, IndexesTo create new databases and tables 
Create routineDatabasesTo use CREATE FUNCTION/PROCEDURE
Create temporary tablesDatabasesTo use CREATE TEMPORARY TABLE    
Create viewTablesTo create new views
Create userServer AdminTo create new users
DeleteTablesTo delete existing rows
DropDatabases, TablesTo drop databases, tables, and views  
EventServer AdminTo create, alter, drop and execute events  
ExecuteFunctions, ProceduresTo execute stored routines
FileFile access on the serverTo read and write files on the server  
Grant optionDatabases,Tables,Functions,ProceduresTo give to other users those privileges you possess
IndexTablesTo create or drop indexes  
InsertTablesTo insert data into tables
Lock tablesDatabasesTo use LOCK TABLES (together with SELECT privilege)
ProcessServer AdminTo view the plain text of currently executing queries
ProxyServer AdminTo make proxy user possible  
ReferencesDatabases, TablesTo have references on tables  
ReloadServer AdminTo reload or refresh tables, logs, and privileges
Replication clientServer AdminTo ask where the slave or master servers are
Replication slaveServer AdminTo read binary log events from the master
SelectTablesTo retrieve rows from a table
Show databasesServer AdminTo see all databases with SHOW DATABASES 
Show viewTablesTo see views with SHOW CREATE VIEW
ShutdownServer AdminTo shut down the server
SuperServer AdminTo use KILL thread, SET GLOBAL, CHANGE MASTER, etc. 
TriggerTablesTo use triggers
Create tablespaceServer AdminTo create/alter/drop tablespaces
UpdateTablesTo update existing rows
UsageServer AdminNo privileges – allow connect only
Table of 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;
MariaDB Server - Privileges | Drop table denied
Drop table denied

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;
MariaDB Server - Privileges | Drop table success
Drop table success

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.

Share this post

Share this link via

Or copy link