RE: Puzzle - Hiding Data of some rows in a Table

It seems we'll be getting additional operational constraints as proposals
are made that otherwise solve the problem.

 

Will partitioning be allowed?

Will a one-time reorganization be allowed?

Will data movement when an account is promoted or demoted from VIP status be
allowed? (Or is the design allowed to not account for that possibility?)

 

If the questions are answered  Y,  Y, and (Y or Y), then you can probably
figure out how to do it using partitioning and views where the pruning will
be fast and smart for the view without the VIPs and you just add the
restricted rows back in with a union all to an aggregated view of the VIP
partition. Without partitioning you'd have to make the OLTP (insert, update,
and delete functions) know whether the data is in the VIP or non-VIP table,
and grant access to the general staff to only the aggregation views.

 

Even this presumes that there are at least two oracle schema, being the
"owner" running the programs that possibly change data and the general staff
not being able to access unaggregated VIP rows.

 

mwf

 

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of VIVEK_SHARMA
Sent: Monday, October 16, 2006 8:25 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Puzzle - Hiding Data of some rows in a Table

 

Folks

Seems I have NOT communicated properly in the previous e-mail

Puzzle 1 - For a Banking Application, for certain V.I.P. Bank Accounts e.g.
of the President of India (translating to respective rows in a set of
Tables), a general Staff Official should NOT be able to View the respective
Account info. But Bank-wide summation reports e.g. "Income & Expenditure
Report of the Bank as a whole" when fired by the "same" person should
consider the all the Data including those from the VIP Accounts too. 

 

General Staff may Number about 10,000 people spread across geographically
dispersed Branches of the Bank. 

 

All Staff currently connect thru a common Oracle user & data is owned by a
single (common) schema owner name.

 

There is NO column for staff_id (Corresponding to each Bank Staff person) in
the Tables currently which probably VPD needs, if I have interpreted VPD
correctly. 

 

NOTE - Application is OLTP in nature with very High performance needs e.g.
Current TPS (Trans/second) stands at a few thousand OLTP transactions. 

Approx a few Million OLTP Transactions (e.g. Debit or Credit transactions to
the Bank Accounts thru respective Staff & thru ATMs) are passed per day.

 

Hence Auditing does NOT seem possible.

 

Also Bank Staff Numbers keep changing with new inductions & attritions.

 

Qs 1 Should the same be achieved thru suitable Application Design & Coding
Or using some Database Feature is also an Option?

 

Qs 2 Is row-level encrypting available in 10.2 & applicable to this
situation?

 

Thanks indeed

**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient,
please notify the sender by e-mail and delete the original message. Further,
you are not to copy, disclose, or distribute this e-mail or its contents to
any other person and any such actions are unlawful. This e-mail may contain
viruses. Infosys has taken every reasonable precaution to minimize this
risk, but is not liable for any damage you may sustain as a result of any
virus in this e-mail. You should carry out your own virus checks before
opening the e-mail or attachment. Infosys reserves the right to monitor and
review the content of all messages sent to or from this e-mail address.
Messages sent to or from this e-mail address may be stored on the Infosys
e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

Other related posts: