基于hana的BO报表数据权限的控制方法

        在任意一个项目,涉及到报表的数据展现时,都得考虑不同的部门及账号等打开报表之后分别能看到什么数据。这就涉及到了报表数据权限的问题。

        常规的,数据权限都会在后台通过写SQL语句进行控制。BO顾问会在后台设计一张专门用于做数据权限的后台表,然后通过围绕这张后台表写SQL做数据权限控制。

如下场景:

        不同账号登录BO后,分别能看到该账号本身所属组织的或其以下组织的数据;

   第一步:后台开发如下后台控制表,列出系统内所有人员及其组织信息。同时,有字段标记该条人员数据的最小组织层级及对应编码。mini_sort,mini_org

        

第二步:通过写SQL 语句实现逻辑控制。代码如下:

           
 /********* Begin Procedure Script ************/ 
 BEGIN 


  declare ini_all integer; --初始值为0 
  
  declare s0 integer; 
  declare s1 integer;
  declare s2 integer;
  declare s3 integer;
  declare s4 integer;
  declare s5 integer;
  declare s6 integer;
  declare s7 integer;
  declare s8 integer;
    
  sort_rs =  select *
             from SAPQ01.zcrmt_016n t16_1
             where mandt = '500';
--             and uname = trim(varuname);
 
  sort_rs1 = select distinct mandt,partner,sort1,sort2,sort3,sort4,sort5,sort6,sort7,sort8,
                            sort11,sort22,sort33,sort44,sort55,sort66,sort77,sort88,sysdat,sysdatend,mini_org
             from SAPQ01.zcrmt_016n t16_3
      where mandt = '500' ;


  select count(*) into s0 from :sort_rs where mini_sort = '' and uname = trim(varuname);
  select count(*) into s1 from :sort_rs where mini_sort = 'SORT1' and uname = trim(varuname);
  select count(*) into s2 from :sort_rs where mini_sort = 'SORT2' and uname = trim(varuname);
  select count(*) into s3 from :sort_rs where mini_sort = 'SORT3' and uname = trim(varuname);
  select count(*) into s4 from :sort_rs where mini_sort = 'SORT4' and uname = trim(varuname);
  select count(*) into s5 from :sort_rs where mini_sort = 'SORT5' and uname = trim(varuname);
  select count(*) into s6 from :sort_rs where mini_sort = 'SORT6' and uname = trim(varuname);  
  select count(*) into s7 from :sort_rs where mini_sort = 'SORT7' and uname = trim(varuname);
  select count(*) into s8 from :sort_rs where mini_sort = 'SORT8' and uname = trim(varuname);  
                                                                                                                                             
if :s0 >= 1 then
  sql_s0 = 
    select
      t16_1.mandt,
      t16_1.uname as "UNAME",
      --t16_1.partner,
 t16_1_1.partner,
 t16_1_1.sort1,
 t16_1_1.sort2,
 t16_1_1.sort3,
 t16_1_1.sort4,
 t16_1_1.sort5,
 t16_1_1.sort6,
 t16_1_1.sort7,
 t16_1_1.sort8,
 t16_1_1.SORT11,
 t16_1_1.SORT22,
 t16_1_1.SORT33,
 t16_1_1.SORT44,
 t16_1_1.sort55,
 t16_1_1.sort66,
 t16_1_1.sort77,
 t16_1_1.sort88,
 t16_1_1.sysdat,
 t16_1_1.sysdatend,
 t16_1_1.mini_org
from :sort_rs t16_1  

left outer join :sort_rs1 t16_1_1 
            on  (t16_1_1.mandt = t16_1.mandt)
    where t16_1.mandt = '500' and t16_1.uname = trim(varuname);            
end if;

if :s1 >= 1 then
  sql_s1 = 
    select
      t16_1.mandt,
      t16_1.uname as "UNAME",
      --t16_1.partner,
      t16_1_1.partner,
 t16_1_1.sort1,
 t16_1_1.sort2,
 t16_1_1.sort3,
 t16_1_1.sort4,
 t16_1_1.sort5,
 t16_1_1.sort6,
 t16_1_1.sort7,
 t16_1_1.sort8,
 t16_1_1.SORT11,
 t16_1_1.SORT22,
 t16_1_1.SORT33,
 t16_1_1.SORT44,
 t16_1_1.sort55,
 t16_1_1.sort66,
 t16_1_1.sort77,
 t16_1_1.sort88,
  t16_1_1.sysdat,
 t16_1_1.sysdatend,
 t16_1_1.mini_org
