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

This article introduces the knowledge of "how to increase the authority of 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!

The Grant statement can be used in mysql to add permissions to the user. The syntax "GRANT permission type ON permission level value TO user [IDENTIFIED BY 'password'] [WITH clause]; where the parameter" user "indicates the user account in the format" user name "@" host name ".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In MySQL, you can use GRANT statements to authorize and increase permissions for users.

The syntax format is as follows:

GRANT priv_type [(column_list)] ON database.tableTO user [IDENTIFIED BY 'password'] [WITH with_option [with_option]...]

Where:

The priv_type parameter indicates the permission type

The columns_list parameter indicates which columns the permission applies to. When this parameter is omitted, it acts on the entire table.

Database.table is used to specify the level of permissions

The user parameter represents the user account, which consists of a user name and a host name, with the format "'username'@'hostname'"

The IDENTIFIED BY parameter is used to set the password for the user

The password parameter is the user's new password.

The groups of permissions that can be granted in MySQL are as follows:

Column permissions, 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 name column in the table students.

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

Database permissions, related to all tables in a specific database. For example, you can have permission to create a new table in an existing database mytest.

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

Accordingly, the values that can be used to specify the permission level in the GRANT statement have the following formats:

*: represents all tables in the current database.

*. *: represents all tables in all databases.

Db_name.*: represents all tables in a database, and db_name specifies the database name.

Db_name.tbl_name: represents a table or view in a database, db_name specifies the database name, and tbl_name specifies the table or view name.

Db_name.routine_name: represents a stored procedure or function in a database, and routine_name specifies the name of the stored procedure or function.

TO clause: if permission is granted to a user that does not exist, MySQL automatically executes a CREATE USER statement to create the user, but a password must be set for that user at the same time.

In MySQL, only users with GRANT privileges can execute GRANT statements.

Example:

Create a new user testUser with a password of testPwd using the GRANT statement. User testUser has the right to query, insert and grant GRANT permission to all data.

Mysql > GRANT SELECT,INSERT ON *. *-> TO 'testUser'@'localhost'-> IDENTIFIED BY' testPwd'-> WITH GRANT OPTION;Query OK, 0 rows affected, 1 warning (0.05 sec)

Use the SHOW GRANTS statement to query the permissions of user testUser, as shown below.

Extended knowledge: permission type description

1) when granting database permissions, you can specify the following values:

The permission name corresponds to the field description in the user table SELECTSelect_priv means that the user is granted permission to access all tables and views in a particular database using the SELECT statement. INSERTInsert_priv means that the user is granted permission to add rows to all tables in a particular database using the INSERT statement. DELETEDelete_priv means that the user is granted permission to delete data rows for all tables in a particular database using the DELETE statement. UPDATEUpdate_priv means that the user is granted permission to update the values of all data tables in a particular database using the UPDATE statement. REFERENCESReferences_priv means that the user is granted permission to create an off-table key in a specific database. CREATECreate_priv represents the permission of authorized users to create new tables in a specific database using the CREATE TABLE statement. ALTERAlter_priv means that the user is granted permission to modify all data tables in a particular database using the ALTER TABLE statement. SHOW VIEWShow_view_priv means that users are granted permission to view view definitions of views that already exist in a particular database. CREATE ROUTINECreate_routine_priv means that the user is granted permission to create stored procedures and stored functions for a specific database. ALTER ROUTINEAlter_routine_priv means that users are granted permission to update and delete stored procedures and stored functions that already exist in the database. INDEXIndex_priv means that users are granted permission to define and delete indexes on all data tables in a particular database. DROPDrop_priv means that the user is granted permission to delete all tables and views in a particular database. CREATE TEMPORARY TABLESCreate_tmp_table_priv means that the user is granted permission to create temporary tables in a specific database. CREATE VIEWCreate_view_priv means that users are granted permission to create new views in a specific database. EXECUTE ROUTINEExecute_priv means that the user is granted permission to call stored procedures and stored functions of a specific database. LOCK TABLESLock_tables_priv means that the user is granted permission to lock existing tables in a specific database. ALL or ALL PRIVILEGES or SUPERSuper_priv indicates all the above permissions / super permissions

2) when granting table permissions, you can specify the following values:

Permission names correspond to fields in the user table that SELECTSelect_priv grants users permission to access specific tables using SELECT statements INSERTInsert_priv grants users permission to add rows to a specific table using INSERT statements DELETEDelete_priv grants users permission to delete rows from a specific table using DELETE statements DROPDrop_priv grants users permission to delete data tables Permission to update a specific data table using the UPDATE statement ALTERAlter_priv grant the user permission to modify the data table using the ALTER TABLE statement REFERENCESReferences_priv grant the user the right to create a foreign key to reference a specific data table CREATECreate_priv grant the user the right to create a data table with a specific name INDEXIndex_priv grant the user the right to define an index on the table ALL or ALL PRIVILEGES or SUPERSuper_priv all permission names

3) when granting column permissions, the values can only be specified as SELECT, INSERT and UPDATE, and the column name list column-list needs to be followed by the permission.

4) the most efficient permissions are user rights.

When you grant a user permission, in addition to all the values you can specify when granting database permissions, you can also have the following values:

CREATE USER: means that the user is granted permission to create and delete new users.

SHOW DATABASES: means to grant the user permission to view the definitions of all existing databases using the SHOW DATABASES statement.

This is the end of the content of "how to increase permissions 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.