MySQL user and authorization management

Outline

I. Preface
2. Create and authorize a user
Iii. Types of GRANT statements
4. revoke permissions and delete users

Recommended reading:

MySQL user management

MySQL user tools

I. Preface

As a Mysql database administrator, managing user accounts is a very important thing to point out which user can connect to the server, where to connect, and what to do after the connection. Mysql introduced two statements from 3.22.11 To Do This. GRANT statements create Mysql users and specify their permissions, while REVOKE statements delete permissions. The CREATE and REVOKE statements affect four tables,

• Users can connect to users on the server and have any global permissions.

• Database-level Permissions

• Tables_priv table-level Permissions

• Columns_priv column-level permission

There are also 5th authorization tables host, but it is not affected by GRANT and REVOKE. Let's take a look at all the tables in the mysql database,

1234567891011121314151617181920212223242526272829303132

mysql> use mysql; Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 24 rows in set (0.00 sec)

When you issue a GRANT statement to a user, create a record for the user in the user table. If the statement specifies any global permissions (administrative permissions or permissions applicable to all databases), these are also recorded in the user table. If you specify database, table, and column-level permissions, they are recorded in db, tables_priv, and columns_priv tables respectively.

2. Create and authorize a user

1. GRANT statement usage

1234567891011

mysql> ? grant Name: 'GRANT'Description: Syntax: GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_specification [, user_specification] ... [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}] [WITH with_option ...]

The syntax of the GRANT statement looks like this,

GRANT privileges [columns] ON what TO user identified by "password" with grant option;

Note: All red-colored items can be defined. Let's explain them one by one!

2. Permission classification (privileges)

First group: the specified delimiter applies to databases, tables, and columns.

• ALTER table and Index

• CREATE Database and table Creation

• DELETE existing records in the table

• DROP delete database and table

• Create or discard an INDEX

• INSERT a new row into the table

• REFERENCE unused

• SELECT to retrieve records in a table

• UPDATE modify existing table records

Group 2: Specify database count management Permissions

• FILE: reads or writes files on the server.

• PROCESS: View information about the thread executed on the server or kill the thread.

• RELOAD: RELOAD the authorization table or clear logs, host cache, or table Cache

• SHUTDOWN the server

The third group has special permissions: ALL means "ALL Permissions", and UASGE means no permissions, that is, creating users, but not granting permissions.

• ALL; all privileges "ALL Permissions"

• USAGE special "no permission" permission

3. columns
The permission column is optional, and you can only set specific permissions for the column. If the command has more than one column, separate them with commas.

4. what
Permission usage level. Permissions Can be global (applicable to all databases and tables), specific databases (applicable to all tables in a database), or specific tables. You can specify a columns statement to indicate that the permission is column-specific.

5. user
The user authorized by the permission, which consists of a user name and host name. In MySQL, you not only specify who can connect, but also where to connect. This allows two users with the same name to connect from different places. MySQL allows you to differentiate them and grant them permissions independently.
A user name in MySQL is the user name specified when you connect to the server. It does not need to be associated with your Unix or Windows Name. By default, if you do not specify a specific name, the customer program uses your login name as the MySQL user name. This is just an agreement. You can change the name to nobody in the authorization table, and then use the nobody connection to perform operations that require superuser permissions.

6. password
The password assigned to the user. It is optional. If you do not specify the identified by clause for a new user, the user is not assigned a password (Insecure ). For existing users, any password you specify will replace the old password. If you do not specify a password, the old password remains unchanged. When you use identified by, the password string uses the literal meaning of the password, and GRANT will encode the password for you, do not use the PASSWORD () function as you use SET password.

7. The with grant option clause is optional. If you include it, you can GRANT permissions to other users through the GRANT statement. You can use this clause to grant permissions to other users.

Note: usernames, passwords, databases, and table names are case sensitive in the authorization table records, but the host name and column name are not.

Iii. Types of GRANT statements

Generally, you can identify the types of GRANT statements by asking a few simple questions:

• Who can connect from there?

• What level of permissions should users have and what do they apply?

• Should the user be allowed to manage permissions?

1. Who can connect from there?

(1). You can allow a user to connect from a specific host or a series of hosts.

1	GRANT ALL ON db.* TO free@localhost IDENTIFIED BY "123456";

Note: db. * indicates "all tables in the db database ".

(2) You may have a free user who often goes out and needs to be connected from any host. In this case, you can allow him to connect from anywhere:

1	GRANT ALL ON db.* TO free@% IDENTIFIED BY "123456";

Note: The "%" character is used as a wildcard and matches the LIKE pattern. In the preceding statement, it means "any host ". So free and free @ % are equivalent. This is the easiest way to build a user, but it is also the least secure.
(3) You can allow a user to access from a restricted host set. For example, to allow mary to connect from any host in the free.net domain, use a % .free.net host specified character:

1	GRANT ALL ON db.* TO mary@%.free.net IDENTIFIED BY "123456";

(4). If you like, the host part of the User Identifier can be specified with an IP address instead of a host name. You can specify an IP address or an address that contains a pattern character. In addition, from MySQL 3.23, you can also specify an IP number with a network mask indicating the number of digits used for the network number:

123	GRANT ALL ON db.* TO free@192.168.12.10 IDENTIFIED BY "123456"; GRANT ALL ON db.* TO free@192.168.12.% IDENTIFIED BY "123456"; GRANT ALL ON db.* TO free@192.168.12.0/24 IDENTIFIED BY "123456";

