Symantec Management Platform (Notification Server)

 View Only

 SQL query to list / map Symantec role based permissions to the user.

Terry@WAB's profile image
Terry@WAB posted Dec 08, 2020 04:18 PM


I have an audit requirement to provide a detailed report of mapping role based permissions / privileges . 

when I contacted Support they provided

 Security and role matrix.  This helps but is not a report I can run for the requested output --> user to allowed privileges. 

Most of the reference "how to's" are dead links and many are not searchable since Broadcom.

I am currently looking in the CMDB for the table that list the admin users to pass on to my DBA, to possible write the query, If you know it please post here. 

At this time I have to screen capture every user and privilege once per quarter, it's a time killer. 

Any help is greatly appreciated.


Igor Perevozchikov's profile image
Broadcom Employee Igor Perevozchikov
Hello Terry@WAB!

Does "Security Privilege Report" default report match to your question?
You can choose there required Security Role and see what privileges it has. Save report output in csv, html, xml file.

Best regards,
Terry@WAB's profile image


thanks for that report but no it does not give the details required for audit. they want to see user "Jane Doe" mapped to each of those Privileges, not the generic Symantec Admin account. 

My DBA is looking to find a correlation between the SMP generated SIDs and permissions.  And the link between the users and the security groups. 

any suggestion appreciated.
Thank you,

Doug Butler's profile image
Broadcom Employee Doug Butler
I was actually asked for this or something similar in the past.  If it isn't what you are looking for it may be something that can help your DBA.

--Declare temp tables
declare @accounts as table (Guid uniqueidentifier)
declare @roles as table (Guid uniqueidentifier)
declare @privileges as table (Guid uniqueidentifier, Name nvarchar (255), Description nvarchar (255), DisplayGroupGuid uniqueidentifier)
declare @privilegesAndAcount as table (Guid uniqueidentifier, Name nvarchar (255), Description nvarchar (255), DisplayGroupGuid uniqueidentifier, AccountGuid uniqueidentifier)
insert into @accounts
select Guid from vRM_Account
while exists (select top 1 1 from @accounts)
declare @accountguid as uniqueidentifier = (select top 1 Guid from @accounts)
--Get all roles the user is a member of
insert into @roles
execute spGetTrusteeMembership @accountguid
--Loop through roles and populate data
while exists (select top 1 Guid from @roles)
declare @roleGuid as nvarchar (50) = (select top 1 '{' + cast (Guid as nvarchar (50)) + '}' from @roles)
insert into @privileges
execute spGetEffectivePrivileges @roleGuid
insert into @privilegesAndAcount
select *, @accountguid from @privileges
delete from @privileges
delete from @roles where Guid = @roleGuid
delete from @accounts where Guid = @accountguid
--get results with the displaygroup added
select distinct a.Name Account, dg.NameRef DisplayGroupName , p.Name Privilege, p.Description
from @privilegesAndAcount p
join SecurityPrivilegeDisplayGroup dg on dg.Guid = p.DisplayGroupGuid
join vRM_Account_Item a on a.Guid = p.AccountGuid
order by 1, 4, 2
Terry@WAB's profile image


Thanks for the assist.  DBA is looking over to get it running. 

she has this also but yields Nulls for users.

select as 'Security Role',
sg.nameref as 'Display Group', as 'Privilege' ,
pu.[Primary User] As 'User'
from securityrole sr
join securityprivilegetrustee spt
on spt.trusteeguid = sr.guid
Join SecurityPrivilege sp
on sp.guid = spt.privilegeguid
join SecurityPrivilegeDisplayGroup sg
on sg.guid = sp.displaygroupguid
Left Join[dbo].[vAC_PrimaryUser] pu
on pu.ResourceGuid = sr.Guid
Any ideas on the joins?
Thanks for any suggestions.

Doug Butler's profile image
Broadcom Employee Doug Butler
There are two different resource types where a person is represented User and Account.  User is what your dba was trying to use and refers to the users of a resource.  Accounts can be the exact same person but are a separate resource in the console and are used to control an entity's security.  

For the most part privileges and permissions are assigned to a role and accounts are assigned to a role.  The query I sent looks to find all of the privileges assigned to an account as well as any role it is in and any role that the role may be part of.