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