MySQL Database user rights

I. List of permissions

Select_priv. Determines whether the user can select data through the Select command.

Insert_priv. Determines whether the user can insert data through the Insert command.

Update_priv. Determines whether the user can modify existing data through the update command.

Delete_priv. Determines whether the user can delete existing data through the Delete command.

Create_priv. Determines whether the user can create a new database and table.

Drop_priv. Determines whether users can delete existing databases and tables.

Reload_priv. Determines whether a user can perform a specific command that refreshes and reloads the various internal caches used by MySQL, including logs, permissions, hosts, queries, and tables.

Shutdown_priv. Determines whether the user can shut down the MySQL server. You should be very cautious when giving this permission to any user other than the root account.

Process_priv. Determines whether users can view the processes of other users through the show Processlist command.

File_priv. Determines whether the user can execute the SELECT INTO outfile and load DATA infile commands.

Grant_priv. Determines whether a user can grant another user the permissions that have been granted to the user. For example, if a user can insert, select, and delete information from the Foo database and grant grant permissions, that user can grant any or all of their permissions to any other user on the system.

References_priv. Currently it is only a placeholder for some future features;

Index_priv. Determines whether the user can create and delete table indexes.

Alter_priv. Determines whether the user can rename and modify the table structure.

Show_db_priv. Determines whether the user can view the names of all databases on the server, including databases with sufficient access to the user. You may consider disabling this permission for all users, unless there is a particularly compelling reason.

Super_priv. Determines whether a user can perform some powerful administrative functions, such as deleting a user process with the KILL command, using the set

Global MySQL variable is modified to perform various commands on replication and logs.

Create_tmp_table_priv. Determines whether the user can create a temporary table.

Lock_tables_priv. Determines whether the user can use the Lock Tables command to block access/modification to the table.

Execute_priv. Determines whether the user can execute the stored procedure. This permission is only meaningful in MySQL 5.0 and later versions.

Repl_slave_priv. Determines whether the user can read the binary log files used to maintain the replicated database environment. This user is located in the primary system and facilitates communication between the host and the client.

Repl_client_priv. Determines whether the user can determine the location of replication from the server and the primary server.

Create_view_priv. Determines whether the user can create a view. This permission is only meaningful in MySQL 5.0 and later versions. For more information about views, see Chapter 34th.

Show_view_priv. Determines whether the user can view the view or understand how the view is performed. This permission is only meaningful in MySQL 5.0 and later versions. For more information about views, see Chapter 34th.

Create_routine_priv. Determines whether a user can change or discard stored procedures and functions. This permission was introduced in MySQL 5.0.

Alter_routine_priv. Determines whether the user can modify or delete stored functions and functions. This permission was introduced in MySQL 5.0.

Create_user_priv. Determines whether the user can execute the Create user command, which is used for creating a new MySQL account.

Event_priv. Determines whether the user can create, modify, and delete events. This permission is new for MySQL 5.1.6.

Trigger_priv. Determines whether the user can create and delete triggers, which is a new addition to MySQL 5.1.6.

Max_queries_per_hour: The number of query statements allowed to be executed per hour by the user;

Max_updates_per_hour: The number of update statements allowed to be performed by the user per hour;

Max_connections_per_hour: The number of times a user is allowed to connect per hour;

Max_user_connections: Allows the user to simultaneously connect the number of servers;

Ii. Creating user SQL statements

#创建用户 (Local access only)Create User 'User'@'localhost'Identified by 'Pass'; #创建用户 (all remote host access)Create User 'User'@'%'Identified by 'Pass'; #用户授权 (grant user users TestDB data queries and increase permissions)Grant Select,Insert  onTestDB to User; #用户授权数据库. *represents the entire database # with Grant optionallow the person granted permission to grant this permission to other people. Generally do not addGrant  All Privileges  onDbname.tablename to 'username'@'localhost'Identified by 'Password'  with Grant option; #username对tdbNames数据库有select,Delete,Update,Create, drop permissions. Grant Select,Delete,Update,Create,Drop  onDbName.*  to 'username'@'192.168.%'Identified by 'Pass'; #授权完成后需要重新载入赋权表flushPrivileges; #解除授权 # Remove all permissions from all databases (tables) of the user (does not include empowering permissions)Revoke  All Privileges  on *.*  from User; #解除赋权权限Revoke Grant option  on *.*  from User; #删除用户Delete  fromMysql.User where User='User'; #设置与更改用户密码SetPassword for 'User'@'localhost' =Password'NewPassword'); SetPassword for 'User'@'%' =Password'123456'); #如果是当前登陆用户用:SetPassword=Password'NewPassword');

MySQL Database user rights