MariaDB: Grant/Revoke Privileges

This MariaDB tutorial explains how to grant and revoke privileges in MariaDB with syntax and examples.

Description

You can GRANT and REVOKE privileges on various database objects in MariaDB. We'll look at how to grant and revoke privileges on tables, function, and procedures in MariaDB.

Grant Privileges on Table

You can grant users various privileges to tables. These permissions can be any combination of SELECT, INSERT, UPDATE, DELETE, INDEX, CREATE, ALTER, DROP, GRANT OPTION or ALL.

Syntax

The syntax for granting privileges on a table in MariaDB is:

GRANT privileges ON object TO user;

privileges

It can be any of the following values:

Privilege	Description
SELECT	Ability to perform SELECT statements on the table.
INSERT	Ability to perform INSERT statements on the table.
UPDATE	Ability to perform UPDATE statements on the table.
DELETE	Ability to perform DELETE statements on the table.
INDEX	Ability to create an index on an existing table.
CREATE	Ability to perform CREATE TABLE statements.
ALTER	Ability to perform ALTER TABLE statements to change the table definition.
DROP	Ability to perform DROP TABLE statements.
GRANT OPTION	Allows you to grant the privileges that you possess to other users.
ALL	Grants all permissions except GRANT OPTION.

object

The name of the database object that you are granting permissions for. In the case of granting privileges on a table, this would be the table name.

user

The name of the user that will be granted these privileges.

Example

Let's look at some examples of how to grant privileges on tables in MariaDB.

For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called websites to a user name techonthenet, you would run the following GRANT statement:

GRANT SELECT, INSERT, UPDATE, DELETE ON websites TO 'techonthenet'@'localhost';

You can also use the ALL keyword to indicate that you wish to grant all permissions except GRANT OPTION to a user named techonthenet. For example:

GRANT ALL ON websites TO 'techonthenet'@'localhost';

If you wanted to grant only SELECT access on the websites table to all users, you could grant the privileges to *. For example:

GRANT SELECT ON websites TO '*'@'localhost';

Revoke Privileges on Table

Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can run a revoke command. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL.

Syntax

The syntax for revoking privileges on a table in MariaDB is:

REVOKE privileges ON object FROM user;

privileges

It can be any of the following values:

Privilege	Description
SELECT	Ability to perform SELECT statements on the table.
INSERT	Ability to perform INSERT statements on the table.
UPDATE	Ability to perform UPDATE statements on the table.
DELETE	Ability to perform DELETE statements on the table.
INDEX	Ability to create an index on an existing table.
CREATE	Ability to perform CREATE TABLE statements.
ALTER	Ability to perform ALTER TABLE statements to change the table definition.
DROP	Ability to perform DROP TABLE statements.
GRANT OPTION	Allows you to grant the privileges that you possess to other users.
ALL	Grants all permissions except GRANT OPTION.

object

The name of the database object that you are revoking privileges for. In the case of revoking privileges on a table, this would be the table name.

user

The name of the user that will have these privileges revoked.

Example

Let's look at some examples of how to revoke privileges on tables in MariaDB.

For example, if you wanted to revoke DELETE and UPDATE privileges on a table called websites from a user named techonthenet, you would run the following REVOKE statement:

REVOKE DELETE, UPDATE ON websites FROM 'techonthenet'@'localhost';

If you wanted to revoke all permissions (except GRANT OPTION) on a table for a user named techonthenet, you could use the ALL keyword as follows:

REVOKE ALL ON websites FROM 'techonthenet'@'localhost';

If you had granted SELECT privileges to * (ie: all users) on the techonthenet table and you wanted to revoke these privileges, you could run the following REVOKE statement:

REVOKE SELECT ON websites FROM '*'@'localhost';

Grant Privileges on Functions/Procedures

When dealing with functions and procedures, you can grant users the ability to EXECUTE these functions and procedures in MariaDB.

Syntax

The syntax for granting EXECUTE privileges on a function/procedure in MariaDB is:

GRANT EXECUTE ON [ PROCEDURE | FUNCTION ] object TO user;

EXECUTE

It means the ability to execute the function or procedure.

PROCEDURE

It is used when the privilege is being granted on a procedure in MariaDB.

FUNCTION

It is used when the privilege is being granted on a function in MariaDB.

object

The name of the database object that you are granting privileges for. In the case of granting EXECUTE privileges on a function or procedure, this would be the function name or the procedure name.

user

The name of the user that will be granted the EXECUTE privileges.

Example - Function

Let's look at some examples of how to grant EXECUTE privileges on a function in MariaDB.

For example, if you had a function called CalcValue and you wanted to grant EXECUTE access to the user named techonthenet, you would run the following GRANT statement:

GRANT EXECUTE ON FUNCTION CalcValue TO 'techonthenet'@'localhost';

If you wanted to grant ALL users the ability to EXECUTE this function, you would run the following GRANT statement:

GRANT EXECUTE ON FUNCTION CalcValue TO '*'@'localhost';

Example - Procedure

Let's look at some examples of how to grant EXECUTE privileges on a procedure in MariaDB.

For example, if you had a procedure called MariaDBProc and you wanted to grant EXECUTE access to the user named techonthenet, you would run the following GRANT statement:

GRANT EXECUTE ON PROCEDURE MariaDBProc TO 'techonthenet'@'localhost';

If you wanted to grant ALL users the ability to EXECUTE this procedure, you would run the following GRANT statement:

GRANT EXECUTE ON PROCEDURE MariaDBProc TO '*'@'localhost';

Revoke Privileges on Functions/Procedures

Once you have granted EXECUTE privileges on a function or procedure, you may need to REVOKE these privileges from a user in MariaDB. To do this, you can execute a REVOKE command.

Syntax

The syntax for the revoking privileges on a function or procedure in MariaDB is:

REVOKE EXECUTE ON [ PROCEDURE | FUNCTION ] object FROM user;

EXECUTE

It means that the ability to execute the function or procedure is being revoked.

PROCEDURE

It is used when the privilege is being revoked on a procedure in MariaDB.

FUNCTION

It is used when the privilege is being revoked on a function in MariaDB.

object

The name of the database object that you are revoking privileges for. In the case of revoking EXECUTE privileges on a function or procedure, this would be the function name or the procedure name.

user

The name of the user that will be revoked the EXECUTE privileges.

Example - Function

Let's look at some examples of how to revoke EXECUTE privileges on a function in MariaDB.

If you wanted to revoke EXECUTE privileges on a function called CalcValue from a user named techonthenet, you would run the following REVOKE statement:

REVOKE EXECUTE ON FUNCTION CalcValue FROM 'techonthenet'@'localhost';

If you had granted EXECUTE privileges to * (all users) on the function called CalcValue and you wanted to revoke these EXECUTE privileges, you could run the following REVOKE statement:

REVOKE EXECUTE ON FUNCTION CalcValue FROM '*'@'localhost';

Example - Procedure

Let's look at some examples of how to revoke EXECUTE privileges on a procedure in MariaDB.

If you wanted to revoke EXECUTE privileges on a procedure called MariaDBProc from a user named techonthenet, you would run the following REVOKE statement:

REVOKE EXECUTE ON PROCEDURE MariaDBProc FROM 'techonthenet'@'localhost';

If you had granted EXECUTE privileges to * (all users) on the procedure called MariaDBProc and you wanted to revoke these EXECUTE privileges, you could run the following REVOKE statement:

REVOKE EXECUTE ON PROCEDURE MariaDBProc FROM '*'@'localhost';