Hierarchical SQL Role Provider
Thursday, October 16, 2008 18:22Posted in category HowTo
No Comments
you can modify the .net sql role provider to be hierarchical in just a few simple steps …
first you need to modify the aspnet_Roles table:
1: ALTER TABLE [dbo].[aspnet_Roles] ADD [ParentRoleId] [uniqueidentifier] NULL
then add two new table value functions:
1: CREATE FUNCTION [tos].[aspnet_Roles_Ancestor_TVF] (
2: @RoleId uniqueidentifier
3: )
4: RETURNS
5: @aspnet_Roles TABLE (
6: ApplicationId uniqueidentifier
7: , RoleId uniqueidentifier
8: , RoleName nvarchar(256)
9: , LoweredRoleName nvarchar(256)
10: , Description nvarchar(256)
11: , ParentRoleId uniqueidentifier
12: )
13: AS
14: BEGIN
15: ; WITH aspnet_Roles_CTE (
16: ApplicationId
17: , RoleId
18: , RoleName
19: , LoweredRoleName
20: , Description
21: , ParentRoleId
22: , HierarchyLevel
23: ) AS (
24: SELECT
25: ApplicationId
26: , RoleId
27: , RoleName
28: , LoweredRoleName
29: , Description
30: , ParentRoleId
31: , 1 AS HierarchyLevel
32: FROM aspnet_Roles
33: WHERE RoleId = @RoleId
34:
35: UNION ALL
36:
37: SELECT
38: aspnet_Roles.ApplicationId
39: , aspnet_Roles.RoleId
40: , aspnet_Roles.RoleName
41: , aspnet_Roles.LoweredRoleName
42: , aspnet_Roles.Description
43: , aspnet_Roles.ParentRoleId
44: , aspnet_Roles_CTE.HierarchyLevel + 1 AS HierarchyLevel
45: FROM aspnet_Roles
46: INNER JOIN aspnet_Roles_CTE
47: ON aspnet_Roles.RoleId = aspnet_Roles_CTE.ParentRoleId
48: )
49:
50: INSERT INTO @aspnet_Roles (
51: ApplicationId
52: , RoleId
53: , RoleName
54: , LoweredRoleName
55: , Description
56: , ParentRoleId
57: )
58: SELECT
59: ApplicationId
60: , RoleId
61: , RoleName
62: , LoweredRoleName
63: , Description
64: , ParentRoleId
65: FROM aspnet_Roles_CTE
66: ORDER BY HierarchyLevel
67:
68: RETURN
69: END
and:
1: ALTER FUNCTION [tos].[aspnet_Roles_Descendant_TVF] (
2: @RoleId uniqueidentifier
3: )
4: RETURNS
5: @aspnet_Roles TABLE (
6: ApplicationId uniqueidentifier
7: , RoleId uniqueidentifier
8: , RoleName nvarchar(256)
9: , LoweredRoleName nvarchar(256)
10: , Description nvarchar(256)
11: , ParentRoleId uniqueidentifier
12: )
13: AS
14: BEGIN
15: ; WITH aspnet_Roles_CTE (
16: ApplicationId
17: , RoleId
18: , RoleName
19: , LoweredRoleName
20: , Description
21: , ParentRoleId
22: , HierarchyLevel
23: ) AS (
24: SELECT
25: ApplicationId
26: , RoleId
27: , RoleName
28: , LoweredRoleName
29: , Description
30: , ParentRoleId
31: , 1 AS HierarchyLevel
32: FROM aspnet_Roles
33: WHERE RoleId = @RoleId
34:
35: UNION ALL
36:
37: SELECT
38: aspnet_Roles.ApplicationId
39: , aspnet_Roles.RoleId
40: , aspnet_Roles.RoleName
41: , aspnet_Roles.LoweredRoleName
42: , aspnet_Roles.Description
43: , aspnet_Roles.ParentRoleId
44: , aspnet_Roles_CTE.HierarchyLevel + 1 AS HierarchyLevel
45: FROM aspnet_Roles
46: INNER JOIN aspnet_Roles_CTE
47: ON aspnet_Roles.ParentRoleId = aspnet_Roles_CTE.RoleId
48: )
49:
50: INSERT INTO @aspnet_Roles (
51: ApplicationId
52: , RoleId
53: , RoleName
54: , LoweredRoleName
55: , Description
56: , ParentRoleId
57: )
58: SELECT
59: ApplicationId
60: , RoleId
61: , RoleName
62: , LoweredRoleName
63: , Description
64: , ParentRoleId
65: FROM aspnet_Roles_CTE
66: ORDER BY HierarchyLevel
67:
68: RETURN
69: END
then you’ll need to modify two stored procedures:
1: ALTER PROCEDURE [tos].[aspnet_UsersInRoles_IsUserInRole]
2: @ApplicationName nvarchar(256),
3: @UserName nvarchar(256),
4: @RoleName nvarchar(256),
5: @Exclusive bit = 0
6: AS
7: BEGIN
8: DECLARE @ApplicationId uniqueidentifier
9: SELECT @ApplicationId = NULL
10: SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
11: IF (@ApplicationId IS NULL)
12: RETURN(2)
13: DECLARE @UserId uniqueidentifier
14: SELECT @UserId = NULL
15: DECLARE @RoleId uniqueidentifier
16: SELECT @RoleId = NULL
17:
18: SELECT @UserId = UserId
19: FROM tos.aspnet_Users
20: WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
21:
22: IF (@UserId IS NULL)
23: RETURN(2)
24:
25: SELECT @RoleId = RoleId
26: FROM tos.aspnet_Roles
27: WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
28:
29: IF (@RoleId IS NULL)
30: RETURN(3)
31:
32: /*
33: IF (EXISTS( SELECT * FROM tos.aspnet_UsersInRoles WHERE UserId = @UserId AND RoleId = @RoleId))
34: RETURN(1)
35: ELSE
36: RETURN(0)
37: */
38:
39: IF @Exclusive = 1
40: BEGIN
41: IF (EXISTS( SELECT * FROM tos.aspnet_UsersInRoles WHERE UserId = @UserId AND RoleId = @RoleId))
42: RETURN(1)
43: ELSE
44: RETURN(0)
45: END
46: ELSE
47: BEGIN
48: IF( EXISTS( SELECT * FROM tos.aspnet_Roles_Ancestor_TVF( @RoleId ) r INNER JOIN tos.aspnet_UsersInRoles ur ON r.RoleId = ur.RoleId WHERE ur.UserId = @UserId ) )
49: RETURN(1)
50: ELSE
51: RETURN(0)
52: END
53:
54: END
and:
1: ALTER PROCEDURE [tos].[aspnet_UsersInRoles_GetRolesForUser]
2: @ApplicationName nvarchar(256)
3: , @UserName nvarchar(256)
4: , @Exclusive bit = 0
5: AS
6: BEGIN
7: DECLARE @ApplicationId uniqueidentifier
8: SELECT @ApplicationId = NULL
9: SELECT @ApplicationId = ApplicationId
10: FROM aspnet_Applications
11: WHERE LOWER(@ApplicationName) = LoweredApplicationName
12: IF (@ApplicationId IS NULL)
13: RETURN(1)
14: DECLARE @UserId uniqueidentifier
15: SELECT @UserId = NULL
16: SELECT @UserId = UserId
17: FROM tos.aspnet_Users
18: WHERE LoweredUserName = LOWER(@UserName)
19: AND ApplicationId = @ApplicationId
20: IF (@UserId IS NULL)
21: RETURN(1)
22:
23: /*
24: SELECT r.RoleName
25: FROM tos.aspnet_Roles r, tos.aspnet_UsersInRoles ur
26: WHERE r.RoleId = ur.RoleId
27: AND r.ApplicationId = @ApplicationId
28: AND ur.UserId = @UserId
29: ORDER BY r.RoleName
30: */
31:
32: IF @Exclusive = 1
33: BEGIN
34: SELECT r.RoleName
35: FROM tos.aspnet_Roles r, tos.aspnet_UsersInRoles ur
36: WHERE r.RoleId = ur.RoleId
37: AND r.ApplicationId = @ApplicationId
38: AND ur.UserId = @UserId
39: ORDER BY r.RoleName
40: END
41: ELSE
42: BEGIN
43: DECLARE @RoleId uniqueidentifier
44: DECLARE @RoleName nvarchar(256)
45: DECLARE @aspnet_Roles TABLE (
46: RoleName nvarchar(256)
47: )
48:
49: SELECT @RoleName = MIN( r.RoleName )
50: FROM tos.aspnet_Roles r, tos.aspnet_UsersInRoles ur
51: WHERE r.RoleId = ur.RoleId
52: AND r.ApplicationId = @ApplicationId
53: AND ur.UserId = @UserId
54:
55: WHILE @RoleName IS NOT NULL
56: BEGIN
57: SET @RoleId = ( SELECT RoleId FROM tos.aspnet_Roles WHERE RoleName = @RoleName )
58:
59: INSERT INTO @aspnet_Roles (
60: RoleName
61: )
62: SELECT
63: RoleName
64: FROM tos.aspnet_Roles_Descendant_TVF( @RoleId ) rd
65:
66: SELECT @RoleName = MIN( r.RoleName )
67: FROM tos.aspnet_Roles r, tos.aspnet_UsersInRoles ur
68: WHERE r.RoleId = ur.RoleId
69: AND r.ApplicationId = @ApplicationId
70: AND ur.UserId = @UserId
71: AND RoleName > @RoleName
72: END
73:
74:
75: SELECT DISTINCT RoleName
76: FROM @aspnet_Roles r
77: ORDER BY r.RoleName
78: END
79:
80:
81: RETURN (0)
82: END
add the parent unique identifiers as necessary and you’re done
You can follow any responses to this entry through the RSS 2.0 feed.
You can skip to the end and leave a response. Pinging is currently not allowed.