Establish a user, authorization database:
mysql> create user ' Byrd ' @ ' localhost ' identified by ' admin123 '; #建立主机为localhost, the password is admin123 user Byrd
Query OK, 0 rows affected (0.05 sec)
Mysql> show grants for ' Byrd ' @ ' localhost '; #查看byrd权限, usage indicates connection permissions
+-------------------------------------------------------------------------------------------------------------+
| Grants for Byrd@localhost |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE on *.* to ' Byrd ' @ ' localhost ' identified by PASSWORD ' *01a6717b58ff5c7eafff6cb7c96f7428ea65fe4c ' |
+-------------------------------------------------------------------------------------------------------------+
1 row in Set (0.00 sec)
Mysql> select User,host from Mysql.user;
+------+-----------+
| user | Host |
+------+-----------+
| Root | 127.0.0.1 |
| Root | :: 1 |
| Root | Lamp |
| Byrd | localhost |
| Root | localhost |
+------+-----------+
5 rows in Set (0.00 sec)
Mysql> Grant all on gbk.* to ' Byrd ' @ ' localhost '; #用户byrd, host localhost have all permissions on the database GBK
Query OK, 0 rows affected (0.01 sec)
Mysql> show Mysqls for ' Byrd ' @ ' localhost ';
+-------------------------------------------------------------------------------------------------------------+
| Grants for Byrd@localhost |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE on *.* to ' Byrd ' @ ' localhost ' identified by PASSWORD ' *01a6717b58ff5c7eafff6cb7c96f7428ea65fe4c ' |
| GRANT all privileges in ' GBK '. * to ' Byrd ' @ ' localhost ' |
+-------------------------------------------------------------------------------------------------------------+
2 rows in Set (0.00 sec)
mysql> flush Privileges;
Mysql> Grant all on gbk.* to test@ ' localhost ' identified by ' admin123 '; #建立用户test, user GBK database all permissions, IBID.
Mysql> Show grants for ' test ' @ ' localhost ';
+-------------------------------------------------------------------------------------------------------------+
| Grants for Test@localhost |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE on *.* to ' test ' @ ' localhost ' identified by PASSWORD ' *01a6717b58ff5c7eafff6cb7c96f7428ea65fe4c ' |
| GRANT all privileges in ' GBK '. * to ' test ' @ ' localhost ' |
+-------------------------------------------------------------------------------------------------------------+
2 rows in Set (0.00 sec)
Query OK, 0 rows Affected (0.00 sec)
Remote Connection ditto:
Mysql> Grant all in gbk.* to ' user ' @ ' Authorize connection to host ' identified by ' admin123 '; #这是Server端
[Root@lamp ~]#/usr/local/mysql/bin/mysql-uroot-p ' admin123 '-H hk.t4x.org #这是client端
Add: All privileges privileges include:
Mysql> show grants for ' Byrd ' @ ' localhost ';
+-------------------------------------------------------------------------------------------------------------+
| Grants for Byrd@localhost |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE on *.* to ' Byrd ' @ ' localhost ' identified by PASSWORD ' *01a6717b58ff5c7eafff6cb7c96f7428ea65fe4c ' |
| GRANT all privileges in ' GBK '. * to ' Byrd ' @ ' localhost ' |
+-------------------------------------------------------------------------------------------------------------+
mysql> revoke insert on ' GBK '. * from ' Byrd ' @ ' localhost ';
Query OK, 0 rows Affected (0.00 sec)
Mysql> Show grants for byrd@ ' localhost ';
+-------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- +
| Grants for byrd@localhost |
+-------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- +
| GRANT USAGE on *.* to ' Byrd ' @ ' localhost ' identified by PASSWORD ' *01a6717b58ff5c7eafff6cb7c96f7428ea65fe4c ' |
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE temporary tables, LOCK tables, EXECUTE, creat E view, show VIEW, CREATE routine, ALTER routine, EVENT, TRIGGER on ' GBK '. * to ' Byrd ' @ ' localhost ' |
+-------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- +
All privileges permissions include: GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE temporary TABLES, LOCK TAB LES, EXECUTE, create VIEW, show view, create routine, ALTER routine, Event,insert