QueryGen Case: Generate Security Reports

Setup The Source Queries

• lq1 ksmenu (sqlite) -- Select * form ksmenu
• lq2 ccwsys SELECT * FROM cctuser
• lq3 ccwsys SELECT * FROM CCTrole
• lq4 ccw### SELECT * FROM cctsectl
• lq5 ccw### SELECT * FROM cctoptn where section_name='SYSTEM' AND OPTION_NAME LIKE '__SYSTEM'
• lq6 ccw### SELECT * FROM CCTUSERROLE

security report

SELECT 1 SECTYPE,lq4.SECTION_NAME SECURITYTYPE,'' SubType,LQ4.SECURITY_CLASS SECURITYLEVEL,lq4.user_id USERROLEID,lq2.FULL_NAME USERNAME,lq3.DESCRIPTION ROLENAME,lq1.* FROM LQ1
INNER JOIN lq4 ON LQ1.PROG_ID=lq4.OPTION_NAME and lq4.section_name='CCMENU'
LEFT OUTER JOIN LQ2 ON  LQ4.USER_ID = LQ2.LOGIN_NAME 
LEFT OUTER JOIN LQ3 ON LQ4.USER_ID = LQ3.ROLE_ID
union
SELECT 2,lq4.GROUP_NAME,OPTION_NAME,LQ4.SECURITY_CLASS SECURITYLEVEL,lq4.user_id,lq2.FULL_NAME,lq3.DESCRIPTION ROLENAME,lq1.* FROM LQ1
INNER JOIN lq4 ON LQ1.PROG_ID=lq4.SECTION_NAME and lq4.GROUP_name='ITEM'
LEFT OUTER JOIN LQ2 ON  LQ4.USER_ID = LQ2.LOGIN_NAME 
LEFT OUTER JOIN LQ3 ON LQ4.USER_ID = LQ3.ROLE_ID
UNION
SELECT 3,lq4.GROUP_NAME,OPTION_NAME,LQ4.SECURITY_CLASS SECURITYLEVEL,lq4.user_id,lq2.FULL_NAME,lq3.DESCRIPTION ROLENAME,lq1.* FROM LQ1
INNER JOIN lq4 ON LQ1.PROG_ID=lq4.SECTION_NAME and lq4.GROUP_name='FUNCTION'
LEFT OUTER JOIN LQ2 ON  LQ4.USER_ID = LQ2.LOGIN_NAME 
LEFT OUTER JOIN LQ3 ON LQ4.USER_ID = LQ3.ROLE_ID
UNION
SELECT 4,lq4.GROUP_NAME,OPTION_NAME,LQ4.SECURITY_CLASS SECURITYLEVEL,lq4.user_id,lq2.FULL_NAME,lq3.DESCRIPTION ROLENAME,lq1.* FROM LQ1
INNER JOIN lq4 ON LQ1.PROG_ID=lq4.SECTION_NAME and lq4.GROUP_name<>'ITEM' AND lq4.SECTION_NAME<>'CCMENU' AND lq4.GROUP_NAME<>'FUNCTION'
LEFT OUTER JOIN LQ2 ON  LQ4.USER_ID = LQ2.LOGIN_NAME 
LEFT OUTER JOIN LQ3 ON LQ4.USER_ID = LQ3.ROLE_ID

1:Menu Items 2:Program Level control (Edit/Add/Change) 3:Functions 4:Fields

user report-- shows levels and optins

SELECT LQ2.LOGIN_NAME,LQ2.FULL_NAME,SECURITY_CLASS,lq5.option_name "App",lq5.option_value ,LQ2.LOGIN_DATETIME "Last Login" FROM LQ2 INNER JOIN LQ5 ON LQ5.USER_GROUP_ID=LQ2.LOGIN_NAME order by login_name,option_name

Menu Item Report

SELECT lq1.*, LQ4.SECURITY_CLASS SECURITYLEVEL,lq4.user_id USERROLEID,lq2.FULL_NAME USERNAME,lq3.DESCRIPTION ROLENAME FROM LQ1 
left outer JOIN lq4 ON LQ1.PROG_ID=lq4.OPTION_NAME and lq4.section_name='CCMENU' 
LEFT OUTER JOIN LQ2 ON LQ4.USER_ID = LQ2.LOGIN_NAME 
LEFT OUTER JOIN LQ3 ON LQ4.USER_ID = LQ3.ROLE_ID 
where COALESCE(app,'') in ('DI','AR') 
ORDER BY APP,MENU,PROG_NAME 

This lists menu control all items with or without security

User Role

(untested - needs new QG)

SELECT LQ2.LOGIN_NAME,LQ2.FULL_NAME,lq6.role_id
 FROM LQ2
INNER JOIN LQ6 ON LQ6.USER_ID=LQ2.LOGIN_NAME
order by login_name,role_id