MySQL creates a new user, adds an account, and uses instance _ MySQL

This article mainly introduces two methods for creating new users and adding accounts in MySQL and how to use instances. This article also introduces how to update and change passwords, you can create a MySQL account in either of the following ways:

1. use the GRANT statement
2. directly operate the MySQL authorization table

The best way is to use the GRANT statement because it is more accurate and has fewer errors.

Create a super user:

The code is as follows:

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;

The two accounts have the same username and password "monty" and "some_pass. Both accounts are superuser accounts and have full permissions to do anything. An account ('Monty '@ 'localhost') is only used for connection from the local machine. Another account ('Monty '@' % ') can be used to connect from other hosts. Note that the two accounts of monty must be able to connect to monty from any host.

If you do not have a localhost account, the anonymous user account of the localhost created by mysql_install_db takes the lead when monty is connected from the local machine. As a result, monty is considered an anonymous user. The reason is that the Host column value of the anonymous user account is more specific than that of the 'Monty '@' % 'account, so that it is ranked first in the sorting order of the user table.

Create a Management user without Database permissions:

The code is as follows:

Mysql> grant reload, process on *. * TO 'admin' @ 'localhost ';

An account has a username admin and no password. This account is only used for local connection. The RELOAD and PROCESS management permissions are granted. These permissions allow admin users to execute mysqladmin reload, mysqladmin refresh, mysqladmin flush-xxx, and mysqladmin processlist. No database access permission is granted. You can use the GRANT statement to add such permissions.

An account has a username, dummy, and no password. This account is only used for local connection. No permission is 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 grant specific permissions to this account in the future.

The code is as follows:

Mysql> grant usage on *. * TO 'dummy' @ 'localhost ';

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 overload the authorization table:

The code is as follows:

Shell> mysql -- user = root mysql
Mysql> insert into user VALUES ('localhost', 'Monty ', PASSWORD ('Some _ pass'), 'y ', 'Y', 'y ', 'Y ');
Mysql> insert into user VALUES ('%', 'Monty ', PASSWORD ('Some _ pass'), '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 why you use flush privileges when using INSERT to create an account is to tell the server to re-read the authorization table. Otherwise, the change will be noticed only after the server is restarted. If you use GRANT, you do not need to use flush privileges.

Use the INSERT function to encrypt the PASSWORD. The GRANT statement encrypts your PASSWORD, so you do not need PASSWORD ().

Create three accounts to allow them to access the dedicated database. The username and password of each account are custom and obscure.

The code is 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 three accounts can be used:

1st accounts can access the bankaccount database, but can only be accessed from the local machine.
2nd accounts can access the expenses database, but only from the host whitehouse.gov.
3rd accounts can access the customer database, but can only access the database from the host server. domain.

If you want to allow a user to access all machines in a given domain (for example, mydomain.com), you can use the GRANT statement containing the '%' wildcard in the host part of the account name:

The code is as follows:

Mysql> GRANT... ON *. * TO 'myname' @ '% .mydomain.com' identified by 'mypass ';

MySQL deletes a user account

Using drop user, you can cancel an account and its permissions as follows:

The code is as follows:

Drop user user;

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

Key point: drop user cannot automatically close any opened USER conversations. In addition, if a user cancels a dialog, the command does not take effect until the user dialog is closed. Once the dialog is closed and the user is canceled, the user will fail to log on again. This is intentionally designed.

Set account password

Use the mysqladmin command to specify the password in the command line

The code is as follows:

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

This command resets the password for the account in the user table matching the user_name and Host columns in the User column.

Another method for granting a PASSWORD to an account is to execute the set password statement:

The code is as follows:

Mysql> set password for 'Jeffrey '@' % '= PASSWORD ('biscuit ');

Only root users who can update the mysql database can change the passwords of other users.

If you do not connect with anonymous users, you can omit the FOR clause to change your password:

The code is as follows:

Mysql> set password = PASSWORD ('biscuit ');

You can also use the grant usage statement (in *. *) at the global level to specify the password of an account without affecting the account's current permissions:

The code is as follows:

Mysql> grant usage on *. * TO 'jeffre' @ '%' identified by 'biscuit ';

In general, it is best to use the above method to specify the password

To create a Password when creating a new account, provide a value in the Password column:

The code is as follows:

Mysql> insert into user (Host, User, Password) VALUES ('%', 'Jeffrey ', PASSWORD ('biscuit '));
Mysql> flush privileges;

To change the Password of an existing account, use UPDATE to set the Password column value:

The code is as follows:

Mysql> UPDATE user SET Password = PASSWORD ('bagel') WHERE Host = '%' AND User = 'Francis ';
Mysql> flush privileges;