Export of Security Profiles and their permissions using SQL queries
search cancel

Export of Security Profiles and their permissions using SQL queries

book

Article ID: 10166

calendar_today

Updated On:

Products

CA Client Automation - IT Client Manager CA Client Automation

Issue/Introduction

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?

 



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) :

 

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

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]