dspConduct

Content WebApp Example: Security Views for Binding Security by OrgUnits and Security that is not Bounded by any OrgUnits

NOTE: This view must be created prior to creating the Role Status BY User Views.

 

USE [DGE]

GO

 

/****** Object:  Table [dbo].[ttSecurityCacheOrgUnit2]    Script Date: 4/22/2016 9:06:07 AM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ttSecurityCacheOrgUnit2]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[ttSecurityCacheOrgUnit2](

              [UserID] [nvarchar](50) NOT NULL,

              [RoleID] [uniqueidentifier] NOT NULL,

              [OrgUnitValue] [nvarchar](50) NOT NULL,

              [ReadOnly] [bit] NOT NULL,

 CONSTRAINT [PK_ttSecurityCacheOrgUnit2] PRIMARY KEY CLUSTERED

(

              [RoleID] ASC,

              [UserID] ASC,

              [OrgUnitValue] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)

)

END

GO

 

/****** Object:  Table [dbo].[ttSecurityCacheOrgUnit3]    Script Date: 4/22/2016 9:06:07 AM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ttSecurityCacheOrgUnit3]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[ttSecurityCacheOrgUnit3](

              [UserID] [nvarchar](50) NOT NULL,

              [RoleID] [uniqueidentifier] NOT NULL,

              [OrgUnitValue] [nvarchar](50) NOT NULL,

              [ReadOnly] [bit] NOT NULL,

 CONSTRAINT [PK_ttSecurityCacheOrgUnit3] PRIMARY KEY CLUSTERED

(

              [RoleID] ASC,

              [UserID] ASC,

              [OrgUnitValue] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)

)

END

GO

 

/****** Object:  Table [dbo].[ttSecurityCacheOrgUnit1]    Script Date: 4/22/2016 9:06:07 AM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ttSecurityCacheOrgUnit1]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[ttSecurityCacheOrgUnit1](

              [UserID] [nvarchar](50) NOT NULL,

              [RoleID] [uniqueidentifier] NOT NULL,

              [OrgUnitValue] [nvarchar](50) NOT NULL,

              [ReadOnly] [bit] NOT NULL DEFAULT ((0)),

 CONSTRAINT [PK_ttSecurityCacheOrgUnit1] PRIMARY KEY CLUSTERED

(

              [RoleID] ASC,

              [UserID] ASC,

              [OrgUnitValue] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)

)

END

GO

 

/*NOTE:  Replace “dgeNameOfContentApp” below with the database name where these views need to be created. There should be only one occurrence.*/

 

USE [dgeNameOfContentApp]

GO

 

