Re: Censoring SQL access -- ideas?

[James Lampert <jamesl@xxxxxxxxxxxxxxxxx>]

CRPence wrote:

>    If the parameter is declared as CHAR(2), then try:
>       SELECT * FROM TABLE(STATECUST( cast('CA' as char(2)) )) FOO

BINGIE!!  THAT WORKED!!

Thanks.

And to those who asked why (given my penchant for backward 
compatibility) I'm pursuing UDTFs instead of just VIEWs for the "new 
data access layer" for use with third-party reporting tools, well, (1) 
the "new data access layer" is in addition to what we already have going 
through our proprietary protocols and client, and something the pre-V6 
customers can certainly get along without, since this new feature also 
uses Integrated Application Server, and (2) I don't know if some of the 
censorship requirements can even be implemented with a purely VIEW-based 
"new data access layer."

To wit:

Suppose we have a database of customers, and a three-level hierarchy of 
users (sales reps, supervisors, and administrators). Customer records 
have a field in them, which may be blank (indicating that the customer 
is unassigned), or may contain the application enrollment number (NOT 
the user profile name!) of the assigned sales rep. Sales reps may access 
customers who are assigned to them, and they may also access unassigned 
customers, but they may not access customers assigned to other sales 
reps. Supervisors may access customers assigned to any of their sales 
reps, and unassigned customers, but they may not access customers 
assigned to sales reps under other supervisors. Administrators may 
access any customer in the system.

We've implemented this with several customers, using exit programs, 
native record-level access, and our proprietary protocol and client. The 
question is whether we can accomplish the equivalent in SQL, and how.

--
JHHL