MySQL user and password access settings (new, delete, modify)

1. Create user syntax: Create user'username'@'Host'Identified by'Password'; Example: CREATE USER'Dog'@'localhost'Identified by'123456'; CREATE USER'Pig'@'192.168.1.101_'Idendified by'123456'; CREATE USER'Pig'@'%'Identified by'123456'; CREATE USER'Pig'@'%'Identified by"'; CREATE USER'Pig'@'%'; Example 1:mysql>Create user JSS; This creates a user who can create a connection from any computer that has a MySQL client installed and has access to the target server, without a password. For example, from IP:10.0.0. 99 Client Execution connection: MySQL-ujss-h172.16.1.110View the user: MySQL>SelectUser,host,password fromUserwhereUser='JSS';    SELECT USER (); //Show Current UserExample 2:mysql> Create user Jss_ps identified by'JSS'; When the user connects, must specify the password, then can create the user, by specifying the identified by clause to set the password with the password login: MySQL-ujss_ps-p-H172.16.1.110If you want the specified user to be accessible only from one of the specified domains (domain) or host, you can specify host when creating the user, for example, specify that the user can only be from ten.0.0. 99 Access MySQL> Create user [email protected]10.0.0.99Identified by password'123456'; 2. Using the GRANT statement syntax: MySQL> Grant permissions 1, permissions 2,... Permission n on the database name. Table name to User name @ user address identified by'Connection Password'; permissions 1, Permissions 2,... Permission N representsSelect, Insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file, etc. 14 instances of permissions: MySQL >grantSelect, Insert,update,delete,create,drop on Vtdc.employee to [email protected]10.163.225.87Identified by'123'give from ten.163.225. 87 user Joe assigns permissions to operations such as Select,insert,update,delete,create,drop on the employee table of the database VTDC, and sets the password to 123. MySQL>grant all privileges in vtdc.* to [email protected]10.163.225.87Identified by'123'give from ten.163.225. 87 user Joe assigns permissions to all operations on the database VTDC all tables, and sets the password to 123. MySQL>grant all privileges on * * to [email protected]10.163.225.87Identified by'123'give from ten.163.225. 87 user Joe assigns permissions to all operations on all tables in all databases and sets the password to 123. MySQL>grant all privileges on * * to [e-mail protected] identified by'123', assign the native user Joe permission to all operations on all tables in all databases, and set the password to 123. 3. Inserting records directly into the Mysql.user table: MySQL> INSERT into User (Host,user,password) VALUES ('%','Jss_insert', Password ('JSS') ); MySQL>flush privileges;//Refresh System Permissions Table4.       To modify the MySQL user password: A. Using mysqladmin syntax: Mysqladmin-U user Name-p old password password new password For example: Mysqladmin-U root-p123Password456; b. Directly modify user password: syntax: Update Mysql.userSetPassword=password ('New Password')whereUser="Phplamp"and host="localhost"; instance: Update UserSetPassword=password ('54netseek')whereUser='Root';       Flush Privileges;c. To modify a password using the SET PASSWORD statement: syntax: set PASSWORD for'username'@'Host'= PASSWORD ('NewPassword'); If you are currently logged in user with set PASSWORD= PASSWORD ("NewPassword"); Example:SetPassword for[Email Protected]=password ("'); SET PASSWORD for Name=password ('New Password'); SET PASSWORD for'Pig'@'%'= PASSWORD ("123456");5.       Delete user and revoke permission: A. Cancel an account and its permissions drop user User;drop user [email protected]'%'drop User [email protected]b. Un-authorized User: Syntax: REVOKE privilege on Databasename.tablename from'username'@'Host'; Example: REVOKE SELECT on*. * FROM'Pig'@'%'; REVOKE SELECT on Test.user from'Pig'@'%'; Revoke all on*.* from[email protected]; Revoke all on user.* from 'Admin'@'%'; SHOW GRANTS for'Pig'@'%';//View Authorizationsc. Delete User: syntax: delete fromUserwhereuser ="user_name"and host ="host_name"; Example: Delete fromUserwhereUser='SSS'and host='localhost'; 

MySQL user and password access settings (new, delete, modify)