Data Control Language

DCL is the abbreviation for “Data Control Language”. And include the following SQL commands such as GRANT and REVOKE. And the SQL commands are listed below with short descriptions:

Command Description
GRANT Command GRANT commands is used to gives a privilege to user.
REVOKE Command REVOKE commands is used to takes back privileges granted from user.

1) MySQL REVOKE Command:

MySQL Revoke command is used to takes back some or all privileges from user. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL.

Syntax: SQL syntax of revoking privileges on a table in MySQL is as follows.

REVOKE Privileges on Table:

REVOKE privileges ON table_name FROM user;

Parameters:

  • privileges: privileges in MySQL is as follows:
    Privilege Description
    SELECT User can able to perform SELECT operation on the table.
    INSERT User can able to perform INSERT operation on the table.
    UPDATE User can able to perform UPDATE operation on the table.
    DELETE User can able to perform DELETE operation on the table.
    INDEX User can able to create an index on an existing table.
    CREATE User can able to perform CREATE TABLE operation.
    ALTER User can able to perform ALTER TABLE operation to change the table definition.
    DROP User can able to perform DROP TABLE operation.
    GRANT OPTION Allows you to grant the privileges that you possess to other users.
    ALL Grants all permissions except GRANT OPTION.
  • table_name: name of table
  • user User’s name that will be granted these privileges.

For example:

Let’s see an some examples of how to revoke, grant privileges from user in MySQL.

For example, if you wanted to takes back UPDATE, and DELETE privileges on a table_name from a user name user_name, Then you would run the following REVOKE command:

REVOKE DELETE, UPDATE ON table_name FROM ‘user_name’@’localhost’;

The ALL keyword to indicate that you want to REVOKE all permissions (except GRANT OPTION) on a table for a user named user_name, you could use the ALL keyword. For example:

REVOKE ALL ON table_name FROM ‘user_name’@’localhost’;

If you had granted SELECT privileges to all users on the table_name table and you wanted to revoke these privileges. For example:

REVOKE SELECT ON table_name FROM ‘*’@’localhost’;

2) MySQL GRANT Command:
MySQL GRANT command is used to grant users various privileges to tables. And These permissions are the any combination of SELECT, INSERT, UPDATE, DELETE, INDEX, CREATE, ALTER, DROP, GRANT OPTION or ALL.

Syntax: SQL syntax of granting privileges on a table in MySQL is as follows.

Grant Privileges on Table:

GRANT privileges ON table_name TO user;

Parameters:

  • privileges: privileges in MySQL is as follows:
    Privilege Description
    SELECT User can able to perform SELECT operation on the table.
    INSERT User can able to perform INSERT operation on the table.
    UPDATE User can able to perform UPDATE operation on the table.
    DELETE User can able to perform DELETE operation on the table.
    INDEX User can able to create an index on an existing table.
    CREATE User can able to perform CREATE TABLE operation.
    ALTER User can able to perform ALTER TABLE operation to change the table definition.
    DROP User can able to perform DROP TABLE operation.
    GRANT OPTION Allows you to grant the privileges that you possess to other users.
    ALL Grants all permissions except GRANT OPTION.
  • table_name: name of table
  • user: User’s name that will be granted these privileges.

For example:

Let’s see an some examples of how to grant privileges on tables in MySQL.

For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table_name to a user name user_name, Then you would run the following GRANT command:

GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO ‘user_name’@’localhost’;

The ALL keyword to indicate that you want to grant all permissions except GRANT OPTION to a user named user_name. For example:

GRANT ALL ON table_name TO ‘user_name’@’localhost’;

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

GRANT SELECT ON table_name TO ‘*’@’localhost’;