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

This article introduces the relevant knowledge of "introduction to the methods and permissions of creating, deleting and changing users in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Create a user

It is officially recommended that the creation syntax is:

CREATE USER [IF NOT EXISTS] user [auth_option] [, user [auth_option]]... [REQUIRE {NONE | tls_option [[AND] tls_option]...}] [WITH resource_option [resource_option]...] [password_option | lock_option]... user: (see Section 6.2.4 "Specifying Account Names") auth_option: {IDENTIFIED BY 'auth_string' | IDENTIFIED WITH auth_plugin | IDENTIFIED WITH auth_plugin BY' auth_string' | IDENTIFIED WITH auth_plugin AS 'auth_string' | IDENTIFIED BY PASSWORD' auth_string'} tls_option: {SSL | X509 | CIPHER 'cipher' | ISSUER' issuer' | SUBJECT 'subject'} resource_option: {MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS _ PER_HOUR count | MAX_USER_CONNECTIONS count} password_option: {PASSWORD EXPIRE | PASSWORD EXPIRE DEFAULT | PASSWORD EXPIRE NEVER | PASSWORD EXPIRE INTERVAL N DAY} lock_option: {ACCOUNT LOCK | ACCOUNT UNLOCK}

Usually, our common creation syntax is:

CREATE USER [IDENTIFIED] BY [PASSWORD]

The syntax is as follows:

1)

Specifies that a user account is created in the format 'user_name'@'host_name'. Where user_name is the user name, and the host_name host name is the name of the host where the user connects to MySQL. If during the creation process, only the user name of the account is given, but no host name is specified, the host name defaults to "%", which represents a group of hosts.

2) PASSWORD

Optional, used to specify the hash password, that is, if you set the password in clear text, you need to ignore the PASSWORD keyword; if you do not want to set the password in clear text and know the hash value returned to the password by the PASSWORD () function, you can specify this hash value in the password setting statement, but you need to add the keyword PASSWORD.

3) IDENTIFIED BY clause

Used to specify the password corresponding to the user account. If the user account does not have a password, this clause can be omitted.

4)

Specify the password for the user account, after the IDENTIFIED BY keyword or PASSWOED keyword. The given password value can be plaintext consisting only of letters and numbers, or a hash value obtained by the PASSWORD () function.

You should pay attention to the following points when using CREATE USER statements:

If no password is specified for the user when using the CREATE USER statement, MySQL allows the user to log on to the system without a password, which is not recommended from a security point of view.

To use the CREATE USER statement, you must have INSERT permissions or global CREATE USER permissions for the mysql database in MySQL.

After you create a user account using the CREATE USER statement, a new record is added to the user table of the system's own MySQL database. If the account you created already exists, an error occurs during the execution of the statement.

Newly created users have very few permissions. They can log in to MySQL and only allow operations that do not require permissions, such as using SHOW statements to query the list of all storage engines and character sets.

If two users have the same user name and different hostname, MySQL treats them as different users and allows the two users to be assigned different permission sets.

Example:

# Note: test_user@'%' and test_user@'localhost' are two different users, CREATE USER 'test_user'@'%' identified by' 123456 creation USER 'test_user'@'localhost' identified by' 123456789. Change user

Changing user information mainly includes renaming, changing password, locking or unlocking users. The following examples will show you these uses:

# rename user RENAME USER 'test_user'@'%' to' test'@'%';# modify password ALTER USER 'test'@'%' identified by' 123456789 password leading # lock or unlock user ALTER USER 'test'@'%' ACCOUNT LOCK;ALTER USER' test'@'%' ACCOUNT UNLOCK;3. Delete user

The DROP USER statement can be used in the MySQL database to delete one or more user accounts and related permissions.

Officially recommended grammatical format:

DROP USER [IF EXISTS] user [, user]...

You should pay attention to the following points when using DROP USER statements:

The DROP USER statement can be used to delete one or more MySQL accounts and revoke their original permissions.

To use the DROP USER statement, you must have DELETE permissions or global CREATE USER permissions for the mysql database in MySQL.

In the use of the DROP USER statement, if the hostname of the account is not explicitly given, the hostname defaults to "%".

4. User authorization

When a user is successfully created, no action can be performed, and the user needs to be assigned the appropriate access rights. You can use the SHOW GRANT FOR statement to query the user's permissions.

Note: the newly created user only has the right to log in to the MySQL server, does not have any other permissions, and cannot do anything else.

USAGE ON. Indicates that the user does not have permissions on any database or any table.

For the newly created MySQL user, it must be authorized, and the GRANT statement can be used to authorize the newly created user. Officially recommended grammatical format:

GRANT priv_type [(column_list)] [, priv_type [(column_list)]]... ON [object_type] priv_level TO user [auth_option] [, user [auth_option]]... [REQUIRE {NONE | tls_option [[AND] tls_option]...}] [WITH {GRANT OPTION | resource_option}...] GRANT PROXY ON user TO user [, user]. [WITH GRANT OPTION] object_type: {TABLE | FUNCTION | PROCEDURE} priv_level: {* | *. * | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_name} user: (see Section 6.2.4 "Specifying Account Names") auth_option: {IDENTIFIED BY 'auth_string' | IDENTIFIED WITH auth_plugin | IDENTIFIED WITH auth_plugin BY' auth_string' | IDENTIFIED WITH auth_plugin AS 'auth_string' | IDENTIFIED BY PASSWORD' auth_string'} tls_option: {SSL | X509 | CIPHER 'cipher' | ISSUER' issuer' | SUBJECT 'subject'} resource_option: {| MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count}

