75

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 [email protected];	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 [email protected];	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 [email protected];	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 [email protected];	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 [email protected];	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 [email protected];	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 “[email protected]” using the following statement:

Next, execute the SHOW GRANT statement to check the privileges assigned to [email protected] 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.

If we want to assign all privileges to all databases in the current server to [email protected], 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 [email protected]

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.

---