from :sort_rs t16_1  

left outer join :sort_rs1 t16_1_1 
            on  (t16_1_1.mandt = t16_1.mandt)
            and (t16_1_1.sort1 = t16_1.mini_org)
    where t16_1.mandt = '500' and t16_1.uname = trim(varuname);            
end if;     
     
if :s2 >= 1 then
  sql_s2 = 
    select
      t16_1.mandt,
      t16_1.uname as "UNAME",
      --t16_1.partner,
      t16_1_1.partner,
 t16_1_1.sort1,
 t16_1_1.sort2,
 t16_1_1.sort3,
 t16_1_1.sort4,
 t16_1_1.sort5,
 t16_1_1.sort6,
 t16_1_1.sort7,
 t16_1_1.sort8,
 t16_1_1.SORT11,
 t16_1_1.SORT22,
 t16_1_1.SORT33,
 t16_1_1.SORT44,
 t16_1_1.sort55,
 t16_1_1.sort66,
 t16_1_1.sort77,
 t16_1_1.sort88,
  t16_1_1.sysdat,
 t16_1_1.sysdatend,
 t16_1_1.mini_org
from :sort_rs t16_1  

left outer join :sort_rs1 t16_1_1 
            on  (t16_1_1.mandt = t16_1.mandt)
            and (t16_1_1.sort2 = t16_1.mini_org)
    where t16_1.mandt = '500' and t16_1.uname = trim(varuname);            
end if;


if :s3 >= 1 then
  sql_s3 = 
    select
      t16_1.mandt,
      t16_1.uname as "UNAME",
      --t16_1.partner,
      t16_1_1.partner,
 t16_1_1.sort1,
 t16_1_1.sort2,
 t16_1_1.sort3,
 t16_1_1.sort4,
 t16_1_1.sort5,
 t16_1_1.sort6,
 t16_1_1.sort7,
 t16_1_1.sort8,
 t16_1_1.SORT11,
 t16_1_1.SORT22,
 t16_1_1.SORT33,
 t16_1_1.SORT44,
 t16_1_1.sort55,
 t16_1_1.sort66,
 t16_1_1.sort77,
 t16_1_1.sort88,
  t16_1_1.sysdat,
 t16_1_1.sysdatend,
 t16_1_1.mini_org
from :sort_rs t16_1  

left outer join :sort_rs1 t16_1_1 
            on  (t16_1_1.mandt = t16_1.mandt)
            and (t16_1_1.sort3 = t16_1.mini_org)
    where t16_1.mandt = '500' and t16_1.uname = trim(varuname);            
end if;


if :s4 >= 1 then
  sql_s4 = 
    select
      t16_1.mandt,
      t16_1.uname as "UNAME",
      --t16_1.partner,
      t16_1_1.partner,
 t16_1_1.sort1,
 t16_1_1.sort2,
 t16_1_1.sort3,
 t16_1_1.sort4,
 t16_1_1.sort5,
 t16_1_1.sort6,
 t16_1_1.sort7,
 t16_1_1.sort8,
 t16_1_1.SORT11,
 t16_1_1.SORT22,
 t16_1_1.SORT33,
 t16_1_1.SORT44,
 t16_1_1.sort55,
 t16_1_1.sort66,
 t16_1_1.sort77,
 t16_1_1.sort88,
  t16_1_1.sysdat,
 t16_1_1.sysdatend,
 t16_1_1.mini_org
from :sort_rs t16_1  

left outer join :sort_rs1 t16_1_1 
            on  (t16_1_1.mandt = t16_1.mandt)
            and (t16_1_1.sort4 = t16_1.mini_org)
    where t16_1.mandt = '500' and t16_1.uname = trim(varuname);            
end if;


if :s5 >= 1 then
  sql_s5 = 
    select
      t16_1.mandt,
      t16_1.uname as "UNAME",
      --t16_1.partner,
      t16_1_1.partner,
 t16_1_1.sort1,
 t16_1_1.sort2,
 t16_1_1.sort3,
 t16_1_1.sort4,
 t16_1_1.sort5,
 t16_1_1.sort6,
 t16_1_1.sort7,
 t16_1_1.sort8,
 t16_1_1.SORT11,
 t16_1_1.SORT22,
 t16_1_1.SORT33,
 t16_1_1.SORT44,
 t16_1_1.sort55,
 t16_1_1.sort66,
 t16_1_1.sort77,
 t16_1_1.sort88,
  t16_1_1.sysdat,
 t16_1_1.sysdatend,
 t16_1_1.mini_org
