Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

Grant Privileges

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

Grant Privileges
As we learnt earlier, a root user is connected to the server (using a password) immediately after installing MySQL. The privileges available to this user are default. The user accessing MySQL using root account has enough privileges to perform basic operations on the data. However, in exceptional cases, the user must manually request the host to grant privileges.
The MySQL Grant Privileges
MySQL provides several SQL statements to allow or restrict administrative privileges for users to interact with the data stored in the database. They are listed below 
GRANT statement
REVOKE statement
In this tutorial, let us learn about the GRANT statement in detail.
The MySQL GRANT Statement
The MySQL GRANT statement is used to assign various privileges or roles to MySQL user accounts. However, it's important to note that you cannot assign both privileges and roles in a single GRANT statement. To grant privileges to users using this statement, you need to have the GRANT OPTION privilege.
Syntax
Following is the syntax of the MySQL GRANT Statement 
GRANT
privilege1, privilege2, privilege3...
ON object_type
TO user_or_role1, user_or_role2, user_or_role3...
[WITH GRANT OPTION]
[AS user
  [WITH ROLE
    DEFAULT
    | NONE
    | ALL
    | ALL EXCEPT role [, role ] ...
    | role [, role ] ...
   ]
]
Example
Assume we have created a user named 'test_user'@'localhost' in MySQL using the CREATE USER statement 
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'testpassword';
Following is the output of the above code 
Query OK, 0 rows affected (0.23 sec)
Now, let us create a database 
CREATE DATABASE test_database;
The output produced is as follows
Query OK, 0 rows affected (0.56 sec)
Next, we will use the created database 
USE test_database;
We get the output as shown below
Database changed
Now, let us create a table in the database 
CREATE TABLE MyTable(data VARCHAR(255));
The output obtained is as follows 
Query OK, 0 rows affected (0.67 sec)
Following query grants SELECT privileges on the table created above to the user 'test_user'@'localhost' 
GRANT SELECT ON test_database.MyTable TO 'test_user'@'localhost';
After executing the above code, we get the following output 
Query OK, 0 rows affected (0.31 sec)
Verification
You can verify the granted privileges using the SHOW GRANTS statements 
SHOW GRANTS FOR 'test_user'@'localhost';
The output we get is as shown below 
Grants for test_user@localhost
GRANT USAGE ON *.* TO `test_user`@`localhost`
GRANT SELECT ON `test_database`.`mytable` TO `test_user`@`localhost`
Granting Various Privileges
We know that the MySQL GRANT statement allows a wide range of privileges to user accounts. Here is a list of some commonly used privileges that can be granted using the GRANT statement 
Privileges
Description
ALTER
Allows users to modify table structures using the ALTER TABLE statement.
CREATE
Grants the ability to create new objects such as tables and databases.
DELETE
Enables users to delete rows from tables.
INSERT
Allows users to insert new records into tables.
SELECT
Provides read access to tables, allowing users to retrieve data.
UPDATE
Allows users to modify existing data in tables.
SHOW DATABASES
Grants the ability to see a list of available databases.
CREATE USER
Allows users to create new MySQL user accounts.
GRANT OPTION
Provides users with the authority to grant privileges to other users.
SUPER
Grants high-level administrative privileges.
SHUTDOWN
Allows users to shut down the MySQL server.
REPLICATION CLIENT
Provides access to replication-related information.
REPLICATION SLAVE
Enables users to act as a replication slave server.
FILE
Grants permission to read and write files on the server's file system.
CREATE VIEW
Allows users to create new database views.
CREATE TEMPORARY TABLES
Allows the creation of temporary tables.
EXECUTE
Enables users to execute stored procedures and functions.
TRIGGER
Provides the ability to create and manage triggers.
EVENT
Grants the ability to create and manage events.
SHOW VIEW
Allows users to see the definition of views.
INDEX
Enables users to create and drop indexes on tables.
PROXY
Provides the capability to proxy or impersonate other users.
Example
To GRANT all the available privileges to a user, you need to use the 'ALL' keyword in the GRANT statement 
GRANT ALL ON test_database.MyTable TO 'test_user'@'localhost';
Output
After executing the above code, we get the following output 
Query OK, 0 rows affected (0.13 sec)
Granting Privileges on Stored Routines
To grant privileges on stored routines, such as tables, procedures or functions, in MySQL, you need to specify the object type (PROCEDURE or FUNCTION) after the ON clause followed by the name of the routine.
You can grant ALTER ROUTINE, CREATE ROUTINE, EXECUTE, and GRANT OPTION privileges on these stored routines.
Example
Assume we have created a stored procedure and a stored function with the name 'sample' in the current database as follows 
//Creating a procedure
DELIMITER //
CREATE PROCEDURE sample ()
   BEGIN
      SELECT 'This is a sample procedure';
   END//
