EDUCBA

Home Software Development Software Development Tutorials MariaDB Tutorial MariaDB add user

MariaDB add user

Updated April 12, 2023

MariaDB add user

 

 

Introduction to MariaDB add user

MariaDB provides different functionality to the users, in which, with the help of creating a user statement, we can add a new MariaDB account. We require global privilege that is creating users or insert privilege to create new users on the MariaDB server; for every user or new each new account created, the user statement creates a new row in the mysql.user table, and that has no privileges. If any permission, privilege specified, and account already exists, then the server returns an error message. It shows the same error for create user, drop user, drop roll and create role. We can perform different operations on users, but we must have a privilege for that.

Watch our Demo Courses and Videos

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

Syntax:

create user user_name@localhost name identified by 'pass_word';

Explanation:

  • In the above syntax, we use the create user statement followed by user name. After that, we used a specified local host name with a password; here, we use identified by key for a password to a specified user, and the password should be in pain text; if we do not specify the password, then the user is able to connect without a password.
  • The password provides a hash, and it will be stored in mysql.user table, and it is an optional clause to the MariaDB user.

How to add user in MariaDB?

Basically, MariaDB is an open-source database management software it is useful to store data, retrieve data, and organize data. Normally MariaDB transmits data between client and server without encryption of data; it is possible when client and server run in the same networks. It has a different privilege, or we can say permissions.

Let’s see how the create statement works in MariaDB as follows.

Here we short listed some common permission or privilege as follows:

  • All Privileges: In all privileges it allows all access to the MariaDB user to design database or global access.
  • Create: Create privilege allows to the user to create a new database or new table.
  • Drop: In this privilege, user is able to delete tables or databases.
  • Delete: In delete privilege, user has permission to delete rows from a specified table.
  • Insert: In delete privilege, user has permission to insert rows from a specified table.
  • Select: We the help of a select command user can read all databases.
  • Update: With the help of update privilege, user can update rows from a table.
  • Grant Option: It allows granting or revoking other user permission or privileges.

Examples of MariaDB add user

Given below are the examples of MariaDB add user:

Example #1

Code:

create user demo@test identified by 'pass123';

Explanation:

  • In the above example, we use to create user statement to create a new account on the MariaDB server, here we created a demo user with password pass123 to secure a new user account.
  • Here we used an identified clause to create a password for the demo user and test is the localhost name. Here we use localhost to run client and server in the same network.
  • The final output of the above query we illustrate by using the following snapshot.

Output:

MariaDB add user 1

Let’s see another example of creating users in MariaDB as follows.

Example #2

Supposed users need to see all users created; then, we use the following statement.

Code:

select user from mysql.user;

Explanation:

  • In the above example, we use a select clause to see all exited users from mysql.user table.
  • The final output of the above query we illustrate by using the following snapshot.

Output:

see all users created

Example #3

Code:

GRANT ALL PRIVILEGES ON *.* TO 'demo'@test IDENTIFIED BY 'pass123';

Explanation:

  • Sometimes a newly created user does not have any permission or privileges to handle the database and tables, so at that time, we can grant the permission by using the above statement. In the above example, we use the grant all privileges command to assign all privileges to the specified user. Here *.* is used to refer to a database or table for a specified user.
  • This command provides access to the entire database on the server; we can replace this symbol by using the database name that we are providing. The final output of the above query we illustrate by using the following snapshot.

Output:

0 rows affected

Now we can see all grant permission by using the following statement as follows.

Code:

SHOW GRANTS FOR 'demo'@test;

Explanation:

  • In the above example, we use the show grants command to see all grants of a specific user; in this exa,mple the user is a demo, and we need all privileges of this user at that time; we use the above statement.
  • The final output of the above query we illustrate by using the following snapshot.

Output:

MariaDB add user 4

Sometimes a user needs to set a password for a particular time period for security purposes. At that time, we use the following statement.

Code:

create user 'demo2'@'test' password expire interval 140 day;

Explanation:

  • In the above example, we use to create user command to create user; in this example, we created a user name as demo2 on the local environment as shown in the above statement. Here we assign an expiry date of the password that is 140 days.
  • After the date of user creation. The final output of the above query we illustrate by using the following snapshot.

Output:

MariaDB add user 5

Now see created user properties by using the show command as follows.

Code:

SHOW CREATE USER 'demo2'@'test';

Explanation:

  • In the above example, we use the show command to see users; in this example, we show details of demo2 users with their local environments.
  • The final output of the above query we illustrate by using the following snapshot.

Output:

MariaDB add user 6

Now let’s see how we can assign resource limits to specific users as follows.

Code:

create user 'demo3'@'test' with
MAX_USER_CONNECTIONS 125
MAX_QUERIES_PER_HOUR 300;

Explanation:

  • In the above example, we used to create a user statement the same as the previous example. Here we created a new user name as demo3 and assigned max user connection as well as max queries per hour as shown above statement.
  • The final output of the above query we illustrate by using the following snapshot.

Output:

demo3

Example #4

The supposed user needs to delete the account from the MariaDB server, or we can say drop user at that time following the statement.

Code:

drop user 'demo'@test;

Explanation:

  • With the help of a statement, we can drop the user; here, we drop the demo user.
  • The final output of the above query we illustrate by using the following snapshot.

Output:

user needs to delete the account

Recommended Articles

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

  1. MariaDB MaxScale
  2. MariaDB AUTO_INCREMENT
  3. MariaDB Foreign Key
  4. MariaDB UPDATE