Complex Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Complex Query

Author

Topic

SirRawlins
Starting Member

14 Posts

Posted - 2007-05-27 : 13:54:51

Hello Guys,

I'm looking for some help writing what I guess is likely to end up being a bit of a complex query. I've put a little thought into the design aspect of this and think I've come up with the best solution to the problem, but if you have any other ideas then I'd love to hear them.

This is for the user authentication system on my latest web application which will be 'role' based and quite granular so it offers a fair amount of controll over whats entitlements my users have in the application. My database layout looks somthing like this, I've cut out any of the irrelevant columns to keep it simple for you.

User
----
User_ID - Int [Primary Key]

Entitlement
-----------
Entitlement_ID - Int [Primary Key]
EntitlementName - Varchar

EntitlementGroup
----------------
EntitlementGroup_ID - Int [Primary Key]

EntitlementGroupEntitlement
---------------------------
EntitlementGroupEntitlement_ID - Int [Primary Key]
EntitlementGroup_ID - Int
Entitlement_ID - Int

UserEntitlement
---------------
UserEntitlement_ID - Int [Primary Key]
User_ID - Int
Entitlement_ID - Int
PermitRevoke - TinyInt

UserEntitlementGroup
--------------------
UserEntitlementGroup - Int [Primary Key]
User_ID - Int
EntitlementGroup_ID - Int

Now, the idea behind this is that my application has a whole host of different 'entitlements' such as 'create account' and 'edit profile' and so on, and so forth. To make it easy to apply these entitlements to a user, I've created the entitlements groups, which are essentialy 'roles' such as 'admin'. Each group can have many entitlements and each entitlement can belong to several groups.

Each user can then have several groups assigned to them, and each group can be assigned to several users. In addition to assigning a group to a user, I've added an additional level which can be used to 'permit' or 'revoke' additional entitlements to thier standard roles, so i could then give a user the 'admin' role, but remove the entitlement to delete a contact or somthing like that. This extra layer is controlled by the UserEntitlement table, you'll see the tinyint switch for each additional permission, dependant on whether its been permitted or revoked.

Now the query I'm looking to build will return ALL entitlements from the entitlements table, and then list a 1 or a 0 dependant on whether the user has that entitlement or not.

To do this the first thing the query has to do is set all the entitlements to 1 which are contained within the 'groups' assigned to the user, and assume that all other permissions are not granted, then go through the 'UserEntitlement' table and make changes to any settings found in it.

I know this is very complicated, so let me try and explain it using an example.

Entitlements
------------
1 - Add Contact
2 - Edit Contact
3 - Move Contact
4 - Delete Contact

Entitlements Group
------------------
1 - Admin

EntitlementsGroupEntitlements
-----------------------------
1 - 1
1 - 2
1 - 3
1 - 4

So far we now have a group called 'admin' which has all the entitlements assigned to it.

UserEntitlementGroup
--------------------
1 - 1

Now, user 1 has the admin group assigned to them, and as a result user 1 now has all the entitlements.

UserEntitlements
----------------
1 - 4 - 0

Now, I've taken the user and the entitlement to 'delete contact' and set it to 0, which means the user does not have that entitlement any more.

So the returned results will look like this.

User 1
------
Add Contact - 1
Edit Contact - 1
Move Contact - 1
Deletecontact - 0

I know thats taken alot of explaining so I'm sure you'll have quite a few questions, feel free to let me know and I'll answer them as best I can, I'm really struggling to picture how this query is going to come together, I would guess we start by selecting all the entitlements from the table and then doing a LEFT OUTER with the others, this will ensure that all the entitlements are returned, its just a case of the rest of it now :-D

Thanks guys for any help you can offer.

Many Thanks,

Rob

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-28 : 04:10:50

something similar to

select e.*
from entitlements
left join (select * from UserEntitlementGroup as eg inner join EntitlementsGroup as f on f.col1 = eg.col1 inner join ...) as o
on o.entitlementid = e.entitlementid

Peter Larsson
Helsingborg, Sweden

SirRawlins
Starting Member

14 Posts

Posted - 2007-05-28 : 11:14:03

Thanks for getting back to me Peter,

Unfortunatly my SQL skills are not so great so trying to place together the rest of that query is proving difficult. Someone else on another board has suggested this method


select
 Rights Name,
 isnull(entitlement id field of UserEntitleMentTableId,entitlement id field of normal join) Rights
from
..........

But again, this is a little over my head, using lots of joins and things. Are you able to eleborate on your example a little more for me?

Many thanks,

Rob

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-28 : 11:19:08

first of all: what kind of database engine are you using?
Oracle, MS Sql server, mySql???

