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

2024-07-24 12:35:25

字体：大中小

来源：转载

供稿：网友

　　用户授权在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                                                                                   | --phpfensi.com
　　+-------------------------------------------------------------------------------------------------------------+
　　| 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。

（编辑：武林网）

上一篇：在linux中安装mysql数据库的2种方法

下一篇：处理MysqlInnoDBFailingassertionret||assertonerror问题