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 a number of permissions as per requirement. In which we can grant the privileges of roles to the specified user account. In 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.
Syntax:
grant all privileges on *.* to ‘specified user name’@local_network identified by ‘password_text’;
Explanation:
In the above syntax we used grant all privileges command to grant all privileges to a specific user, here *.* This symbol is used to refer to a database or table for which the user is given privileges. This statement specifically used to provide access to all databases or table stores on the MariaDB server. For security purposes, we can use a database name instead of this symbol. 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 grant all privileges work in MariaDB, basically, it includes different parameters in grant privileges. We can set globally 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, and a user account as well as privileges for functions, tables, and procedures. All global privileges are stored in the MySQL.user table.
Database privileges type:
The database privileges use db_name.* for granting the database privileges for a specific user or we can just use * symbol for the default database. This privilege includes creating tables as well as functions, procedures in the database and it is stored on the MySQL.db.table.
Table privileges type:
This type uses db_name. specified table name to grant the privileges or we can just use table name to specify table name in the default database to grant the table privileges. In this type table 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 privileges all of us to control columns from specified tables that mean users can change records or select records as per requirement.
Functional privileges type:
These privileges we grant using the function db_name.routine_name or we can just use 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 we can use procedure routine_name to specify procedure in the default database.
Examples
Let’s see a different example to better understand the grant privileges as follows.
So first create a user for different operations by using the following statement.
7 Online Courses | 8 Hands-on Projects| 73+ Hours| Verifiable Certificate of Completion
4.5
View Course
CREATE USER 'Jeny'@localhost IDENTIFIED BY 'Jent123';
Explanation:
With the help of the above statement, we created the user name Jeny with password Jent123 as shown in the above statement. The final output of the above query we illustrate by using the following snapshot.
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. The final output of the above query we illustrate by using the following snapshot.
Now we can see if the right permission is granted or not 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 is Jeny. The final output of the above query we illustrate by using the following snapshot.
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 as a sample that is already created and user name as Jeny as shown above statement. The final output of the above query we illustrate by using the following snapshot.
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 grant select command with the specified database name and user name, here database name as sample and user name Jeny as shown in the above statement. Similarly, we grant globally by using *.* this symbol. The final output of the above query we illustrate by using the following snapshot.
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 by using the above syntax.
Conclusion
We hope from this article you have understood about the MariaDB grant all privileges. From the above article, we have learned the basic syntax of MariaDB grant all privileges and we also see different examples of grant privileges statements. From this article, we learned how and when we use MariaDB grant all privileges.
Recommended Articles
This is a guide to MariaDB Grant All Privileges. Here we discuss How to grant all privileges in MariaDB? and Examples with code implementation. You can also go through our other suggested articles to learn more –
