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

This article will explain in detail the example analysis of user and authorization management in MySQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

I. Preface

As a Mysql database administrator, it is important to manage user accounts, indicating which user can connect to the server, where to connect, and what to do after connecting. Mysql has introduced two statements to do this since 3.22.11, the GRANT statement creates the Mysql user and specifies its permissions, and the REVOKE statement removes the permissions. CREATE and REVOKE statements affect four tables

Users who user can connect to the server and any global permissions they have

Db database-level permissions

Tables_priv table-level permissions

Columns_priv column-level permissions

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

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

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 that user in the user table. If the statement specifies any global permissions (administrative permissions or permissions that apply to all databases), these are also recorded in the user table. If you specify database, table, and column-level permissions, they are recorded in the db, tables_priv, and columns_ private tables, respectively.

Create a user and authorize it

The usage of 1.GRANT statement

one

two

three

four

five

six

seven

eight

nine

ten

eleven

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: red color marks out, can be defined, let's give a description below!

two。 Permission classification (privileges)

First group: specifiers apply to databases, tables, and columns

ALTER modifies tables and indexes

CREATE creates databases and tables

DELETE deletes existing records from the table

DROP deletes databases and tables

INDEX creates or discards indexes

INSERT inserts a new row into the table

REFERENCE not used

Records in the SELECT search table

UPDATE modifies existing table records

Group 2: specify the number of databases management permissions

FILE reads or writes files on the server

PROCESS views the thread information executed on the server or kills the thread

RELOAD overloads the authorization table or clears the log, host cache, or table cache

SHUTDOWN shuts down the server

The third group of permissions is special: ALL means "all permissions" and UASGE means no permissions, that is, users are created, but permissions are not granted.

ALL all; ALL PRIVILEGES "all permissions"

USAGE's special "No permission" permission

3.columns

The column in which permissions are applied, which is optional, and you can only set column-specific permissions. If the command has more than one column, you should separate them with a comma.

4.what

The level at which permissions are used. Permissions can be global (applicable to all databases and all tables), specific databases (applicable to all tables in a database), or specific tables. You can specify that a columns statement is column-specific.

5.user

The user to whom permission is granted, which consists of a user name and a host name. In MySQL, you specify not only who can connect, but also where to connect. This allows you to connect two users with the same name from different places. MySQL lets you distinguish between them and grant permissions independently of each other.

A user name in MySQL is the user name you specify when you connect to the server, and this name does not have to be associated with your Unix login or Windows name. By default, if you do not specify a name explicitly, the client will use your login name as the MySQL user name. It's just a pact. You can change the name to nobody in the authorization table, and then use the nobody connection to perform operations that require superuser privileges.

6.password

The password given to the user, it is optional. If you do not specify an 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 the same, when you use IDENTIFIED BY, the password string uses the literal meaning of the password, GRANT will code the password for you, do not use the password () function as you use SET PASSWORD.

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

Note: usernames, passwords, databases, and table names are case-sensitive in authorization table records, while hostnames and column names are not.

III. Types of GRANT statements

In general, 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 to?

Should users be allowed administrative privileges?

1. Who can connect, from there?

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

one

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

Description: db.* means "all tables in the db database"

(2)。 You may have a user free who is out a lot and needs to be able to connect from any host. In this case, you can allow him to connect wherever he comes from:

one

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

Note: the "%" character acts as a wildcard and has the same meaning as LIKE pattern matching. In the above statement, it means "any host". So free and free@% are equivalent. This is the easiest way to build users, but it is also the least secure.

(3)。 You can allow a user to access from a restricted host collection. For example, to allow mary to connect from any host in the free.net domain, use a% .free.net host specifier:

one

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

(4)。 If you prefer, the host portion of the user identifier can be given with an IP address instead of a host name. You can specify an IP address or an address that contains pattern characters, and from MySQL 3.23, you can also specify the IP number with a netmask that indicates the number of bits used for the network number:

one

two

three

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 the specific host to which the user can connect, the second specifies the IP mode for the Class C subnet 192.168.12, and in the third statement, 192.168.12.0 Universe 24 specifies a 24-bit network number and matches the IP address with 192.168.12 first 24 bits.

