Re: Business Objects with DB2 data

My guess Chuck's thinking of

AllowUser Region
FRED 1
GINGER 2

Then you can have a view

select * from creditfile inner join UserRegion
where allowuser = CURRENT_USER


Now when fred looks in this view, the only records he sees are his...

Charles

On Fri, Aug 16, 2013 at 10:26 AM, Buck Calabro <kc2hiz@xxxxxxxxx> wrote:

On 8/15/2013 7:12 PM, CRPence wrote:

On 02 Aug 2013 07:20, Buck Calabro wrote:

<<SNIP>> Data access security is hard coded in the programs
(users X, Y and Z can access region 3 but not region 1; all data
in the same tables. OS security doesn't help here. <<SNIP>>

I understand that the statements are based upon the implication that
an application already had been designed as the sole means to implement
access [possibly directly to the physical file], but...

I just want to clarify... that the OS security _can_ be used, with the
database via logical views of the data and authorities to both the
physical data file and the logical file, to accomplish exactly that
[certain users access to specific data], without having to instead use
an application to control access; i.e. all accesses, irrespective of the
application that is being used [while not adopting excessive
authorities], can be prevented from undesirable access to the data in
the files.

Given this one file:

Region Customer CreditScore
1 1 100
1 2 83
2 3 90

The application has internal restrictions such that Fred can read region
1 and Ginger can read region 2. For 'application', let's simplify to a
single inquiry program written in RPG. That RPG program looks vaguely
like this:

FCREDITRATE IF E K DISK

C USER LOOKUP TABUSR TABRGN 20
...
C CUST CHAIN CREDITRATER 80
C IF REGION <> TABRGN
C* issue error
C GOTO GETCUST
C ENDIF

The database is indeed capable of supporting many logical views with
criteria that limit the visible records to specified selections. So we
could have:

CREDITFILE1 Select region 1
CREDITFILE2 select region 2
...

Is this what you were thinking of? If so, is there a way to bring all
these logical views into a single F-specification like the original
code? Where a CHAIN to an unauthorised row yields an error I can trap?
When I first puzzled this problem out I thought of dynamic SQL: use the
user/region table to generate the SELECT for the proper table and off we
go. This would be easier to write and maintain than several
F-specifications and a pile of CASE...WHEN statements to choose the
proper file to CHAIN to, but it seems to me that it will still require
significant application changes in order to implement.

When I wrote the quoted post, I didn't see a way to use the database to
restrict a subset of rows by user without application changes.
Hopefully, I'll soon see the flaw in my thinking!

--buck