from :sort_rs t16_1  

left outer join :sort_rs1 t16_1_1 
            on  (t16_1_1.mandt = t16_1.mandt)
            and (t16_1_1.sort5 = t16_1.mini_org)
    where t16_1.mandt = '500' and t16_1.uname = trim(varuname);            
end if;


if :s6 >= 1 then
  sql_s6 = 
    select
      t16_1.mandt,
      t16_1.uname as "UNAME",
      --t16_1.partner,
 t16_1_1.partner,
 t16_1_1.sort1,
 t16_1_1.sort2,
 t16_1_1.sort3,
 t16_1_1.sort4,
 t16_1_1.sort5,
 t16_1_1.sort6,
 t16_1_1.sort7,
 t16_1_1.sort8,
 t16_1_1.SORT11,
 t16_1_1.SORT22,
 t16_1_1.SORT33,
 t16_1_1.SORT44,
 t16_1_1.sort55,
 t16_1_1.sort66,
 t16_1_1.sort77,
 t16_1_1.sort88,
  t16_1_1.sysdat,
 t16_1_1.sysdatend,
 t16_1_1.mini_org
from :sort_rs t16_1  

left outer join :sort_rs1 t16_1_1 
            on  (t16_1_1.mandt = t16_1.mandt)
            and (t16_1_1.sort6 = t16_1.mini_org)
    where t16_1.mandt = '500' and t16_1.uname = trim(varuname);            
end if;


if :s7 >= 1 then
  sql_s7 = 
    select
      t16_1.mandt,
      t16_1.uname as "UNAME",
      --t16_1.partner,
 t16_1_1.partner,
 t16_1_1.sort1,
 t16_1_1.sort2,
 t16_1_1.sort3,
 t16_1_1.sort4,
 t16_1_1.sort5,
 t16_1_1.sort6,
 t16_1_1.sort7,
 t16_1_1.sort8,
 t16_1_1.SORT11,
 t16_1_1.SORT22,
 t16_1_1.SORT33,
 t16_1_1.SORT44,
 t16_1_1.sort55,
 t16_1_1.sort66,
 t16_1_1.sort77,
 t16_1_1.sort88,
  t16_1_1.sysdat,
 t16_1_1.sysdatend,
 t16_1_1.mini_org
from :sort_rs t16_1  

left outer join :sort_rs1 t16_1_1 
            on  (t16_1_1.mandt = t16_1.mandt)
            and (t16_1_1.sort7 = t16_1.mini_org)
    where t16_1.mandt = '500' and t16_1.uname = trim(varuname);            
end if;


if :s8 >= 1 then
  sql_s8 = 
    select
      t16_1.mandt,
      t16_1.uname as "UNAME",
      --t16_1.partner,
 t16_1_1.partner,
 t16_1_1.sort1,
 t16_1_1.sort2,
 t16_1_1.sort3,
 t16_1_1.sort4,
 t16_1_1.sort5,
 t16_1_1.sort6,
 t16_1_1.sort7,
 t16_1_1.sort8,
 t16_1_1.SORT11,
 t16_1_1.SORT22,
 t16_1_1.SORT33,
 t16_1_1.SORT44,
 t16_1_1.sort55,
 t16_1_1.sort66,
 t16_1_1.sort77,
 t16_1_1.sort88,
  t16_1_1.sysdat,
 t16_1_1.sysdatend,
 t16_1_1.mini_org
from :sort_rs t16_1  

left outer join :sort_rs1 t16_1_1 
            on  (t16_1_1.mandt = t16_1.mandt)
            and (t16_1_1.sort8 = t16_1.mini_org)
    where t16_1.mandt = '500' and t16_1.uname = trim(varuname);            
end if;


  sql_s9 = 
    select
      t16_1.mandt,
      t16_1.uname as "UNAME",
      --t16_1.partner,
      t16_1_1.partner,
 t16_1_1.sort1,
 t16_1_1.sort2,
 t16_1_1.sort3,
 t16_1_1.sort4,
 t16_1_1.sort5,
 t16_1_1.sort6,
 t16_1_1.sort7,
 t16_1_1.sort8,
 t16_1_1.SORT11,
 t16_1_1.SORT22,
 t16_1_1.SORT33,
 t16_1_1.SORT44,
 t16_1_1.sort55,
 t16_1_1.sort66,
 t16_1_1.sort77,
 t16_1_1.sort88,
  t16_1_1.sysdat,
 t16_1_1.sysdatend,
 t16_1_1.mini_org
