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

How to set up user authorization in MySQL? for this question, this article introduces the corresponding analysis and answer in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

Use the SHOW GRANTS statement to display the user's authorization

You can view the authorization table directly, or you can use the SHOW GRANTS statement to check a user's authorization, in which case it is obviously more convenient to use the SHOW GRANTS statement.

Syntax: SHOW GRANTS FOR user_name

To accommodate the rights granted to users of any host, MySQL supports specifying user_ name values in format.

For example, the following statement shows the permissions of a user admin:

Mysql > SHOW GRANTS FOR

The result is to create a GRNAT authorization statement for the user:

GRANT RELOAD, SHUTDOWN, PROCESS ON *. * TO IDENTIFIED BY PASSWORD 28e89ebc62d6e19a

The password is encrypted.

Use the Grant statement to create a user and authorize

Syntax of the GRANT statement

GRANT priv_type (columns)

ON what

TO user IDENTIFIED BY "password"

WITH GRANT OPTION

To use this statement, you need to complete the following sections:

The permissions assigned to the user by priv_type.

Priv_type can specify any of the following:

ALL PRIVILEGES FILE RELOAD

ALTER INDEX SELECT

CREATE INSERT SHUTDOWN

DELETE PROCESS UPDATE

DROP REFERENCES USAGE

ALL is a synonym for ALL PRIVILEGES, REFERENCES has not been implemented, and USAGE is currently a synonym for "no permissions". It can be used when you want to create an unauthorized user.

For tables, the only priv_ type values you can specify are SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, INDEX, and ALTER.

For columns, the only priv_ type values you can specify are (that is, when you use a column_ list clause) are SELECT, INSERT, and UPDATE.

The column to which the columns permission applies.

This is optional and only sets column-specific permissions. If you name more than one column, separate it with a comma.

Level at which what permissions are applied

GRANT allows the system supervisor to authorize MySQL users at four permission levels:

Global level

Global permissions apply to all on a given server. These permissions are stored in the .user table. You can set global permissions by using ON *. * syntax

Database level

Database permissions apply to all tables in a given database. These permissions are stored in the mysql.db and mysql.host tables. You can set database permissions by using ON db_name.* syntax. If you specify ON * and you have a current database, you will set permissions for that database. Warning: if you specify ON * and you do not have a current database, you will affect global permissions!

Table level

Table permissions apply to all columns of a given table. These permissions are stored in the mysql.tables_ private table. You can set permissions for specific table names through ON tbl_name.

Column level

Column permissions apply to a single column in a given table. These permissions are stored in the mysql.columns_ private table. You can grant permissions to specific columns by specifying a columns clause, while specifying a specific table in the ON clause.

Permissions on a table or column are formed by the logic of four permission levels. For example, if the mysql.user table specifies that a user has a global select permission, it cannot be denied by an entry in the database, table, or column.

Permissions for a column can be calculated as follows:

Global privileges

OR (database privileges AND host privileges)

OR table privileges

OR column privileges

In most cases, you only grant the user permission at one permission level, so the reality is usually not as complicated as described above.

The user with the permission to use user.

To accommodate the rights granted to users of any host, MySQL supports specifying user_ name values in format. If you want to specify a user string for a special character (for example, "-"), or a host string containing special characters or wildcards (for example, "%"), you can enclose the user or host name in parentheses (for example,).

You can specify wildcards in the hostname. For example, user for any host in loc.gov domain, and user for any host in 144.155.166 class C subnet.

The simple form of user is a synonym for ". Note: if you allow anonymous users to connect to the MySQL server (it is the default), you should also add all local users such as, because otherwise, the anonymous user entry for the local host in the mysql.user table will be used when the user tries to log in to the MySQL server from the local machine! Anonymous users are defined by inserting entries with User= into the mysql.user table. By executing this query, you can verify that it works on you:

Mysql > SELECT Host,User FROM mysql.user WHERE User=

