mysql管理数据库权限 数据库视图 视图授权示例

2018-11-04 15:59:00
admin
原创 812
摘要:mysql管理数据库权限 数据库视图 视图授权示例

一、mysql管理数据库权限

查询user表(主键host+user)

select user,host,password from mysql.user;

修改user表

update mysql.user set host='unknown' where user='unknown';
set password for unknown@unknown=password('mypass');

create user unknown@unknown identified by 'mypass';
drop user unknown@unknown;


用户允许访问的数据库(主键db,user,host)

select db,user,host from mysql.db;

用户允许访问的表(主键db,table_name,user,host)

select db,table_name,user,host from tables_priv;


数据库和数据表权限变化规则

1、授权整个数据库权限时修改mysql.user和mysql.db表;

2、授权单个数据表权限时修改mysql.user和mysql.tables_priv表;


二、数据库视图

1、视图是虚表,本身不存储数据,而是按照指定的方式进行查询;

2、可以通过视图插入数据,但只能对单表进行插入,不能对多表进行插入;

3、创建视图一般加上with check option,此时插入数据会做满足视图条件检查;


三、视图授权示例(视图权限归类为表权限)

create table student(name varchar(32), age int(4));
create table grade(name varchar(32), class varchar(32), grade int(4));
insert into student values('feinen',21);
insert into grade values('feinen', 'math', 90);
insert into grade values('feinen', 'physics', 90);
insert into student values('mate',21);
insert into grade values('mate', 'math', 80);
insert into grade values('mate', 'physics', 80);

create view student_grade_over_80 as select
student.name as name,
student.age as age
from student where name in (select distinct(name) from grade where grade>80);

grant all privileges on test.student_grade_over_80 to view@'%' identified by '123456';
revoke all privileges on test.student_grade_over_80 from view@'%';
flush privileges;

发表评论
评论通过审核之后才会显示。