EDUCBA

Home Software Development Software Development Tutorials MariaDB Tutorial MariaDB create user

MariaDB create user

Updated April 12, 2023

MariaDB create user

 

 

Introduction to MariaDB create user.

MariaDB provides different functionality to the users, in which, with the help of creating a user statement, we can create a new MariaDB account. We require global privilege that is creating users or inserts 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 an account already exist, then the server returns an error message. It shows the same error for create user, drop user, drop the roll and create the role. We can perform different operations on users, but we must have a privilege for that.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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 create a 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.

Some common permission or privilege are 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, the user is able to delete tables or databases.
  • Delete: In delete privilege, the user has permission to delete rows from a specified table.
  • Insert: In delete privilege, the 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 create user.

Given below are the examples of MariaDB create a 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 the 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 create user 1

Example #2

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

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:

users need to 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:

MariaDB create user 3

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 example, 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 create 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 a 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 create 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 create 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:

how we can assign resource

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:

delete the account

Conclusion

From the above article, we have seen the basic syntax of MariaDB create a user, and we also saw different examples of create a user. From this article, we saw how and when we use MariaDB to create user.

Recommended Articles

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

  1. MariaDB Foreign Key
  2. MariaDB Commands
  3. MariaDB UPDATE
  4. Database Management Software