Engineering Mathematics Discrete Mathematics Digital Logic and Design Computer Organization and A

MySQL | Grant / Revoke Privileges

Shubrodeep Banerjee

Read Discuss Courses Practice

Granting Privileges

We have already learned about how to create users in MySQL using MySQL |
create user statement. But using the Create User Statement only creates a new
user but does not grant any privileges to the user account. Therefore to grant
privileges to a user account, the GRANT statement is used. Syntax:

GRANT privileges_names ON object TO user;

Parameters Used:

privileges_name: These are the access rights or privileges granted to the user.
object:It is the name of the database object to which permissions are being
granted. In the case of granting privileges on a table, this would be the table
name.
user:It is the name of the user to whom the privileges would be granted.
Privileges: The privileges that can be granted to the users are listed below along

with the description: Let us

now learn about different ways of granting privileges to the users:

1. Granting SELECT Privilege to a User in a Table: To grant Select Privilege to a

table named “users” where User Name is Amit, the following GRANT statement
should be executed.

GRANT SELECT ON Users TO'Amit'@'localhost;

1. Granting more than one Privilege to a User in a Table: To grant multiple

Privileges to a user named “Amit” in a table “users”, the following GRANT
statement should be executed.

GRANT SELECT, INSERT, DELETE, UPDATE ON Users TO 'Amit'@'localhost;

1. Granting All the Privilege to a User in a Table: To Grant all the privileges to a
user named “Amit” in a table “users”, the following Grant statement should be
executed.

GRANT ALL ON Users TO 'Amit'@'localhost;

1. Granting a Privilege to all Users in a Table: To Grant a specific privilege to all

the users in a table “users”, the following Grant statement should be executed.

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

1. In the above example the “*” symbol is used to grant select permission to all the
users of the table “users”.
2. Granting Privileges on Functions/Procedures: While using functions and
procedures, the Grant statement can be used to grant users the ability to
execute the functions and procedures in MySQL. Granting Execute Privilege:
Execute privilege gives the ability to execute a function or procedure. Syntax:

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

Different ways of granting EXECUTE Privileges:

Granting EXECUTE privileges on a function in MySQL.: If there is a function

named “CalculateSalary” and you want to grant EXECUTE access to the user
named Amit, then the following GRANT statement should be executed.

GRANT EXECUTE ON FUNCTION Calculatesalary TO 'Amit'@localhost';

Granting EXECUTE privileges to all Users on a function in MySQL.: If there is a

function named “CalculateSalary” and you want to grant EXECUTE access to all the
users, then the following GRANT statement should be executed.

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

Granting EXECUTE privilege to a Users on a procedure in MySQL.: If there is a

procedure named “DBMSProcedure” and you want to grant EXECUTE access to the
user named Amit, then the following GRANT statement should be executed.

GRANT EXECUTE ON PROCEDURE DBMSProcedure TO 'Amit'@localhost';

Granting EXECUTE privileges to all Users on a procedure in MySQL.: If there is a

procedure called “DBMSProcedure” and you want to grant EXECUTE access to all
the users, then the following GRANT statement should be executed.

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

Checking the Privileges Granted to a User: To see the privileges granted to a user
in a table, the SHOW GRANTS statement is used. To check the privileges granted
to a user named “Amit” and host as “localhost”, the following SHOW GRANTS
statement will be executed:
 SHOW GRANTS FOR 'Amit'@localhost';

Output:

GRANTS FOR Amit@localhost

