MySQL Account management

One account Management

1 Create an account:

Example 1:

Set up account ZWJ with all permissions on all databases

Mysql> Grant All on * * to ' zwj ' @ ' 192.168.154.180 ';

Query OK, 0 rows affected (0.01 sec)

Set a password for ZWJ

Mysql> Grant All on * * to ' zwj ' @ ' 192.168.154.180 ' identified by ' ZWJ ';

Query OK, 0 rows Affected (0.00 sec)

Example 2:

Set up the user User01, permissions for all tables in the test library for Select, UPDATE, INSERT, delete operations, the password is "A123".

Mysql> Grant Select,update,insert,delete on test.* to ' User01 ' @ ' 192.168.154.% ' identified by ' A123 ';

Query OK, 0 rows affected (0.09 sec)

2 Viewing permissions

View permissions for a specified account

Mysql> Show grants for ' User01 ' @ ' 192.168.154.% ';

+-------------------------------------------------------------------------------------------------------------- -----+

| Grants for [email protected]% |

+-------------------------------------------------------------------------------------------------------------- -----+

| GRANT USAGE on *. User01 ' @ ' 192.168.154.% ' identified by PASSWORD ' *ff680e568727c9c00fabfee03d13ba727047cc65 ' |

| GRANT SELECT, INSERT, UPDATE, DELETE on ' test '. * to ' User01 ' @ ' 192.168.154.% ' |

+-------------------------------------------------------------------------------------------------------------- -----+

2 rows in Set (0.00 sec)

View Current User Permissions

Mysql> Show grants;

+-------------------------------------------------------------------------------------------------------------- --------------------------+

|                                                                                                              Grants for [email protected] |

+-------------------------------------------------------------------------------------------------------------- --------------------------+

| GRANT all privileges on * * to ' root ' @ ' localhost ' identified by PASSWORD ' *dcb7df5ffc82c441503300fff165257bc551a598 ' with GRANT OPTION |

|                                                                           Grant PROXY on "@" to "root" @ ' localhost ' with GRANT OPTION |

+-------------------------------------------------------------------------------------------------------------- --------------------------+

2 rows in Set (0.00 sec)

3 Changing permissions

New user

Mysql> Grant Select on test.*-' BBS ' @ ' 192.168.154.% ' identified by ' BBS ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show grants for ' BBS ' @ ' 192.168.154.% ';

+-------------------------------------------------------------------------------------------------------------- --+

| Grants for [email protected]% |

+-------------------------------------------------------------------------------------------------------------- --+

| GRANT USAGE on * * to ' BBS ' @ ' 192.168.154.% ' identified by PASSWORD ' *74baeac2cafc5c7162ef373c5c85efbc7fc8b803 ' |

| GRANT SELECT on ' test '. * to ' BBS ' @ ' 192.168.154.% ' |

+-------------------------------------------------------------------------------------------------------------- --+

2 rows in Set (0.00 sec)

Increase permissions

Mysql> Grant Select,insert,delete on test.* to ' BBS ' @ ' 192.168.154.% ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show grants for ' BBS ' @ ' 192.168.154.% ';

+-------------------------------------------------------------------------------------------------------------- --+

| Grants for [email protected]% |

+-------------------------------------------------------------------------------------------------------------- --+

| GRANT USAGE on * * to ' BBS ' @ ' 192.168.154.% ' identified by PASSWORD ' *74baeac2cafc5c7162ef373c5c85efbc7fc8b803 ' |

| GRANT SELECT, INSERT, DELETE on ' test '. * to ' BBS ' @ ' 192.168.154.% ' |

+-------------------------------------------------------------------------------------------------------------- --+

2 rows in Set (0.00 sec)

Revoke permissions

Mysql> revoke Delete,insert on test.* from ' bbs ' @ ' 192.168.154.% ';

Query OK, 0 rows affected (0.01 sec)

Mysql> Show grants for ' BBS ' @ ' 192.168.154.% ';

+-------------------------------------------------------------------------------------------------------------- --+

| Grants for [email protected]% |

+-------------------------------------------------------------------------------------------------------------- --+

| GRANT USAGE on * * to ' BBS ' @ ' 192.168.154.% ' identified by PASSWORD ' *74baeac2cafc5c7162ef373c5c85efbc7fc8b803 ' |

| GRANT SELECT on ' test '. * to ' BBS ' @ ' 192.168.154.% ' |

+-------------------------------------------------------------------------------------------------------------- --+

2 rows in Set (0.00 sec)

4 Changing your account password

Mode 1:

mysql> set password for ' BBS ' @ ' 192.168.154.% ' = password (' abc-123 ');

Query OK, 0 rows affected (0.01 sec)

mysql> flush Privileges;

Query OK, 0 rows Affected (0.00 sec)

Mode 2:

mysql> Update Mysql.user Set Password=password (' AAA ') where user= ' BBS ' and host= ' 192.168.154.% ';

Query OK, 1 row Affected (0.00 sec)

Rows matched:1 changed:1 warnings:0

mysql> flush Privileges;

Query OK, 0 rows Affected (0.00 sec)

Mode 3:

[[email protected] tmp]# mysqladmin-u bbs-h 192.168.154.180 Password "CCC"-P

Enter Password:

Warning:using a password on the command line interface can is insecure.

To change the password for root:

[[email protected] tmp]# mysqladmin-uroot password ' root '-p

Enter Password:

Warning:using a password on the command line interface can is insecure.

5 Deleting an account

mysql> drop user ' bbs ' @ ' 192.168.154.% ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show grants for ' BBS ' @ ' 192.168.154.% ';

ERROR 1141 (42000): There is no such grant defined for the user ' BBS ' on Host ' 192.168.154.% '

This article from "10,000 years too long, seize" blog, please be sure to keep this source http://zengwj1949.blog.51cto.com/10747365/1921317

MySQL Account management