Data Restriction in Security Group Functional or Technical Bulk SQL Scripts – IBM Maximo

To implement Data restrictions, we have to options:

1. Functional Side, front end, one by one
2. Technical Side, by using SQL editor, bulk loading

Customize Data Restriction for specific Security Group or groups according to your needs, for which data to be shown or access to security group.
Conditional Expressions are necessary to define and apply before Restrictions.

Navigation: Goto > Security > Security Group Application.

1. select the Security Group for which you want to set restrictions.
2. On Data Restriction Tab, Select Object Restriction Sub-tab
3. Click New Row Button
4. Select Object by using Lookup for which you want to restriction (e.g. KPIMAIN)
5. Select Application (e.g. KPILCONFIG)
6. Select Type of restriction (e.g. QUALIFIED)
7. Check Flag for Reevaluate
8. Select Condition you already created in Conditional Expression (e.g. myExpression)
9. Save your changes.

Note: There are 3 types of Data Restrictions, but this example is using Object Restriction which is the most common restriction type.

2^nd Method, Bulk Security Restriction loading, or by using SQL insert statement

For example, if we need to implement and condition or restriction for our every security group by using SQL script, then we just need to run the following query:

SELECT
'insert into securityrestrict s ' ||
'(SECURITYRESTRICTID ' ||
',GROUPNAME' ||
',APP' ||
',OBJECTNAME' ||
',REEVALUATE' ||
',RESTRICTION' ||
',CONDITIONNUM' ||
',TYPE' ||
',SRESTRICTNUM' ||
')' ||
'values' ||
'(' ||
'SECURITYRESTRICTSEQ.nextval' ||','||
q'#'#'||  M.groupname ||q'#'#'||','||
'''KPILCONFIG''' ||','||
'''KPIMAIN''' ||','||
1 ||','||
'''QUALIFIED''' ||','||
'''KPI_EXPRESSION''' ||','|| --TYPE YOUR CONDITIONAL EXPRESSION NAME
'''ROW''' ||','||
--'''12345'''||
'(SELECT MAX(SRESTRICTNUM)+1 FROM SECURITYRESTRICT WHERE SRESTRICTNUM not like ''%BMX%'')'||
')' as a
from MAXGROUP M
where 1=1
--and m.groupname in ('ALLSITES')
;

 

 The result of the select will be like this:

insert into securityrestrict s (SECURITYRESTRICTID ,GROUPNAME,APP,OBJECTNAME,REEVALUATE,RESTRICTION,CONDITIONNUM,TYPE,SRESTRICTNUM)values(SECURITYRESTRICTSEQ.nextval,'ALLSITES','KPILCONFIG','KPIMAIN',1,'QUALIFIED','KPI_EXPRESSION','ROW',(SELECT MAX(SRESTRICTNUM)+1 FROM SECURITYRESTRICT WHERE SRESTRICTNUM not like '%BMX%'))
insert into securityrestrict s (SECURITYRESTRICTID ,GROUPNAME,APP,OBJECTNAME,REEVALUATE,RESTRICTION,CONDITIONNUM,TYPE,SRESTRICTNUM)values(SECURITYRESTRICTSEQ.nextval,'BEDFORDSITE','KPILCONFIG','KPIMAIN',1,'QUALIFIED','KPI_EXPRESSION','ROW',(SELECT MAX(SRESTRICTNUM)+1 FROM SECURITYRESTRICT WHERE SRESTRICTNUM not like '%BMX%'))
insert into securityrestrict s (SECURITYRESTRICTID ,GROUPNAME,APP,OBJECTNAME,REEVALUATE,RESTRICTION,CONDITIONNUM,TYPE,SRESTRICTNUM)values(SECURITYRESTRICTSEQ.nextval,'CONTRACTMGR','KPILCONFIG','KPIMAIN',1,'QUALIFIED','KPI_EXPRESSION','ROW',(SELECT MAX(SRESTRICTNUM)+1 FROM SECURITYRESTRICT WHERE SRESTRICTNUM not like '%BMX%'))
insert into securityrestrict s (SECURITYRESTRICTID ,GROUPNAME,APP,OBJECTNAME,REEVALUATE,RESTRICTION,CONDITIONNUM,TYPE,SRESTRICTNUM)values(SECURITYRESTRICTSEQ.nextval,'DEFLTREG','KPILCONFIG','KPIMAIN',1,'QUALIFIED','KPI_EXPRESSION','ROW',(SELECT MAX(SRESTRICTNUM)+1 FROM SECURITYRESTRICT WHERE SRESTRICTNUM not like '%BMX%'))
insert into securityrestrict s (SECURITYRESTRICTID ,GROUPNAME,APP,OBJECTNAME,REEVALUATE,RESTRICTION,CONDITIONNUM,TYPE,SRESTRICTNUM)values(SECURITYRESTRICTSEQ.nextval,'EVERYONE','KPILCONFIG','KPIMAIN',1,'QUALIFIED','KPI_EXPRESSION','ROW',(SELECT MAX(SRESTRICTNUM)+1 FROM SECURITYRESTRICT WHERE SRESTRICTNUM not like '%BMX%'))
insert into securityrestrict s (SECURITYRESTRICTID ,GROUPNAME,APP,OBJECTNAME,REEVALUATE,RESTRICTION,CONDITIONNUM,TYPE,SRESTRICTNUM)values(SECURITYRESTRICTSEQ.nextval,'EXECUTIVES','KPILCONFIG','KPIMAIN',1,'QUALIFIED','KPI_EXPRESSION','ROW',(SELECT MAX(SRESTRICTNUM)+1 FROM SECURITYRESTRICT WHERE SRESTRICTNUM not like '%BMX%'))
insert into securityrestrict s (SECURITYRESTRICTID ,GROUPNAME,APP,OBJECTNAME,REEVALUATE,RESTRICTION,CONDITIONNUM,TYPE,SRESTRICTNUM)values(SECURITYRESTRICTSEQ.nextval,'FINANCE','KPILCONFIG','KPIMAIN',1,'QUALIFIED','KPI_EXPRESSION','ROW',(SELECT MAX(SRESTRICTNUM)+1 FROM SECURITYRESTRICT WHERE SRESTRICTNUM not like '%BMX%'))
insert into securityrestrict s (SECURITYRESTRICTID ,GROUPNAME,APP,OBJECTNAME,REEVALUATE,RESTRICTION,CONDITIONNUM,TYPE,SRESTRICTNUM)values(SECURITYRESTRICTSEQ.nextval,'HR','KPILCONFIG','KPIMAIN',1,'QUALIFIED','KPI_EXPRESSION','ROW',(SELECT MAX(SRESTRICTNUM)+1 FROM SECURITYRESTRICT WHERE SRESTRICTNUM not like '%BMX%'))

Now run these all INSERT statements in a single transaction against your database and you are done.