MySQL user permission settings

1. The basic syntax format of grant is:Grant permission on database objects to users

Grant [-option operation permission] on [-database]. [-table] to [-user operation user] @ "[operation user IP address]" [-set password]

Option operation permission:

1. select # Only select Operation permissions for the Operation user

2. insert # the user only has the permission to insert data.

3. update # Only users with the permission to update data

4. delete # Only users with the permission to delete data

5. create # The operation user has only the create permission.

6. alter # the user only has the alter permission.

7. drop # operation users only have the drop permission

8. references # operation users only have the permission to execute foreign keys

9. index # The operation user has only the permission to execute the index

10. execute # Only users with the permission to execute stored procedures and functions

You can also execute Multiple permissions:

grant select, insert, update, delete on testdb.* to common_user@'%' 

2. view database Permissions

2.1 View Current User Permissions

show grants

2.2 view other mysql user permissions

show grants  for  username@dohost

3. Sales User Permissions

The syntax of revoke is similar to that of grant. You only need to replace the keyword "to" with "from:

grant all on *.* to dba@localhost;  revoke all on *.* from dba@localhost; 

For security reasons, the root account can only be accessed locally, but the root remote access permission may be required during development. The basic steps are as follows:

1. log on to mysql and grant remote access permissions to the root user. Run the following command:

 

Mysql> grant all privileges on *. * TO root @ "%" identified by "root ";

Mysql> flush privileges;

 

In the first sentence, "%" indicates that any host can be remotely logged on to the server for access. If you want to restrict access to only one machine, replace it with the corresponding IP address, for example:

Grant all privileges on *. * TO root @ "172.16193.25" identified by "root ";

The second statement indicates that the permission data is reloaded from the grant table of the mysql database. Because MySQL puts all permissions in the cache, it needs to be reloaded after modification.