This is a site only for MS Sql Server, so if you're using another db engine we can't really halp you much.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SirRawlins
Starting Member

14 Posts

Posted - 2007-05-28 : 11:32:41

Hey Spirit,

I should have mentioned this earlier, I'm running SQL Server 2005 Standard, so this should be right up your street.

Thanks,

Rob

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-28 : 11:39:00

cool.

well as far as i know this is invalid syntax for sql server:
select
Rights Name,
isnull(entitlement id field of UserEntitleMentTableId,entitlement id field of normal join) Rights
from

well first of all read up on joins

joins are performed between 2 sets on a common key.
inner join returns only data that appear in both columns.
what Peso did is called a subquery. read about that too.
It's basically a select statement returning a set that he joined to your table.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SirRawlins
Starting Member

14 Posts

Posted - 2007-05-28 : 11:55:15

Thanks for getting back to me Spirit,

I have a pretty fair grasp of JOIN's and Subqueries, but what I'm struggling to do here is have the query return the dataset that I want. See, the bit referance that I want in the second column of my query results doesnt realy exist, we'll have to fabricate it out of my data. Let me give another quick example of my data.

Entitlement
-----------
1 - Add User
2 - Delete User

This is my entitlement table, I have two columns, one of which is the ID for the entitlement, the other is a freindly name for the entitlement, its this name that I want returned in the left column of my query, I also want ALL the entitlements returned in my query, regardless of what is in the other tables.

EntitlementGroupEntitlement
---------------------------
1 - 1
1 - 2

What this does is add both entitlement into a group (ID 1), this group is then likely to be assigned to a user in the table below like this.

UserEntitlementGroup
--------------------
1 - 1

User 1 now has group 1 assigned to them, so with a basic JOIN we could return all the entitlements assigned to user one, but we need a little more information than that, and this is where i get totaly lost.

I have another table, that lets me overright the standard entitlements assigned in that group table, I use the bit column to define if the entitlment is granted additionaly, or if it should be revoke additionaly.

UserEntitlement
---------------
1 - 1 - False

Now in my final results I want to return all the entitlements, and then set a true/false value, all of those entitlements found belonging to a group assigned to a user should be set to True, as should any additional ones granted in the UserEntitlement table, and all those NOT found in the groups assigned to the user should be set to False, as should those revoked in the UserEntitlement table, so my final query results for this example would look like.

User_ID EntitlementName Entitled
1 1 False
1 2 True

Its difficult for me to explain, I hope i'm doing an ok job.

Thanks for any further help on this.

Rob

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-28 : 12:02:25

please follow instructions in this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

this is the best way to get help fast.

because from reading it i didn't quite understand your requirement.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SirRawlins
Starting Member

14 Posts

Posted - 2007-05-28 : 12:24:57

Thanks Spirit,

Whats the simplest way to generate those DDL and DML scripts?

Thanks,

Rob

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-28 : 12:33:46

well... you cna script objects in SSMS.
insert statemens:
http://vyaskn.tripod.com/code.htm#inserts

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SirRawlins
Starting Member

14 Posts

Posted - 2007-05-28 : 12:35:50

