Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/sql/mysql-create-user-statement/ below:

MySQL CREATE USER Statement - GeeksforGeeks

Last Updated : 21 Aug, 2024

The CREATE USER statement in MySQL is an essential command used to create new user accounts for database access. It enables database administrators to define which users can connect to the MySQL database server and specify their login credentials.

In this article, We will learn about MySQL CREATE USER Statement in detail and so on.

• The CREATE USER statement in SQL is used to create a new user and a password to access that user.
  
• MySQL allows us to specify which user account can connect to a database server. The user account details in MySQL contain two information - username and host from which the user is trying to connect in the format username@host-name. 
  
• If the admin user is connecting through localhost then the user account will be admin@localhost.  MySQL stores the user account in the user grant table of the MySQL database. 
  
• The CREATE USER statement in MySQL allows us to create new MySQL accounts or in other words, the CREATE USER statement is used to create a database account that allows the user to log into the MySQL database.

Syntax

The syntax for the CREATE USER statement in MySQL is: 

CREATE USER user_account IDENTIFIED BY password;

1. user_account: It is the name that the user wants to give to the database account. The user_account should be in the format 'username'@'hostname' 
2. password: It is the password used to assign to the user_account.The password is specified in the IDENTIFIED BY clause.

Let's look at some examples of the CREATE USER command in MySQL, and understand it's working.

In this example, we will create a new user "gfguser1" that connects to the MySQL database server from the localhost with the password "abcd".

Query: 

CREATE USER gfguser1@localhost IDENTIFIED BY 'abcd';

Note: The create user statement only creates a new user, it does not grant any permissions to the user.  

In this example, we will create more than one new user that connects to the MySQL database server from the localhost.

Queries: 

CREATE USER
'gfguser2'@'localhost' IDENTIFIED BY 'efgh',
'gfguser3'@'localhost' IDENTIFIED BY 'uvxy';

The above code creates two new users with username "gfguser2" and "gfguser3" with passwords "efgh" and "uvxy" respectively. 

To allow a user account to connect from any host, the percentage (%) wildcard is used in the following way.

Query:

CREATE USER gfguser1@'%'
IDENTIFIED BY 'abcd'; 

To allow the user account to connect to the database server from any subdomain of the "mysqltutorial.org" host, then the percentage wildcard % should be used as follows: 

Query: 

CREATE USER gfguser@'%.mysqltutorial.org'
IDENTIFIED by 'abcd'

The SHOW GRANTS statement is used to view the permissions of a user account.

Syntax 

SHOW GRANTS FOR user-account;

SHOW GRANTS FOR gfguser1@localhost;

Output: 

The *.* in the output denotes that the "gfguser1" user account can only login to the database server and has no other privileges. 

In conclusion, the CREATE USER statement in MySQL is a fundamental command that allows the creation of user accounts with specific login credentials. By understanding how to create both single and multiple users, as well as allowing connections from specific hosts or wildcard domains, database administrators can effectively manage user access to the MySQL database server. Additionally, using the SHOW GRANTS command enables administrators to view the permissions of user accounts, ensuring that proper access control measures are in place.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4