Updated June 3, 2023
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.
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.
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.
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.
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.
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.
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.
