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.
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.
The following are the basic syntax of using the GRANT statement:
In the above syntax, we can have the following parameters:
||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.
||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.
||It determines the account name of the user to whom the access rights would be granted.
MySQL supports the following privilege levels:
|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.
|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.
|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.
||GRANT SELECT (col1), INSERT (col1, col2), UPDATE (col2)
|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.
ON PROCEDURE mydb.myprocedure
|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.
|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.
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.
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.
- SELECT: It enables us to view the result set from a specified table.
- INSERT: It enables us to add records in a given table.
- DELETE: It enables us to remove rows from a table.
- CREATE: It enables us to create tables/schemas.
- ALTER: It enables us to modify tables/schemas.
- UPDATE: It enables us to modify a table.
- DROP: It enables us to drop a table.
- INDEX: It enables us to create indexes on a table.
- ALL: It enables us to give ALL permissions except GRANT privilege.
- GRANT: It enables us to change or add access rights.