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

Source: Internet
Author: User
Tags mysql client

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.