Secure lookups

Standard lookups show the lookup result; you cannot open the record if you lack access.

Secure lookups do not return data if you have no access to the record.

Optiva is shipped with example Secure Lookups for each data object. Any lookup can be converted to Secure.

Query looks at object ownership and security, and at Access Control List permissions.

Placeholder	Description	Returns
[%%GROUPS]	A list of groups to which the user belongs.	Show the list of groups that are defined on the user profile.
[%%ROLES]	A list of roles to which the user belongs.	Show the list of roles that are defined on the user profile.
[%USER]	The user.	Show the current user code.

Here is the query. The text in italic are key additions to making any lookup a Secure Lookup. Include other columns and conditions as required.

SELECT DISTINCT P.PROJECT_CODE, P.DESCRIPTION 
FROM FSPROJECT P
LEFT OUTER JOIN FSPROJECTACL A ON (A.PROJECT_CODE = P.PROJECT_CODE)
WHERE P.PROJECT_CODE >= [%1] AND P.TYPE_IND = 0 AND P.LOGICAL_DELETE = 0
AND
(
    ((P.OWNER_CODE = [%USER] AND P.OWNER_SECURITY >= 3)
   OR (P.GROUP_CODE IN ([%%GROUPS]) AND P.GROUP_SECURITY >= 3)
   OR (P.ROLE_SECURITY >= 3 AND 
   EXISTS(SELECT * FROM FSOBJECTROLESECURITY RS 
      WHERE RS.SET_TYPE = 'PROJECT' 
      AND RS.ROLE_CODE IN ([%%ROLES]) 
      AND RS.SECURITY_IND >= 3)))
 OR
   ((A.KEY_CODE = [%USER] AND A.TYPE_IND = 0 AND A.ACL_SECURITY >= 3)
   OR (A.KEY_CODE IN ([%%ROLES]) AND A.TYPE_IND = 1 AND A.ACL_SECURITY >= 
3)
   OR (A.KEY_CODE IN ([%%GROUPS]) AND A.TYPE_IND = 2 AND A.ACL_SECURITY >= 
3))
)
ORDER BY P.PROJECT_CODE