mysql数据库域名授权_玩转mysql授权

0. 删除系统多余账号

use mysql;

select user,host from mysql.user;

delete from user where user=‘‘;

flush privileges;

mysql创建账号:

mysqladmin -u root password ‘123456‘

以demo库test库分别demo_1和test_1表为例:

create database demo;

create table demo_1(id int);

insert into demo_1(id) values(1),(2),(3);

create database test;

create table test_1(id int);

insert into test_1(id) values(1),(2),(3);

create table test_2(id int);

insert into test_2(id) values(1),(2),(3);

1. 授权所有库

创建新用户并授权,且密码为空: grant all on *.* to test@‘localhost‘

创建新用户并授权,且设密码: grant all on *.* to test@‘localhost‘ identified by ‘123456‘;

或  grant select, insert, update, delete on *.* to test@‘localhost‘

刷新权限: flush privileges;

查看权限: show grants for test@‘localhost‘;

GRANT ALL PRIVILEGES ON *.* TO ‘test‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘

测试权限(可以): mysql -utest -p123456 -e ‘select * from demo.demo_1‘;

1.1 收回权限

收回权限: revoke all on *.* from test@‘localhost‘;

刷新权限: flush privileges;

查看权限: show grants for test@‘localhost‘;

GRANT USAGE ON *.* TO ‘test‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘

测试权限(不可以): mysql -utest -p123456 -e ‘select * from demo.demo_1‘;

ERROR 1142 (42000) at line 1: SELECT command denied to user ‘test‘@‘localhost‘ for table ‘demo_1‘

2. 授权指定库(1个或多个库)

grant all on test.* to test@‘localhost‘;

grant all on demo.* to test@‘localhost‘;

注意：不能一次对指定的多个库进行授权，只能一个一个授权：grant all on test.*,demo.* to test@‘localhost‘;

查看权限: show grants for test@‘localhost‘;

GRANT USAGE ON *.* TO ‘test‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘

GRANT ALL PRIVILEGES ON `test`.* TO ‘test‘@‘localhost‘

GRANT ALL PRIVILEGES ON `demo`.* TO ‘test‘@‘localhost‘

测试权限(可以): mysql -utest -p123456 -e ‘select * from demo.demo_1‘;

2.1 收回权限

revoke all on test.* from test@‘localhost‘;

revoke all on demo.* from test@‘localhost‘;

flush privileges;

测试权限：mysql -utest -p123456 -e ‘select * from demo.demo_1‘;

ERROR 1142 (42000) at line 1: SELECT command denied to user ‘test‘@‘localhost‘ for table ‘demo_1‘

3. 授权指定库(1张或多张表)

授权: grant all on test.test_2 to test@‘localhost‘;

刷新权限: flush privileges;

查看权限: show grants for test@‘localhost‘;

GRANT USAGE ON *.* TO ‘test‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘

GRANT ALL PRIVILEGES ON `test`.`test_2` TO ‘test‘@‘localhost‘

测试权限: mysql -utest -p123456 -e ‘select * from test.test_1‘;

ERROR 1142 (42000) at line 1: SELECT command denied to user ‘test‘@‘localhost‘ for table ‘test_1‘

mysql -utest -p123456 -e ‘select * from test.test_2‘; 查询有东西

注意：授权一个不存在的表居然也可以成功，还可以查看出权限。

3.1 收回权限

revoke all on test.test_2 from test@‘localhost‘;

flush privileges;

4. 隐藏库不让授权

information_schema  该库存储了mysql一些元数据，如数据库名或表名，列的数据类型，或访问权限等

mysql

5. 将ip整成域名访问数据库

192.168.11.17 www.db.com

navcat中就可以直接填写域名，指定端口，需要注意host是控制访问的权限 %/192.168.11.%/192.168.11.18/等等

本文出自 “开发与运维” 博客，谢绝转载！

玩转mysql授权

标签：玩转mysql授权

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://yeqing.blog.51cto.com/3159086/1892595