View source for 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==

<PRE>
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
</PRE>
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==
<pre>
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 
</pre>

This lists menu control all items with or without security

==User Role==
(untested - needs new QG)
<pre>
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
</pre>