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.

REVOKE Statement
Earlier, we discussed how a root user gets access to a MySQL server with default privileges after installation. These privileges are sufficient for performing basic operations on the data. However, in some special situations, users might need to request the server's host to take away certain privileges. To do so, we use the MySQL REVOKE statement.
The MySQ REVOKE statement
The MySQL REVOKE statement is used to remove certain administrative privileges or roles from users. It revokes permissions that were previously granted.
Syntax
Following is the syntax of the MySQL REVOKE Statement 
REVOKE privileges
   ON database_name.table_name
   FROM 'user'@'host';
Example
Assume we have created a user named 'test_user'@'localhost' in MySQL using the CREATE USER statement as shown below 
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'testpassword';
Following is the output produced 
Query OK, 0 rows affected (0.23 sec)
Now, let us create a database named 'test_database' 
CREATE DATABASE test_database;
The output produced is as follows 
Query OK, 1 row 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 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)
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`
Now, you can revoke the above granted privilege using the REVOKE statement as shown below 
REVOKE SELECT ON test_database.MyTable FROM 'test_user'@'localhost';
We get the output as follows 
Query OK, 0 rows affected (0.25 sec)
Verification
We can verify whether the SELECT privilege has been revoked or not using the SHOW GRANTS statements as shown below 
SHOW GRANTS FOR 'test_user'@'localhost';
We can see that the output no longer lists the SELECT privilege, indicating that it has been revoked 
Grants for test_user@localhost
GRANT USAGE ON *.* TO `test_user`@`localhost`
Revoking All Privileges
If a user has multiple privileges with a user, you can revoke all those privileges at once using the REVOKE ALL statement in MySQL.
Syntax
Following is the syntax to revoke all privileges in MySQL 
REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'host';
Example
Assume we have created a user as follows 
CREATE USER 'sample_user'@'localhost';
Following is the output produced 
Query OK, 0 rows affected (0.18 sec)
We also create a procedure as shown below 
DELIMITER //
CREATE PROCEDURE sample ()
   BEGIN
      SELECT 'This is a sample procedure';
   END//
DELIMITER ;
The output obtained is as follows 
Query OK, 0 rows affected (0.29 sec)
Additionally, we create a table named 'sample' in a database 
CREATE TABLE sample(data INT);
We get the output as shown below 
Query OK, 0 rows affected (0.68 sec)
Now, the following queries grants ALTER ROUTINE, EXECUTE privileges on the above created procedure to the user named 'sample_user'@'localhost'.
GRANT ALTER ROUTINE, EXECUTE 
ON PROCEDURE test_database.sample TO 'sample_user'@'localhost';
Output of the above code is as shown below 
Query OK, 0 rows affected (0.20 sec)
Similarly, following query grants SELECT, INSERT and UPDATE privileges on the table 'sample' to the user 'sample_user'@'localhost 
GRANT SELECT, INSERT, UPDATE 
ON test.sample TO 'sample_user'@'localhost';
The result produced is 
Query OK, 0 rows affected (0.14 sec)
You can verify the list of all privileges granted for the user using the SHOW GRANTS statement 
SHOW GRANTS FOR 'sample_user'@'localhost';
The result obtained is as follows 
Grants for sample_user@localhost
GRANT USAGE ON *.* TO `sample_user`@`localhost`
GRANT SELECT, INSERT, UPDATE ON `test`.`sample` TO `sample_user`@`localhost`
GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test_database`.`sample` TO `sample_user`@`localhost`
Finally, to revoke all the privileges granted to 'sample_user'@'localhost', you can use the following statement 
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'sample_user'@'localhost';
The result produced is 
Query OK, 0 rows affected (0.30 sec)
Verification
After revoking privileges, you can check the user's grants again 
SHOW GRANTS FOR 'sample_user'@'localhost';
The output below confirms that all privileges have been revoked 
Grants for sample_user@localhost
GRANT USAGE ON *.* TO `sample_user`@`localhost`
Revoking Proxy Privilege
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 users named sample_user, proxy_user in MySQL using the CREATE statement 
CREATE USER sample_user, proxy_user IDENTIFIED BY 'testpassword';
Following is the output obtained 
Query OK, 0 rows affected (0.52 sec)
Now, we are creating a table 'Employee' 
CREATE TABLE Employee (
ID INT, Name VARCHAR(15), Phone INT, SAL INT);
We get the output as shown below 
Query OK, 0 rows affected (6.47 sec)
Following query grants SELECT and INSERT privileges on the table created above, to the user sample_user 
GRANT SELECT, INSERT ON Emp TO sample_user;
The output obtained is as follows 
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 result produced is 
Query OK, 0 rows affected (1.61 sec)
You can revoke a proxy privilege using the REVOKE PROXY statement as shown below 
REVOKE PROXY ON sample_user FROM proxy_user;
We get the following result 
Query OK, 0 rows affected (0.33 sec)
Revoking a Role
A 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
Following query creates a role named TestRole_ReadOnly 
CREATE ROLE 'TestRole_ReadOnly';
Following is the output of the above code 
Query OK, 0 rows affected (0.13 sec)
Now, let us grant read only privilege to the created role using the GRANT statement 
GRANT SELECT ON * . * TO 'TestRole_ReadOnly';
The result obtained is 
Query OK, 0 rows affected (0.14 sec)
Then, you can GRANT the created role to a user as follows 
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
Output of the above code is as follows 
Query OK, 0 rows affected (0.14 sec)
Next, you can grant the 'TestRole_ReadOnly' role to the 'newuser'@'localhost'
GRANT 'TestRole_ReadOnly' TO 'newuser'@'localhost';
We get the following result 
Query OK, 0 rows affected (0.13 sec)
Following query revokes the role from the user
REVOKE 'TestRole_ReadOnly' FROM 'newuser'@'localhost';
After executing the above code, we get the following output 
Query OK, 0 rows affected (1.23 sec)
Revoking Privileges Using a Client Program
We can also revoke privileges from a MySQL user using a client program.
Syntax
Following are the syntaxes to revoke MySQL Privileges in various programming languages 
PHPNodeJSJavaPython
To revoke all the privileges granted to an user in MySQL database using the PHP program, we need to execute the REVOKE ALL statement as shown below 
$sql = "REVOKE ALL, GRANT OPTION FROM user_name";
$mysqli->query($sql);
Example
Following are the implementations of this operation in various programming languages 
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 = "REVOKE ALL, GRANT OPTION FROM Sarika";
if($result = $mysqli->query($sql)){
   printf("Revoke privileges executed successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as follows 
Revoke privileges executed successfully...!
﻿
﻿
﻿
﻿
﻿
﻿
﻿

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

REVOKE Statement

The MySQ REVOKE statement

Syntax

Example

Verification

Revoking All Privileges

Syntax

Example

Verification

Revoking Proxy Privilege

Example

Revoking a Role

Example

Revoking Privileges Using a Client Program

Syntax

Example

Output