Not entirely sure it applies in your case but an interesting alternative
approach is described in here un der the section SQL VIEWS
A snip of the SQL in the document is:
CREATE VIEW secureDept
(division ,deptname ,lname ,fname ,salary )
SELECT division, deptname, lname, fname,
protectsalary(deptno, salary) AS salary
FROM staff a
INNER JOIN org b ON a.deptno = b.deptno
WHERE division IN
(SELECT division FROM org c
INNER JOIN staff d ON c.deptno = d.deptno
WHERE usrprf = SESSION_USER)
The SESSION_USER value is the interesting thing - it allows you to create a
generic view where what the user sees is restricted by dynamically using
their user profile as part of the select statement; in effect a kind of
logical authorization rather than the resource security we are used to,
although it is obviously tied to the security implementation through the use
of the user profile specification.
I haven't had a need to even try this yet, but I think it's pretty
interesting (and kinda cool).
[mailto:security400-bounces@xxxxxxxxxxxx] On Behalf Of Mike
Sent: Thursday, 18 December 2008 4:13 a.m.
To: Security Administration on the AS400 / iSeries
Subject: [Security400] Object Security Problem
We are creating a logical view of data for a user who needs access to only
data for their department. We are using an exit program with a switch user
to lock this person out of the main table. Normally we give this switch user
*READ access to the physical file and *USE access to the logical. This is
the first logical with a select in it. Do they need more access to the
physical file for a select? If so, what do they need?