Mysql下建立用户授权权限例子

用户授权在mysql中使用grant命令就可以了，我相信各位都会有了解过了，下面小编为各位介绍一个Mysql下建立用户授权权限例子，希望本文章对各位有帮助．
 
建立用户，授权数据库：
mysql> create user 'byrd'@'localhost' identified by 'admin123'; #建立主机为localhost，密码为admin123的用户byrd
 Query OK, 0 rows affected (0.05 sec)
 mysql> show grants for 'byrd'@'localhost'; #查看byrd权限，USAGE表示连接权限
+-------------------------------------------------------------------------------------------------------------+
 | 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、主机localhost对数据库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 ON `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，用户gbk数据库所有权限，同上
mysql> show grants for 'test'@'localhost';
 +-------------------------------------------------------------------------------------------------------------+
 | Grants for test@localhost |
 +-------------------------------------------------------------------------------------------------------------+
 | GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' |
 | GRANT ALL PRIVILEGES ON `gbk`.* TO 'test'@'localhost' |
 +-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
 Query OK, 0 rows affected (0.00 sec)
远程连接同上：
 
mysql> grant all on gbk.* to 'user'@'授权可连接主机' identified by 'admin123'; #这是Server端
[root@lamp ~]# /usr/local/mysql/bin/mysql -uroot -p'admin123' -h hk.t4x.org #这是client端
 
补充：ALL PRIVILEGES权限包括：
 
mysql> show grants for 'byrd'@'localhost';
 +-------------------------------------------------------------------------------------------------------------+
 | Grants for byrd@localhost |
 +-------------------------------------------------------------------------------------------------------------+
 | GRANT USAGE ON *.* TO 'byrd'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' |
 | GRANT ALL PRIVILEGES ON `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, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `gbk`.* TO 'byrd'@'localhost' |
 +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
ALL PRIVILEGES权限包括：GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT,insert