EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development Software Development Tutorials MariaDB Tutorial MariaDB add user
Secondary Sidebar
MariaDB Tutorial
  • MariaDB
    • MariaDB Versions
    • MariaDB? list users
    • MariaDB Commands
    • MariaDB odbc
    • MariaDB Workbench
    • MariaDB for windows
    • MariaDB Server
    • MariaDB? Data Types
    • MariaDB? boolean
    • MariaDB phpMyAdmin
    • MariaDB Mysqldump
    • MariaDB Java Connector
    • MariaDB insert
    • MariaDB UPDATE
    • MariaDB? rename column
    • MariaDB AUTO_INCREMENT
    • MariaDB Timezone
    • MariaDB GROUP_CONCAT
    • MariaDB wait_timeout
    • MariaDB MaxScale
    • MariaDB? with
    • MariaDB GUI
    • MariaDB? create?table
    • MariaDB? SHOW TABLES
    • MariaDB alter table
    • MariaDB List Tables
    • MariaDB JSON Functions
    • MariaDB Foreign Key
    • MariaDB? trigger
    • MariaDB Grant All Privileges
    • MariaDB Select Database
    • MariaDB? create database
    • MariaDB Delete Database
    • MariaDB Join
    • MariaDB JSON
    • MariaDB? show databases
    • MariaDB List Databases
    • MariaDB Functions
    • MariaDB? TIMESTAMP
    • MariaDB create user
    • MariaDB add user
    • MariaDB Max Connections
    • MariaDB show users
    • MariaDB Delete User
    • MariaDB? change user password
    • MariaDB? change root password
    • MariaDB reset root password
    • MariaDB IF
    • MariaDB bind-address
    • MariaDB Transaction
    • MariaDB Cluster
    • MariaDB Logs
    • MariaDB Encryption
    • MariaDB? backup
    • MariaDB Replication
    • MariaDB max_allowed_packet
    • MariaDB? performance tuning
    • MariaDB export database
    • MariaDB? import SQL

MariaDB add user

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.

Syntax:

create user user_name@localhost name identified by ‘pass_word’;

Explanation:

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

  • 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 in One Software Development Bundle(600+ Courses, 50+ projects)
Python TutorialC SharpJavaJavaScript
C Plus PlusSoftware TestingSQLKali Linux
Price
View Courses
600+ Online Courses | 50+ projects | 3000+ Hours | Verifiable Certificates | Lifetime Access
4.6 (86,560 ratings)
  • 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

This is a guide to MariaDB add user. Here we discuss the introduction, how to add user in MariaDB? and examples, respectively. You may also have a look at the following articles to learn more –

  1. MariaDB MaxScale
  2. MariaDB AUTO_INCREMENT
  3. MariaDB Foreign Key
  4. MariaDB UPDATE
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Software Development Course

C# Programming, Conditional Constructs, Loops, Arrays, OOPS Concept

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more