QueryGen Case: Security Work

Security in QueryGen

Duplicate Elimination

This will eliminate duplicate items (which can be difficult to delete) and creates a good script that can replace security in another database (like when copying from one company to another).

Select:

select SECURITY_CLASS,USER_ID,SECTION_NAME,GROUP_NAME,OPTION_NAME,min(LAST_CHANGE_DATETIME) LAST_CHANGE_DATETIME,min(LAST_CHANGE_USER) LAST_CHANGE_USER
from cctsectl
group by SECTION_NAME,GROUP_NAME,OPTION_NAME,SECURITY_CLASS,USER_ID

Header:

DELETE FROM CCTSECTL;

Model:

INSERT INTO cctsectl
( SECURITY_CLASS, USER_ID, SECTION_NAME, GROUP_NAME, OPTION_NAME, LAST_CHANGE_DATETIME, LAST_CHANGE_USER )  VALUES 
( '{SECURITY_CLASS}', '{USER_ID}', '{SECTION_NAME}', '{GROUP_NAME}', '{OPTION_NAME}', '{LAST_CHANGE_DATETIME}', '{LAST_CHANGE_USER}'    );

Add ZZ Items to a Role

Example of creating a role items from a new "ZZ" security items:

Select:

SELECT * FROM cctsectl
where last_change_datetime between '7/26/19' and '7/29/19'

DELETE FROM CCTSECTL WHERE LAST_CHANGE_DATETIME='8/1/19';

INSERT INTO cctsectl
( SECURITY_CLASS, USER_ID, SECTION_NAME, GROUP_NAME, OPTION_NAME, LAST_CHANGE_DATETIME, LAST_CHANGE_USER )  VALUES 
( NULL, '~DISPATCH', '{SECTION_NAME}', '{GROUP_NAME}', '{OPTION_NAME}', '8/1/19', 'HIT'    );