Note: The first example indicates that the user can connect to a specific host, and the second one specifies the IP Mode for Class C subnet 192.168.12. In the third statement, 192.168.12.0/24 specifies a 24-bit network number and matches an IP address with the 24-bit header of 192.168.12.

(5). If the user value you specified reports an error, you may need to use quotation marks (only separate the user name and host name with quotation marks ).

1	GRANT ALL ON db.* TO "free"@"test.free.net" IDENTIFIED BY "123456";

2. What level of permissions should users have and what should they apply?

(1) You can grant different levels of permissions. Global permissions are the most powerful because they apply to any database. To make free a Super User who can do anything, including authorizing other users, issue the following statement:

1	GRANT ALL ON *.* TO free@localhost IDENTIFIED BY "123456" WITH GRANT OPTION;

Note: *. * In the ON Clause indicates "all databases and tables ". For security considerations, we specify that free can only be connected locally. It is usually wise to restrict the host that a Super User can connect to because it limits the host that tries to crack the password.
Some permissions (FILE, PROCESS, RELOAD, and SHUTDOWN) are administrative permissions and can only be authorized with the "ON *. *" Global permissions. If you want to, you can grant these permissions without authorizing database permissions. For example, if the following statement sets a flush user, it can only issue flush statements. This may be useful when you need to execute management scripts such as clearing logs:

1	GRANT RELOAD ON *.* TO flushl@localhost IDENTIFIED BY "123456";

Generally, you want to authorize management permissions, because users with these permissions can affect the operations on your servers.

(2). Database-level permissions apply to all tables in a specific database. They can be granted by using the ON db_name. * clause:

12	GRANT ALL ON db TO free@test.free.net INDETIFIED BY "123456"; GRANT SELECT ON db TO free@% INDETIFIED BY "123456";

Note: The first statement grants permissions to all tables in the database free. The second statement creates a user free (read-only) with strict access restrictions and can only access all tables in the database, but only read, that is, the user can only issue the SELECT statement. You can list a series of permissions granted at the same time. For example, if you want users to read and modify the content of an existing database but cannot create or DELETE a new table, GRANT the following permissions: grant select, INSERT, DELETE, update on db TO free@test.net indetified by "123456 ";

(3) For more refined access control, you can grant permissions on each table or even on each column of the table. When you want to hide a part of a table from a user, or you want a user to modify only specific columns, column-specific permissions are very useful. For example:

12	GRANT SELECT ON db.member TO free@localhost INDETIFIED BY "123456"; GRANT UPDATE (expiration) ON db. member TO free@localhost;

Note: The first statement grants read permission to the entire member table and sets a password. The second statement adds the UPDATE permission when only the expiration column is configured. You do not need to specify a password because the first statement has already been specified.

(4) If you want to grant permissions to multiple columns, specify a list separated by commas. For example, to add the UPDATE permission for the address field of the member table to a free user, use the following statement to add the new permission to the user's existing permissions:

1	GRANT UPDATE (street,city,state,zip) ON db TO free@localhost ;

Note: Generally, you do not want to grant any permissions that are higher than the permissions you actually need. However, when you want users to create a temporary table to save intermediate results, but you do not want them to do so in a database that contains the content they should not modify, A relatively loose permission is granted to a database. You can create a separate database (such as tmp) and grant all permissions to the database. For example, if you want any user from a host in the test.net domain to use the tmp database, you can issue the following GRANT statement:

1	GRANT ALL ON tmp.* TO ""@test.net;

After you have done this, you can create a table in tmp. tb_name and reference it in the form of tmp (create an anonymous user in "" in the user-specified character, and any user matches the blank user name ).

3 Should users be allowed to manage permissions?

You can allow a database owner to control database access by granting all database owner permissions. During authorization, specify with grant option. For example, if you want free to connect to any host in the big.free.com domain and have the Administrator permission for all tables in the sales database, you can use the following GRANT statement:

1	GRANT ALL ON sales.* TO free@%.big.free.com INDETIFIED BY "123456" WITH GRANT OPTION;

In effect, the with grant option clause allows you to GRANT the Access Authorization right to another user. Note that two users with the GRANT permission can authorize each other. If you only grant select permission to the first user and grant select permission to the other user, the second user can be the first user more powerful ".

4. revoke permissions and delete users
To cancel a user's permissions, use the REVOKE statement. The syntax of REVOKE is very similar TO the GRANT statement, except that it is replaced by from without the indetifed by and with grant option clauses:

123456789

mysql> ? REVOKE Name: 'REVOKE'Description: Syntax: REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ...

The syntax of the REVOKE statement looks like this,
REVOKE privileges (columns) ON what FROM user;

The red section is described as follows,

1. the user part must match the user part of the user you want to revoke permission from the original GRANT statement.

2. privileges does not need to be matched. You can use the GRANT statement to GRANT permissions, and then use the REVOKE statement to REVOKE only some permissions.
3. The REVOKE statement only deletes permissions and does not delete users. Even if you revoke all permissions, the user records in the user table are retained, which means that the user can still connect to the server. To completely DELETE a user, you must use a DELETE statement to explicitly DELETE user records from the user table. The specific operations are as follows:

123	mysql -u root -p 123456 mysql mysql>DELETE FROM user WHERE User="user_name" and Host="host_name"; mysql>FLUSH PRIVILEGES;

The DELETE statement deletes user records, while the FLUSH statement tells the server to overload the authorization table. (When you use the GRANT and REVOKE statements, the table is automatically reloaded, but not when you directly modify the authorization table ).

V. Summary

After the above instructions, I think everyone should be clear about user authorization. Hey, hey! Pai_^ ......