GRANT USAGE ON *.* TO `SUPER`@localhost`

Revoking Privileges from a Table

The Revoke statement is used to revoke some or all of the privileges which have
been granted to a user in the past. Syntax:

REVOKE privileges ON object FROM user;

Parameters Used:

object: It is the name of the database object from which permissions are being
revoked. In the case of revoking privileges from a table, this would be the table
name.
user: It is the name of the user from whom the privileges are being revoked.
Privileges can be of the following values:

Different Ways of revoking privileges from a user:

1. Revoking SELECT Privilege to a User in a Table: To revoke Select Privilege to a

table named “users” where User Name is Amit, the following revoke statement
should be executed.

REVOKE SELECT ON users FROM 'Amit'@localhost';

1. Revoking more than Privilege to a User in a Table: To revoke multiple

Privileges to a user named “Amit” in a table “users”, the following revoke
statement should be executed.

REVOKE SELECT, INSERT, DELETE, UPDATE ON Users FROM 'Amit'@'localhost;

1. Revoking All the Privilege to a User in a Table: To revoke all the privileges to a
user named “Amit” in a table “users”, the following revoke statement should be
executed.
REVOKE ALL ON Users FROM 'Amit'@'localhost;

1. Revoking a Privilege to all Users in a Table: To Revoke a specific privilege to all

the users in a table “users”, the following revoke statement should be executed.

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

1. Revoking Privileges on Functions/Procedures: While using functions and

procedures, the revoke statement can be used to revoke the privileges from
users which have been EXECUTE privileges in the past. Syntax:

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

Revoking EXECUTE privileges on a function in MySQL.: If there is a function

called “CalculateSalary” and you want to revoke EXECUTE access to the user
named Amit, then the following revoke statement should be executed.

REVOKE EXECUTE ON FUNCTION Calculatesalary FROM 'Amit'@localhost';

Revoking EXECUTE privileges to all Users on a function in MySQL.: If there is

a function called “CalculateSalary” and you want to revoke EXECUTE access to
all the users, then the following revoke statement should be executed.

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

Revoking EXECUTE privilege to a Users on a procedure in MySQL.: If there is a

procedure called “DBMSProcedure” and you want to revoke EXECUTE access to
the user named Amit, then the following revoke statement should be executed.

REVOKE EXECUTE ON PROCEDURE DBMSProcedure FROM 'Amit'@localhost';

Revoking EXECUTE privileges to all Users on a procedure in MySQL.: If there

is a procedure called “DBMSProcedure” and you want to revoke EXECUTE
access to all the users, then the following revoke statement should be executed.

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

Last Updated : 02 Aug, 2022 43

Similar Reads
1. Difference between Grant and Revoke

2. Oracle DataBase - Grant Privileges to a User in SQL Command Line

3. Types of Discretionary Privileges

4. MySQL | Common MySQL Queries

5. MySQL | LEAD() and LAG() Function

6. PHP | MySQL UPDATE Query

7. PHP | MySQL Database Introduction

8. PHP | MySQL ( Creating Database )

9. PHP | MySQL ( Creating Table )

10. PHP | Inserting into MySQL database

Previous Next

Article Contributed By :

Shubrodeep Banerjee
Shubrodeep Banerjee

Vote for difficulty

Current difficulty : Medium

Easy Normal Medium Hard Expert

Improved By : nvishal2580

Article Tags : mysql, DBMS, SQL

Practice Tags : DBMS, SQL

Improve Article Report Issue

A-143, 9th Floor, Sovereign Corporate

Tower, Sector-136, Noida, Uttar Pradesh -
201305

feedback@geeksforgeeks.org

Company Explore
About Us Job Fair For Students

Legal POTD: Revamped

Careers Python Backend LIVE

In Media Android App Development

Contact Us DevOps LIVE

Advertise with us DSA in JavaScript

Languages Data Structures

Python Array

Java String

C++ Linked List

PHP Stack

GoLang Queue

SQL Tree

R Language Graph
 Android Tutorial

Algorithms Web Development

Sorting HTML

Searching CSS

Greedy JavaScript

Dynamic Programming Bootstrap

Pattern Searching ReactJS

Recursion AngularJS

Backtracking NodeJS

Computer Science Python

GATE CS Notes Python Programming Examples

Operating Systems Django Tutorial

Computer Network Python Projects

Database Management System Python Tkinter

Software Engineering OpenCV Python Tutorial

Digital Logic Design Python Interview Question

Engineering Maths

Data Science & ML DevOps

Data Science With Python Git

Data Science For Beginner AWS

Machine Learning Tutorial Docker

Maths For Machine Learning Kubernetes

Pandas Tutorial Azure

NumPy Tutorial GCP

NLP Tutorial

Deep Learning Tutorial

Competitive Programming System Design

Top DSA for CP What is System Design

Top 50 Tree Problems Monolithic and Distributed SD

Top 50 Graph Problems Scalability in SD

Top 50 Array Problems Databases in SD

 Top 50 String Problems High Level Design or HLD

Top 50 DP Problems Low Level Design or LLD

Top 15 Websites for CP Top SD Interview Questions

Interview Corner GfG School

Company Preparation CBSE Notes for Class 8

Preparation for SDE CBSE Notes for Class 9

Company Interview Corner CBSE Notes for Class 10

Experienced Interview CBSE Notes for Class 11

Internship Interview CBSE Notes for Class 12

Competitive Programming English Grammar

Aptitude

Commerce UPSC

Accountancy Polity Notes

Business Studies Geography Notes

Microeconomics History Notes

Macroeconomics Science and Technology Notes

Statistics for Economics Economics Notes

Indian Economic Development Important Topics in Ethics

Income Tax UPSC Previous Year Papers

Management

SSC/ BANKING Write & Earn

SSC CGL Syllabus Write an Article

SBI PO Syllabus Improve an Article

SBI Clerk Syllabus Pick Topics to Write

IBPS PO Syllabus Write Interview Experience

IBPS Clerk Syllabus Internships

Aptitude Questions Video Internship

SSC CGL Practice Papers

@geeksforgeeks , Some rights reserved