One way you could do this without impacting your existing code too badly is
as follows:
- Add a new column to the tables that you need to check on (e.g.
SECURITY_LEVEL)
- For each of your tables, create a view that selects all the columns from
the table with a where clause that calls a packaged function. For instance:
CREATE VIEW my_tab_v AS SELECT * FROM my_tab
WHERE sec.check_access(SECURITY_LEVEL) = 'Y'
- You could actually name the views the same as the table (by putting them
in a separate schema) to avoid having to make any changes to your code
- So what does the package do?
In our case users could be assigned to one or more roles that gave them
access rights.
The package had some instantiation code (run the 1st time the package is
referenced) that read in all the users access rights and cached them in a
pl/sql table.
The "check access" function referenced in the view compares the tag of the
row SECURITY_LEVEL with the users access rights in the table. What this
check does is dependant on your requirements. It could simply check that
the user has a numeric access right >= that of the row. To make the model
more sophisticated the view could pass the table name to the function to
allow access rights to vary per table... The main advantages of this
solution are that it is simple, cheap (you don't have to buy a 3rd party
product). It is relatively efficient since apart form the first time the
package is referenced there is no database access. The biggest advantage is
that you can make it 100% transparent to the application.
I experimented with using the WITH CHECK OPTION on the view creation so
that users could insert rows that the view wouldn't allow them to select
but from what I can remember I had to put triggers on the table to control
DML statements.
Hope this helps
--
Ian Stevenson
co author of "Oracle Design" published by O'Reilly and associates
http://www.westmail.demon.co.uk
Steven Whatley <swhatley_at_blkbox.com> wrote in article
<5nh3ui$cvd_at_news.blkbox.com>...
> Hi all,
>
> My company is being asked to look into setting up row lever security on
> our databse. I admit, I dread the thought of having to add this level of
> security. My understanding is that it is a big performance hit. We will
> be needing to add this security to 20 to 30 tables out of 100 tables.
> Some of these tables contain 100,000s of rows. How bad is this
> performance hit? Also, how does it affect existing code and future
> developement?
>
> We are checking into a product called DB Fortify from Three Tier. Is
> this a good product?
>
> Any info will be appreciated.
>
> Thanks,
> Steven
> http://www.blkbox.com/~swhatley/
> _|_ | _|_ "I am the way and the truth and
> Steven Whatley | --|-- | the life. No one comes to the
> swhatley_at_blkbox.com | | | Father except through me."
> Houston, Texas | -- Jesus Christ (John 14:6 NIV)
> |
>
Received on Tue Jun 10 1997 - 00:00:00 CDT