The password assigned to the user by password. This is also optional.

On or after MySQL 3.22.12, if you create a new user or if you have global permissions, the user's password will be set to the password specified in the IDENTIFIED BY clause, if one is given. If the user already has a password, it is replaced by a new one.

Warning: if you create a new user but do not specify an IDENTIFIED BY clause, the user does not have a password. It's not safe.

The WITH GRANT OPTION clause is optional.

The WITH GRANT OPTION clause gives a user the ability to grant any permission to other users at a specified permission level. You should be careful with the user you give him grant permission, because two users with different permissions may be able to merge permissions!

Create an instance of a user and authorize it

Create a user with superuser rights:

Mysql > GRANT ALL ON *. * TO IDENTIFIED BY "passwd"-> WITH GRANT OPTION

This statement turns on all permissions in the user table to create a record.

Database-level permissions are authorized with an ON db_name.* clause instead of ON *. *:

Mysql > GRANT ALL ON sample.* TO IDENTIFIED BY "ruby"

These permissions are not global, so they are not stored in the user table. We still need to create a record in the user table (so that the user can connect), but we also need the permission to create a database set of db table records.

Directly modify the authorization table to create a user and authorize

If you remember the previous introduction, you should be able to do what GRANT does even without the Grant statement. Remember that when you modify the authorization table directly, you will notify the server to reload the authorization form, otherwise he will not know about your change. You can execute a mysqladmin flush-privileges or mysqladmin reload command and a FLUSH PRIVILEGES statement to force an overload. If you forget to do this, you will wonder why the server doesn't do what you want to do.

The following GRANT statement creates a superuser with ownership. Including the ability to delegate to others:

GRANT ALL ON *. * TO IDENTIFIED BY "passwd" WITH GRANT OPTION

This statement will turn on all permissions in the user table to create a record, because this is where the superuser (global) permissions are stored, and you want to do the same thing with the INSERT statement, which is:

INSERT INTO user VALUES ("localhost", "anyname", PASSWORD ("passwd")

"Y", "Y")

You may find that it doesn't work, depending on your MySQL version. The structure of the authorization table has changed and you may not have 14 permission columns in your user table. Use SHOW COLUMNS to find out each permission column contained in your authorization table and adjust your INSERT statements accordingly. The following GRANT statement also creates a user with superuser status, but with only a single permission:

GRANT RELOAD ON *. * TO IDENTIFIED BY "flushpass"

The INSERT statement in this example is simpler than the previous one. It is easy to list column names and specify only one permission column. All other columns will be set to the default "N":

INSERT INTO user (Host,Password,Reload) VALUES ("localhost", "flush", PASSWORD ("flushpass"), "Y")

Database-level permissions are authorized with an ON db_name.* clause instead of ON *. *:

GRANT ALL ON sample.* TO IDENTIFIED BY "ruby"

These permissions are not global, so they are not stored in the user table. We still need to create a record in the user table (so that the user can connect), but we also need to create a db table record database set permissions:

Mysql > INSERT INTO user (Host,User,Password)

-> VALUES ("localhost", "boris", PASSWORD ("ruby"))

Mysql > INSERT INTO db VALUES

-> ("localhost", "sample_db", "boris", "Y", "N", "Y", "Y", "Y")

The "N" column is GRANT permission; for a database-level GRANT statement with WITH GRANT OPTION at the end, you want to set the column "Y".

To set table-level or column-level permissions, you use insert statements on tables_priv or columns_priv. Of course, if you don't have GRANT statements, you won't have these tables because they appear at the same time in MySQL. If you do have these tables and want to manipulate them manually for some reason, know that you cannot enable permissions with separate columns.

You set the tables_priv.Table_priv or columns_priv.Column_priv column to include the rights you want to enable. For example, to enable SELECT and INSERT permissions on a table, you need to set Table_priv to "Select,Insert" in the records of the relevant tables_priv.

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.