Members Table
GO
/****** Object: Table [dbo].[Member] Script Date: 05/28/2007 17:32:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Member](
[Member_ID] [int] IDENTITY(1,1) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[Active] [tinyint] NOT NULL,
[Locked] [tinyint] NOT NULL,
CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED
(
[Member_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Entitlement Table
GO
/****** Object: Table [dbo].[Entitlement] Script Date: 05/28/2007 17:33:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Entitlement](
[Entitlement_ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
CONSTRAINT [PK_Entitlement] PRIMARY KEY CLUSTERED
(
[Entitlement_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

UserEntitlement Table
GO
/****** Object: Table [dbo].[UserEntitlement] Script Date: 05/28/2007 17:33:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserEntitlement](
[User_ID] [int] NOT NULL,
[Entitlement_ID] [int] NOT NULL,
[AllowRevoke] [bit] NOT NULL
) ON [PRIMARY]

GO
USE [YourBlue]
GO
ALTER TABLE [dbo].[UserEntitlement] WITH CHECK ADD CONSTRAINT [FK_UserEntitlement_Entitlement] FOREIGN KEY([Entitlement_ID])
REFERENCES [dbo].[Entitlement] ([Entitlement_ID])
GO
ALTER TABLE [dbo].[UserEntitlement] WITH CHECK ADD CONSTRAINT [FK_UserEntitlement_Member] FOREIGN KEY([User_ID])
REFERENCES [dbo].[Member] ([Member_ID])

Entitlement Group
GO
/****** Object: Table [dbo].[EntitlementGroup] Script Date: 05/28/2007 17:34:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EntitlementGroup](
[EntitlementGroup_ID] [int] IDENTITY(1,1) NOT NULL,
[GroupName] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
CONSTRAINT [PK_EntitlementGroup] PRIMARY KEY CLUSTERED
(
[EntitlementGroup_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

EntitlementGroupEntitlement Table
GO
/****** Object: Table [dbo].[EntitlementGroupEntitlement] Script Date: 05/28/2007 17:34:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EntitlementGroupEntitlement](
[Entitlement_ID] [int] NOT NULL,
[EntitlementGroup_ID] [int] NOT NULL
) ON [PRIMARY]

GO
USE [YourBlue]
GO
ALTER TABLE [dbo].[EntitlementGroupEntitlement] WITH CHECK ADD CONSTRAINT [FK_EntitlementGroupEntitlement_Entitlement] FOREIGN KEY([Entitlement_ID])
REFERENCES [dbo].[Entitlement] ([Entitlement_ID])
GO
ALTER TABLE [dbo].[EntitlementGroupEntitlement] WITH CHECK ADD CONSTRAINT [FK_EntitlementGroupEntitlement_EntitlementGroup] FOREIGN KEY([EntitlementGroup_ID])
REFERENCES [dbo].[EntitlementGroup] ([EntitlementGroup_ID])

UserEntitlementGroup Table
GO
/****** Object: Table [dbo].[UserEntitlementGroup] Script Date: 05/28/2007 17:35:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserEntitlementGroup](
[User_ID] [int] NULL,
[EntitlementGroup_ID] [int] NULL
) ON [PRIMARY]

GO
USE [YourBlue]
GO
ALTER TABLE [dbo].[UserEntitlementGroup] WITH CHECK ADD CONSTRAINT [FK_UserEntitlementGroup_Member] FOREIGN KEY([User_ID])
REFERENCES [dbo].[Member] ([Member_ID])

That should get you going with some empty tables I hope, sorry if thats not quite right, fingers crossed it'll help.

Rob

SirRawlins
Starting Member

14 Posts

Posted - 2007-05-28 : 12:50:44

Create Member
INSERT INTO [YourDB].[dbo].[Member]
([CreatedDate]
,[ModifiedDate]
,[Active]
,[Locked])
VALUES
('2007-01-01',
'2007-01-01',
1,
0)

Create Entitlements
INSERT INTO [YourDB].[dbo].[Entitlement]
([Name])
VALUES
('Add User')
INSERT INTO [YourDB].[dbo].[Entitlement]
([Name])
VALUES
('Edit User')
INSERT INTO [YourDB].[dbo].[Entitlement]
([Name])
VALUES
('Delete User')

Set The Revoke for Entitlement 3
INSERT INTO [YourBlue].[dbo].[UserEntitlement]
([User_ID]
,[Entitlement_ID]
,[AllowRevoke])
VALUES
(1
,3
,False)

Create a Group Called Admin
INSERT INTO [YourBlue].[dbo].[EntitlementGroup]
([GroupName])
VALUES
('Admin')

Assign all entitlements to admin group
INSERT INTO [YourBlue].[dbo].[EntitlementGroupEntitlement]
([Entitlement_ID]
,[EntitlementGroup_ID])
VALUES
(1
,1)
INSERT INTO [YourBlue].[dbo].[EntitlementGroupEntitlement]
([Entitlement_ID]
,[EntitlementGroup_ID])
VALUES
(3
,1)
INSERT INTO [YourBlue].[dbo].[EntitlementGroupEntitlement]
([Entitlement_ID]
,[EntitlementGroup_ID])
VALUES
(2
,1)

Assign user the admin group
INSERT INTO [YourBlue].[dbo].[UserEntitlementGroup]
([User_ID]
,[EntitlementGroup_ID])
VALUES
(1
,1)

SirRawlins
Starting Member

14 Posts

Posted - 2007-05-28 : 12:54:57

Finally, results would look like this.

Member.Member_ID ------------- Entitlement.EntitlementName ------- Entitled
1 ----------- 1 ------------- True
1 ----------- 2 ------------- True
1 ----------- 3 ------------- False

This gets ALL the entitlements from the entitlement table, and then sets the entitled colummn to True if the entitlement is assigned to the user indevidualy in the 'UserEntitlement' table or belongs to the group assigned to the user.

And sets it to False if it is revoked indevidualy in the UserEntitlement table or is not in the groups assigned to the user.

Thanks again mate,

Rob

SirRawlins
Starting Member

14 Posts

Posted - 2007-05-28 : 12:55:49

Sorry,

1 ----------- Add User ------------- True
1 ----------- Edit User ------------- True
1 ----------- Delete User ------------- False

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources