玩转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'> 或grant select, insert, update, delete on *.* to test@'localhost'
刷新权限: flush privileges;
查看权限: show grants for test@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost'> 测试权限(可以): 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'> 测试权限(不可以): 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'> 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'> 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/等等
页:
[1]