In this tutorial, we will learn about MySQL User privileges, how we can create users in MySQL, the types of privileges assigned to a user, and how to grant user privileges. GRANT is a keyword used for assigning privileges to users.
Privileges are the rights that are entitled to a database user. There are different types of operations performed on a database or table, such as inserting new records, deleting a record, creating a table, and so on. All these operations on a table can be performed by a MySQL user, but the user must have the privileges.
The root user is usually the user will all the privileges, whereas the default user has a little fewer privileges than the root. We can assign new privileges to users and we can revoke their privileges as well.
Syntax:
GRANT privilege ON database_name TO 'username'@'hostname' IDENTIFIED BY 'password';Code language: JavaScript (javascript)How to Create Users in MySQL
Before we get started with the GRANT command, Let’s see how we can create a user in MySQL. We use CREATE USER command to create a new user.
Syntax:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';Code language: SQL (Structured Query Language) (sql)Let’s see an example to understand it better, we will create a user named ‘Harsha’, and the hostname is ‘localhost’ and ‘root’ is the password. Let’s open the MySQL command line and write the below code:
CREATE USER 'Harsha'@'localhost' IDENTIFIED BY 'root';Code language: SQL (Structured Query Language) (sql)And this is how you create a user.
Types of Privileges
There are several types of privileges in MySQL which can be granted to users:
- Global Privilege: These privileges are assigned when giving full access to a user over the MySQL server.
- Database Privilege: This is a full or partial privilege on a particular database.
- Table Privilege: This is a full or partial privilege on a particular table present in a database.
- Column Privilege: This is a full or partial privilege on a particular column of a table present in a database.
- Stored Routine Privilege: This is a privilege granted to a user to create a routine or execute procedures on a particular database.
- Proxy User Privilege: This privilege enables one user to be a proxy for another user.
We will touch upon these pointers in the next section, we will see how to grant privilege for different operations as well as on particular databases and tables.
For more information on privileges, you can read the official documentation.
Granting User Privileges
In this section, we’ll learn how to grant privileges to the users, open the MySQL command line, or use the command prompt to log into MySQL with a specific user.
If we want to grant privilege for operations such as insert, delete, update, drop, etc, we can use the GRANT operation as below. Let’s see an example:
GRANT CREATE on *.* TO 'harsha'@'localhost' IDENTIFIED BY 'root';Code language: SQL (Structured Query Language) (sql)If we want to grant a read-only privilege to a user, then we can use the below query:
GRANT SELECT on *.* TO 'harsha'@'localhost' IDENTIFIED BY 'root';Code language: SQL (Structured Query Language) (sql)Grant privilege for a particular database, see the below query:
GRANT ALL on database.* TO 'harsha'@'localhost' IDENTIFIED BY 'root';Code language: SQL (Structured Query Language) (sql)If you want to grant privilege to a user only for a certain column on a table, then you can use the below query:
GRANT SELECT (col1), INSERT (col1, col2) ON databaseName.tableName TO 'harsha'@'localhost' IDENTIFIED BY 'root';Code language: SQL (Structured Query Language) (sql)When we want to grant all the privileges to the user, we’ll use GRANT ALL PRIVILEGE command. Usually, we don’t assign any user all privileges as this user will have all privileges like a root user, therefore, it must not be given lightly. Let’s see the SQL query for the same:
GRANT ALL PRIVILEGES ON *.* TO 'harsha'@'localhost' IDENTIFIED BY 'root';Code language: SQL (Structured Query Language) (sql)In the above command, *.* refers that the privilege given for all the databases and tables, respectively.
Conclusion
In this tutorial, we learn how to create a user in MySQL, the types of privileges, and how to grant the privilege. Although we touched upon a lot of ways in which we can grant privileges to the users, you have now enough knowledge to start experimenting yourself on the GRANT Command.
For more details, you can refer to the official website.