Mysql Create new user, add account 2 ways and use example _mysql

You can create a MySQL account in two ways:

1. Use the GRANT statement
2. Direct operation of MySQL authorization form

The best way to do this is to use the GRANT statement because it is more accurate and less error.

To create a super User:

Copy Code code as follows:

Mysql> grant all privileges in *.* to ' Monty ' @ ' localhost ' identified by ' Some_pass ' with GRANT OPTION;
Mysql> grant all privileges in *.* to ' Monty ' @ '% ' identified by ' Some_pass ' with GRANT OPTION;

Two of these accounts have the same username Monty and password Some_pass. Two accounts are superuser accounts with full privileges to do anything. An account (' Monty ' @ ' localhost ') is used only when connecting from the local computer. Another account (' Monty ' @ '% ') can be used to connect from other hosts. Please note that the two accounts of Monty must be able to connect Monty from any host.

If there is no localhost account, the localhost anonymous user account created by mysql_install_db will be preempted when Monty is connected from the local computer. As a result, Monty will be treated as an anonymous user. The reason is that the host column value for the anonymous user account is more specific than the ' Monty ' @ '% ' account, which is listed first in the User table sort order.

Create an administrative user and do not grant database permissions:

Copy Code code as follows:

Mysql> GRANT reload,process on *.* to ' admin ' @ ' localhost ';

An account has user name Admin, no password. This account is only used to connect from the local computer. Reload and process management permissions are granted. These permissions allow the Admin user to perform mysqladmin reload, mysqladmin refresh and mysqladmin flush-xxx commands, and Mysqladmin processlist. Permission to access the database is not granted. You can add such permissions through the GRANT statement.

An account has user name dummy, no password. This account is only used to connect from the local computer. 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 '. Assume that you will later grant specific permissions to the account.

Copy Code code as follows:

Mysql> GRANT USAGE on *.* to ' dummy ' @ ' localhost ';

In addition to grant, you can create the same account directly with the INSERT statement, and then use flush privileges to tell the server to overload the authorization table:

Copy Code code as follows:

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 you use flush privileges when you create an account with insert is to tell the server to reread the authorization form. Otherwise, the change will be noticed only after restarting the server. Using GRANT, you do not need to use flush privileges.

Use the password () function with insert to encrypt the password. The GRANT statement encrypts your password so that no password () is required.

Create 3 accounts that allow them to access the private database. The username for each account is custom, and the password is obscure.

Copy Code code as follows:

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.* to ' custom ' @ ' whitehouse.gov ' identified by ' Obscure ';
Mysql> GRANT Select,insert,update,delete,create,drop on customer.* to ' custom ' @ ' Server.domain ' identified by ' Obscure ';

These 3 accounts can be used to:

A 1th account can access the BankAccount database, but only from this computer.
A 2nd account can access the expenses database, but only from the host whitehouse.gov.
A 3rd account can access the customer database, but only from the host Server.domain.

If you want a user to access all machines from 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:

Copy Code code as follows:

Mysql> GRANT ... On *.* to ' myname ' @ '%.mydomain.com ' identified by ' mypass ';

MySQL Delete user account

With drop user, you can cancel an account and its permissions by doing the following:

Copy Code code as follows:

DROP user User;

This statement can delete account permission records from all authorization tables.

Important: The DROP user does not automatically close any open user conversations. Also, if the user has an open conversation and the user is canceled, the command does not take effect until the user dialog is closed. Once the conversation is closed and the user is canceled, the user will fail when he or she attempts to log on again. This is intended to be designed.

Set Account password

To specify a password on the command line with the Mysqladmin command

Copy Code code as follows:

shell> mysqladmin-u user_name-h host_name password "newpwd"

This command resets the account for the password to the user_name and host columns that match the user column in the user table, and the record of the client you initiated the connection to.

Another way to assign a password to an account is to execute the SET Password statement:

Copy Code code as follows:

mysql> SET PASSWORD for ' Jeffrey ' @ '% ' = PASSWORD (' biscuit ');

Only users such as root can update the MySQL database to change the password of other users.

If you are not connected to an anonymous user, you can change your password by omitting the FOR clause:

Copy Code code as follows:

mysql> SET PASSWORD = PASSWORD (' biscuit ');

You can also use the Grant Usage statement (in *.*) at the global level to specify the password for an account without affecting the current permissions on the account:

Copy Code code as follows:

Mysql> GRANT USAGE on *.* to ' Jeffrey ' @ '% ' identified by ' biscuit ';

It is generally preferable to use the above method to specify a password

To establish a password when creating a new account, provide a value in the password column:

Copy Code code as follows:

Mysql> INSERT into User (Host,user,password) VALUES ('% ', ' Jeffrey ', Password (' biscuit '));
mysql> FLUSH privileges;

To change the password for an existing account, use Update to set the password column value:

Copy Code code as follows:

mysql> UPDATE user SET Password = Password (' bagel ') WHERE Host = '% ' and user = ' Francis ';
mysql> FLUSH privileges;