Shulou(Shulou.com)05/31 Report--

In this issue, Xiaobian will bring you about how to create user accounts in Mysql. The article is rich in content and analyzed and described from a professional perspective. After reading this article, I hope you can gain something.

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 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.

An account can be given a password using the optional IDENTIFIED BY clause. 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 a hash value returned by the PASSWORD() function, include the keyword PASSWORD.

2. Use the GRANT statement

The best approach is to use the GRANT statement because it is more precise and makes fewer errors. GRANT has been provided since MySQL 3.22.11; its primary purpose is to authorize accounts, but it can also be used to create new accounts and authorize them simultaneously. Note: When mysql runs on no_auto_create_user, provide the password of the new user, otherwise you cannot create a new user.

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 MySQL root:

shell> MySQL --user=root MySQL

If you specify a password for the root account, you will also need to provide the--password or-p option for this MySQL command and the other commands in this section.

After connecting to the server as root, you can add new accounts. The following statement uses GRANT to set up four new accounts:

mysql> GRANT ALL PRIVILEGES ON *.* TO

-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO

-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT RELOAD,PROCESS ON *.* TO ;

mysql> GRANT USAGE ON *.* TO ;

Accounts created with the GRANT statement have the following attributes:

Two of the accounts have the same username monty and password some_pass. Both accounts are super user accounts with full privileges to do anything. An account () is only used when connected from the local machine. Another account () can be used to connect from other hosts. Please note that both accounts of monty must be able to connect with monty from any host. Without localhost accounts, anonymous user accounts for localhost created by mysql_install_db will take precedence when monty connects from native. As a result, monty will be treated as an anonymous user. The reason is that anonymous user accounts have more specific Host column values than accounts, so they rank first in the user table sort order. (user See the mysql manual for a discussion of table sorting.)

·An account has the username admin and no password. This account is only used to connect from the local machine. RELOAD and PROCESS administrative privileges granted. These permissions allow the admin user to execute mysqladmin reload, mysqladmin refresh, and mysqladmin flush xxx commands, as well as mysqladmin processlist. Access to the database was not granted. You can add such permissions via the GRANT statement.

·An account has a username dummy and no password. This account is only used to connect from the local machine. Permission not granted. With the USAGE permission in the GRANT statement, you can create accounts without granting any permissions. It can set all global permissions to 'N'. Assume that you will later grant specific permissions to the account.

3. Direct operation MySQL authorization table

Instead of GRANT, you can create the same account directly with INSERT statements and then tell the server to overload the authorization table using FLUSH PRIVILEGES.

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, changes will only be noticed after restarting the server. With GRANT, FLUSH PRIVILEGES is not required.

PASSWORD() is used with INSERT to encrypt passwords. The GRANT statement encrypts your password, so PASSWORD() is not required.

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 with the specified values. None of the permission columns are explicitly set, so MySQL assigns them all to the default 'N'. This is equivalent to the operation of GRANT USAGE.

Note that to set up a superuser account, you only need to create a user table entry with the permissions column set to 'Y'. The user table permissions are global, so no entries are required for other authorization tables.

4. A few examples of using grant to create accounts and authorizations

The following example creates 3 accounts and allows them to access a private database. The username for 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

-> IDENTIFIED BY 'obscure';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

-> ON expenses.*

-> TO

-> IDENTIFIED BY 'obscure';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

-> ON customer.*

-> TO

-> IDENTIFIED BY 'obscure';

These three accounts can be used for:

·The first account can access the bankaccount database, but only from its own computer.

The second account has access to 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 custom accounts without GRANT, modify the authorization table directly using the INSERT statement:

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 a given password, but without granting global permissions (all permissions are set to the default 'N'). The next three INSERT statements add entries to the user table, granting bankaccount, expenses, and customer database permissions to custom, but only from the appropriate host. In general, if you modify the authorization table directly, you should tell the server to reload the authorization table with FLUSH PRIVILEGES for the permission change to take effect.

If you want a user to be accessible from all machines in a given domain (e.g. mydomain.com), you can use a GRANT statement with a '%' wildcard in the host portion of the account name:

mysql> GRANT ...

-> ON *.*

-> TO

-> IDENTIFIED BY 'mypass';

To do this by modifying the authorization table directly:

mysql> INSERT INTO user (Host,User,Password,...)

-> VALUES('%.mydomain.com','myname',PASSWORD('mypass'),...);

mysql> FLUSH PRIVILEGES;

5. Other ways to create accounts are third-party programs that use MySQL's account management capabilities. MyAdmin is a program.

Delete user accounts from MySQL

To remove an account, use the DROP USER statement.

The above is how to create a user account in Mysql shared by Xiaobian. If you happen to have similar doubts, you may wish to refer to the above analysis for understanding. If you want to know more about it, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.