Re: Censoring SQL access -- ideas?

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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