Portal Permissions – Decoding the Permission Mask
In an earlier post, I created a quick View to show which Portal Area and TeamSite users could access. This time I’m going to show what permissions the users have. Ideally, I want a scalar function that will return a semi-colon delimited list of permissions like in the manage security page in SPS but in abbreviated form.
Permissions for WSS are fairly straight forward but permissions for portal areas are quite a bit more complex. They are stored in the PortalMask field of the PortalPerm table as a bit mask.
Here are the values of each permission:
|
Value |
Short Desc |
|
1 |
View Area |
|
2 |
Add Items |
|
4 |
Edit Items |
|
8 |
Delete items |
|
16 |
Cancel Check-Out |
|
32 |
Manage Personal Views |
|
64 |
View Pages |
|
128 |
Add and Customize Pages |
|
256 |
Apply Style Sheets |
|
512 |
Browse Directories |
|
1024 |
Add/Remove Personal Web Parts |
|
2048 |
Update Personal Web Parts |
|
4096 |
Create Area |
|
8192 |
Manage Area |
|
16384 |
Manage Area Permissions |
This would be easy in C# or VB but I want stick with a T-SQL only solution so I can use it in a data view web part later.
First I’ll create a table of the permission values:
CREATE TABLE [PortalRights] (
[iOrder] [int] NOT NULL ,
[iVal] [int] NULL ,
[ShortDesc] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_PortalRights] PRIMARY KEY CLUSTERED
(
[iOrder]
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET NOCOUNT ON
PRINT ‘Inserting values into [PortalRights]’
INSERT INTO [PortalRights] ([iOrder],[iVal],[ShortDesc])VALUES(1,1,’View Area’)
INSERT INTO [PortalRights] ([iOrder],[iVal],[ShortDesc])VALUES(2,64,’View Pages’)
INSERT INTO [PortalRights] ([iOrder],[iVal],[ShortDesc])VALUES(3,2,’Add Items’)
INSERT INTO [PortalRights] ([iOrder],[iVal],[ShortDesc])VALUES(4,4,’Edit Items’)
INSERT INTO [PortalRights] ([iOrder],[iVal],[ShortDesc])VALUES(5,8,’Delete items’)
INSERT INTO [PortalRights] ([iOrder],[iVal],[ShortDesc])VALUES(6,32,’Manage Personal Views’)
INSERT INTO [PortalRights] ([iOrder],[iVal],[ShortDesc])VALUES(7,1024,’Add/Remove Personal Web Parts’)
INSERT INTO [PortalRights] ([iOrder],[iVal],[ShortDesc])VALUES(8,2048,’Update Personal Web Parts’)
INSERT INTO [PortalRights] ([iOrder],[iVal],[ShortDesc])VALUES(9,16,’Cancel Check-Out’)
INSERT INTO [PortalRights] ([iOrder],[iVal],[ShortDesc])VALUES(10,128,’Add and Customize Pages’)
INSERT INTO [PortalRights] ([iOrder],[iVal],[ShortDesc])VALUES(11,4096,’Create Area’)
INSERT INTO [PortalRights] ([iOrder],[iVal],[ShortDesc])VALUES(12,8192,’Manage Area’)
INSERT INTO [PortalRights] ([iOrder],[iVal],[ShortDesc])VALUES(13,16384,’Manage Area Permissions’)
INSERT INTO [PortalRights] ([iOrder],[iVal],[ShortDesc])VALUES(14,256,’Apply Style Sheets’)
INSERT INTO [PortalRights] ([iOrder],[iVal],[ShortDesc])VALUES(15,512,’Browse Directories’)
SET NOCOUNT OFF
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Next I’ll create a scalar function to decode the bit mask:
CREATE FUNCTION sFn_DecodePermMask
(@PortalMask int)
RETURNS varchar(255)
AS
BEGIN
— =============================================
— Declare and using a READ_ONLY cursor
— =============================================
DECLARE PortalPerm CURSOR
READ_ONLY
FOR
SELECT [iVal]
, [ShortDesc]
FROM [SharePointHelper].[dbo].[PortalRights]
ORDER BY [iOrder]
DECLARE @FullDesc varchar(255)
DECLARE @TempMask int
Set @TempMask = @PortalMask
Set @FullDesc = ”
DECLARE @Desc varchar(255)
DECLARE @Val int
OPEN PortalPerm
FETCH NEXT FROM PortalPerm INTO @Val, @Desc
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
If @TempMask & @Val > 0
BEGIN
SET @FullDesc = @FullDesc + @Desc + ‘; ‘
END
END
FETCH NEXT FROM PortalPerm INTO @Val, @Desc
END
CLOSE PortalPerm
DEALLOCATE PortalPerm
RETURN @FullDesc
END
GO
Next, I’ll add the function to my earlier View
CREATE view SiteAccess as
SELECT
‘HTTP://dev/’ + PBCVirtu1_SITE.dbo.Webs.FullUrl + ‘/’ As URL
, PBCVirtu1_SITE.dbo.CatPath.Path AS [SiteName]
, [SharePointHelper].[dbo].[sFn_DecodePermMask](PBCVirtu1_SITE.dbo.PortalPerm.PortalMask) AS ROLE
, PBCVirtu1_SITE.dbo.PortalPerm.PortalMask AS ROLEID
, PBCVirtu1_SITE.dbo.UserInfo.tp_Title
, PBCVirtu1_SITE.dbo.UserInfo.tp_Email
, PBCVirtu1_SITE.dbo.UserInfo.tp_Login
FROM PBCVirtu1_SITE.dbo.Webs INNER JOIN
PBCVirtu1_SITE.dbo.UserInfo ON PBCVirtu1_SITE.dbo.Webs.SiteId = PBCVirtu1_SITE.dbo.UserInfo.tp_SiteID INNER JOIN
PBCVirtu1_SITE.dbo.PortalPerm ON PBCVirtu1_SITE.dbo.UserInfo.tp_ID = PBCVirtu1_SITE.dbo.PortalPerm.UserGroupID INNER JOIN
PBCVirtu1_SITE.dbo.CatPath ON PBCVirtu1_SITE.dbo.PortalPerm.PermID = PBCVirtu1_SITE.dbo.CatPath.PermID INNER JOIN
PBCVirtu1_SITE.dbo.CatDef ON REPLACE(PBCVirtu1_SITE.dbo.CatDef.WebUrl, ‘*’, ”) = PBCVirtu1_SITE.dbo.Webs.FullUrl AND PBCVirtu1_SITE.dbo.CatPath.CatID = PBCVirtu1_SITE.dbo.CatDef.CatID
UNION
SELECT
‘HTTP://dev/’ + PBCVirtu1_SITE.dbo.Webs.FullUrl + ‘/’ AS URL
,PBCVirtu1_SITE.dbo.Webs.Title AS [SiteName]
,PBCVirtu1_SITE.dbo.WebGroups.Title AS ROLE
,PBCVirtu1_SITE.dbo.WebGroups.Type AS ROLEID
, PBCVirtu1_SITE.dbo.UserInfo.tp_Title
, PBCVirtu1_SITE.dbo.UserInfo.tp_Email
, PBCVirtu1_SITE.dbo.UserInfo.tp_Login
FROM PBCVirtu1_SITE.dbo.WebGroupMembership
INNER JOIN PBCVirtu1_SITE.dbo.Webs
ON PBCVirtu1_SITE.dbo.WebGroupMembership.WebID = PBCVirtu1_SITE.dbo.Webs.Id
INNER JOIN PBCVirtu1_SITE.dbo.WebGroups
ON PBCVirtu1_SITE.dbo.WebGroupMembership.WebID = PBCVirtu1_SITE.dbo.WebGroups.WebID
AND PBCVirtu1_SITE.dbo.WebGroupMembership.GroupID = PBCVirtu1_SITE.dbo.WebGroups.ID
INNER JOIN PBCVirtu1_SITE.dbo.UserInfo
ON PBCVirtu1_SITE.dbo.WebGroups.SiteID = PBCVirtu1_SITE.dbo.UserInfo.tp_SiteID
AND PBCVirtu1_SITE.dbo.WebGroupMembership.MemberID = PBCVirtu1_SITE.dbo.UserInfo.tp_ID
INNER JOIN PBCVirtu1_SITE.dbo.Sites
ON PBCVirtu1_SITE.dbo.Webs.SiteId = PBCVirtu1_SITE.dbo.Sites.Id
WHERE (PBCVirtu1_SITE.dbo.Webs.FullUrl LIKE ‘sites/%’)
I’ve abbreviated the permissions in my table to make them easier to fit on the screen.
