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