MySQL User Permissions

Introduction to MySQL User Permissions

In MySQL, the user permissions are granted to the MySQL user account which determines operations that can be performed in the server. These user permissions may differ in the levels of privileges in which they are applied for several query executions. Administrative permissions are the global privileges and are not specified to a specific MySQL database that enables the account users to regulate the admin operations in the MySQL server.

Database permissions are granted globally and applied for either a particular MySQL database or, all MySQL databases. These privileges are implemented to an available database and all other database objects present within it. User Permissions also provide rights for database objects within a particular database such as indexes, tables, stored routines, and views along with data types. Also, it can be applied generally for all data objects available in all databases.

 How to Apply User Permissions in MySQL?

• In MySQL, the administration concept is more than just executing query statements. For security and data maintenance, we manage the databases records using user access available to this data and permissions. In bigger corporations, we may have several users having several permissions to access the tables data. But root access should be given to every user because root access provides complete controlling permission to a user in the server. We can restrict some admin-level access to a few users and implements unique permission to each user.
• When any user account is created in MySQL, then we have to allocate some permission to it for any prior tasks to perform. There is a difference between other database platforms and MySQL server that the host name and user are the main keys to determine the user permissions. here, the host name is responsible in MySQL to recognize users that are holding access to certain hosts defined. For demonstration, suppose that we have a user named ‘myadmin’ but we only require to provide myadmin the local database access. Due to this, myadmin user will have access to only the localhost but cannot access the remote hosting server. The host name can be an IP address, a wholly qualified name or can be a wildcard host name that provides access to different databases.
• MySQL database administrators may allocate privileges to columns, tables or say whole databases. For illustration, assume we have a table having information about the products. To know certain data about product prices, we can only give access to table columns for a specific number of users. Here, we will not provide the right to access the Product id or security code if present but for instance, we can provide access to few columns such as product name, category, unit and supplier so that the reports can be prepared. This type of access control in database is denoted as column-level security.

Examples of MySQL User Permissions

Let us elaborate some examples and query commands to show the MySQL User Permissions in the server:

When we work on the server like localhost we know that we have already installed MariaDB or, MySQL server on our system. We have to execute all commands in the MySQL shell as am admin user or root user. We need to use CREATE USER and GRANT privileges command for creating user account permissions. This user account in MySQL includes two sections host name and user name. Now, if we want to create a new user account in MySQL then, we need to query the following command:

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'userpassword';

Output:

Here, you need to use the actual username in place of test user and associated password as user password. The host name is set to the localhost that defines the user is able to connect to the server from localhost in the system where the MySQL runs.

Suppose we want to replace or modify the host name with any other grant access with the remote IP address then, we can use the command like this:

CREATE USER ‘testuser’@’10.2.0.8’ IDENTIFIED BY ‘userpassword’;

The remote access from a machine having IP 10.2.0.8 is set as the host name with the related password to create a new user.

Also, there may be the case where we want to connect the user from any host or machine address then, in such a case we will apply the symbol ‘%’ that denotes wildcard to define the host name. View the query below for this:

CREATE USER ‘testuser’@’%’ IDENTIFIED BY ‘userpassword’;

Now, let us discuss more types of privileges that a user account can grant. Following is the list of permissions of the user account that are supported by MySQL:

• ALL PRIVILEGES: Allows all permissions to a user account.
• CREATE:  Permits a user account to create databases and tables in the MySQL server.
• DROP: Allows a user account to remove tables and databases in the server.
• DELETE: Permits a user to drop rows from a particular table in the server.
• INSERT: Allows a user to enter rows into a particular table in the database.
• SELECT: Permits a MySQL user to view or read the database present on the server.
• UPDATE: Allows a user to alter the table rows in the database server.

If we want to show all the permissions provided to the MySQL User Account for any specific databases then, we will use the following:

SHOW GRANTS FOR ‘database_user’@’localhost’;

Query:

SHOW GRANTS FOR 'root'@'localhost';

Output:

Again, to provide multiple permissions to a MySQL user account over a particular database:

GRANT SELECT, INSERT, DELETE ON DatabaseName.* TO ‘Database_User’@’localhost’;

Similarly, if we need to cancel or revoke all permissions from a MySQL user account over a particular MySQL database we need to execute the succeeding query:

REVOKE ALL PRIVILEGES ON DatabaseName.* FROM ‘Database_User’@’localhost’;

At last, if we want to remove any present MySQL user account including its permissions then, we can run the following query:

DROP USER 'testuser'@'localhost';

Output:

Conclusion

As we know that MySQL is a database storage and management system that is provided as open-source software that is responsible to store data, organize and manage them, and fetch when required. But one needs to be granted specific user permissions at various privilege levels to execute the operations in the server. The root user can access the whole database and all related activities can be administered. User permissions can be also only specified to tables or table columns and not the full database in the server which is useful in the security of data records and server maintenance.

Recommend ed Articles

This is a guide to MySQL User Permissions. Here we also discuss the definition and how to apply for user permissions in MySQL? along with different examples and its code implementation. You may also have a look at the following articles to learn more –

1. MySQL UNION ALL
2. MySQL Stored Procedure
3. MySQL add user
4. MySQL CURDATE