Re: Censoring SQL access -- ideas?

On Wed, Mar 16, 2011 at 2:23 PM, James Lampert <jamesl@xxxxxxxxxxxxxxxxx> wrote:

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)?

Sure, a join would be a prefered option if you can do it easily enough...

Another option as stated in my original post and that Rob gave an
example of would be using a UDF

create view myview as
select * from mytable
where SecurityCheck(accountnm, user) = '1';

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.

Doubtful...how are _YOU_ going to stop a user from installing
something on his PC?

Personally, I'd ship your product with the PF's PUBLIC *EXCLUDE and
have views available for users to access the data. Now your product
is secure by default. If your customer chooses to unsecure it by
making the table PUBLIC *USE, that's on him.

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?

it's not automatic, but you can rename the fields when you create the view
create view myview
as select shrtfld as my_long_field_name
from my table

Charles