RE: Column Access
- From: "Carol Bristow" <Carol.Bristow@xxxxxxxx>
- To: <BurtonL@xxxxxxxxxxx>
- Date: Fri, 11 Mar 2005 17:37:20 -0600
Actually, you can make this work with views, as long as your application
is logging into Oracle as the individual user.
Based on the login, dynamically create a view of the table WITH THE SAME
NAME AS THE TABLE. It will be created only for the user, but will
supercede the table when the select is done. Make sure that you do it
as create or replace, so that if an individual's role changes, they get
the new view. =20
In our case, we have SQL that is built dynamically from code stored in a
table in the database, to maintain consistency across various uses
within the application. Since trying to build some of the subsetting
criteria into a SQL statement can be a pain (multiple fields that may or
may not be part of the current reporting need), we store a standard SQL
statement that will bring back all rows, and, prior to executing it,
dynamically create the view that will take the place of the table, using
the user-selected criteria. =20
Carol Bristow
DPRA Inc.
1300 N 17th St Suite 950
Rosslyn, VA 22209
Work: 703-841-8025
Fax: 703-524-9415
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Burton, Laura
Sent: Friday, March 11, 2005 6:18 PM
To: Paul Drake
Cc: Oracle-L (E-mail)
Subject: RE: Column Access
Thanks for everyone's response. I should have noted that it is a
'canned' package that is being used to access the table so a view would
not work in this situation. I will look at the Fine Grained Access
Control that a few of you suggested. We are using 8i so hopefully this
will be an option.
Thank you again,
Laura
-----Original Message-----
From: Paul Drake [mailto:bdbafh@xxxxxxxxx]=3D20
Sent: Friday, March 11, 2005 3:20 PM
To: Burton, Laura
Cc: Oracle-L (E-mail)
Subject: Re: Column Access
Laura,
What you might be after is "Fine Grained Access Control" or "Virtual
Private Database".
http://www.oracle.com/technology/oramag/oracle/04-mar/o24tech_security.h
tml
This is available as an option on top of Enterprise Edition.=3D20
What version of the database software are you using?
Its easy to confuse Fine Grained Auditing (FGA) which is included in
Enterprise Edition, with Fine Grained Access Control (FGAC), but that
is Oracle terminology for you.
other papers are available at Pete Finnigan's site:
http://www.petefinnigan.com/orasec.htm
http://www.securityfocus.com/infocus/1743
hth.
Paul
On Fri, 11 Mar 2005 13:36:41 -0600, Burton, Laura <BurtonL@xxxxxxxxxxx>
wrote:
>=3D20
> I have read where you can grant access to a column to a user/role. I
> need to take it one step further. I need to restrict access to the
> column if it is for a certain group of people. i.e. I have
supervisors
> and mechanics. Everyone can look at the salary column, except that
> mechanics can only look at mechanics, but supervisors can look at
> everyone. Can this be done with grants? The alternative is two
> separate databases and that seems extreme. If not with a grant is
there
> a better way?
>=3D20
> =3D3D20
>=3D20
> Thank you,
>=3D20
> Laura
>=3D20
> --
> //www.freelists.org/webpage/oracle-l
>=3D20
--=3D20
#/etc/init.d/init.cssd stop
# f=3D3Dma, divide by 1, convert to moles.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
