EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL User Permissions
Secondary Sidebar
MySQL Tutorial
  • Functions
    • MySQL Function
    • MySQL Aggregate Function
    • MySQL String functions
    • MySQL Date Functions
    • MySQL Window Functions
    • MySQL Math Functions
    • MySQL Boolean
    • Cursor in MySQL
    • Condition in MySQL
    • MySQL BETWEEN
    • Insert in MySQL
    • MySQL IFNULL()
    • MySQL TIMESTAMPDIFF()
    • MySQL COALESCE()
    • MySQL count()
    • MIN() in MySQL
    • MySQL Numeric
    • MySQL field()
    • MySQL FIND_IN_SET()
    • MySQL avg()
    • MySQL MAX() Function
    • MySQL BIN()
    • MySQL Concat
    • MySQL DECODE()
    • MySQL REGEXP_REPLACE()
    • MySQL Asynchronous
    • MySQL innodb_buffer_pool_size
    • MySQL key_buffer_size
    • MySQL TRUNCATE()
    • MySQL ROW_NUMBER()
    • NOT in MySQL
    • MySQL IN Operator
    • LIKE in MySQL
    • ANY in MySQL
    • MySQL NOT IN
    • MySQL CHECK Constraint
    • MySQL DISTINCT
    • MySQL ALL
    • MySQL Union
    • MySQL UNION ALL
    • MySQL EXISTS
    • MySQL ON DELETE CASCADE
    • MySQL REGEXP
    • MySQL Index
    • MySQL Add Index
    • MySQL REINDEX
    • MySQL UNIQUE INDEX
    • MySQL Clustered Index
    • MySQL? InnoDB Cluster
    • Table in MySQL
    • ALTER TABLE MySQL
    • MySQL Temporary Table
    • MySQL Clone Table
    • MySQL Repair Table
    • MySQL Lock Table
    • MySQL Optimize Table
    • TRUNCATE TABLE MySQL
    • MySQL?Table?Size
    • MySQL Table Dump
    • MySQL Update Set
    • MySQL ALTER TABLE Add Column
    • MySQL RANK()
    • MySQL CTE
    • MySQL LAG()
    • MySQL GROUP_CONCAT()
    • MySQL EXTRACT()
    • MySQL REPLACE
    • MySQL AUTO_INCREMENT
    • MySQL SYSDATE()
    • MySQL NULLIF()
    • MySQL Substring
    • MySQL SUBSTRING_INDEX()
    • MySQL LOWERCASE
    • MySQL Row
    • MySQL NOW
    • MySQL CEIL
    • MySQL Alias
    • MySQL Trigger
    • MySQL SHOW Triggers
    • MySQL UPDATE Trigger
    • MySQL DELETE Trigger
    • MySQL AFTER UPDATE Trigger
    • MySQL Stored Procedure
    • ROLLUP in MySQL
    • MySQL? INSTR()
    • MySQL Subquery
    • MySQL Timestamp
    • MySQL? Hour()
    • MySQL MOD()
    • MySQL DATE_FORMAT()
    • ALTER Column in MySQL
    • MySQL Rename Column
    • MySQL Interval
    • MySQL CURDATE
    • MySQL BIT
    • MySQL Binlog
    • MySQL Average
    • MySQL TEXT
    • MySQL SHOW
    • MySQL Offset
    • MySQL Timezone
    • mysql_real_escape_string
    • MySQL Datetime
    • MySQL DATE_SUB()
    • MySQL FULLTEXT
    • MySQL DATE_ADD()
    • MySQL sum()
    • MySQL Merge
    • MySQL BigInt
    • MySQL ROUND
    • MySQL VARCHAR
    • MySQL Decimal
    • MySQL Limit
    • MySQL today()
    • MySQL WEEKDAY
    • MySQL Split
    • MySQL Create Function
    • MySQL BLOB
    • MySQL encode()
    • MySQL Primary Key
    • MySQL Foreign Key
    • Unique Key in MySQL
    • MySQL Drop Foreign Key
    • MySQL DROP TRIGGER
    • MYSQL Database
    • Delete Database MySQL
    • MySQL Root
    • MySQL Root Password
    • MySQL Client
    • MySQL Users
    • MySQL?User Permissions
    • MySQL add user
    • MySQL List User
    • MySQL Show Users
    • MySQL User Password
    • MySQL?Cardinality
    • MySQL Workbench
    • MySQL Backup
    • MySQL REVOKE
    • MySQL Dump
    • MySQL Cluster
    • MySQL Full Text Search
    • MySQL Admin Tool
    • MySQL Export Database
    • MySQL Export to CSV
  • Basic
    • MySQL floor
    • MySQL DESCRIBE table
    • MySQL encryption
    • Introduction to MySQL
    • Is SQL Server a Database?
    • What is MySQL
    • Is MySQL Programming Language
    • MySQL Server
    • MySQL AB
    • MySQL Community Server
    • How To Install MySQL
    • MySQL Versions
    • MySQL OpenSource
    • MySQL GUI Tools
    • MySQL Grant
    • MySQL Error 1064
    • MySQL Drop Database
    • MySQL not equal
    • MySQL SELECT INTO Variable
    • MySQL Commands
    • MySQL Operators
    • What is MySQL Schema
    • Wildcards in MySQL
    • MySQL Constraints
    • MySQL Administration
    • MySQL Data Type
    • MYSQL COMMIT
    • MySQL FORMAT
    • Timestamp to Date in MySQL
    • MySQL DATEDIFF
    • MySQL?Incremental Backup
    • MySQL JSON Data Type
    • MySQL ENUM
    • MySQL Default Port
    • Cheat Sheet MySQL
  • Queries
    • MySQL Queries
    • MySQL Query Commands
    • SELECT in MySQL
    • MySQL INSERT IGNORE
    • MySQL having
    • ORDER BY in MySQL
    • MySQL Cheat Sheet
    • MySQL ORDER BY Random
    • MySQL ORDER BY DESC
    • MySQL GROUP BY
    • MySQL GROUP BY Count
    • MySQL GROUP BY month
    • MySQL WHERE Clause
    • MySQL WITH
    • MySQL FETCH
    • MySQL DDL
    • MySQL DML
    • MySQL WHERE IN Array
    • MySQL Fetch Array
    • MySQL ISNULL
    • MySQL Index Types
    • Mysql? Export Schema
    • Amazon RDS for MySQL
    • MySQL greatest
  • Database
    • What is Data Modeling
    • What is Data Processing
    • DBMS Architecture
    • DBMS Keys
    • Careers in Database Administration
    • What is MySQL Database
    • MySQL Relational Database
    • How to Connect Database to MySQL
    • MySQL Database Repair
    • RDBMS Interview Questions
    • DBMS Interview Questions
  • Joins
    • Joins in MySQL
    • MySQL Outer Join
    • Left Outer Join in MySQL
    • MySQL Self Join
    • Natural Join in MySQL
    • MySQL DELETE JOIN
    • MySQL Update Join
    • MySQL Cross Join
  • Advanced
    • MySQL Formatter
    • MySQL TINYINT
    • MySQL Grant All Privileges
    • MySQL DROP TABLE
    • MySQL rename database
    • MySQL Flush Privileges
    • MySQL super Privilege
    • MySQL Character Set
    • MySQL Log File
    • MySQL Flush Log
    • Grant Privileges MySQL
    • MySQL WHILE LOOP
    • IF Statement in MySQL
    • MySQL CASE Statement
    • MySQL IF Function
    • MySQL IF EXISTS
    • MySQL UUID
    • Views in MySQL
    • MySQL Replication
    • MySQL Partition
    • Toad for MySQL
    • Navicat for MySQL
    • MySQL AES_Encrypt
    • MySQL Performance Tuning
    • MySQL Transaction
    • MySQL? sort_buffer_size
    • MySQL? Sync
    • MySQL? Query Cache
    • MySQL Collation
    • MySQL ODBC Driver
    • MySQL Partitioning
    • MySQL InnoDB
    • MySQL Float vs Decimal
    • MySQL Union vs Union All
  • Interview Questions
    • MySQL Interview Questions

MySQL User Permissions

By Aanchal SharmaAanchal Sharma

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

 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:

MySQL User Permissions-1.1

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:

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

Output-1.3

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
Popular Course in this category
MySQL Training Program (12 Courses, 10 Projects)
  12 Online Courses |  10 Hands-on Projects |  92+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

MS SQL Training (16 Courses, 11+ Projects)4.9
Oracle Training (17 Courses, 8+ Projects)4.8
PL SQL Training (4 Courses, 2+ Projects)4.7
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*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?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more