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:
GRANT privilege_name(s)
ON object
TO user_account_name; Parameter Explanation
In the above syntax, we can have the following parameters:https://imasdk.googleapis.com/js/core/bridge3.507.1_en.html#goog_21253984958.2M160The Future of the Digital Dollar in the United States
| 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. |
Privilege levels in MySQL
There are six privilege levels used for granting privileges to the user: global, database, table, column, stored procedure or function, and proxy, as shown in the below image.
Examples of Grant Privileges MySQL
Let us create a new user named grant demo using the following create user statement, which we can use to grant privileges to-
Query:
<code>CREATE USER 'grantdemo'@'localhost' IDENTIFIED BY '123';</code>
Output:
Let us check all the granted privileges for this user using the following query statement –
Query:
<code>SHOW GRANTS FOR grantdemo@localhost;</code>
Output:
We can see from the output that the newly created user does not have any grants assigned to it. It can just log in to the database but not access the contents and can neither modify them. Using the GRANT statement, let us grant the select privilege on all the educba_writers tables of the educba database. This can be done by using the following query statement –
Query:
<code>GRANT SELECT<br>ON educba.educba_writers<br>TO grantdemo@localhost;</code>
Output:
We can even assign multiple privileges to the grantdemo user using the GRANT statement. For example, let us assign UPDATE, DELETE and INSERT privileges to the grantdemo user on the table educba_writers located in the educba database. For this, we will make the use of the following query statement where the privileges to be granted are mentioned in comm-separated format –
Query:
<code>GRANT UPDATE, DELETE, INSERT<br>ON educba.educba_writers<br>TO grantdemo@localhost;</code>
Output:
How Privileges can be Assigned on Different Levels?
Let us now see, one by one, how privileges on different levels can be assigned.
1. Global Level
We can grant certain privileges to the user that has the privilege level that is scope globally on all the tables of all the databases. This is called granting privileges at the global level. Suppose we want to grant SELECT privilege on all the tables of all the databases to the grantdemo user. This can be done by using the following query –
Query:
<code>GRANT SELECT<br>ON *.*<br>TO grantdemo@localhost;</code>
Output:
*.* is used to specify the global privilege level.
2. Database Level
When we want to grant the privileges to the user on all the tables of the ceratin database, then we can use the database privilege level. We need to specify name_of_database.* to mention the database level of privilege. We can grant INSERT privilege on all the tables of the educba database to grantdemo user by using the following query –
Query:
<code>GRANT INSERT<br>ON educba.*<br>TO grantdemo@localhost;</code>
Output:
3. Table Privilege Level
When we want to restrict the granting of privilege on a particular table of the database, then table-level privileges are granted. For example, if we want to give DELETE privilege to grantdemo user on just educba_writers table, then we can use the following query –
Query:
<code>GRANT DELETE<br>ON educba.educba_writers<br>TO grantdemo@localhost;</code>
Output:
4. Column, Stored Routine and Proxy Level Privilege Granting
We can even grant all the privileges assigned to a particular user to some other user. This is called the proxy level privilege granting, and the user to which the privileges are being granted is called the proxy of the original user whose privileges are being granted.
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:
mysql> CREATE USER john@localhost IDENTIFIED BY 'jtp12345';
Next, execute the SHOW GRANT statement to check the privileges assigned to john@localhost using the following query:
mysql> SHOW GRANTS FOR john@localhost;
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:
mysql> GRANT ALL ON mystudentdb.* TO john@localhost;
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:
mysql> GRANT EXECUTE ON FUNCTION calculatesalary TO john@localhost;
If there is a need to provide the EXECUTE privilege to all users, we must run the below command:
mysql> GRANT EXECUTE ON FUNCTION calculatesalary TO *@localhost;
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.
