Re: Censoring SQL access -- ideas?

[James Lampert <jamesl@xxxxxxxxxxxxxxxxx>]

Thanks. I just set up a very simple model of the "user-sensitive view" 
concept, with a PF, "FOO," defined thusly:
> A          R FOO_REC            
> A            ACCTNAME      50A  
> A            ARBAL         15S 2
> A            SALESUSER     10A  

and a view defined on it thusly:
> create view mercury/bar               
>   as SELECT acctname, arbal, salesuser
>        from mercury/foo               
>        where salesuser = USER         

("Mercury" is a catch-all library for utilities and experiments on the 
AS/400 of that same name, that don't have some other home library)

It works beautifully, whether I look at it from QuestView (which uses 
native record-level access, rather than SQL), or from Squirrel SQL on my 
WinDoze box.

But there's another wrinkle, at least with the existing customer 
installations.

It seems that in most cases, the field that indicates which sales rep 
owns an account contains something other than the actual user profile 
name. It might be the user's enrollment number in the application (5 
decimal digits in either a zoned or alphanumeric field), or it might be 
the user's "Sales Rep ID" in some native application we've tied into.

How would that work? A join with the user enrollment file (or with 
whatever we use to cross-reference sales rep IDs in the native system)?

****

I don't know enough about the third party reporting tools being 
contemplated (in fact, I don't know SCAT about ANY third party reporting 
tools), but I'm thinking that we can probably control access to the 
reporting tools in such a way that we probably wouldn't need to lock 
users out of the PFs.

****

And a question from the "peanut gallery": Is there a way to 
automatically set up a view that would not only censor the data, but 
change the field names to something based on the field text (or the 
column headings) in the PF?

--
JHHL