Thursday, January 24, 2008

PS Queries / a user can not see / Security

This query was developed to capture a listing of queries that a user does not have access to.

SELECT DISTINCT A.OPRID, A.QRYNAME, A.DESCR, B.RECNAME
FROM PSQRYDEFN A, PSQRYRECORD B
WHERE A.OPRID = B.OPRID AND
A.QRYNAME = B.QRYNAME AND
A.QRYTYPE = 1
and not exists (select 'x' from PSROLECLASS D , PSROLEUSER E , PSQRYACCLSTRECS F
WHERE D.ROLENAME = E.ROLENAME AND
F.CLASSID = D.CLASSID AND
F.VERSION = ( SELECT VERSION FROM PSVERSION D WHERE D.OBJECTTYPENAME = 'QAL') AND
E.ROLEUSER = A.OPRID and
f.recname = b.recname)
and a.oprid = 'Place OPRID here'

No comments: