How do an export of security profiles and their permissions ?

Products

• CA Client Automation - IT Client Manager
• CA Client Automation

Issue/Introduction

It is 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 ?

 

Environment

Client Automation - All versions

Resolution

With a SQL Query it is possible to get the list of the security profiles and the permissions on object class.

 

1- Open Microsoft SQL Server Management Studio and connect to the mdb database

2- Execute these queries in order to create 2 functions (this step should be done once) :

use mdb

 

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)

      

       IF @ace=0 RETURN 'No Access'

       IF @ace=255 RETURN 'Full Control (CVRWXDPO)'

       IF @ace=64 RETURN 'View (V)'

       IF @ace=65 RETURN 'Read (VR)'

       IF @ace=81 RETURN 'Manage (VRX)'

       IF @ace=87 RETURN 'Change (VRWXD)'

      

       SET @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 @SecurityClassName VARCHAR(25)

       DECLARE @DomainType int

 

       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    RETURN 'Class Permissions'

       IF @class_id=2    RETURN 'Security Profile'

       IF @class_id=3 AND @DomainType=0    RETURN 'Security Areas'

       IF @class_id=10   RETURN 'Database Credentials'

       IF @class_id=1000 RETURN 'Computer'

       IF @class_id=1001 RETURN 'User Profile'

       IF @class_id=1002 RETURN 'User Account'

       IF @class_id=1003 RETURN 'Manager'

       IF @class_id=1004 RETURN 'Scalability Server'

       IF @class_id=1006 RETURN 'Domain'

       IF @class_id=1007 RETURN 'Common Query'

       IF @class_id=1008 RETURN 'Software Definition'

       IF @class_id=1009 RETURN 'Software Category'

       IF @class_id=1011 RETURN 'Control Panel Access'

       IF @class_id=1012 RETURN 'External Asset'

       IF @class_id=1013 RETURN 'Remote Control Access'

       IF @class_id=1014 RETURN 'Health Monitoring Alert'

       IF @class_id=1015 RETURN 'Job Container Priority Access'

       IF @class_id=1016 RETURN 'Patch Management'

       IF @class_id=2000 RETURN 'Software Package'

       IF @class_id=2001 RETURN 'Procedure'

       IF @class_id=2002 RETURN 'Software Group'

       IF @class_id=2003 RETURN 'Procedure Group'

       IF @class_id=2004 RETURN 'Software Job Container'

       IF @class_id=2005 RETURN 'Software Job'

       IF @class_id=2006 AND @DomainType=1 RETURN 'Software Distribution Container'

       IF @class_id=2008 AND @DomainType=1 RETURN 'Software Distributions'

       IF @class_id=2009 RETURN 'Policy – Software Based'

       IF @class_id=3005 RETURN 'Engine'

       IF @class_id=3100 RETURN 'Asset Job'

       IF @class_id=3101 RETURN 'Engine Task'

       IF @class_id=3201 RETURN 'Inventory Task'

       IF @class_id=3202 RETURN 'Template Task'

       IF @class_id=3203 RETURN 'Software Discovery Task'

       IF @class_id=3204 RETURN 'Software Usage Task'

       IF @class_id=3205 RETURN 'Virtual Host Inventory Task'

       IF @class_id=3300 RETURN 'Policy – Query Based'

       IF @class_id=3301 RETURN 'Policy – Event Based'

       IF @class_id=4000 RETURN 'OS Installation Image'

       IF @class_id=4500 RETURN 'Policy - Configuration Computer'

       IF @class_id=5000 RETURN 'Configured Directory'

       IF @class_id=5100 AND @DomainType=0 RETURN 'Deployment Job'

       IF @class_id=7000 RETURN 'Asset Group'

       IF @class_id=7004 RETURN 'Scalability Server Group'

       IF @class_id=7006 RETURN 'Domain Group'

       IF @class_id=8000 RETURN 'Report Template'

       IF @class_id=8001 RETURN 'Report Scheduling'

      

       RETURN ''

END

 

GO

 

 

 

3- To get the list of security profiles and permissions, following SQL query could be executed :

 

SELECT p.name,  dbo.ITCM_GetSecurityClassName(c.class_id) as [Class_Name], dbo.ITCM_GetRights(a.ace) as [Rights]

FROM ca_class_ace a INNER JOIN

    ca_security_class_def c ON a.class_def_uuid=c.class_def_uuid INNER JOIN

    ca_security_profile p ON a.security_profile_uuid=p.security_profile_uuid

WHERE type in(0,1,3,5) and dbo.ITCM_GetSecurityClassName(c.class_id) <>''

ORDER BY p.name, [Class_Name]