MySQL user permissions

MySQL can assign strict and complex permissions to different users. Most of these operations can be implemented using the SQL command grant (assign permission) and revoke (revoke permission. Grant can assign the specified permission to a specific user. If the user does not exist, a user is created.

Grant

Common formats of Grant:
GrantPrivileges[Columns]
OnItem
ToUser_name[Identified'Password']
[RequireSsl_options]
[With [grant option |Limit_options]

Ssl_optionsIndicates some SSL-related options. With grant option indicates that a specified user is allowed to grant permissions to others. You can also use the following with clause:

• Max_queries_per_hourN
• Max_updates_per_hourN
• Max_connections_per_hourN

Specify the maximum number of queries, updates, and connections that a user can perform per hour.

More importantlyPriveleges(Permission ).

Permissions of common users

Permission	Apply	Description
Select	Table and Column	Allow users to select rows (Records) from the table)
Insert	Table and Column	Allows users to insert new rows in a table
Update	Table and Column	Allows you to modify values in rows in an existing table.
Delete	Table	Allows you to delete rows in an existing table.
Index	Table	Allows users to create and drag a specific table Index
Alter	Table	Allows you to change the structure of an existing table. For example, you can add a column, rename a column or table, and modify the Data Type of a column.
Create	Databases and tables	Allows users to create new databases or tables. If a specific database or table is specified in grant, they can only create the database or table, that is, they must first Delete (drop) it.
Drop	Databases and tables	Allows users to drag (delete) databases or tables

Administrator privilege

Permission	Description
Create temporary tables	Allow the Administrator to use the temporary keyword in the create table statement.
File	Allow reading data from a file to a table or from a table to a file
Lock tables	Allow the use of the lock tables statement
Process	Allows administrators to view server processes of all users
Reload	Allows administrators to reload authorization tables, clear authorization, hosts, logs, and tables.
Replication Client	Allows the use of show status on the replication host (master) and slave (slave)
Replication slave	Allow replication from server to master server
Show Databases	You can use the show databases statement to view the list of all databases. Without this permission, users can only see the databases they can see.
Shutdown	Allow the Administrator to disable the MySQL server
Super	Allow the Administrator to close threads belonging to any user

 

Special Permissions

Permission	Description
All (or all previleges)	Grant all Permissions
Usage	Do not grant permissions. This will create a user and allow him to log on, but not

Revoke

Revoke is opposite to grant in syntax format:
RevokePrivileges[(Columns)]
OnItem
FromUser_name

Example

Create user Bob with the password "Bob", but do not grant him any permissions:
Grant usage on * to Bob identified by 'bob ';

Grant Bob the query and insert permissions in the books database:
Grant select, insert on books. * to Bob;

Cancel all Bob's permissions in the books database:
Revoke all on books. * from Bob;

Note:It should be noted that revoke all... only revokes user permissions and does not delete users. In MySQL, user information is stored in MySQL. User. MySQL can use drop user to completely delete a user. Its usage is as follows:
Drop userUser_name;

For example, to delete user Bob, you can use:
Drop user Bob;