MySQL security mechanism

There are 6 permissions tables in MySQL library under MySQL

Mysql.user

User fields, permission fields, security fields, resource control fields

Mysql.db, Mysql.host

User fields, permission fields

Mysql.tables_priv,mysql.columms_priv,mysql.procs_priv

First, user management

(1) Three ways to create a user

1. create user [email protected]'localhost'Identified by'123456';2. Insert into Mysql.user (User,host,password,ssl_cipher,x509_issuer,x509_subject) VALUES ('User2','localhost', Password ('123456'),"',"',"');3. GrantSelectOn *. * to [email protected]'localhost'Identified by'123'//grant Select Right all libraries and all tables to User3, password 123
Flush Privileges

(2) Delete user

1. Drop user [email protected]'localhost'2fromwhere user='user1' and host='localhost';

(3) Root user to modify his/her password

1. mysqladmin-uroot-proot Password'123'2. Update Mydql.userSetPassword=password ('New_password')whereUser='Root'and host='localhost';3.SetPassword=password ('New_password')
Flush privileges//Refresh Authorization Form

(4) Root user to modify other user's password

1.SetPassword for[Email protected]'localhost'=password ('New_password'); flush privileges;2. Updatae Mysql.userSetPassword=password ('New_password')whereUser='User3'and host='localhost'; flush privileges;3. GrantSelectOn *. * to [email protected]'localhost'Identified by'pwd'; flush privileges;

(5) Ordinary users to modify their own password

set password=password ('new_password');

(6) lost root user password

vim/etc/My.cnfskip-grant-tables//Remove the comment from the sentenceService mysqld Restartmysql-uroot//then you can skip the permission table and go to MySQL.Update Mysql.userSetPassword=password ('New_password')whereUser='User3'and host='localhost'; flush Privileges;\q//quit MySQLvim/etc/My.cnf#skip-grant-tables//to re-annotate this sentence again

II. Rights Management

Syntax format: Grant permission list on Library name. Table name to User name @ ' client ' [identified by ' Password ' with GRANT OPTION]

Where: The with_option parameter is as follows

Grant OPTION: Max_queries_per_hour: Defines the number of queries allowed per hour Max_updates_per_hou: Defines the number of updates that are allowed to be performed per hour Max_connections_per -Hour: Defines the number of connections that can be established per hour Max-user_connections: Defines the number of connections that a single user can make

Authorization example

Grant all on * * to [email protected]'%'Identified by'Password'; Grant all on*. * to [email protected]'%'Identified by'PW'With Grant Option;grant all on* * bbs.* to [email protected]'%'Identified by'PW'Grant all on bbs.user to [email protected]'%'Identified by'PW'; GrantSelect(col1), insert (COL2,COL3) on bbs.user to [email protected]'%'Identified by'PW'; flush Privileges

View Permissions

 for [Email protected] ' % ' \g;

Reclaim Permissions revoke permissions list on database name from user name @ ' client Host '

1  from [Email protected] ' % '; // Reclaim Partial Permissions 2  from [Email protected] ' % '   from [email protected]'%'// Reclaim all permissions (including authorization)
Flush privileges;    Refresh Authorization

MySQL security mechanism