8/18/2019 2 Mariadb Grant

1/3

MariaDB GRANT

Let’s practice with some examples of using MariaDB GRANT statement to have a better

understanding.

If you want to create a super  account that can do anything including being able to grant

 privileges to other users, you can use the following statements:

MariaDB

12

3

CREATE USER 'super'@'localhost' IDENTIFIED BY 'SecurePass1';

GRANT ALL ON *.* TO 'super'@'localhost' WITH GRANT OPTION;

The ON *. *  clause means all databases and all objects in the databases. The only limitation of

the super  user is that it can only connect to the database server from the l ocal host , whichmakes the MariaDB server more secure.

To create a user that has all access in the classicmodels database and can connect from any hostyou use the following statements:

MariaDB

1

23

CREATE USER 'super2'@'%' IDENTIFIED BY 'SecurePass2';

GRANT ALL classicmodels.* TO 'super2'@'%' WITH GRANT OPTION;

You can grant multiple privileges using a single GRANT statement. For example, you can create a

user that can execute the SELECT, INSERT and UPDATE statements against thecl assi cmodel s  sample database using the following statements:

MariaDB

12

3

CREATE USER 'rfc'@'%' IDENTIFIED BY 'SecurePass3';

GRANT SELECT, UPDATE, DELETE ON classicmodels.* TO 'rfc'@'%';

Available privileges to use with MariaDB GRANT

The following table illustrates all privileges available in MariaDB.

Privilege Description

ALL [PRIVILEGES] Grant all privileges at specified access level except GRANT OPTION

ALTER Allow to use of ALTER TABLE statement

8/18/2019 2 Mariadb Grant

2/3

Privilege Description

ALTER ROUTINE Allow user to alter or drop stored routine

CREATE Allow user to create database and table

CREATE ROUTINE Allow user to create stored routine

CREATETABLESPACE Allow user to create, alter or drop tablespaces and log file groups

CREATE

TEMPORARYTABLES

Allow user to create temporary table by using CREATETEMPORARY TABLE

CREATE USERAllow user to use the CREATE USER, DROP USER, RENAMEUSER, and REVOKE ALL PRIVILEGES statements.

CREATE VIEW Allow user to create or modify view

DELETE Allow user to use DELETE

DROP Allow user to drop database, table and view

EVENT Allow user to schedule events in Event SchedulerEXECUTE Allow user to execute stored routines

FILE Allow user to read any file in the database directory.

GRANT OPTIONAllow user to have privileges to grant or revoke privileges from otheraccounts

INDEX Allow user to create or remove indexes.

INSERT Allow user to use INSERT statement

LOCK TABLESAllow user to use LOCK TABLES on tables for which you have the

SELECT privilege

PROCESS Allow user to see all processes with SHOW PROCESSLIST statement.

PROXY Enable user proxying

REFERENCES Not implemented

RELOAD Allow user to use FLUSH operations

REPLICATIONCLIENT

Allow user to query to see where master or slave servers are

REPLICATIONSLAVE

Allow the user to use replicate slaves to read binary log events from themaster.

SELECT Allow user to use SELECT statement

SHOW DATABASES Allow user to show all databases

SHOW VIEW Allow user to use SHOW CREATE VIEW statementSHUTDOWN Allow user to use mysqladmin shutdown command

SUPERAllow user to use other administrative operations such as CHANGEMASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and

mysqladmin command

TRIGGER Allow user to use TRIGGER operations.

UPDATE Allow user to use UPDATE statement

8/18/2019 2 Mariadb Grant

3/3

Privilege Description

USAGE Equivalent to “no privileges”