from :sort_rs t16_1  
left outer join :sort_rs1 t16_1_1 
            on  (t16_1_1.mandt = t16_1.mandt)
            and (t16_1_1.sort8 = t16_1.mini_org)
    where t16_1.mandt = '500' and 1 = 0;
    
-------------------------------------------------------------------------------------------------------------------------------------
    
-- ini_all = 0;
select count(*) into ini_all from :sort_rs where 1=0;


if ( :s8 >= 1 ) then
  if ( :ini_all < 1 ) then
    sql_all = 
      select * from :sql_s8;
  else
    sql_all = 
      select * from :sql_all 
      union all 
      select * from :sql_s8;
  end if;  
  select count(*) into ini_all from :sort_rs ;
end if;


if ( :s7 >= 1 ) then
  if ( :ini_all < 1 ) then
    sql_all = 
      select * from :sql_s7;
  else
    sql_all = 
      select * from :sql_all 
      union all 
      select * from :sql_s7;
  end if;  
  select count(*) into ini_all from :sort_rs ;
end if;


if ( :s6 >= 1 ) then
  if ( :ini_all < 1 ) then
    sql_all = 
      select * from :sql_s6;
  else
    sql_all = 
      select * from :sql_all 
      union all 
      select * from :sql_s6;
  end if;  
  select count(*) into ini_all from :sort_rs ;
end if;


if ( :s5 >= 1 ) then
  if ( :ini_all < 1 ) then
    sql_all = 
      select * from :sql_s5;
  else
    sql_all = 
      select * from :sql_all 
      union all 
      select * from :sql_s5;
  end if;  
  select count(*) into ini_all from :sort_rs ;
end if;


if ( :s4 >= 1 ) then
  if ( :ini_all < 1 ) then
    sql_all = 
      select * from :sql_s4;
  else
    sql_all = 
      select * from :sql_all 
      union all 
      select * from :sql_s4;
  end if;  
  select count(*) into ini_all from :sort_rs ;
end if;


if ( :s3 >= 1 ) then
  if ( :ini_all < 1 ) then
    sql_all = 
      select * from :sql_s3;
  else
    sql_all = 
      select * from :sql_all 
      union all 
      select * from :sql_s3;
  end if;  
  select count(*) into ini_all from :sort_rs ;
end if;


if ( :s2 >= 1 ) then
  if ( :ini_all < 1 ) then
    sql_all = 
      select * from :sql_s2;
  else
    sql_all = 
      select * from :sql_all 
      union all 
      select * from :sql_s2;
  end if;  
  select count(*) into ini_all from :sort_rs ;
end if;


if ( :s1 >= 1 ) then
  if ( :ini_all < 1 ) then
    sql_all = 
      select * from :sql_s1;
  else
    sql_all = 
      select * from :sql_all 
      union all 
      select * from :sql_s1;
  end if;  
  select count(*) into ini_all from :sort_rs ;
end if;


if ( :s0 >= 1 ) then
  if ( :ini_all < 1 ) then
    sql_all = 
      select * from :sql_s0;
  else
    sql_all = 
      select * from :sql_all 
      union all 
      select * from :sql_s0;
  end if;  
  select count(*) into ini_all from :sort_rs ;
end if;


  if ( :ini_all < 1 ) then
    sql_all = 
      select * from :sql_s9;
  else
    sql_all = 
      select * from :sql_all 
      union all 
      select * from :sql_s9;
  end if;  
-------------------------------------------------------------------------------------
--var_out = 
--  select * from :sql_all;


var_out = 
  select distinct mandt,uname,partner,sort1,sort2,sort3,sort4,sort5,sort6,sort7,sort8,
                  sort11,sort22,sort33,sort44,sort55,sort66,sort77,sort88,sysdat,sysdatend,mini_org
  from :sql_all;


END 


 --updated on 2014.08.15 by huanghuajun----------------------------------------------------------------------------
 


--END /********* End Procedure Script ************/

展开阅读全文

没有更多推荐了,返回首页