Account Management of MySQL
One account management
1 create an account:
Example 1:
Create an 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 the 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 a user user01 with the permission to perform select, update, insert and delete operations on all tables in the test database with a password of "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 View permissions
View the permissions of the specified account
Mysql > show grants for 'user01'@'192.168.154.%'
+-+
| | Grants for user01@192.168.154.% |
+-+
| | GRANT USAGE ON *. * TO '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 root@localhost |
+- -+
| | 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 change permissions
New user
Mysql > grant select on test.* to '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 bbs@192.168.154.% |
+-+
| | 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 bbs@192.168.154.% |
+-+
| | 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 the authority
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 bbs@192.168.154.% |
+-+
| | 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 change the account password
Method 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:
[root@localhost tmp] # mysqladmin-u bbs-h 192.168.154.180 password "ccc"-p
Enter password:
Warning: Using a password on the command line interface can be insecure.
Set the password for root for the first time:
[root@www ~] # mysqladmin-uroot password "abc-123"
Change the password for root:
[root@localhost tmp] # mysqladmin-uroot password 'root'-p
Enter password:
Warning: Using a password on the command line interface can be insecure.
5 Delete 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 user 'bbs' on host' 192.168.154%'
