EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL User Permissions
 

MySQL User Permissions

Aanchal Sharma
Article byAanchal Sharma
EDUCBA
Reviewed byRavi Rathore

Updated June 6, 2023

Introduction to MySQL User Permissions

The MySQL user account is assigned user permissions, which determine the actions that the user is allowed to perform on the server. The degrees of rights these user permissions apply to for various query executions may vary. Administrative permissions are global privileges not specified to a specific MySQL database that enables the account users to regulate the admin operations in the MySQL server.

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

MySQL User Permissions

How to Apply User Permissions in MySQL?

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

Examples of MySQL User Permissions

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

When we work on a server like a 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 an admin user or root user. We must use CREATE USER and GRANT privileges command to create 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:

MySQL User Permissions-1.1

Here, it would be best to use the actual username in place of the test user and the associated password as the user password.

Suppose we want to replace or modify the hostname 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 hostname and associated password for remote access from a device with IP 10.2.0.8 are set to establish a new user.

Also, there may be a 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 hostname. 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 MySQL supports:

  • 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 specific 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:

Output-1.2

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 specific MySQL database, we need to execute the following query:

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

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

DROP USER 'testuser'@'localhost';

Output:

Output-1.3

Conclusion

MySQL, a well-known database storage and management system, is responsible for storing, organizing, and retrieving data as needed. However, one must be given particular user permissions at different authority levels to do the server actions. The root user can access the whole database and administer all related activities. User permissions can be 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

We hope that this EDUCBA information on “MySQL User Permissions” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW