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