Query OK, 0 rows affected (0.29 sec)
﻿
//Creating a function
CREATE FUNCTION sample()
   RETURNS VARCHAR(120)
   DETERMINISTIC
   BEGIN
      DECLARE val VARCHAR(120);
      SET val = 'This is a sample function';
      return val;
   END// 
DELIMITER ;
Following is the output obtained 
Query OK, 0 rows affected (0.34 sec)
After creating these stored routines, you can grant ALTER ROUTINE, EXECUTE privileges on the above created procedure to the user named 'test_user'@'localhost' as follows 
GRANT ALTER ROUTINE, EXECUTE ON 
PROCEDURE test_database.sample TO 'test_user'@'localhost';
The output produced is as shown below 
Query OK, 0 rows affected (0.24 sec)
Now, the query below grants ALTER ROUTINE, EXECUTE privileges on the above created function to the user named 'test_user'@'localhost'.
GRANT ALTER ROUTINE, EXECUTE ON 
FUNCTION test_database.sample TO 'test_user'@'localhost';
Following is the output of the above query 
Query OK, 0 rows affected (0.15 sec)
Privileges to Multiple Users
You can grant privileges to multiple users. To do so, you need to provide the names of the objects or users separated by commas.
Example
Assume we have created a table named 'sample' and three user accounts using the CREATE statement as shown below.
Creating a table 
﻿
CREATE TABLE sample (data VARCHAR(255));
We will get the output as shown below 
Query OK, 0 rows affected (3.55 sec)
Now, let us create the user accounts.
Creating User 'test_user1' 
﻿
CREATE USER test_user1 IDENTIFIED BY 'testpassword';
The output obtained is as follows 
Query OK, 0 rows affected (0.77 sec)
Creating User 'test_user2' 
﻿
CREATE USER test_user2 IDENTIFIED BY 'testpassword';
Following is the output produced 
Query OK, 0 rows affected (0.28 sec)
Creating the 3rd user 
Creating User 'test_user3' 
﻿
CREATE USER test_user3 IDENTIFIED BY 'testpassword';
We get the output as follows 
Query OK, 0 rows affected (0.82 sec)
Following query grant SELECT, INSERT and UPDATE privileges on the tables 'sample1', 'sample2' and 'sample3' to to all three users ('test_user1', 'test_user2', and 'test_user3') using a single GRANT statement.
GRANT SELECT, INSERT, UPDATE ON 
TABLE sample TO test_user1, test_user2, test_user3;
Output
After executing the above code, we get the following output 
Query OK, 0 rows affected (0.82 sec)
Global Privileges
Instead of specifying the table, procedure or a function you can grant global privileges: privileges that apply to all databases to a user. To do so, you need to use *.* after the ON clause.
Example
Following query grants SELECT, INSERT and UPDATE privileges on all databases to the user named 'test_user'@'localhost' 
GRANT SELECT, INSERT, UPDATE ON *.* TO 'test_user'@'localhost';
Output
Following is the output obtained 
Query OK, 0 rows affected (0.43 sec)
Example
Similarly, following query grants all privileges on all the databases to the 'test_user'@'localhost 
GRANT ALL ON *.* TO 'test_user'@'localhost';
Output
The output produced is as shown below 
Query OK, 0 rows affected (0.41 sec)
Database Level Privileges
You can grant privileges to all the objects in a database by specifying the database name followed by ".*" after the ON clause.
Example
Following query grants SELECT, INSERT and UPDATE privileges on all objects in the database named test to the user 'test_user'@'localhost' 
GRANT SELECT, INSERT, UPDATE 
ON test.* TO 'test_user'@'localhost';
Output
Following is the output of the above code 
Query OK, 0 rows affected (0.34 sec)
Example
Similarly, following query grants all privileges on all the databases to the 'test_user'@'localhost 
GRANT ALL ON test.* TO 'test_user'@'localhost';
Output
Output of the above code is as follows 
Query OK, 0 rows affected (0.54 sec)
Column Level Privileges
You can grant privileges on a specific column of a table to a user. To do so, you need to specify the column names after the privileges.
Example
Assume we have created a table named Employee using the CREATE query as 
CREATE TABLE Employee (
ID INT, Name VARCHAR(15), Phone INT, SAL INT);
The output produced is as shown below 
Query OK, 0 rows affected (6.47 sec)
Following query grants SELECT privilege to the user named 'test_user'@'localhost' on the ID column and INSERT and UPDATE privileges on the columns Name and Phone of the Employee table 
GRANT SELECT (ID), INSERT (Name, Phone) 
ON Employee TO 'test_user'@'localhost';
The output obtained is as follows 
Query OK, 0 rows affected (0.54 sec)
Proxy User Privileges
You can make one user as a proxy of another by granting the PROXY privilege to it. If you do so, both users have the same privileges.
Example
Assume we have created a users named sample_user, proxy_user in MySQL using the CREATE statement as shown below 
CREATE USER sample_user, proxy_user IDENTIFIED BY 'testpassword';
Following is the output obtained 
Query OK, 0 rows affected (0.52 sec)
The following query grants SELECT and INSERT privileges on the Employee table created above to the user sample_user 
GRANT SELECT, INSERT ON Emp TO sample_user;
We get the output as shown below 
Query OK, 0 rows affected (0.28 sec)
Now, we can assign proxy privileges to the user proxy_user using the GRANT statement as shown below 
GRANT PROXY ON sample_user TO proxy_user;
The output is as follows 
Query OK, 0 rows affected (1.61 sec)
Granting Roles
Role in MySQL is a set of privileges with name. You can create one or more roles in MySQL using the CREATE ROLE statement. If you use the GRANT statement without the ON clause, you can grant a role instead of privileges.
Example
Let us start by creating a role named TestRole_ReadOnly.
CREATE ROLE 'TestRole_ReadOnly';
Following is the output obtained 
Query OK, 0 rows affected (0.13 sec)
Now, let us grant read only privilege to the created role using the GRANT statement for accessing all objects within the database 
GRANT SELECT ON * . * TO 'TestRole_ReadOnly';
The output of this GRANT statement should be 
Query OK, 0 rows affected (0.14 sec)
Then, you can GRANT the created role to a specific user. First, you will need to create the user as shown below 
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
Following is the output produced 
Query OK, 0 rows affected (0.14 sec)
Now, you can grant the 'TestRole_ReadOnly' role to 'newuser'@'localhost' 
GRANT 'TestRole_ReadOnly' TO 'newuser'@'localhost';
The output obtained is as shown below 
Query OK, 0 rows affected (0.13 sec)
Granting Privileges Using a Client Program
Now, let us see how to grant privileges to a MySQL user using the client program.
Syntax
Following are the syntaxes 
PHPNodeJSJavaPython
To grant all the privileges to an user in MySQL database using the PHP program, we need to execute the GRANT ALL statement as shown below 
$sql = "GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost'";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.');
$sql = "GRANT ALL PRIVILEGES ON tutorials.* TO 'Revathi'@'localhost'";
if($result = $mysqli->query($sql)){
   printf("Grant privileges executed successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as follows 
Grant privileges executed successfully...!
﻿
﻿
﻿
﻿
﻿

Privileges	Description
ALTER	Allows users to modify table structures using the ALTER TABLE statement.
CREATE	Grants the ability to create new objects such as tables and databases.
DELETE	Enables users to delete rows from tables.
INSERT	Allows users to insert new records into tables.
SELECT	Provides read access to tables, allowing users to retrieve data.
UPDATE	Allows users to modify existing data in tables.
SHOW DATABASES	Grants the ability to see a list of available databases.
CREATE USER	Allows users to create new MySQL user accounts.
GRANT OPTION	Provides users with the authority to grant privileges to other users.
SUPER	Grants high-level administrative privileges.
SHUTDOWN	Allows users to shut down the MySQL server.
REPLICATION CLIENT	Provides access to replication-related information.
REPLICATION SLAVE	Enables users to act as a replication slave server.
FILE	Grants permission to read and write files on the server's file system.
CREATE VIEW	Allows users to create new database views.
CREATE TEMPORARY TABLES	Allows the creation of temporary tables.
EXECUTE	Enables users to execute stored procedures and functions.
TRIGGER	Provides the ability to create and manage triggers.
EVENT	Grants the ability to create and manage events.
SHOW VIEW	Allows users to see the definition of views.
INDEX	Enables users to create and drop indexes on tables.
PROXY	Provides the capability to proxy or impersonate other users.

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Grant Privileges

The MySQL Grant Privileges

The MySQL GRANT Statement

Syntax

Example

Verification

Granting Various Privileges

Granting Privileges on Stored Routines

Example

Privileges to Multiple Users

Example

Output

Global Privileges

Example

Output

Example

Output

Database Level Privileges

Example

Output

Example

Output

Column Level Privileges

Example

Proxy User Privileges

Example

Granting Roles

Example

Granting Privileges Using a Client Program

Syntax

Example

Output