How to create user account in Mysql

1.CREATE USER
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
 [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
CREATE USER is used to create a new MySQL account. To use CREATE USER, you must have global CREATE USER permission on the mysql database, or have INSERT permission. For each account, CREATE USER creates a new record in the mysql.user table without permissions. If the account already exists, an error occurs.
Use the optional IDENTIFIED BY clause to give the account a password. The user value and password are given in the same way as the GRANT statement. In particular, to specify a password in plain text, ignore the PASSWORD keyword. To specify a password as the hashed value returned by the PASSWORD() function, include the keyword PASSWORD.
2. Use the GRANT statement
The best way is to use the GRANT statement, because it is more precise and has fewer errors. GRANT is provided as of MySQL 3.22.11; its main purpose is to authorize accounts, but it can also be used to create new accounts and authorize them at the same time. Note: When mysql is running in no_auto_create_user, the password for the new user must be provided, otherwise the user cannot be created.
The following example shows how to use the MySQL client program to set up a new user.
First, use the MySQL program to connect to the server as the MySQL root user:
shell> MySQL --user=root MySQL
If you specify a password for the root account, you also need to provide -- for this MySQL command and other commands in this section. password or -p option.
After connecting to the server as root, you can add a new account. The following statement uses GRANT to set up four new accounts:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
->   IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> GRANT USAGE ON ***. Both accounts are superuser accounts with full permissions to do anything. An account ('monty'@'localhost') is only used when connecting from local machine. Another account ('monty'@'%') can be used to connect from other hosts. Please note that both monty accounts must be able to connect to monty from any host. Without a localhost account, when monty connects from this machine, the anonymous user account of localhost created by mysql_install_db will take precedence. As a result, monty will be treated as an anonymous user. The reason is that the Host column value of the anonymous user account is more specific than the 'monty'@'%' account, so it is ranked higher in the user table sort order. (For discussion of user table sorting, please refer to the mysql manual).
· An account has the username admin and no password. This account is only used to connect from this machine. RELOAD and PROCESS administrative rights are granted. These permissions allow the admin user to execute the mysqladmin reload, mysqladmin refresh and mysqladmin flush-xxx commands, as well as the mysqladmin processlist. Permission to access the database is not granted. You can add such permissions through the GRANT statement.
· An account has the username dummy and no password. This account is only used to connect from this machine. Permission not granted. With the USAGE permission in the GRANT statement, you can create an account without granting any permissions. It can set all global permissions to 'N'. It is assumed that you will grant specific permissions to this account later.
3. Directly operate the MySQL authorization table
In addition to GRANT, you can directly use the INSERT statement to create the same account, and then use FLUSH PRIVILEGES to tell the server to reload the authorization table.
shell> mysql --user=root mysql
mysql> INSERT INTO user
-> VALUES('localhost','monty',PASSWORD('some_pass'),
-> 'Y','Y',' Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> ; INSERT INTO user
-> VALUES('%','monty',PASSWORD('some_pass'),
-> 'Y','Y','Y','Y','Y',' Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User ='admin',
-> Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;
The reason for using FLUSH PRIVILEGES when you create an account with INSERT is to tell the server to reread the authorization table. Otherwise, the changes will not be noticed until the server is restarted. Using GRANT, there is no need to use FLUSH PRIVILEGES.
Use the PASSWORD() function with INSERT to encrypt the password. The GRANT statement encrypts the password for you, so PASSWORD() is not needed.
The 'Y' value enables account permissions. For admin accounts, you can also use the more readable INSERT extended syntax (using SET).
In the INSERT statement for the dummy account, only the Host, User and Password columns in the user table are recorded as the specified values. None of the permission columns are explicitly set, so MySQL assigns them all to the default value 'N'. This is equivalent to the operation of GRANT USAGE.
Please note that to set up a super user account, you only need to create a user table entry with the permission column set to 'Y'. The user table permissions are global permissions, so no entries are needed in other authorization tables.
4. Give a few examples of applying grant to create accounts and authorizations
The following example creates 3 accounts and allows them to access a dedicated database. The user name of each account is custom and the password is obscure.
To create an account with GRANT, use the following statement:
shell> MySQL --user=root MySQL
shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
- > ON bankaccount.*
-> TO 'custom'@'localhost'
-> IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON expenses. - & gt; to'custom'@'SERVER.DAIN'I- & gt; Identify by 'obscure';
These 3 accounts can be used for:
· 1 account can access the BankAcCount database, but it can only be accessed from this machine.
· The second account can access the expenses database, but only from the host whitehouse.gov.
· The third account can access the customer database, but only from the host server.domain.
To set up a custom account without GRANT, use the INSERT statement to modify the authorization table directly:
shell> mysql --user=root mysql
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost', 'custom',PASSWORD('obscure'));
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('whitehouse.gov','custom',PASSWORD('obscure'));
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('server.domain','custom',PASSWORD('obscure'));
mysql> INSERT INTO db
-> (Host,Db ,User,Select_priv,Insert_priv,
-> Update_priv,Delete_priv,Create_priv,Drop_priv)
-> VALUES('localhost','bankaccount','custom',
-> 'Y','Y',' Y','Y','Y','Y');
mysql> INSERT INTO db
-> (Host,Db,User,Select_priv,Insert_priv,
-> Update_priv,Delete_priv,Create_priv,Drop_priv)
-> VALUES('whitehouse.gov','expenses','custom',
-> 'Y','Y','Y','Y','Y','Y');
mysql> ; INSERT INTO db
-> (Host,Db,User,Select_priv,Insert_priv,
-> Update_priv,Delete_priv,Create_priv,Drop_priv)
-> VALUES('server.domain','customer','custom' ,
-> 'Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;

The first 3 INSERT statements add entries to the user table, allowing users to custom connect from various hosts with the given password, but do not grant global permissions (all permissions are set to the default value 'N') . The next three INSERT statements add entries to the user table, granting bankaccount, expenses, and customer database permissions to custom, but they can only be accessed from the appropriate host. Usually, if you directly modify the authorization table, you should tell the server to use FLUSH PRIVILEGES to reload the authorization table to make the permission changes take effect.
If you want to give a user access from all machines in a given domain (for example, mydomain.com), you can use the GRANT statement with the '%' wildcard character in the host part of the account name:
mysql> GRANT ...
- -> ON *.*
- -> TO 'myname'@'%.mydomain.com'
-> IDENTIFIED BY 'mypass';
To achieve this by directly modifying the authorization table:
mysql> INSERT INTO user (Host,User,Password,...)
-> VALUES('%.mydomain.com','myname',PASSWORD('mypass'),...);
mysql> FLUSH PRIVILEGES;
5 .Another way to create an account is to use a third-party program using the MySQL account management functionality. phpMyAdmin is a program.

Delete user account from MySQL
To remove an account, you should use the DROP USER statement.