This MariaDB tutorial explains how to Assign privileges to MariaDB user with syntax and examples with our MySQL support services. Bobcares answers all your questions no matter how small or large.
Assign privileges to MariaDB user
MariaDB is an open-source, fully compatible, relational database management system (RDBMS), it is said that one of the most common tasks when administering a database is to oversee access and permissions. The MariaDB is an easy method to add new users and grants different degrees of privileges.
Initially, the newly created user will not have privileges assigned to manage databases nor to the MariaDB shell.
To grant all privileges to user1:
GRANT ALL PRIVILEGES ON *.* TO 'user1'@localhost IDENTIFIED BY 'password';
In this statement ” *.* ” refers to the database or table to which the user is given privileges. This specific command gives access to all databases located on the server. As this might have major security issue, you just need to replace the symbol with the name of the database you are providing access to.
To grant privileges only for yourDB then type:
GRANT ALL PRIVILEGES ON 'yourDB'.* TO 'user1'@localhost;
It’s crucial to refresh the privileges once new ones have awarded with the command:
FLUSH PRIVILEGES;
The user that you have created has full privileges and access to the specified tables and database. You can also verify the new user1 right permissions by using the below command:
SHOW GRANTS FOR 'user1'@localhost;
This grants all the privileges to user information, provided by the system displayed on the terminal. In MariaDB, you can utilize the SHOW GRANTS command to show all grant information for the user. This would display privileges that are assigned to the user using the GRANT command.
Syntax:
The syntax for SHOW GRANTS command in MariaDB is:
SHOW GRANTS [ FOR username ]
Parameters or Arguments:
The name of the database account for which to display the grant information is user_name
To view the privileges of a user, you must have SELECT privilege in the MariaDB database.
Privileges:
It can be any of the below values in the description:
Privilege | Description |
---|---|
SELECT | Ability to perform SELECT statements on the table. |
INSERT | Ability to perform INSERT statements on the table. |
UPDATE | Ability to perform UPDATE statements on the table. |
DELETE | Ability to perform DELETE statements on the table. |
INDEX | Ability to create an index on an existing table. |
CREATE | Ability to perform CREATE TABLE statements. |
ALTER | Ability to perform ALTER TABLE statements to change the table definition. |
DROP | Ability to perform DROP TABLE statements. |
GRANT OPTION | Allows you to grant the privileges that you possess to other users. |
ALL | Grants all permissions except GRANT OPTION. |
[Need assistance with similar queries? We are here to help]
Conclusion
To conclude, You can successfully create a MariaDB user and grant full user privileges this basic task should quickly become a routine. There are several ways to customize privileges and adjust them to your need.
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
0 Comments