Skip to main content
InSource Solutions

TN - 1187 Updating Attribute Security Classifications Using SQL

Description

  • Author: Frank Ross
  • Published: 09/30/2021
  • Applies to: System Platform Global Repository IDE (Tested on SP2020)

Details

Updating Security Classification With a SQL Query

This article from InSource shows how to change the attribute security classification using a Sql Server query.  

Synopsis:  Need the ability to mass update many attributes within a user-defined object from one security classification to another.    The IDE does not permit selecting all attributes and modifying the security classification in a single mass change.     Using the IDE, each attribute would have to be selected individually and modified.    If the user -defined object has hundreds of attributes, this could be a very tedious process to convert security classifications.    

An example would be where the Developer has used the Bulk Import Utility (BIU) to extract attributes from a .CSV file to build a user-defined object.  In this scenario, each of the attributes have a security classification of 5 (Configure).    We will need to reclassify each attribute to use security classification of 2 (Operate).  

Security Classifications:

0=Free Access
1=Operate
2=Secured Write
3=Verified Write
4=Tune
5=Configure
6=View Only

 

SQL Server Queries

The following SQL constructions are broken into three (2) query scripts.      

1.    This query will display the existing security classifications for each of the attributes within the object.  This query should be executed before and after the updates to verify the results.  The query will need to explicitly define the user-defined template on which the security classifications will be modified.   In this example, the template is called $aTank.    

select gobj.tag_name 
       , da.attribute_name
       ,'True' as IsUDA,
       case when da.gobject_id <> da.owned_by_gobject_id then 'True'
             else'False' end as IsInherited
       , da.security_classification
       , da.gobject_id
       , da.package_id
       , da.mx_primitive_id
       , da.mx_attribute_id
from dynamic_attribute da inner join gobject gobj 
       on gobj.gobject_id = da.gobject_id 
       and gobj.checked_in_package_id = da.package_id
       and gobj.tag_name = '$aTank'
       and da.mx_primitive_id = 2
       and da.attribute_name NOT LIKE '%.Description'
order by da.attribute_name

 

2.  This query is broken down into two parts.   The 1st part declares variables and passes results to part two of the query which performs the update.    Because part-1 query passes results to part-2 query, both queries must be run as a single execution.    Alternately, you can run the part-1 query, record the results of the variables, and hard-code them into the part-2 query.    

--Part 1 Query

DECLARE @TemplateName as nvarchar(100)
DECLARE @ObjectID as int
DECLARE @PackageID as int
DECLARE @UpdateSC as int
DECLARE @CurrSC as int     
 
SET @TemplateName = '$aTank'

SET @UpdateSC = 1
SET @CurrSC = 5 

SELECT @ObjectID = da.gobject_id 
       , @CurrSC = da.security_classification
       , @PackageID = da.package_id
FROM dynamic_attribute da INNER JOIN 
       gobject gobj ON gobj.gobject_id = da.gobject_id 
             AND gobj.checked_in_package_id = da.package_id
             AND gobj.tag_name = @TemplateName
             AND da.mx_primitive_id = 2
             AND da.attribute_name NOT LIKE '%.Description'
ORDER BY da.attribute_name ASC;

 

--Part 2 Query

 
IF @UpdateSC <> @CurrSC
BEGIN
       -- Gets Data From Aove for Update Below
       UPDATE dbo.dynamic_attribute
       SET dynamic_attribute.security_classification = @UpdateSC
       WHERE dynamic_attribute.gobject_id = @ObjectID
       AND dynamic_attribute.package_id = @PackageID
       AND dynamic_attribute.mx_primitive_id = 2
       AND dynamic_attribute.security_classification = @currSC
       AND dynamic_attribute.mx_attribute_id IN
             (SELECT da.mx_attribute_id
                    FROM dynamic_attribute da INNER JOIN 
                    gobject gobj ON gobj.gobject_id = da.gobject_id 
                           AND gobj.checked_in_package_id = da.package_id
                           AND gobj.tag_name = @TemplateName
                           AND da.mx_primitive_id = 2
                           AND da.attribute_name NOT LIKE '%.Description'
             )
END
GO