Go to MySQL to create a user account

Document directory

• 1. create user
• 4. Examples of Application Grant Account creation and authorization

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 the global create user permission for the MySQL database or the insert permission. For each account, create user creates a new record in the mysql. User table without permission. If the account already exists, an error occurs.

You can use the self-selected identified by clause to specify a password for your account. 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 the password as the mixed value returned by the password () function, it must contain the keyword password.

2. Use the grant statement

The best way is to use the grant statement because it is more accurate and has fewer errors. Grant is provided from MySQL 3.22.11. It is mainly used to grant permissions to accounts, but can also be used to create new accounts and grant permissions at the same time. Note: When MySQL runs on no_auto_create_user, you must provide the new user password. Otherwise, you cannot create new users.
The following example shows how to useMySQLClient program to set new users.
First, useMySQLThe program uses the MySQL Root User to connect to the server:

Shell> MySQL-- User = rootMySQL

If you specify a password for the root accountMySQLThe -- password or-P option is provided for other commands in this section.

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 *. * To 'dummy' @ 'localhost ';

The account created using the grant statement has the following attributes:

· 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. No localhost account. When Monty is connected from the local machine,Mysql_install_dbThe anonymous user account of the created localhost takes the lead. 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. (For more information about sorting user tables, see the MySQL manual ).

· An account has a username admin without a password. This account is only used for local connection. The reload and process management permissions are granted. These permissions allow the admin user to executeMysqladmin reload,Mysqladmin refreshAndMysqladmin flush-XxxCommand, andMysqladmin 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.

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 overload 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 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 ().

'Y' value to enable account permissions. For admin accounts, you can also use a more readable insert extension syntax (use 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. No permission column is explicitly set, so MySQL specifies them as the default value 'n '. This is equivalent to the grant usage operation.

Note that to set a Super User Account, you only need to create a user table entry with the permission column set to 'y. The User table permission is global, so no entry is required for other authorization tables.

4. Examples of Application Grant Account creation and authorization

In the following example, three accounts are created to allow them to access the dedicated database. The username and password of each account are custom and obscure.

To create an account using 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.*

    ->     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 access the database 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.

To set a custom account without grant, use the insert statement to directly modify the authorization table:

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 three insert statements add entries to the user table, allowing the User custom to connect from various hosts with the given password, but does not grant global permissions (all permissions are set to the default value 'n '). The following three insert statements add entries to the user table and grant the bankaccount, expenses, and customer database permissions to custom, but can only be accessed from the appropriate host. If the authorization table is directly modified, the server should be notified to use the flush privileges to overload the authorization table so that the permission change takes effect.

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:

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 of the MySQL account management function. PhpMyAdmin is a program.

 

Delete a user account from MySQL

To remove an account, use the drop user statement.