(5)。 If you specify an error in the user value, you may need to use quotation marks (only separate quotation marks for the user name and host name).

one

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

two。 What level of permissions should users have and what should they apply to?

(1)。 You can grant different levels of permissions, and global permissions are the most powerful because they apply to any database. To make free a superuser who can do anything, including being able to authorize other users, issue the following statement:

one

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

Description: *. * in the ON clause means "all databases, all tables". For security reasons, we specify that free can only connect locally. It is usually wise to limit the hosts to which a superuser can connect, because it limits hosts that try to crack passwords.

Some permissions (FILE, PROCESS, RELOAD, and SHUTDOWN) are administrative rights and can only be authorized with the "ON *. *" global permission specifier. If you prefer, you can grant these permissions instead of database permissions. For example, the following statement sets up a flush user who can only issue flush statements. This may be useful when you need to execute administrative scripts such as emptying logs:

one

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

In general, you want to authorize administrative rights and be stingy, because the users who own them can affect the operation of your server.

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

one

two

GRANT ALL ON db TO free@test.free.net INDETIFIED BY "123456"

GRANT SELECT ON db TO free@% INDETIFIED BY "123456"

Description: the first statement authorizes the permissions of all tables in the db database to free, and the second creates a strictly restricted user free (read-only user), which can only access all tables in the db database, but only read, that is, the user can only issue SELECT statements. You can list a series of permissions granted at the same time. For example, if you want users to be able to read and modify the contents of an existing database, but cannot create new tables or delete tables, grant these permissions as follows: GRANT SELECT,INSERT,DELETE,UPDATE ON db TO free@test.net INDETIFIED BY "123456"

(3)。 For more sophisticated access control, you can authorize it on each table, or even on each column of the table. Column-specific permissions are useful when you want to hide parts of a table from a user, or when you want a user to modify only specific columns. Such as:

one

two

GRANT SELECT ON db.member TO free@localhost INDETIFIED BY "123456"

GRANT UPDATE (expiration) ON db. Member TO free@localhost

Description: the first statement grants read access to the entire member table and sets a password, and the second statement adds UPDATE permissions when only for the expiration column. There is no 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 UPDATE permissions to the address field of the member table for free users, use the following statement, and the new permissions will be added to the existing permissions of the user:

one

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

Description: in general, you don't want to grant any permissions that are wider than the permissions the user really needs. However, when you want users to create a temporary table to hold intermediate results, but you don't want them to do so in a database that contains what they should not modify, a relatively loose permission to be granted on a database occurs. You can do this by creating a separate database (such as tmp) and granting all permissions on the open database. For example, if you want any user from a host in the test.net domain to use the tmp database, you can issue a GRANT statement like this:

one

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

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

Should users be allowed to manage permissions?

You can allow the owner of a database to control access to the database by granting permissions to all owners on the database, specifying WITH GRANT OPTION when authorizing. For example, if you want free to connect from any host in the big.free.com domain and have administrator privileges on all tables in the sales database, you can use the following GRANT statement:

one

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 access authorization to another user. Note that two users with GRANT privileges can authorize each other. If you only give the first user SELECT permission, and the other user has GRANT plus SELECT permission, then the second user can be more "powerful" than the first user.

Remove the right and delete the user

To revoke a user's privileges, use the revoke statement. The syntax of REVOKE is very similar to the GRANT statement, except that TO is replaced by FROM and there are no INDETIFED BY and WITH GRANT OPTION clauses:

one

two

three

four

five

six

seven

eight

nine

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

Let's give a specific description of the red part.

The 1.user part must match the user part of the original GRANT statement for the user you want to revoke.

The 2.privileges part does not need to match, you can use the Grant statement to authorize, and then use the revoke statement to revoke only part of the permission.

The 3.REVOKE statement removes only permissions, not users. Even if you revoke all permissions, the user record in the user table remains, which means that the user can still connect to the server. To delete a user completely, you must explicitly delete the user record from the user table with a DELETE statement, as follows:

one

two

three

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 the user record, while the FLUSH statement tells the server to reload the authorization table. The table is automatically overloaded when you use the GRANT and REVOKE statements, but not when you modify the authorization table directly.

This is the end of this article on "sample Analysis of user and Authorization Management in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.