新增文章

文章标题分类内容

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--【视图】 删除

if exists (select * from sys.views where object_id = OBJECT_ID(N'dbo.a_AccessControl'))
              drop     view                                         dbo.a_AccessControl
   go

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--【视图】 a_AccessControl 用户权限

create view dbo.a_AccessControl
   as 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   select 
       a3.FGroupUserId
      ,a1.FUserID
      ,a4.FName    FUserName
      
      ,isnull( a7.FGroupID ,'' )  FGroupID
      ,isnull( a7.FSubSys  ,'' )  FSubSys
      ,isnull( a7.FAccess  ,'' )  FAccess
      
      ,a1.FObjectType ,a8.FName  FObjectTypeName
      ,a1.FObjectID   ,a5.FName  FObjectName
       
      ,a2.FIndex ,a2.FName ,a2.FDescription
      
      ,a1.FAccessType 
      ,a1.FAccessMask  FAccessMaskCtl
      ,a2.FAccessMask  FAccessMaskType
      ,a2.FAccessUse
      
   from t_AccessControl     a1
   join t_ObjectAccessType  a2  on   a2.FObjectType =  a1.FObjectType  and a2.FObjectID = a1.FObjectID  
                               and   a2.FAccessUse  <>    262144 
                               and   a2.FAccessMask <>    262144  
                               and ( a1.FAccessMask & a2.FAccessMask = a2.FAccessMask ) 
                               
   join ( select (case when FGroupID = 0 then FUserID else FGroupID end) FGroupID ,FUserID  FGroupUserId from t_Group ) a3 on a3.FGroupID = a1.FUserID
   
   join t_User             a4  on a4.FUserID   = a1.FUserID
   join t_ObjectType       a5  on a5.FObjectType = a1.FObjectType  and a5.FObjectID = a1.FObjectID
   join t_ObjectType       a8  on a8.FObjectType = a1.FObjectType  and a8.FObjectID = 0
   
   left join t_ObjectAccess     a6  on a6.FObjectType = a1.FObjectType  and a6.FObjectID = a1.FObjectID  and a6.FIndex = a2.FIndex
   left join t_GroupAccessType  a7  on a7.FGroupID    = a6.FGroupID

/* 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select * from a_AccessControl  -- 用户权限
   
   where FGroupUserId  = 16398
     and FObjectType   = 18
     and FObjectID     = 210001202
   
   order by FUserId,FObjectType,FObjectID,FIndex  ,FSubSys,FObjectTypeName,FObjectName,FName,FDescription
------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select distinct  FGroupUserId,FObjectType,FObjectID,FIndex  ,FSubSys,FObjectTypeName,FObjectName,FName,FDescription
   from a_AccessControl
   
   where FGroupUserId = 16398  and FObjectType = 18  and FObjectID = 210001202  and FDescription = 'NoAmount'

16398	郭少锋
      16400	刘桂香
      16403	马文琴

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select * from t_User   order by FUserID
   select * from t_Group  order by FUserID
   
   select (case when FGroupID = 0 then FUserID else FGroupID end) FUserID from t_Group where FUserID = 16398
   
   
   
   select * from t_AccessControl  where FObjectID like '210001202%'  -- a
   select * from t_ObjectType     where FObjectID like '21000%'      -- o   a.FObjectType =  o.FObjectType  and a.FObjectID =  o.FObjectID
    
   select * from t_ObjectType     where FObjectID = 0 and FObjectType = 18
   
   select * from t_ObjectAccess   where FObjectID like '210001202%'  -- y1  a.FObjectType = y1.FObjectType  and a.FObjectID = y1.FObjectID
   
   -- t3  not ( t3.fobjecttype=3 and t3.fobjectid=20 )

select * from t_ObjectAccessType where FObjectID like '210001202%'  -- t4  t3.FObjectType = t4.FObjectType and t3.FObjectID = t4.FObjectID  and t4.FAccessUse <> 262144 and t4.FAccessMask <> 262144  and ( t3.FAccessMask & t4.FAccessMask = t4.FAccessMask ) 
   select * from t_GroupAccessType  where FGroupID  in   ( 15 ,16 )    -- w2  t3.fgroupid=w2.fgroupid

select * from t_AccessControl    where FObjectID like '210001202%'  -- a
   select * from t_ObjectAccessType where FObjectID like '210001202%'  and FAccessUse <> 262144 and FAccessMask <> 262144  and ( 2129920 & FAccessMask = FAccessMask )

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 源代码

select distinct w2.FSubSys as FSubSys,t3.FTypeName,t4.FName as FItemName,t4.FDescription as FDescription,t3.FUserName,t3.FObjectType,t3.FObjectID,t4.FIndex 
   from (

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      select t1.FObjectType,t1.FObjectID,t1.FAccessMask,t1.FName as FTypeName,t2.FUserID,t2.FName as FUserName,t1.fgroupid 
      from (
      
         ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         select a.*,o.FName as FName,y1.fgroupid 
         from t_AccessControl a
             ,t_ObjectType o 
             ,( select DISTINCT fobjecttype , fobjectid,fgroupid from t_objectaccess ) y1  
         
         where a.fObjectType=o.FObjectType and a.FObjectID=o.FObjectID  and a.fobjecttype=y1.fobjecttype and a.fobjectid=y1.fobjectid  and a.fobjectid = 210001202
         ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      
      ) t1
      ,( select FUserID,FName from t_user  where (FUserID>16394 or (FUserID>9 and fuserid <16384 )) and fuserid in(16398 ) 
      ) t2 
      
      where t1.FUserID=t2.FUserID  
      
      Union  
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      select t1.FObjectType,t1.FObjectID,t1.FAccessMask,t1.FName as FTypeName,t2.FUserID,t2.FName as FUserName,t1.fgroupid 
      from (
      
         ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         select a.*,o.FName as FName,y1.fgroupid 
         from t_AccessControl a
             ,t_ObjectType o 
             ,( select DISTINCT fobjecttype , fobjectid,fgroupid from t_objectaccess ) y1  
         
         where a.fObjectType=o.FObjectType and a.FObjectID=o.FObjectID  and a.fobjecttype=y1.fobjecttype and a.fobjectid=y1.fobjectid  and a.fobjectid = 210001202
         ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      
      ) t1
      ,( select g.FUserID,FGroupID,FName from t_group g,t_user u where g.fuserid=u.fuserid and u.fuserid in(16398) and u.FUserID>16394 
      ) t2 
      
      where t1.FUserID=t2.FGroupID
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   
   ) t3
   ,t_ObjectAccessType t4
   ,t_GroupAccessType w2  
   
   where  t3.FObjectType = t4.FObjectType 
     and  t3.FObjectID   = t4.FObjectID 
     and  t3.fgroupid    = w2.fgroupid 
     
     and (t3.FAccessMask & t4.FAccessMask = t4.FAccessMask ) 
     
     and  not ( t3.fobjecttype =  3       and t3.fobjectid = 20 )
     and        t4.FAccessUse  <> 262144 
     and        t4.FAccessMask <> 262144 
   
   order by w2.FSubSys,t3.FObjectType,t3.FObjectID,t4.FIndex,t3.FTypeName,t4.FName,t4.FDescription

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*/
go