/****** Object:  View [dbo].[ttSecurityCacheOrgUnit3]    Script Date: 4/22/2016 9:06:07 AM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ttSecurityCacheOrgUnit3]'))

EXEC dbo.sp_executesql @statement = N'

 

CREATE VIEW [dbo].[ttSecurityCacheOrgUnit3]

AS

SELECT        UserID, RoleID, OrgUnitValue, ReadOnly

FROM            DGE.dbo.ttSecurityCacheOrgUnit3

 

 

'

GO

 

/****** Object:  View [dbo].[ttSecurityCacheOrgUnit2]    Script Date: 4/22/2016 9:06:07 AM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ttSecurityCacheOrgUnit2]'))

EXEC dbo.sp_executesql @statement = N'

CREATE VIEW [dbo].[ttSecurityCacheOrgUnit2]

AS

SELECT        UserID, RoleID, OrgUnitValue, ReadOnly

FROM            DGE.dbo.ttSecurityCacheOrgUnit2

 

'

GO

 

/****** Object:  View [dbo].[ttSecurityCacheOrgUnit1]    Script Date: 4/22/2016 9:06:07 AM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ttSecurityCacheOrgUnit1]'))

EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[ttSecurityCacheOrgUnit1]

AS

SELECT        UserID, RoleID, OrgUnitValue, ReadOnly

FROM            DGE.dbo.ttSecurityCacheOrgUnit1

'

GO

 

USE [DGE]

GO

 

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DF__ttSecurit__ReadO__0EF1425F]') AND type = 'D')

BEGIN

ALTER TABLE [dbo].[ttSecurityCacheOrgUnit2] ADD  DEFAULT ((0)) FOR [ReadOnly]

END

 

GO

 

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DF__ttSecurit__ReadO__0FE56698]') AND type = 'D')

BEGIN

ALTER TABLE [dbo].[ttSecurityCacheOrgUnit3] ADD  DEFAULT ((0)) FOR [ReadOnly]

END

 

GO

 

CREATE VIEW [dbo].[webSecurity_UnboundedSel]

AS

SELECT        RequestID, RoleID, UserID AS boaUserID, ReadOnly

FROM            dbo.ttRequestRoleUser

 

GO

 

 

CREATE VIEW [dbo].[webSecurity_BoundByOrgUnit1Sel]

AS

SELECT        dbo.ttRequestRoleUser.RequestID, dbo.ttSecurityCacheOrgUnit1.RoleID, dbo.ttSecurityCacheOrgUnit1.OrgUnitValue AS OrgUnit1, dbo.ttSecurityCacheOrgUnit1.UserID AS boaUserID,

                         dbo.ttSecurityCacheOrgUnit1.ReadOnly

FROM            dbo.ttSecurityCacheOrgUnit1 INNER JOIN

                         dbo.ttRequestRoleUser ON dbo.ttSecurityCacheOrgUnit1.RoleID = dbo.ttRequestRoleUser.RoleID AND dbo.ttSecurityCacheOrgUnit1.UserID = dbo.ttRequestRoleUser.UserID

 

GO

 

/****** Object:  View [dbo].[webSecurity_BoundByOrgUnit2Sel]    Script Date: 3/1/2016 10:39:11 PM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE VIEW [dbo].[webSecurity_BoundByOrgUnit2Sel]

AS

SELECT        dbo.ttRequestRoleUser.RequestID, dbo.ttSecurityCacheOrgUnit2.RoleID, dbo.ttSecurityCacheOrgUnit2.OrgUnitValue AS OrgUnit2, dbo.ttSecurityCacheOrgUnit2.UserID AS boaUserID,

                         dbo.ttSecurityCacheOrgUnit2.ReadOnly

FROM            dbo.ttSecurityCacheOrgUnit2 INNER JOIN

                         dbo.ttRequestRoleUser ON dbo.ttSecurityCacheOrgUnit2.RoleID = dbo.ttRequestRoleUser.RoleID AND dbo.ttSecurityCacheOrgUnit2.UserID = dbo.ttRequestRoleUser.UserID

 

GO

 

/****** Object:  View [dbo].[webSecurity_BoundByOrgUnit3Sel]    Script Date: 3/1/2016 10:39:11 PM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE VIEW [dbo].[webSecurity_BoundByOrgUnit3Sel]

AS

SELECT        dbo.ttRequestRoleUser.RequestID, dbo.ttSecurityCacheOrgUnit3.RoleID, dbo.ttSecurityCacheOrgUnit3.OrgUnitValue AS OrgUnit3, dbo.ttSecurityCacheOrgUnit3.UserID AS boaUserID,

                         dbo.ttSecurityCacheOrgUnit3.ReadOnly

FROM            dbo.ttSecurityCacheOrgUnit3 INNER JOIN

                         dbo.ttRequestRoleUser ON dbo.ttSecurityCacheOrgUnit3.UserID = dbo.ttRequestRoleUser.UserID AND dbo.ttSecurityCacheOrgUnit3.RoleID = dbo.ttRequestRoleUser.RoleID

 

GO

/*A Content Request page may need ScenarioID to be set on the Vertical View to drive configuration for scenario-based field controls on the Request page.  In this case, the Request page must have an INSERT MODE of “Horizontal Insert/Switch To Vertical” and the following Trigger that calls the above-suggested OnValidate procedure at the trigger level.*/

 

CREATE TRIGGER [dbo].[trgRequest_BusinessProcessScenarioIns] ON [dbo].[ttRequest] 

FOR INSERT 

AS 

DECLARE @BusinessProcessID uniqueidentifier 

DECLARE @ScenarioID uniqueidentifier

DECLARE @ContentRequestID uniqueidentifier

DECLARE @RequestID int 

DECLARE @boaUserID nvarchar(50)

IF UPDATE (ScenarioID) 

BEGIN 

SET @ScenarioID = ISNULL((SELECT ScenarioID FROM INSERTED), NULL) 

SET @ContentRequestID = (SELECT ContentRequestID FROM INSERTED)

SET @boaUserID = (SELECT AddedBy FROM INSERTED) 

IF (@ScenarioID IS NULL) 

BEGIN 

SET @BusinessProcessID = (SELECT BusinessProcessID FROM ttRequest WHERE ContentRequestID =@ContentRequestID) 

 

EXEC webRequest_OnValidate_CreateRequest @ContentRequestID, @BusinessProcessID, @boaUserID

END

END