rls-sql-example-db

/****** Object:  Table [dbo].[AttributeFilters]    Script Date: 3/18/2022 11:17:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AttributeFilters](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[AttributeKey] [varchar](20) NOT NULL,
	[ValueToFilter] [varchar](100) NOT NULL,
	[RoleId] [varchar](10) NOT NULL,
	[SecurityFilterId] [int] NOT NULL,
 CONSTRAINT [PK_AttributeFilters] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ProtectedEntity]    Script Date: 3/18/2022 11:17:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProtectedEntity](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](20) NOT NULL,
 CONSTRAINT [PK_ProtectedEntity] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ProtectedEntityAttributes]    Script Date: 3/18/2022 11:17:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProtectedEntityAttributes](
	[EntityId] [int] NOT NULL,
	[AttributeKey] [varchar](20) NOT NULL,
	[AttributeValue] [varchar](100) NOT NULL,
 CONSTRAINT [PK_ProtectedEntityAttributes] PRIMARY KEY CLUSTERED 
(
	[EntityId] ASC,
	[AttributeKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Role]    Script Date: 3/18/2022 11:17:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Role](
	[Id] [varchar](10) NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[User_Role]    Script Date: 3/18/2022 11:17:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User_Role](
	[UserId] [int] NOT NULL,
	[RoleId] [varchar](10) NOT NULL,
 CONSTRAINT [PK_User_Role] PRIMARY KEY CLUSTERED 
(
	[UserId] ASC,
	[RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Users]    Script Date: 3/18/2022 11:17:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Username] [varchar](50) NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[AttributeFilters] ON 
GO
INSERT [dbo].[AttributeFilters] ([Id], [AttributeKey], [ValueToFilter], [RoleId], [SecurityFilterId]) VALUES (1, N'Country', N'NL', N'reg-user', 1)
GO
INSERT [dbo].[AttributeFilters] ([Id], [AttributeKey], [ValueToFilter], [RoleId], [SecurityFilterId]) VALUES (2, N'Country', N'UK', N'reg-user', 2)
GO
INSERT [dbo].[AttributeFilters] ([Id], [AttributeKey], [ValueToFilter], [RoleId], [SecurityFilterId]) VALUES (3, N'Urgency', N'High', N'priority', 3)
GO
INSERT [dbo].[AttributeFilters] ([Id], [AttributeKey], [ValueToFilter], [RoleId], [SecurityFilterId]) VALUES (4, N'Country', N'UK', N'priority', 3)
GO
INSERT [dbo].[AttributeFilters] ([Id], [AttributeKey], [ValueToFilter], [RoleId], [SecurityFilterId]) VALUES (5, N'Country', N'UK', N'ukdefr', 1)
GO
INSERT [dbo].[AttributeFilters] ([Id], [AttributeKey], [ValueToFilter], [RoleId], [SecurityFilterId]) VALUES (6, N'Country', N'DE', N'ukdefr', 2)
GO
INSERT [dbo].[AttributeFilters] ([Id], [AttributeKey], [ValueToFilter], [RoleId], [SecurityFilterId]) VALUES (7, N'Country', N'FR', N'ukdefr', 3)
GO
INSERT [dbo].[AttributeFilters] ([Id], [AttributeKey], [ValueToFilter], [RoleId], [SecurityFilterId]) VALUES (8, N'Country', N'NL', N'nlusers', 1)
GO
SET IDENTITY_INSERT [dbo].[AttributeFilters] OFF
GO
SET IDENTITY_INSERT [dbo].[ProtectedEntity] ON 
GO
INSERT [dbo].[ProtectedEntity] ([Id], [Name]) VALUES (1, N'UK person dossier')
GO
INSERT [dbo].[ProtectedEntity] ([Id], [Name]) VALUES (2, N'FR person dossier')
GO
INSERT [dbo].[ProtectedEntity] ([Id], [Name]) VALUES (3, N'NL person dossier')
GO
INSERT [dbo].[ProtectedEntity] ([Id], [Name]) VALUES (4, N'Urgent use case')
GO
INSERT [dbo].[ProtectedEntity] ([Id], [Name]) VALUES (5, N'Some low use case')
GO
INSERT [dbo].[ProtectedEntity] ([Id], [Name]) VALUES (7, N'urgent use case1')
GO
INSERT [dbo].[ProtectedEntity] ([Id], [Name]) VALUES (8, N'urgent use case2')
GO
SET IDENTITY_INSERT [dbo].[ProtectedEntity] OFF
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (1, N'Country', N'UK')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (1, N'Type', N'some val')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (1, N'Urgency', N'Low')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (2, N'Country', N'FR')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (2, N'Type', N'some other val')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (2, N'Urgency', N'High')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (3, N'Country', N'NL')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (3, N'Old Attr', N'some val')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (3, N'Urgency', N'Low')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (4, N'Country', N'SE')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (4, N'Descr', N'my description')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (4, N'Urgency', N'High')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (5, N'Country', N'Unknown')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (5, N'Urgency', N'Low')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (7, N'Country', N'UK')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (7, N'Desc', N'some desc')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (7, N'Urgency', N'High')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (8, N'Country', N'UK')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (8, N'Desc', N'other desc')
GO
INSERT [dbo].[ProtectedEntityAttributes] ([EntityId], [AttributeKey], [AttributeValue]) VALUES (8, N'Urgency', N'High')
GO
INSERT [dbo].[Role] ([Id], [Name]) VALUES (N'g-admin', N'Global Admin')
GO
INSERT [dbo].[Role] ([Id], [Name]) VALUES (N'nlusers', N'NL')
GO
INSERT [dbo].[Role] ([Id], [Name]) VALUES (N'priority', N'High Urgency Causes')
GO
INSERT [dbo].[Role] ([Id], [Name]) VALUES (N'reg-user', N'Reg User (UK, NL)')
GO
INSERT [dbo].[Role] ([Id], [Name]) VALUES (N'ukdefr', N'UK, DE, FR')
GO
INSERT [dbo].[User_Role] ([UserId], [RoleId]) VALUES (2, N'reg-user')
GO
INSERT [dbo].[User_Role] ([UserId], [RoleId]) VALUES (3, N'priority')
GO
INSERT [dbo].[User_Role] ([UserId], [RoleId]) VALUES (4, N'ukdefr')
GO
INSERT [dbo].[User_Role] ([UserId], [RoleId]) VALUES (5, N'nlusers')
GO
INSERT [dbo].[User_Role] ([UserId], [RoleId]) VALUES (5, N'priority')
GO
SET IDENTITY_INSERT [dbo].[Users] ON 
GO
INSERT [dbo].[Users] ([Id], [Username]) VALUES (1, N'global-admin')
GO
INSERT [dbo].[Users] ([Id], [Username]) VALUES (5, N'priority-nl')
GO
INSERT [dbo].[Users] ([Id], [Username]) VALUES (3, N'priority-person')
GO
INSERT [dbo].[Users] ([Id], [Username]) VALUES (2, N'regional-user')
GO
INSERT [dbo].[Users] ([Id], [Username]) VALUES (4, N'uk-de-fr-person')
GO
SET IDENTITY_INSERT [dbo].[Users] OFF
GO
ALTER TABLE [dbo].[AttributeFilters]  WITH CHECK ADD  CONSTRAINT [FK_AttributeFilters_Role] FOREIGN KEY([RoleId])
REFERENCES [dbo].[Role] ([Id])
GO
ALTER TABLE [dbo].[AttributeFilters] CHECK CONSTRAINT [FK_AttributeFilters_Role]
GO
ALTER TABLE [dbo].[ProtectedEntityAttributes]  WITH CHECK ADD  CONSTRAINT [FK_ProtectedEntityAttributes_ProtectedEntity] FOREIGN KEY([EntityId])
REFERENCES [dbo].[ProtectedEntity] ([Id])
GO
ALTER TABLE [dbo].[ProtectedEntityAttributes] CHECK CONSTRAINT [FK_ProtectedEntityAttributes_ProtectedEntity]
GO
ALTER TABLE [dbo].[User_Role]  WITH CHECK ADD  CONSTRAINT [FK_User_Role_Role] FOREIGN KEY([RoleId])
REFERENCES [dbo].[Role] ([Id])
GO
ALTER TABLE [dbo].[User_Role] CHECK CONSTRAINT [FK_User_Role_Role]
GO
ALTER TABLE [dbo].[User_Role]  WITH CHECK ADD  CONSTRAINT [FK_User_Role_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[Users] ([Id])
GO
ALTER TABLE [dbo].[User_Role] CHECK CONSTRAINT [FK_User_Role_User]
GO