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 Software Development Software Development Tutorials MariaDB Tutorial MariaDB Grant All Privileges
 

MariaDB Grant All Privileges

Sohel Sayyad
Article bySohel Sayyad
EDUCBA
Reviewed byRavi Rathore

Updated June 3, 2023

MariaDB Grant All Privileges

 

 

Definition of MariaDB Grant All Privileges

MariaDB provides all privileges or we can say roles functionality to the user. MariaDB is a compatible, open-source, and relational database management system. We can easily add new users to the MariaDB server and grant them several permissions as per requirement. We can grant the privileges of roles to the specified user account. In the grant privileges statement account name is specified as the user name from the MariaDB server, when we grant all permission to the specified user, then it will be able to access specific databases, and it will also be able to write data if necessary.

Watch our Demo Courses and Videos

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

Syntax:

grant all privileges on *.* to 'specified user name'@local_network identified by 'password_text';

Explanation:

In the above syntax, we used the grant all privileges command to grant all privileges to a specific user, here *.* This symbol refers to a database or table for which the user is given privileges. This statement is specifically used to provide access to all databases or table stores on the MariaDB server. We can use a database name instead of this symbol for security purposes. Specified user name means that the user we need to provide all access with identified by password, as shown in the above syntax.

How grant all privileges work in MariaDB?

Let’s see how to grant all privileges work in MariaDB, basically, it includes different parameters in grant privileges. We can set global privileges for the whole database, table, and individual column in a specified table. Let’s see different parameters as follows.

Global privileges type:

This type of privileges uses *.* This symbol grants global privileges. In this privilege, we can manage administration, database, user account, and privileges for functions, tables, and procedures. All global privileges are stored in MySQL.user table.

Database privileges type:

The database privileges use db_name.* for granting the database privileges for a specific user, or we can use the * symbol for the default database. This privilege includes creating tables, functions, and procedures in the database stored on the MySQL.db.table.

Table privileges type:

This type uses db_name. specified table name to grant the privileges, or we can use table name to specify table name in the default database to grant the table privileges. In this type of table, the keyword is optional. The table privilege is able to change or select records in the table, and we can also grant table privileges for individual columns.

Column privileges type:

In column privileges, we can grant the privileges on a specified column from a table by providing a list of columns after the privileges type. This allows us to control columns from specified tables, meaning users can change records or select records as per requirement.

Functional privileges type:

We grant these privileges using the function db_name.routine_name or the function routine_name to specify the function for the default database.

Procedure privileges type:

In this type of privilege, we use procedure db_name or procedure routine_name to specify the procedure in the default database.

Examples

Let’s see a different example to understand the grant privileges better.

So first create a user for different operations by using the following statement.

CREATE USER 'Jeny'@localhost IDENTIFIED BY 'Jent123';

Explanation:

With the help of the above statement, we created the user name Jeny with the password Jent123, as shown in the above information. We tWe illustrate the final output of the above query using the following snapshot.

MariaDB Grant All Privileges 1

Now let’s see how we can grant all privileges to created users as follows.

GRANT ALL PRIVILEGES ON *.* TO 'Jeny'@localhost IDENTIFIED BY 'Jent123';

Explanation:

In the above example, we grant all privileges command to grant all privileges to specific users that Jeny as shown in the above statement. We illustrate the final output of the above query using the following snapshot.

MariaDB Grant All Privileges 2

Now we can see if the right permission is granted by using the following statement.

SHOW GRANTS FOR 'Jeny'@localhost;

Explanation:

In the above statement, we use the show grant command to see all privileges of the specified user name Jeny. We illustrate the final output of the above query using the following snapshot.

MariaDB Grant All Privileges 3

Now let’s see how we can grant all permission by using the following statement as follows.

GRANT ALL PRIVILEGES ON sample.* TO 'Jeny'@localhost;

Explanation:

In the above example, we grant all privileges command with a specified database here database name is a sample that is already created, and the user name is Jeny, as shown above, statement. The final output of the above query we illustrate by using the following snapshot.

Snapshot 4

Now let’s see how we can grant global privileges on specified databases as follows.

GRANT SELECT ON sample.*  TO Jeny@localhost;

Explanation:

In the above example, we use the grant select command with the specified database name and user name, here database name as a sample, and the user name Jeny as shown in the above statement. Similarly, we grant globally by using *.* this symbol. We illustrate the final output of the above query using the following snapshot.

output 1

Now let’s see how we can grant table privileges as follows.

Syntax:

grant clause on database name.table name to user_name@localhoast;

Explanation:

In the above syntax, we implement table privileges, in which we can use grants with different clauses such as select, delete and update with the database name and table name to specified user name as shown above syntax.

Now see Column privileges syntax as follows.

Syntax:

grant select (colm name 1, colm name 2,……colm name N), update(colm name) on specified table name to specified user name @loalhost;

Explanation:

In the above syntax, we use the grant command to implement column privileges, here, we use different clauses with different column names followed by a specified table name and user name as shown in the above syntax.

So in this way, we can grant different privileges using the above syntax.

Conclusion

We hope you understand that MariaDB grants all privileges from this article. From the above article, we have learned the basic syntax of MariaDB grant all privileges and different examples of grant privileges statements. This article taught us how and when to use MariaDB to grant all privileges.

Recommended Articles

We hope that this EDUCBA information on “MariaDB Grant All Privileges” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MariaDB Foreign Key
  2. Commands in MariaDB
  3. MariaDB UPDATE
  4. MariaDB vs MySQL

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 Software Development Course

Web development, programming languages, Software testing & 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