用友U8快速设置敏感字段

业务场景：某客户主营药品生产，为防止部分员工根据所用原料名称推断产品结构从而盗取产品配方，在ERP系统中必须将存货名称对全部人员隐藏。
常规情况下只能依靠数据权限对每个操作员或角色设置，但U8中数据权限需要对每个表或档案设置；后查阅资料发现可以直接将相应字段设置为敏感字段并启用敏感字段控制，但手工添加单据列表太多令人头疼：
首先需要先增加对象；该步骤是前提条件 无论手工增加还是批量增加

1. 手工增加
   选择所有需要隐藏字段的单据及字段名称后保存映射
   
2. 批量增加

在账套库中，执行以下语句（执行前请务必做好备份！！！）

declare @CSensName nvarchar(100)--敏感资源中用户新添加的敏感字段名称 例如'存货名称'
set @CSensName=N'存货名称隐藏'
declare @VoucherFldName nvarchar(100)--单据中要映射的字段名 例如'存货名称'，默认搜索包含关键字的都会添加映射

set @VoucherFldName=N'存货名称'
--注：此处‘存货名称’只能隐藏单据上为‘存货名称’的部分，但比如生产订单存货名称叫做‘产品名称’需要替换后再次执行

declare @CSensFld nvarchar(100)
select @CSensFld= BusObId from BusSenObject where langid ='zh-CN' and BusObName=@CSensName order by grade asc,busobname 
print @CSensFld

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_busColumndic]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
	drop table tmp_busColumndic 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_busColumndic1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
	drop table tmp_busColumndic1 
if object_id(N'tempdb..#tempUapSenseTable') IS NOT null
	drop table #tempUapSenseTable ;
select distinct cbusobid into #tempUapSenseTable from aa_busobject_base where csub_id <> 'XX' and langid = 'zh-CN' AND iauthtype = 1  ; 

declare @accid nvarchar(20)
select @accid = db_name()
set @accid=substring(@accid, charindex('_',@accid)+1,3)
print @accid
declare @sql nvarchar(4000)
set @sql='select ckey,cfld,ccaption,localeid into tmp_busColumndic from ufmeta_'+@accid+'..aa_columndic_base where localeid = ''zh-CN'' and ckey in (select cbusobid from aa_busobject_base where csub_id <> ''XX'' and langid = ''zh-CN'' and iauthtype = 1)' 
exec (@sql)
insert into tmp_busColumndic(ckey,cfld,ccaption,localeid) 
                                   (select col.ckey,col.cfld,col.ccaption,col.localeid from aa_columndic_base col INNER JOIN 
                                #tempUapSenseTable bus ON col.cKey=bus.cbusobid
                                where col.localeid = 'zh-CN' 
                                AND col.cFld NOT IN 
                                (
                                SELECT cfld FROM dbo.tmp_busColumndic dic INNER JOIN #tempUapSenseTable bus
                                ON dic.ckey=bus.cbusobid  
                                ))

select * into tmp_busColumndic1 from tmp_busColumndic where left(ccaption,1) <> '@' 
insert into tmp_busColumndic1(ckey,cfld,ccaption,localeid) (select A.ckey,A.cfld,B.ccaption,A.localeid from (select * from tmp_busColumndic where left(ccaption,1) = '@') as A inner join (select * from ua_caption where clocaleid = 'zh-CN') as B on A.ccaption = B.cVer) 
drop table tmp_busColumndic

set @sql='                                
INSERT INTO AA_ColumnAuthMapping (cmappingid, cbusobid, cbussensobid, cfld, CFldBusSens)
SELECT NEWID(), ckey, N''inventoryvoucher'', cfld, '''+@CSensFld+'''
FROM tmp_busColumndic1 AS Field
WHERE cCaption LIKE '''+@VoucherFldName+'''
AND NOT EXISTS (
    SELECT 1
    FROM AA_ColumnAuthMapping AS CAM
    WHERE CAM.cbusobid = Field.ckey
    AND CAM.cfld = Field.cfld
	AND cam.CBusSensObId=N''inventoryvoucher''
	AND cam.CFldBusSens='''+@CSensFld+'''
);'
print @sql
exec(@sql)					
						
			

执行完成后退出UAP 重新进入就看到我们需要的字段已经都添加好了

再次提醒 所有在数据库操作前 务必先备份！！！！