45IT.COM- 亚博888娱乐唯一官网学习从此开始!
亚博888娱乐唯一官网柏林赫塔赞助商亚博亚博app官方最新下载装机配置
设计Photoshop网页设计特效
系统注册表DOS系统命令其它
存储主板显卡外设键鼠内存
维修显卡CPU内存打印机
WinXPVistaWin7unix/linux
CPU光驱电源/散热显示器其它
修技主板硬盘键鼠显示器光驱
办公ExcelWordPowerPointWPS
编程数据库CSS脚本PHP
网络局域网QQ服务器
软件网络系统图像安全
页面导航: 首页 > 设计学院 > 网络编程 > 数据库 >

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

亚博888娱乐唯一官网软硬件应用网 45IT.COM 时间:2015-03-09 12:30 作者:佚名
用户授权在mysql中使用grant命令就可以了,我相信各位都会有了解过了,下面小编为各位介绍一个Mysql下建立用户授权权限例子,希望本文章对各位有帮助. 建立用户,授权数据库: mysql create user byrd@localhost id
用户授权在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

顶一下
(0)
0%
踩一下
(0)
0%
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
验证码:点击我更换图片 匿名?
推荐知识