It's sometimes useful to extract the list of Security Profiles and their class permissions, in order to have an overview of all permissions applied on a ITCM Domain or Enterprise.
How to do this extract?
Client Automation - All versions
With a SQL Query it is possible to get the list of the security profiles and the permissions on object class.
IF OBJECT_ID('mdb.dbo.ITCM_GetRights') IS NOT NULL DROP FUNCTION dbo.ITCM_GetRights
GO
CREATE FUNCTION [dbo].[ITCM_GetRights](@ace int)
RETURNS VARCHAR(25) AS
BEGIN
DECLARE @RIGHTS VARCHAR(25)
SET @RIGHTS=''
IF @ace=0 SET @RIGHTS='No Access'
IF @ace=255 SET @RIGHTS='Full Control (CVRWXDPO)'
IF @ace=64 SET @RIGHTS='View (V)'
IF @ace=65 SET @RIGHTS='Read (VR)'
IF @ace=81 SET @RIGHTS='Manage (VRX)'
IF @ace=87 SET @RIGHTS='Change (VRWXD)'
IF @RIGHTS<>'' RETURN @RIGHTS
IF (@ace & 128)=128 SET @RIGHTS=@RIGHTS+'C'
IF (@ace & 64)=64 SET @RIGHTS=@RIGHTS+'V'
IF (@ace & 1)=1 SET @RIGHTS=@RIGHTS+'R'
IF (@ace & 2)=2 SET @RIGHTS=@RIGHTS+'W'
IF (@ace & 16)=16 SET @RIGHTS=@RIGHTS+'X'
IF (@ace & 4)=4 SET @RIGHTS=@RIGHTS+'D'
IF (@ace & 8)=8 SET @RIGHTS=@RIGHTS+'P'
IF (@ace & 32)=32 SET @RIGHTS=@RIGHTS+'O'
RETURN @RIGHTS
END
GO
IF OBJECT_ID('mdb.dbo.ITCM_GetSecurityClassName') IS NOT NULL DROP FUNCTION dbo.ITCM_GetSecurityClassName
GO
CREATE FUNCTION [dbo].[ITCM_GetSecurityClassName](@class_id int)
RETURNS VARCHAR(40) AS
BEGIN
DECLARE @DomainType int
DECLARE @ClassName VARCHAR(40)
SELECT @DomainType=domain_type FROM ca_n_tier WHERE domain_uuid=(SELECT set_val_uuid FROM ca_settings WHERE set_id=1)
IF @class_id=1 SET @ClassName='Class Permissions'
IF @class_id=2 SET @ClassName='Security Profile'
IF @class_id=3 AND @DomainType=0 SET @ClassName='Security Areas'
IF @class_id=10 SET @ClassName='Database Credentials'
IF @class_id=1000 SET @ClassName='Computer'
IF @class_id=1001 SET @ClassName='User Profile'
IF @class_id=1002 SET @ClassName='User Account'
IF @class_id=1003 SET @ClassName='Manager'
IF @class_id=1004 SET @ClassName='Scalability Server'
IF @class_id=1006 SET @ClassName='Domain'
IF @class_id=1007 SET @ClassName='Common Query'
IF @class_id=1008 SET @ClassName='Software Definition'
IF @class_id=1009 SET @ClassName='Software Category'
IF @class_id=1011 SET @ClassName='Control Panel Access'
IF @class_id=1012 SET @ClassName='External Asset'
IF @class_id=1013 SET @ClassName='Remote Control Access'
IF @class_id=1014 SET @ClassName='Health Monitoring Alert'
IF @class_id=1015 SET @ClassName='Job Container Priority Access'
IF @class_id=1016 SET @ClassName='Patch Management'
IF @class_id=2000 SET @ClassName='Software Package'
IF @class_id=2001 SET @ClassName='Procedure'
IF @class_id=2002 SET @ClassName='Software Group'
IF @class_id=2003 SET @ClassName='Procedure Group'
IF @class_id=2004 SET @ClassName='Software Job Container'
IF @class_id=2005 SET @ClassName='Software Job'
IF @class_id=2006 AND @DomainType=1 SET @ClassName='Software Distribution Container'
IF @class_id=2008 AND @DomainType=1 SET @ClassName='Software Distributions'
IF @class_id=2009 SET @ClassName='Policy – Software Based'
IF @class_id=3005 SET @ClassName='Engine'
IF @class_id=3100 SET @ClassName='Asset Job'
IF @class_id=3101 SET @ClassName='Engine Task'
IF @class_id=3201 SET @ClassName='Inventory Task'
IF @class_id=3202 SET @ClassName='Template Task'
IF @class_id=3203 SET @ClassName='Software Discovery Task'
IF @class_id=3204 SET @ClassName='Software Usage Task'
IF @class_id=3205 SET @ClassName='Virtual Host Inventory Task'
IF @class_id=3300 SET @ClassName='Policy – Query Based'
IF @class_id=3301 SET @ClassName='Policy – Event Based'
IF @class_id=4000 SET @ClassName='OS Installation Image'
IF @class_id=4500 SET @ClassName='Policy - Configuration Computer'
IF @class_id=5000 SET @ClassName='Configured Directory'
IF @class_id=5100 AND @DomainType=0 SET @ClassName='Deployment Job'
IF @class_id=7000 SET @ClassName='Asset Group'
IF @class_id=7004 SET @ClassName='Scalability Server Group'
IF @class_id=7006 SET @ClassName='Domain Group'
IF @class_id=8000 SET @ClassName='Report Template'
IF @class_id=8001 SET @ClassName='Report Scheduling'
RETURN @ClassName
END
GO