1. Home
  2. MySQL Grant Privilege

MySQL Grant Privilege

MySQL has a feature that provides many control options to the administrators and users on the database. We have already learned how to create a new user using CREATE USER statement in MySQL server. Now, we are going to learn about grant privileges to a user account. MySQL provides GRANT statements to give access rights to a user account.

GRANT Statement

The grant statement enables system administrators to assign privileges and roles to the MySQL user accounts so that they can use the assigned permission on the database whenever required.

Syntax

The following are the basic syntax of using the GRANT statement:

Parameter Explanation

In the above syntax, we can have the following parameters:

Parameter Name Descriptions
privilege_name(s) It specifies the access rights or grant privilege to user accounts. If we want to give multiple privileges, then use a comma operator to separate them.
object It determines the privilege level on which the access rights are being granted. It means granting privilege to the table; then the object should be the name of the table.
user_account_name It determines the account name of the user to whom the access rights would be granted.

Privilege Levels

MySQL supports the following privilege levels:

Privilege Level Syntax Descriptions
Global GRANT ALL
ON *.*
TO john@localhost;		 It applies to all databases on MySQL server. We need to use *.* syntax for applying global privileges. Here, the user can query data from all databases and tables of the current server.
Database GRANT ALL
ON mydb.*
TO john@localhost;		 It applies to all objects in the current database. We need to use the db_name.* syntax for applying this privilege. Here, a user can query data from all tables in the given database.
Table GRANT DELETE
ON mydb.employees
TO john@localhsot;		 It applies on all columns in a specified table. We need to use db_name.table_name syntax for assigning this privilege. Here, a user can query data from the given table of the specified database.
Column GRANT SELECT (col1), INSERT (col1, col2), UPDATE (col2)
ON mydb.mytable
TO john@localhost;		 It applies on a single column of a table. Here, we must have to specify the column(s) name enclosed with parenthesis for each privilege. The user can select one column, insert values in two columns, and update only one column in the given table.
Stored Routine GRANT EXECUTE
ON PROCEDURE mydb.myprocedure
TO john@localhost;		 It applies to stored routines (procedure and functions). It contains CREATE ROUTINE, ALTER ROUTINE, EXECUTE, and GRANT OPTION privileges. Here, a user can execute the stored procedure in the current database.
Proxy GRANT PROXY
ON root
TO peter@localhost;		 It enables one user to be a proxy for other users.

GRANT Statement Example

Let us understand the GRANT privileges through the example. First, we need to create a new user named "john@localhost" using the following statement:

Next, execute the SHOW GRANT statement to check the privileges assigned to john@localhost using the following query:

It will give the below output. Here, the USAGE means a user can log in to the database but does not have any privileges.

MySQL Grant Privilege

If we want to assign all privileges to all databases in the current server to john@localhost, execute the below statement:

Again, execute the SHOW GRANT statement to verify the privileges. After the successful execution, we will get the below output. Here all privileges are assigned to all databases in the current server to john@localhost.

MySQL Grant Privilege

Stored Routine Example

Here, the grant privileges are applied to procedures and functions where a user can execute the stored procedure in the current MySQL database. The EXECUTE privilege provides the ability to execute a function and procedure.

Let us understand it with the example. Suppose we have a function calculatesalary and want to grant EXECUTE privilege to a user john, run the following query:

If there is a need to provide the EXECUTE privilege to all users, we must run the below command:

We can choose access right from the below list on which privileges can be applied.

  1. SELECT: It enables us to view the result set from a specified table.
  2. INSERT: It enables us to add records in a given table.
  3. DELETE: It enables us to remove rows from a table.
  4. CREATE: It enables us to create tables/schemas.
  5. ALTER: It enables us to modify tables/schemas.
  6. UPDATE: It enables us to modify a table.
  7. DROP: It enables us to drop a table.
  8. INDEX: It enables us to create indexes on a table.
  9. ALL: It enables us to give ALL permissions except GRANT privilege.
  10. GRANT: It enables us to change or add access rights.
Next TopicMySQL Revoke Privilege




Hot Tutorials

Contact US

Email:[email protected]

MySQL Grant Privilege
10/30