向有条件的行授予选择特权 [英] Grant select privilege to rows with condition

问题描述

如何授予对视图DRVADM中包含的所有信息的读取特权 除了用户的出生日期不为空的行之外?

how to grant a read privilege to all information included in a view DRVADM except the rows where a date of birth is not empty to a user?

推荐答案

请考虑以下内容:

drop table if exists variousRights;
create table variousRights
(   -- whitelist table of various privileges
    id int auto_increment primary key,
    rightType varchar(100) not null,
    username varchar(100) not null
);

-- sample data below. For this exercise, all we care about is 'seeNullBirthDateRows'
-- but other data is inserted to ferret out troubles with strategy (too many rows returned)
insert variousRights (rightType,userName) values
('seeNullBirthDateRows','root@localhost'),
('seeNullBirthDateRows','sam@localhost'),
('seeSecretIDs','root@localhost'),
('insertThing101','root@localhost');

drop table if exists employees;
create table employees
(   id int auto_increment primary key,
    empName varchar(100) not null,
    birthDate date null
);

-- sample data inserted. One has a null for birthDate (empty as you say in the question)
insert employees(empName,birthDate) values
('John Smith',null),
('Sally Higgins','2016-02-07'),
('John Smith','2010-01-27');

查询:

select id,empName,birthDate 
from employees
where birthDate is not null
union
select e.id,e.empName,e.birthDate 
from employees e
cross join (select id from variousRights where rightType='seeNullBirthDateRows' and userName=current_user()) vr
where e.birthDate is null;

查询依赖于交叉连接和联合.至于联合，第一部分对所有用户都是相同的:employees中的所有行，且nirthday为非空.联合的第二部分将为您在variousRights表中获得特权的用户返回空值.

The query relies on a Cross Join and a union. As for the union, the first part will be the same for all users: all rows from employees with a non-null birthDay. The second part of the union will return the nulls for users so privileged in the variousRights table where you dream up your privileges.

上面的查询自然可以放入视图中.

Naturally the above query can be plopped into a view.

有关 CURRENT_USER的信息，请参见mysql手册页()函数.

对于cross join，请这样考虑.它是笛卡尔积.但是联接的表(别名vr)将具有1行或0返回.这就是确定特权用户是否看到空的birthDate行的原因.

As for the cross join, think of it this way. It is a cartesian product. But the table joined on (alias vr) will either have 1 row or 0 coming back. That is what determines whether or not privileged users see the null birthDate rows.

注意:上面已经过测试.似乎工作正常.

Note: The above has been tested. Seems to work fine.

这篇关于向有条件的行授予选择特权的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！