| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: security without using different usernames
This is a multi-part message in MIME format.
------=_NextPart_000_10DE_01C34B01.4E7A8A50 Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
partitioning is not an option. one of our ingestion methods to bring = data to the production database is transportable tablespaces. It wont = work. So we cant just cram everything into the same table. We ingest = data from different 'staging' servers. that have differing logic. this = data is then transportable to the production servers. We also use = imports and sqlloaders as well. depends on how we get the data. Some = data we get from the client, some we get from other groups in the = company. =20
so FGAC is out and I know how to use it. =20
From: JApplewhite_at_austin.isd.tenet.edu=20
To: Multiple recipients of list ORACLE-L=20
Sent: Tuesday, July 15, 2003 7:24 PM
Subject: Re: security without using different usernames
Ryan,=20
To make a particular schema the focus for a session:=20
Alter Session Set Current_Schema =3D <TheSchema> ;=20
Better yet, instead of proliferating the same schema for each client, = convert the tables to partitioned tables in a single schema, with each = partiion being for a specific client. That way you can add and remove = clients by adding and dropping partitions. Each client's partitions = could even be in tablespace(s) on separate drives to isolate their I/O = from others. If all the schemas will always be identical, then = partitioned tables are the way to go. If you'll customize the app and = data structures for some clients, then you'll have to stick with = individual schemas.=20
If you use partitioned tables, use FGAC (Fine-Grained Access Control, = AKA Row-Level Security) and Application Context to control security - = see the Concepts doc and Application Developers Guide for details. = Using those features of Oracle, you could limit each client to only = their own partitions.=20
I've used it in the past and It works very well. In fact, I'm in the = last stages of designing and implementing an FGAC solution by which the = 162 Campus-level Student Information databases in our District will be = consolidated into a single Oracle database, with users at each Campus = only seeing the tables for their School. We already have all 53,000 = tables and 75,000 indexes (those are not typos!) of this 3rd Party App = in an Oracle database. I'm just adding the security piece to keep them = out of each other's business (or, "bidness", as we say in Texas). = ;-)=20
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
JApplewhite_at_austin.isd.tenet.edu
"Ryan" <rgaffuri_at_cox.net>=20
Sent by: ml-errors_at_fatcity.com=20
07/15/2003 05:29 PM=20
Please respond to ORACLE-L=20
=20
To: Multiple recipients of list ORACLE-L =
<ORACLE-L_at_fatcity.com>=20
cc: =20
Subject: security without using different =
usernames=20
I know this is terrible design, but the GUI was created by a software = engineering group that is seperate from the database group. Its not = scalable. So Im trying to come up with a more scalable method. I have no = power to change their gui. It rides on the database. I have to live with = it. This is not a high enough transaction database to warrant seperate = instances.=20
=20
We have a variety of customers. Each of them has their own versions of =
data. However, the schema is exactly the same. These tables can get =
huge, so we dont want to throw them all into the same schema.=20
=20
Right now, due to the fact that the GUI has a series of logins that =
are the same across clients, each client has its own instance. This isnt =
very scalable as we get more business. We have to create another =
instance and ingest data to it.=20
=20
Id like to find a way to get all the clients in the same instance with =
just different schemas and tablespaces. One thing I may have control =
over would be to slightly rename the executable. If you check v$session, =
in a client-server application the name of the product connecting to the =
database is recording. I can handle security based off of that.=20
=20
My question is what would be the best way? Cant do synonyms for this =
since its the same login. I think I saw somewhere that there is a =
session based 'set' command where you can say use this schema. I think =
it was on asktom and in reference to a question about public synonyms. I =
cant find it. Anyone know it?=20
=20
Also is it viable to base a context off of what is in v$sesion with a =
logon trigger? How would I 'redirect' all queries to a specific schema?=20
=20
To stress, I cant change the application. Different group with =
different skillsets. Any suggestions?=20
------=_NextPart_000_10DE_01C34B01.4E7A8A50 Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>partitioning is not an option. one of =
our ingestion=20
methods to bring data to the production database is transportable =
tablespaces.=20
It wont work. So we cant just cram everything into the same table. We =
ingest=20
data from different 'staging' servers. that have differing logic. this =
data is=20
then transportable to the production servers. We also use imports and =
sqlloaders=20
as well. depends on how we get the data. Some data we get from the =
client, some=20
we get from other groups in the company. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>so FGAC is out and I know how to =
use it.=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT><FONT face=3DArial =
size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<BLOCKQUOTE=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV=20
style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: =
black"><B>From:</B>=20
<A title=3DJApplewhite_at_austin.isd.tenet.edu=20
=
href=3D"mailto:JApplewhite_at_austin.isd.tenet.edu">JApplewhite_at_austin.isd.t=
enet.edu</A>=20
</DIV>
<DIV style=3D"FONT: 10pt arial"><B>To:</B> <A =
title=3DORACLE-L_at_fatcity.com=20
href=3D"mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list =
ORACLE-L</A>=20
</DIV>
<DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Tuesday, July 15, 2003 =
7:24=20
PM</DIV>
<DIV style=3D"FONT: 10pt arial"><B>Subject:</B> Re: security without =
using=20
different usernames</DIV>
<DIV><FONT face=3DArial size=3D2></FONT><BR></DIV><BR><FONT =
face=3Dsans-serif=20
size=3D2>Ryan,</FONT> <BR><BR><FONT face=3Dsans-serif size=3D2>To make =
a particular=20
schema the focus for a session:</FONT> <BR><BR><FONT face=3Dsans-serif =
size=3D2>Alter Session Set Current_Schema =3D <TheSchema> ; =
</FONT><BR><FONT=20
face=3Dsans-serif size=3D2><BR>Better yet, instead of proliferating =
the same=20
schema for each client, convert the tables to partitioned tables in a =
single=20
schema, with each partiion being for a specific client. That way =
you can=20
add and remove clients by adding and dropping partitions. Each =
client's=20
partitions could even be in tablespace(s) on separate drives to =
isolate their=20
I/O from others. If all the schemas will always be identical, =
then=20
partitioned tables are the way to go. If you'll customize the =
app and=20
data structures for some clients, then you'll have to stick with =
individual=20
schemas.</FONT> <BR><BR><FONT face=3Dsans-serif size=3D2>If you use =
partitioned=20
tables, use FGAC (Fine-Grained Access Control, AKA Row-Level Security) =
and=20
Application Context to control security - see the Concepts doc and =
Application=20
Developers Guide for details. Using those features of Oracle, =
you could=20
limit each client to only their own partitions.</FONT> <BR><BR><FONT=20
face=3Dsans-serif size=3D2>I've used it in the past and It works very =
well.=20
In fact, I'm in the last stages of designing and implementing an =
FGAC=20
solution by which the 162 Campus-level Student Information databases =
in our=20
District will be consolidated into a single Oracle database, with =
users at=20
each Campus only seeing the tables for their School. We already =
have all=20
53,000 tables and 75,000 indexes (those are not typos!) of this 3rd =
Party App=20
in an Oracle database. I'm just adding the security piece to =
keep them=20
out of each other's business (or, "bidness", as we say in Texas). =
=20
;-)</FONT> <BR><FONT face=3Dsans-serif size=3D2><BR>Jack C.=20
Applewhite<BR>Database Administrator<BR>Austin Independent School=20
District<BR>Austin, Texas<BR>512.414.9715 (wk)<BR>512.935.5929=20
(pager)<BR>JApplewhite_at_austin.isd.tenet.edu<BR></FONT><BR><BR><BR>
<TABLE width=3D"100%">
<TBODY>
<TR vAlign=3Dtop>
<TD>
<TD><FONT face=3Dsans-serif size=3D1><B>"Ryan"=20
<rgaffuri_at_cox.net></B></FONT> <BR><FONT face=3Dsans-serif=20
size=3D1>Sent by: ml-errors_at_fatcity.com</FONT>=20
<P><FONT face=3Dsans-serif size=3D1>07/15/2003 05:29 PM</FONT> =
<BR><FONT=20
face=3Dsans-serif size=3D1>Please respond to ORACLE-L</FONT> =
<BR></P>
<TD><FONT face=3DArial size=3D1> =
</FONT><BR><FONT=20
face=3Dsans-serif size=3D1> To: =
=20
Multiple recipients of list ORACLE-L=20
<ORACLE-L_at_fatcity.com></FONT> <BR><FONT face=3Dsans-serif=20
size=3D1> cc: =20
</FONT> <BR><FONT face=3Dsans-serif size=3D1> =
=20
Subject: security without =
using=20
different usernames</FONT></TR></TBODY></TABLE><BR><BR><BR><FONT =
face=3DArial=20
trigger? How would I 'redirect' all queries to a specific =
schema?</FONT>=20
<BR><FONT face=3D"Times New Roman" size=3D3> </FONT> <BR><FONT =
Received on Tue Jul 15 2003 - 17:45:48 CDT
 |
 |