How to report on user permissions on the Software Portal items

Products

• Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Question

The permissions for the software portal packages are kept in the the "Hidden Folder for SWD programs" folder located under the Software Delivery Tasks folder on the Task tab of the Altiris console. Selecting "Replace permissions on all child objects" on the Software Delivery Tasks tree will cause these custom permissions to be lost.

Is there a report that can be run to list all users and their permissions on Software Portal items? Having this list will enable us in the re-creation of the permissions if they happen to get removed.

Answer

This process will create a report that will show the permissions each user has on the items in the "Hidden Folder for SWD programs"which is the folder that contains programs for the Software Portal packages. User names are NOT inherently contained in the Altiris database. This process will also create a temp_trustee table that associates the SIDs to actual user/group names.

Step 1: Perform Once. Run the following SQL Query to create a sp_SecurityTrusteeNames stored procedure. This stored procedure original created a temporary table #temp_trustee, but it has been modified to make this a permanent table. The query also has been modified so that the table doesn’t get truncated.

create proc sp_SecurityTrusteeNames
as
declare @sid varchar(4000)
declare @guid uniqueidentifier
declare @work varbinary(4000), @bin varbinary(4000)
declare @textwork varchar(4000), @tempwork varchar(4000)
declare @pre int, @cur int

create table temp_trustee (Guid uniqueidentifier, Trustee varchar(4000), Name varchar(4000))

declare trustee_cursor cursor for
select Guid, Trustee from SecurityTrustee  where left(Trustee,2) = 'S-'

open trustee_cursor

fetch next from trustee_cursor
into @guid, @sid

while @@FETCH_STATUS = 0
begin

select @work = cast(1 as varbinary(1)) --revision number, 'S-1' = 01
select @work = @work + cast(len(@sid) - len(replace(@sid, '-','')) -2 as varbinary(1)) --Number of dashes - 2
select @work = @work + cast(cast(substring(@sid, 5, charindex('-', @sid,6) - 5) as bigint) as varbinary(6))

set @pre = charindex('-',@sid,5) + 1
set @pre = case
when @pre = len(@sid) then @pre -1
else @pre
end

while @pre < len(@sid)
begin


set @cur = case
when charindex('-', @sid, @pre+1) - @pre > 0 then charindex('-', @sid, @pre+1) - @pre
else len(@sid)
end

select @textwork = master.dbo.fn_varbintohexstr(cast(substring(@sid, @pre, @cur) as bigint))

exec sp_ByteFlipBinaryFromString @textwork, @bin output

select @work = @work + cast(@bin as varbinary(4))

set @pre = case
when @cur > @pre then @cur
else @pre + charindex('-', @sid, @pre+1) - @pre + 1
end
end

insert into temp_trustee values (
@guid,
@sid,
suser_sname(@work)
)
fetch next from trustee_cursor
into @guid, @sid
end

select * from temp_trustee
--drop table #temp_trustee

close trustee_cursor
deallocate trustee_cursor
go

Step2: Perform Once. Run the following SQL to create a sp_ByteFlipBinaryFromString stored procedure.

create proc sp_ByteFlipBinaryFromString(@strIn nvarchar(3990), @binOut Varbinary(4000) out)as
declare @sql nvarchar(4000)
declare @strWork nvarchar(4000)
declare @i int

set @i = 1
set @strWork = '0X'
set @strIn = replace(@strIn, lower('0x'),'')

while len(@strIn) - @i > 0
begin
set @strWork = @strWork + substring(@strIn, len(@strIn) - @i,2)
set @i = @i + 2
end

set @sql = N'set @b = ' + @strWork
exec sp_executesql @sql, N'@b varbinary(4000) out', @binOut out
go

Step 3: Run the following query to delete the temp_trustee database. This must be run prior to doing step 4 if this table already exists.

drop table temp_trustee

Step 4: Run each time that you want to refresh the temp_trustee table. Run the following stored procedure in query analyzer. This will create a temp_trustee table containing the usernames associated with the SIDs in the SecurityTrustee table. If the table already exists, step 3 must be ran first.

exec sp_securitytrusteenames

Step 5: Run the following query to get the list of users and their permissions on the Software Portal packages.

select distinct sp.name [ProgramName], s2.name [PackageName], tt.name as 'TrusteeName', s5.guid as 'TrusteeGuid',
s6.name as 'Permission' from swdProgram sp
join itemfolder i1 on sp.programid = i1.itemguid
join itemreference i2 on sp.programid = i2.childitemguid
join swdpackage s2 on i2.parentitemguid = s2.packageid
join securityace s3 on sp.programid = s3.entityguid --trusteepermissionid
join securitytrusteepermission s4 on s3.trusteepermissionid = s4.id -- trusteepermissionid, trusteeguid, permissionguid
join securitytrustee s5 on s4.trusteeguid = s5.guid
join securitypermission s6 on s4.permissionguid = s6.guid
join temp_trustee tt on s5.guid = tt.guid
where  i1.parentfolderguid = '1FC86E41-B94F-4769-9457-1CF90A47BC6A'
order by programname asc