Re: Censoring SQL access -- ideas?

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