MySQL Grant

Introduction to MySQL Grant

MySQL Grant is used to grant privileges to the user account. At first, when we create a new user, he doesn’t have any privilege such as selecting a database and querying data from the table. Such privileges of selecting, inserting, updating, and so on will be given to the user by the superuser. Usually, the superuser has all the privileges. The privilege levels are off

• Global
• Database
• table
• column
• stored routine
• proxy

The global level privilege applies to all databases in the MySQL servers. We use*.* in syntax which mentions all. Database level privilege is used to apply for all the objects in the database. We must specify the database name in the syntax. Table level privilege is applied for all the columns in a table. We specify the table name in the syntax. Column level privilege is to give privilege to specified columns in a table. We must specify the column names in the syntax. Stored level privilege is to give privilege to stored procedures and stored functions. Proxy level privilege is to allow the proxy of one user to another. In this session let us see in detail the GRANT along with the example.

Syntax:

Below is the syntax:

GRANT [privilege_1] [,privilege 2],..
ON privilege_item
TO user_name;

How does MySQL Grant works?

Now let us see the privilege level for each with an example:

1. Global level privilege

Code:

GRANT privilege / * - - - - -  global Level privilege - - - - * /
ON *.*
TO user_name;

Explanation: Here we are providing the select privilege to all the databases in the MySQL server. The global level of privilege applies to all databases in the MySQL server. We use *.* in syntax which mentions all.

2. Database level privilege

Code:

GRANT privilege/ * - - - - -  database Level privilege - - - - * /
ON database_name.*
TO user_name;

Explanation: Here we are providing the select privilege to all tables in the mentioned database in the MySQL server. Database level privilege is used to apply for all the objects in the database. We must specify the database name in the syntax.

3. Table level privilege

Code:

GRANT privilege/ * - - - - -  table Level privilege - - - - * /
ON database_name.table_name
TO user_name;

Explanation: Here we are providing the select privilege to the table in the mentioned database in the MySQL server. Table level privilege is applied for all the columns in a table. We specify the table name in the syntax.

4. Column level privilege

Code:

GRANT privilege_1 (col1, col2, . . , coln)/ * - - - - -  column Level privilege - - - - * /
ON table_name
TO user_name;

Explanation: Here we are providing the select privilege to all columns mentioned above from the table mentioned in the MySQL server. Column level privilege is to give privilege to specified columns in a table. We must specify the column names in the syntax.

5. Stored level privilege

Code:

GRANT privilege/ * - - - - -  stored Level privilege - - - - * /
ON PROCEDURE procedure_name
TO user_name;

Explanation: Here we are providing the execute privilege to the mentioned stored procedure or stored function in the MySQL server. Stored level privilege is to give privilege to stored procedures and stored functions.

6. Proxy Level privilege

Code:

GRANT PROXY / * - - - - -  Proxy Level privilege - - - - * /
ON ROOT
TO user_name;

Explanation: Here we are providing the PROXY privilege to the mentioned ROOT in the MySQL server for the mentioned user. Proxy level privilege is to allow proxy of one user to another

Examples to Implement MySQL Grant

As mentioned in the above session with the syntax of privilege. Now let us see the privilege level for each with an example:

1. Global level privilege

Code:

GRANT SELECT/ * - - - - -  global Level privilege - - - - * /
ON *.*
TO rose;

Output:

Explanation: Here we are providing the “Select” privilege to all the databases for the user “rose”.

2. Database level privilege

Code:

GRANT SELECT/ * - - - - -  database Level privilege - - - - * /
ON sourcedb.*
TO rose;

Output:

Explanation: Here “sourcedb” is the database and we are providing “select” privilege to the mentioned database for the user “rose”.

3. Table level privilege

Code:

GRANT SELECT/ * - - - - -  table Level privilege - - - - * /
ON dbo.loan
TO rose;

Output:

Explanation: Here “dbo” is in the database and we are providing “select” privilege to the “loan” table from the mentioned database for the user “rose”.

4. Column level privilege

Code:

GRANT SELECT (loan_no, loan_status, state, loan_amount, ap_date)/ * - - - - -  column Level privilege - - - - * /
ON loan
TO rose;

Output:

Explanation: Here loan_no, loan_status, state, loan_amount, ap_date are the column names of the table loan for which we are providing “select” privilege for the user “rose”.

5. Stored level privilege

Code:

GRANT EXECUTE/ * - - - - -  stored Level privilege - - - - * /
ON PROCEDURE stored_procedure
TOrose;

Output:

Explanation: Here we provide the EXECUTE privilege to the stored procedure name “stored_procedure” for the user rose.

6. Proxy Level privilege

Code:

GRANT PROXY/ * - - - - -  Proxy Level privilege - - - - * /
ON ROOT
TO rose;

Output:

Explanation: Here we are providing the PROXY privilege to the mentioned ROOT in MySQL server for the mentioned user.

Recommended Articles

This is a guide to MySQL Grant. Here we discuss an introduction to MySQL Grant, syntax, how does it work, and examples with code and output. You can also go through our other related articles to learn more –

1. MySQL Trigger
2. What is MySQL?
3. Cheat Sheet MySQL
4. Uses of SQL