July 17, 2014 at 1:04 am
I'm trying to integrate the security settings for our system into the reports and search and its a nightmare trying to fit in all the rules. Could anyone help suggest the best way to do this.
Basically I have a massive custom search query which I now have filter even further. (see http://www.sqlservercentral.com/Forums/Topic1589189-392-1.aspx for previous discussions on this query, which I'm currently happy with).
so we have 6 different types of transactions. each type can have different admins. the search can be done for either 1 type or all types.
Transactions by default are available to everyone, But there are a few (probably less than 1% of all) that security is enabled which is simply done by added people to the security table.
each transaction can be see by
* Everyone If no record found in security table for transaction.
* If even just one transaction found, the below have permission to view it.
- Person who raised transaction
- person currently responsible for it.
- person currently working on it.
- everyone in the security table for this transaction.
- Admins for this Transaction Type.
So when someone does a search I need to fit all the above. Because I'm building the search query it does make it a little easier to accommodate the various scenarios.
And I think I have them all EXCEPT
someone who is trying to search for records under ALL types, but is an admin of just 1 or 2 of the types. Ie they have too be able to see all records for those they are admins for, and then have normal security imposed for the other types of records.
My current query at the bottom of this post.
The section near "DECLARE @IsUserAdmin AS BIT" sets admin flags for when they are an admin of a single type if searching for a single type, or sets admin flag if they are an admin for all types.
ALTER PROCEDURE [dbo].[uspJobSearch]
@csType as nvarchar(20) = '-1',
@status as nvarchar(20) = '-1',
@startID as nvarchar(50) = '',
@endID as nvarchar(50) = '',
@complaintType as nvarchar(50) ='',
@Subject as nvarchar(100) = '',
@ComplaintDateStart as DATETIME = NULL,
@ComplaintDateEnd as dateTime = NULL,
@plannedCompDateStart as DATETIME = NULL,
@plannedCompDateEnd as dateTime = NULL,
@Responsible as nvarchar(50) = '',
@Reference as nvarchar(50) = '',
@ProjectJobNo as nvarchar(50) = '',
@priority as nvarchar(50) = '',
@NextToAction as nvarchar(50) = '',
@Initator as nvarchar(50) = '',
@siteID as int = '',
@BUName as nvarchar(50) = '',
@userid as nvarchar(50) = '',
@General as nVARCHAR(200) = '',
@company as nvarchar(200) = '',
@LastUpdatedDateStart as dateTime = NULL,
@LastUpdatedDateEnd as dateTime = NULL,
@region as nvarchar(200) = '',
@InherentRisk INT = 0,
@ControlRating INT = 0,
@ResidualRisk INT = 0,
@RiskOption VARCHAR(20) = NULL
--,@SearchUser VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
--SET ARITHABORT ON;
--SET ansi_warnings OFF
--IF ISNULL(@General,'') = '' SET @General = '""';
IF @Region<> ''
BEGIN
WITH DirectReports (RegionID, RegionName, Level)
AS
(
-- Anchor member definition
SELECT RegionID, RegionName, 0 AS Level
FROM dbo.tblRegion AS r
WHERE regionID =@region
UNION ALL
-- Recursive member definition
SELECT r.RegionID, r.RegionName, Level + 1
FROM dbo.tblRegion AS r
INNER JOIN DirectReports AS d
ON r.parentID = d.RegionID
)
--SELECT @region = COALESCE(@region+',' ,'') + CAST(s.siteID AS VARCHAR(10))
SELECT siteID
INTO #RegionSite
FROM DirectReports LEFT OUTER JOIN dbo.tblSiteRegion AS s ON DirectReports.RegionID = s.regionID
ORDER BY SiteID
END
--SELECT @region
--SELECT * FROM #RegionSite
--dbo.DelimitedSplit8K('''+@region +''','','')
DECLARE @IsUserAdmin AS BIT
DECLARE @SecurityAdminCount AS INTEGER
IF @csType<>'-1'
BEGIN
IF EXISTS (SELECT [Login] FROM [bknewho-crm].[Security].[dbo].[Application] WHERE [Application] =@csType AND Access = 'SUPERUSER' AND [login] = @userid)
SET @IsUserAdmin = 1
ELSE SET @IsUserAdmin = 0
END
ELSE
BEGIN
SELECT @SecurityAdminCount= COUNT(*) FROM [bknewho-crm].[Security].[dbo].[Application] WHERE [Application] IN ('BIR', 'gete','getm','IT', 'mkt', 'RISK') AND Access = 'SUPERUSER' AND [login] = @user-id
IF @SecurityAdminCount=6
SET @IsUserAdmin = 1
ELSE SET @IsUserAdmin = 0
END
SELECT @IsUserAdmin, @SecurityAdminCount
DECLARE @SQL AS nVARCHAR(4000)
-- d.domain,
SET @SQL = 'SELECT DISTINCT C.IdentificationNo, C.ComplaintType, C.csType, B.subject, C.ComplaintDate, C.BUInitiatingComplaint, mu.status,
RE.userID, Re.userName, C.CustomerRef, C.ProjectJobNo, C.dateReceived, mu.modUser, mu.modDate, mu.actionCompleted, mu.plannedCompDate,
B.priority, e.eID, e.sDate as DateActioned, e.NextAction, NextToAction.userName AS NextToAction, s.siteName
,CASE WHEN AC.MyCount IS NULL THEN 0 ELSE AC.MyCount END AS AttachmentCount'
IF @csType='Risk' OR @csType='-1' --@InherentRisk <> 0 OR @ControlRating <> 0 OR @ResidualRisk <> 0
SET @SQL = @SQL + ', RRC.riskColour AS ResidualRiskCorpColour, RRB.riskColour AS ResidualRiskColour
, IHC.riskValue AS InherentRiskCorpValue, IHB.riskValue AS InherentRiskValue '
ELSE
SET @SQL = @SQL + ', '''' AS ResidualRiskCorpColour, '''' AS ResidualRiskColour
, '''' AS InherentRiskCorpValue, '''' AS InherentRiskValue '
SET @SQL = @SQL +' FROM dbo.sgleComplaint AS C
INNER JOIN dbo.tblModUsers AS mu ON mu.modIDNo = C.IdentificationNo '
IF @NextToAction <> ''
SET @SQL = @SQL + 'INNER JOIN dbo.tblEmail AS e ON e.IdentificationNo = C.IdentificationNo '
ELSE
SET @SQL = @SQL + 'LEFT OUTER JOIN dbo.tblEmail AS e ON e.IdentificationNo = C.IdentificationNo '
SET @SQL = @SQL + 'LEFT OUTER JOIN dbo.tblEmailAddress AS NextToAction ON NextToAction.fullUserID = e.NextToAction
INNER JOIN dbo.tblBIR AS B ON C.IdentificationNo = B.IdentificationNo'
IF @Responsible <>''
SET @SQL = @SQL + ' INNER JOIN dbo.tblEmailAddress AS RE ON RE.eID = B.responsibleEmployee'
ELSE
SET @SQL = @SQL + ' LEFT OUTER JOIN dbo.tblEmailAddress AS RE ON RE.eID = B.responsibleEmployee'
IF @siteID <> ''
SET @SQL = @SQL + ' INNER JOIN dbo.tblSites AS s ON s.siteID = B.site'
ELSE
SET @SQL = @SQL + ' LEFT OUTER JOIN dbo.tblSites AS s ON s.siteID = B.site'
IF @region <> '' SET @SQL = @SQL + ' INNER JOIN #RegionSite R on S.siteID = R.SiteID'
IF @csType='Risk' OR @csType='-1'--@InherentRisk <> 0 OR @ControlRating <> 0 OR @ResidualRisk <> 0
SET @SQL = @SQL + ' LEFT OUTER JOIN tblBIRRisk as BR on B.IdentificationNo = BR.IdentificationNo
LEFT OUTER JOIN tblRiskLookup as RRC on BR.riskRisidualRiskCorp = RRC.RiskID
LEFT OUTER JOIN tblRiskLookup as RRB on BR.riskRisidualRisk = RRB.RiskID
LEFT OUTER JOIN tblRiskLookup as IHC on BR.riskInherentRiskCorp = IHC.RiskID
LEFT OUTER JOIN tblRiskLookup as IHB on BR.riskInherentRisk = IHB.RiskID'
IF @IsUserAdmin=0
SET @SQL = @SQL + ' LEFT OUTER JOIN [tblGETApproveList] SL on B.identificationNo = SL.identificationNo
LEFT OUTER JOIN tblEmailAddress SEA on SL.eid = SEA.eID '
SET @SQL = @SQL + ' LEFT OUTER JOIN (SELECT IdentificationNo, COUNT(*) AS MyCount
FROM dbo.tblAttachment
GROUP BY IdentificationNo) AS AC ON AC.IdentificationNo = C.IdentificationNo
WHERE (e.eid IS null OR e.eid = (SELECT MAX(eID) AS mEID
FROM dbo.tblEmail AS tblEmail_1
WHERE (IdentificationNo = C.IdentificationNo))
)'
IF @csType<>'-1' SET @SQL = @SQL + ' AND C.csType = @_cstype '
IF @status='MyTasks'
BEGIN
SET @SQL = @SQL + ' AND NOT (mu.status = ''Closed'' OR mu.status= ''Canceled'') '
SET @SQL = @SQL + 'AND ( ( RE.fullUserID = @_userID )'
SET @SQL = @SQL + 'OR (e.NextToAction = @_userID)'
SET @SQL = @SQL + 'OR (MU.modUser = @_userID))'
END
ELSE IF @status = 'myOverdue'
BEGIN
SET @SQL = @SQL + ' AND NOT (mu.status = ''Closed'' OR mu.status= ''Canceled'') '
SET @SQL = @SQL + 'AND ( ( RE.fullUserID = @_userID )'
SET @SQL = @SQL + 'OR (e.NextToAction = @_userID)'
SET @SQL = @SQL + 'OR (MU.modUser = @_userID))'
SET @SQL = @SQL + 'AND (mu.plannedCompDate <= ''' + convert(varchar(10),getdate(),102) + ''')'
END
ELSE IF @status = 'allOverdue'
BEGIN
SET @SQL = @SQL + ' AND NOT (mu.status = ''Closed'' OR mu.status= ''Canceled'') '
SET @SQL = @SQL + 'AND (mu.plannedCompDate <= ''' + convert(varchar(10),getdate(),102) + ''')'
END
ELSE IF @status='-1'
SET @SQL = @SQL + ' AND NOT (mu.status = ''Closed'' OR mu.status= ''Canceled'') '
ELSE
IF @status <> '0' SET @SQL = @SQL + ' AND mu.status = @_status '
IF @ComplaintType <> '' SET @SQL = @SQL + ' AND C.ComplaintType = @_ComplaintType '
IF NOT(@startID='') SET @SQL = @SQL + ' AND C.IdentificationNo >= @_startID '
IF NOT(@endID='') SET @SQL = @SQL + ' AND C.IdentificationNo <= @_endID '
IF NOT(@LastUpdatedDateStart is null) SET @SQL = @SQL + ' AND C.ComplaintDate >= convert(varchar(10),@_LastUpdatedDateStart,102) '
IF NOT(@LastUpdatedDateEnd is null) SET @SQL = @SQL + ' AND C.ComplaintDate <= convert(varchar(10),@_LastUpdatedDateEnd+1,102) '
IF NOT(@ComplaintDateStart is null) SET @SQL = @SQL + ' AND MU.modDate >= convert(varchar(10),@_ComplaintDateStart,102) '
IF NOT(@ComplaintDateEnd is null) SET @SQL = @SQL + ' AND MU.modDate <= convert(varchar(10),@_ComplaintDateEnd+1 ,102) '
IF NOT(@plannedCompDateStart is null) SET @SQL = @SQL + ' AND mu.plannedCompDate >= convert(varchar(10), @_plannedCompDateStart,102) '
IF NOT(@plannedCompDateEnd is null) SET @SQL = @SQL + ' AND mu.plannedCompDate <= convert(varchar(10),@_plannedCompDateEnd+1 ,102) '
IF @subject <> '' SET @SQL = @SQL + ' AND B.subject like ''%'' + @_subject + ''%'''
IF @BUName <> '' SET @SQL = @SQL + ' AND C.BUInitiatingComplaint = @_BUName '
IF @siteID <> '' SET @SQL = @SQL + ' AND S.SiteID = CAST(@_siteID AS VARCHAR)'
IF @Responsible <> '' SET @SQL = @SQL + ' AND RE.userName = @_Responsible '
IF @NextToAction <> '' SET @SQL = @SQL + ' AND e.NextToAction= @_NextToAction '
IF @Initator <> '' SET @SQL = @SQL + ' AND MU.modUser = @_Initator '
IF @Reference <> '' SET @SQL = @SQL + ' AND C.CustomerRef like ''%'' + @_Reference + ''%'''
IF @ProjectJobNo <> '' SET @SQL = @SQL + ' AND C.ProjectJobNo like ''%'' + @_ProjectJobNo + ''%'''
IF @priority <> '' SET @SQL = @SQL + ' AND B.priority = @_priority '
IF @company <> '' SET @SQL = @SQL + ' AND B.company like ''%'' + @_company + ''%'''
IF @General <> '' SET @SQL = @SQL + ' AND FREETEXT(B.History, @_General)'
--IF @region <> '' SET @SQL = @SQL + ' AND S.SiteID = R.SiteID'
IF @RiskOption ='BOTH'
BEGIN
IF @InherentRisk <> 0SET @SQL = @SQL + ' AND (riskInherentRisk = @_InherentRisk OR riskInherentRiskCorp = @_InherentRisk) '
IF @ControlRating <> 0SET @SQL = @SQL + ' AND (riskControlRating = @_ControlRating OR riskControlRatingCorp = @_ControlRating) '
IF @ResidualRisk <> 0SET @SQL = @SQL + ' AND (riskRisidualRisk = @_ResidualRisk OR riskRisidualRiskCorp = @_ResidualRisk) '
END
ELSE IF @RiskOption ='Corporate'
BEGIN
IF @InherentRisk <> 0SET @SQL = @SQL + ' AND ( riskInherentRiskCorp = @_InherentRisk) '
IF @ControlRating <> 0SET @SQL = @SQL + ' AND ( riskControlRatingCorp = @_ControlRating) '
IF @ResidualRisk <> 0SET @SQL = @SQL + ' AND ( riskRisidualRiskCorp = @_ResidualRisk) '
END
ELSE IF @RiskOption ='Business'
BEGIN
IF @InherentRisk <> 0SET @SQL = @SQL + ' AND (riskInherentRisk = @_InherentRisk ) '
IF @ControlRating <> 0SET @SQL = @SQL + ' AND (riskControlRating = @_ControlRating ) '
IF @ResidualRisk <> 0SET @SQL = @SQL + ' AND (riskRisidualRisk = @_ResidualRisk ) '
END
IF @IsUserAdmin=0
SET @SQL = @SQL + ' AND (sea.FullUserID is NULL
OR (sea.fullUserID = @_userID
OR e.NextToAction = @_userID
OR MU.modUser = @_userID
OR RE.fullUserID = @_userID)) '
--SET @SQL = @SQL + ' AND b.identificationNo IN
-- (SELECT IdentificationNo
--FROM dbo.tblBIR
--EXCEPT
--SELECT identificationNo FROM dbo.tblGETApproveList
--UNION
--SELECT identificationNo FROM dbo.tblGETApproveList gal INNER JOIN dbo.tblEmailAddress ea ON ea.eID = gal.eID WHERE fullUserID = @_userID) '
SET @SQL = @SQL + ' ORDER BY C.IdentificationNo'
SELECT @SQL
EXEC sp_executesql @SQL,
N'@_cstype nvarchar(20), @_userID nvarchar(50), @_status nvarchar(20), @_ComplaintType nvarchar(50), @_startID int, @_endID int
, @_LastUpdatedDateStart dateTime, @_LastUpdatedDateEnd dateTime, @_ComplaintDateStart dateTime, @_ComplaintDateEnd dateTime, @_plannedCompDateStart dateTime
, @_plannedCompDateEnd dateTime, @_subject nvarchar(100), @_BUName nvarchar(50),@_siteID int, @_Responsible nvarchar(50), @_NextToAction nvarchar(50)
, @_Initator nvarchar(50), @_Reference nvarchar(50), @_ProjectJobNo nvarchar(50), @_priority nvarchar(50), @_company nvarchar(200), @_General nVARCHAR(200)
,@_InherentRisk int, @_ControlRating int, @_ResidualRisk int'
, @_cstype=@cstype, @_userID=@userID, @_status=@status, @_ComplaintType=@ComplaintType, @_startID=@startID, @_endID=@endID
,@_LastUpdatedDateStart=@LastUpdatedDateStart, @_LastUpdatedDateEnd=@LastUpdatedDateEnd, @_ComplaintDateStart=@ComplaintDateStart, @_ComplaintDateEnd=@ComplaintDateEnd, @_plannedCompDateStart=@plannedCompDateStart
,@_plannedCompDateEnd=@plannedCompDateEnd, @_subject=@subject, @_BUName=@BUName, @_siteID=@siteID, @_Responsible=@Responsible, @_NextToAction=@NextToAction
, @_Initator=@Initator, @_Reference=@Reference, @_ProjectJobNo=@ProjectJobNo, @_priority=@priority, @_company=@company, @_General=@General
,@_InherentRisk = @InherentRisk, @_ControlRating = @ControlRating, @_ResidualRisk=@ResidualRisk
END
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply