Summary of MySQL authorization issues_PHP tutorial-PHP Tutorial-php.cn

Summary of MySQL authorization issues_PHP tutorial

WBOY

Release： 2016-07-21 15:55:12

Original

941 people have browsed it

I can't connect using the root account of localhost. Finally, please ask the DBA group to create a new user and fix it!

Now get some authorized user information for emergencies

Examples of authorization table usage

grant is used to add users and create permissions, revoke is used to delete user permissions .

The following are some examples of using grant to add users and create permissions:

mysql> grant all privileges on *.* to test@localhost identified by 'test' with grant option;

This sentence adds a local test user (super user) with all permissions, and the password is test. *.* in the ON clause means "all databases, all tables". with grant option means that it has grant permissions.

mysql> grant select,insert,update,delete,create,drop privileges on test.* to test1@'192.168.1.0/255.255.255.0' identified by 'test';

This The sentence is that a test1 user has been added with the password test, but it can only connect from the Class C subnet 192.168.1 and has select, insert, update, delete, create, and drop operation permissions on the test library.

Using the grant statement to create permissions does not require manual refresh of the authorization table, because it has been refreshed automatically.

You can also create permissions for users by directly modifying the authorization table:

mysql> insert into user

values("localhost","test",password("test" ),"Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y", "Y","Y");

mysql> flush privileges;

These two sentences have the same effect as the first grant sentence above, and also add a local test super user . We see that using grant is much more convenient, and there is no need to flush privileges.

mysql> insert into user (host,user,password) values("192.168.1.0/255.255.255.0","test1",PASSWORD("test")); mysql> insert into db values(" 192.168.1.0/255.255.255.0","test","test1","Y","Y","Y","Y","Y","Y","N","N"," N","N") mysql> flush privileges;

These three sentences have the same effect as the second sentence grant above. They also add a connection that can only be connected from the Class C subnet 192.168.1. The test library has the test1 user with select, insert, update, delete, create, and drop operation permissions, and the password is test. To revoke a user's permissions, use the revoke statement. The syntax of revoke is very similar to the grant statement, except that to is replaced by from and there is no identified by and with grant option clauses. The following is an example of using revoke to delete user permissions:

mysql> revoke all on test.* from test1@'192.168.1.0/255.255.255.0';

This revoke revokes the permission created by the second sentence grant above, but the test1 user has not been deleted and must be manually deleted from the user table:

mysql> delete from user where user='test1';

mysql> flush privileges;

In this way, the test1 user is completely deleted.

These are just simple uses of the MySQL authorization table. For more detailed information, please see the manual provided by MySQL.