First of all, we should know that permissions are graded. There are several groups of permissions that can be granted:

Column level, related to a specific column in the table. For example, you can use the UPDATE statement to update the permissions for the values of the student_name column in the table students.

Table level, which is related to all the data in a specific table. For example, you can use the SELECT statement to query permissions for all data in the table students.

The database level, which is related to all tables in a specific database. For example, you can have permission to create a new table in an existing database mytest.

Global, related to all databases in MySQL. For example, you can delete an existing database or create a new database.

The following table shows all the permissions that can be granted and their meaning:

Permission meaning and grantable level ALL [PRIVILEGES] grants all permissions at the specified access level, except GRANT OPTION and PROXY. ALTER enables ALTER TABLE. Level: global, database, table. ALTER ROUTINE allows you to change or delete stored procedures. Level: global, database. CREATE enables database and table creation. Level: global, database, table. CREATE ROUTINE enables stored procedure creation. Level: global, database. CREATE TABLESPACE enables tablespaces and log filegroups to create, change, or delete. Level: overall. CREATE TEMPORARY TABLES enables CREATE TEMPORARY TABLE. Level: global, database. CREATE USER allows the use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES. Level: global. CREATE VIEW enables the view you want to create or change. Level: global, database, table. DELETE enables DELETE. Level: global, database, table. DROP enables you to delete databases, tables, and views. Level: global, database, table. EVENT enables event usage. Level: global, database. EXECUTE enables users to execute stored procedures. Level: global, database. FILE enables users to enable the server to read or write files. Level: overall. GRANT OPTION enables permissions granted to or removed from other accounts. Level: global, database, table, agent. INDEX enables the index to be created or deleted. Level: global, database, table. INSERT enables INSERT. Level: global, database, table, column. LOCK TABLES allows you to use the LOCK TABLES table to which you have the SELECT permission. Level: global, database. PROCESS enables users to view all process SHOW PROCESSLIST. Level: overall. PROXY enables user agents. Level: from user to user. REFERENCES enables foreign key creation. Level: global, database, table, column. RELOAD enables FLUSH operations. Level: overall. REPLICATION CLIENT enables users to ask for the location of the master or slave server. Level: overall. REPLICATION SLAVE enables replication slaves to read binary logs from the primary server. Level: overall. SELECT enables SELECT. Level: global, database, table, column. SHOW DATABASES enables SHOW DATABASES to display all databases. Level: overall. SHOW VIEW enables SHOW CREATE VIEW. Level: global, database, table. SHUTDOWN enables mysqladmin shutdown. Level: overall. SUPER can use debug commands such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin. Level: overall. TRIGGER enables the trigger operation. Level: global, database, table. UPDATE enables UPDATE. Level: global, database, table, column. Synonyms for USAGE "no privileges"

In fact, the grant statement can directly create a user and authorize it. It is recommended that you first use the create user statement to create a user and then authorize it separately. Here I will show you how to authorize it with an example:

# Global permissions GRANT super,select on *. * to 'test_user'@'%';# library permissions GRANT select,insert,update,delete,create,alter,execute on `testdb`. * to' test_user'@'%';# table permissions GRANT select,insert on `testdb`.tb to 'test_user'@'%';# column permissions GRANT select (col1), insert (col1, col2) ON `testdb`.mytbl to' test_user'@'%';5. Reclaim permission

In MySQL, you can use the REVOKE statement to reclaim the permissions of a user, and that user will not be deleted.

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]]... ON [object_type] priv_level FROM user [, user]... REVOKE ALL [PRIVILEGES], GRANT OPTION FROM user [, user]...

The syntax is as follows:

The syntax format of REVOKE syntax and GRANT statement is similar, but has the opposite effect.

The first syntax format is used to reclaim certain permissions.

The second syntax format is used to reclaim all permissions for a specific user.

To use the REVOKE statement, you must have global CREATE USER or UPDATE permissions for the MySQL database.

In general, we will use show grants syntax to query the user's permissions, and if we find that the permissions are too large, we will use revoke syntax to retrieve the permissions. Examples are as follows:

# View user permissions mysql > show grants for 'test_user'@'%' +-+ | Grants for test_user@% | +- -+ | GRANT USAGE ON *. * TO 'test_user'@'%' | | GRANT SELECT INSERT, UPDATE, DELETE, CREATE, DROP, ALTER EXECUTE ON `testdb`. * TO 'test_user'@'%' | +-+ 2 rows in Set (0.00 sec) # found that the permission is too large If you want to reclaim drop,alter permissions, you can reclaim them like this: REVOKE drop,alter on `testdb`. * from 'test_user'@'%' This is the end of the introduction to "methods and permissions for creating, deleting and changing users in MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.