MySQL create user and manage user rights

First, create a user:

Mysql>create user ' username ' @ ' host ' identified by ' password ';

User name created by username

host specifies on which host the user can log on, if localhost is available to the local user, and if you want the user to be able to log on from any remote host, you can use a wildcard%

Password The user login password, the password can be empty, if it is empty the user can not require a password to log on to the server

ii. Grant Description:

Grant adds permissions to the user, the permissions are automatically superimposed, the previously granted permissions are not overwritten, and when Grant is granted to the user, the flush priveleges Refresh permission is not required

mysql>grant permissions 1, Permissions 2 ... Permission n on the database name. Table name to User name @ user address identified by ' connection password ' with GRANT option;

1. Permissions 1, Permissions 2 ... Permission n represents 14 permissions such as Select,instert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file
2. When permissions 1, permissions 2 ... Permission n is replaced by all privileges or all to give the user full permission
3. When the database name is. The table name is replaced by *. * to give the user permission to manipulate all tables on the server
4. The user address can be localhost, or it can be an IP address, a robot name, and a domain name. You can also use '% ' to indicate connections from any address
5. ' Connection password ' cannot be empty or creation failed
6. With GRANT option allows users to grant their own permissions to other users (

   With GRANT option, user testuser1 can pass Select, update permissions to other users (such as TestUser2)
   Grant Select,update on Bd_corp to TestUser2
   If you do not have with GRANT option, then user TestUser1 cannot grant TestUser2 authorization

   ）

e.g:

Mysql>Grant Select,insert,update,delete,create,drop on Testdb.users to [e-mail protected] identified by ' 123456 ' /c1>

Assign the user testuser from 10.240.171.103 to the Users table select,insert,update,create,drop the database TestDB and set the login password to 123456

Mysql>Grant all privileges to testdb.* [email protected] identified by ' 123456 '

To the 10.240.171.103 user TestUser assign permissions to all operations on the database TestDB all tables and set the login password to 123456

Mysql>Grant all privileges on * * to [e-mail protected] identified by ' 123 '

Assign the user testuser from 10.240.171.103 to all operations on all tables in all databases and set the login password to 123

Mysql>Flush Privileges

MySQL new setup user or change password after the need to flush privileges to refresh the MySQL system permissions related tables, otherwise there will be access denied, there is another way, is to restart the MySQL server, the new settings take effect

about the root user's access settings:

Set all users can remotely access MySQL, modify the my.cnf configuration file, the bind-address=127.0.0.1 front plus "#" comment out, so that other robots to allow remote access to the native MySQL;

Mysql>grant all privileges on * * to [e-mail protected] '% ' identified by ' 123456 '; Set user root to access MySQL remotely

Turn off root user remote access permission

Mysql>delete from user where user= "root" and host= "%"; Disable root user access to MySQL on remote machine

Third, revoke revoke permission:

Mysql>revoke all privileges in test.* from ' testuser ' @ '% '

REVOKE only revoke grant creation permissions, but the testuser user is not deleted and must be removed manually from the user table

Iv. setting up and changing user passwords

Mysql>set password for ' username ' @ ' host ' =password (' newpassword ')//If it is currently logged in user with Set Password=password (' NewPassword ' )

MySQL create user and manage user rights