MySQL User management

1.mysql database table Structure

1) All tables of MySQL database:

2) Structure of User Table 3) DB table structure 4) Permissions table (Tables_priv and Columns_priv table) ①TABLES_PRIV table structure: ②COLUMNS_PRIV table structure: 6) PROCS_PRIV table Structure 2. Account management 1) New user ① Use the grant user statement to add a user ② using the INSERT statement Adding a user ③ using the GRANT statement new normal user 3) Delete user ① use drop User statement Delete users ② use DELETE statement to delete user 4) root Modify your password ① use the Mysqladmin tool to change the password (note: the password must be enclosed in double quotes) ② Use the UPDATE statement to change the user table ③ use the SET command to change the password 5) the root user modifies the password of the normal user ① use the SET command to modify the password ( Change the password to Yunshu, the password to enclose in double quotation marks, with single quotation marks will be a problem ② use the UPDATE statement changes the user table (change the password to Password③ use the GRANT statement to change the password (yunshu password changed to SERCET6) Ordinary users modify their own password ① use the SET statement to modify ② use the Mysqladmin tool to modify the password (password Yunshu can not be separated with a semicolon, Otherwise, the semicolon will be treated as a password 7) solution for root user password loss ① start the MySQL service with the--skip-grant-tables option Windows:mysqld--skip-grant-tablesmysqld-nt--skip-grant-tablesnet start MySQL --skip-grant-tables Linux:mysqld_safe--skip-grant-tables user=mysql/etc/init.d/mysql Start--mysqld --skip-grant-tables ② using the UPDATE statement to modify the password, load the permissions table example: (log in MySQL, no password can be logged in at this time (you cannot use the SET statement to modify the password with the UPDATE statement to modify the password, and flush privileges load the permissions table (log in again, ok! 3, Rights Management 1) User table Permissions column 2) Authorization syntax: Grant Priv_type [(column_list)] on database.table to user [identified by [PASSWORD] ' PASSWORD '] [ , user [iddentified by [PASSWORD] ' PASSWORD '] ... [With With_option [with_option] ...] With_option parameters: A, Grant OPTION: Authorized users can assign these permissions to other users, B, Max_queries_per_hour_count: Set each hour to perform count queries; c, max_updates _per_hour_count:set each hour to perform count updates;D, Max_connections_per_hour_count: set each hour can establish count times connection; E,max_user_connections_per_count: Sets the number of count connections that a single user can have at the same time; Example: (to Yunshu user authorization (with Yunshu user to YUNSHU2 User authorization 3) The basic syntax of the REVOKE statement that withdraws the permission to retract the specified permission is: REVOKE Priv_type [(column_list)] ... On database.table from user [, user] ... The basic syntax for a REVOKE statement that reclaims full permissions is: REVOKE all privileges,grant OPTION from user [, user] ... Example: 4) To view user Permissions ① View users table ② using the show GRANT statement basic syntax: show GRANTS for 'username '@ 'hostname' Example:

MySQL User management