http://in.compucrete.com/index.php?action=history&feed=atom&title=QueryGen_Case%3A_Generate_Security_Reports 2026-05-15T17:00:38Z Revision history for this page on the wiki MediaWiki 1.40.0 http://in.compucrete.com/index.php?title=QueryGen_Case:_Generate_Security_Reports&diff=532&oldid=prev 2023-07-14T13:00:35Z

<p>1 revision imported</p> <table style="background-color: #fff; color: #202122;" data-mw="interface"> <col class="diff-marker" /> <col class="diff-content" /> <col class="diff-marker" /> <col class="diff-content" /> <tr class="diff-title" lang="en"> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 13:00, 14 July 2023</td> </tr> <!-- diff cache key my_wiki:diff::1.12:old-531:rev-532 --> </table>

WikiAdmin http://in.compucrete.com/index.php?title=QueryGen_Case:_Generate_Security_Reports&diff=531&oldid=prev 2019-04-09T20:24:20Z

<p><span dir="auto"><span class="autocomment">User Role</span></span></p> <p><b>New page</b></p><div><br /> ==Setup The Source Queries==<br /> *'''lq1''' ksmenu (sqlite) -- Select * form ksmenu<br /> *'''lq2''' ccwsys SELECT * FROM cctuser<br /> *'''lq3''' ccwsys SELECT * FROM CCTrole<br /> *'''lq4''' ccw### SELECT * FROM cctsectl<br /> *'''lq5''' ccw### SELECT * FROM cctoptn where section_name='SYSTEM' AND OPTION_NAME LIKE '__SYSTEM'<br /> *'''lq6''' ccw### SELECT * FROM CCTUSERROLE<br /> <br /> ==security report==<br /> <br /> &lt;PRE&gt;<br /> 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<br /> INNER JOIN lq4 ON LQ1.PROG_ID=lq4.OPTION_NAME and lq4.section_name='CCMENU'<br /> LEFT OUTER JOIN LQ2 ON LQ4.USER_ID = LQ2.LOGIN_NAME <br /> LEFT OUTER JOIN LQ3 ON LQ4.USER_ID = LQ3.ROLE_ID<br /> union<br /> SELECT 2,lq4.GROUP_NAME,OPTION_NAME,LQ4.SECURITY_CLASS SECURITYLEVEL,lq4.user_id,lq2.FULL_NAME,lq3.DESCRIPTION ROLENAME,lq1.* FROM LQ1<br /> INNER JOIN lq4 ON LQ1.PROG_ID=lq4.SECTION_NAME and lq4.GROUP_name='ITEM'<br /> LEFT OUTER JOIN LQ2 ON LQ4.USER_ID = LQ2.LOGIN_NAME <br /> LEFT OUTER JOIN LQ3 ON LQ4.USER_ID = LQ3.ROLE_ID<br /> UNION<br /> SELECT 3,lq4.GROUP_NAME,OPTION_NAME,LQ4.SECURITY_CLASS SECURITYLEVEL,lq4.user_id,lq2.FULL_NAME,lq3.DESCRIPTION ROLENAME,lq1.* FROM LQ1<br /> INNER JOIN lq4 ON LQ1.PROG_ID=lq4.SECTION_NAME and lq4.GROUP_name='FUNCTION'<br /> LEFT OUTER JOIN LQ2 ON LQ4.USER_ID = LQ2.LOGIN_NAME <br /> LEFT OUTER JOIN LQ3 ON LQ4.USER_ID = LQ3.ROLE_ID<br /> UNION<br /> SELECT 4,lq4.GROUP_NAME,OPTION_NAME,LQ4.SECURITY_CLASS SECURITYLEVEL,lq4.user_id,lq2.FULL_NAME,lq3.DESCRIPTION ROLENAME,lq1.* FROM LQ1<br /> INNER JOIN lq4 ON LQ1.PROG_ID=lq4.SECTION_NAME and lq4.GROUP_name&lt;&gt;'ITEM' AND lq4.SECTION_NAME&lt;&gt;'CCMENU' AND lq4.GROUP_NAME&lt;&gt;'FUNCTION'<br /> LEFT OUTER JOIN LQ2 ON LQ4.USER_ID = LQ2.LOGIN_NAME <br /> LEFT OUTER JOIN LQ3 ON LQ4.USER_ID = LQ3.ROLE_ID<br /> &lt;/PRE&gt;<br /> 1:Menu Items<br /> 2:Program Level control (Edit/Add/Change)<br /> 3:Functions<br /> 4:Fields<br /> <br /> ==user report-- shows levels and optins==<br /> SELECT LQ2.LOGIN_NAME,LQ2.FULL_NAME,SECURITY_CLASS,lq5.option_name &quot;App&quot;,lq5.option_value ,LQ2.LOGIN_DATETIME &quot;Last Login&quot; FROM LQ2<br /> INNER JOIN LQ5 ON LQ5.USER_GROUP_ID=LQ2.LOGIN_NAME<br /> order by login_name,option_name<br /> <br /> <br /> <br /> ==Menu Item Report==<br /> &lt;pre&gt;<br /> SELECT lq1.*, LQ4.SECURITY_CLASS SECURITYLEVEL,lq4.user_id USERROLEID,lq2.FULL_NAME USERNAME,lq3.DESCRIPTION ROLENAME FROM LQ1 <br /> left outer JOIN lq4 ON LQ1.PROG_ID=lq4.OPTION_NAME and lq4.section_name='CCMENU' <br /> LEFT OUTER JOIN LQ2 ON LQ4.USER_ID = LQ2.LOGIN_NAME <br /> LEFT OUTER JOIN LQ3 ON LQ4.USER_ID = LQ3.ROLE_ID <br /> where COALESCE(app,'') in ('DI','AR') <br /> ORDER BY APP,MENU,PROG_NAME <br /> &lt;/pre&gt;<br /> <br /> This lists menu control all items with or without security<br /> <br /> ==User Role==<br /> (untested - needs new QG)<br /> &lt;pre&gt;<br /> SELECT LQ2.LOGIN_NAME,LQ2.FULL_NAME,lq6.role_id<br /> FROM LQ2<br /> INNER JOIN LQ6 ON LQ6.USER_ID=LQ2.LOGIN_NAME<br /> order by login_name,role_id<br /> &lt;/pre&gt;</div>

Intra>Chanson