Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: security without using different usernames

RE: security without using different usernames

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Tue, 15 Jul 2003 17:59:15 -0400
Message-Id: <25929.337896@fatcity.com>


This is a multi-part message in MIME format.

------_=_NextPart_001_01C34B1C.55543D9C
Content-Type: text/plain;

        charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Ryan,
=20

    What would be much better is to create the single schema and = partition the tables so that each customer's data lands into it's own = partition. As for this other group, make some friends. It's a lot = easier to get your problems and concerns addressed if the people your = talking to are on a friendly basis with you. You can also bring up the = problems of scaling to your management in terms of dollars needed for = additional servers, memory, hard disk, and software. For some reason = that is something pointy headed managers seem to understand, especially = when you start talking about Oracle licenses at $40K per CPU. =20

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA=20

-----Original Message-----
From: Ryan [mailto:rgaffuri_at_cox.net]
Sent: Tuesday, July 15, 2003 6:29 PM
To: Multiple recipients of list ORACLE-L Subject: security without using different usernames

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
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
To stress, I cant change the application. Different group with different = skillsets. Any suggestions?=20

------_=_NextPart_001_01C34B1C.55543D9C
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=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<TITLE>RE: upgrade to AIX 5</TITLE>

<META content=3D"MSHTML 6.00.2800.1141" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><SPAN class=3D887315421-15072003><FONT face=3DArial color=3D#0000ff =

size=3D2>Ryan,</FONT></SPAN></DIV>
<DIV><SPAN class=3D887315421-15072003><FONT face=3DArial color=3D#0000ff =

size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D887315421-15072003>&nbsp;&nbsp;&nbsp; <FONT =
face=3DArial=20
color=3D#0000ff size=3D2>What would be much better is to create the = single schema=20
and partition the tables so that each customer's data lands into it's = own=20
partition.&nbsp; As for this other group, make some friends.&nbsp; It's = a lot=20
easier to get your problems and concerns addressed if the people your = talking to=20
are on a friendly basis with you.&nbsp; You can also bring up the = problems of=20
scaling to your management in terms of dollars needed for additional = servers,=20
memory, hard disk, and software.&nbsp; For some reason that is something = pointy=20
headed managers seem to understand, especially when you start talking = about=20
Oracle licenses at $40K per CPU.</FONT></SPAN></DIV>
<DIV>&nbsp;</DIV>
<P><FONT size=3D2>Dick Goulet<BR>Senior Oracle DBA<BR>Oracle Certified =
8i DBA=20
</FONT></P>
<DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT =
face=3DTahoma=20
size=3D2>-----Original Message-----<BR><B>From:</B> Ryan=20 [mailto:rgaffuri_at_cox.net]<BR><B>Sent:</B> Tuesday, July 15, 2003 6:29=20 PM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B> =

security without using different usernames<BR><BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>I know this is terrible design, but the =
GUI was=20
created by a software engineering group that is seperate from the = database=20
group. Its not scalable. So Im trying to come up with a more scalable = method. I=20
have no power to change their gui. It rides on the database. I have to = live with=20
it. This is not a high enough transaction database to warrant seperate=20 instances. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>We have a variety of customers. Each of =
them has=20
their own versions of data. However, the schema is exactly the same. = These=20
tables can get huge, so we dont want to throw them all into the same=20 schema.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Right now, due to the fact that the GUI =
has a=20
series of logins that are the same across clients, each client has its = own=20
instance. This isnt very scalable as we get more business. We have to = create=20
another instance and ingest data to it. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Id like to find a way to get all the =
clients in the=20
same instance with just different schemas and tablespaces. One thing I = may have=20
control over would be to slightly rename the executable. If you check = v$session,=20
in a client-server application the name of the product connecting to the =

database is recording. I can handle security based off of that. =
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>My question is what would be the best =
way? Cant do=20
synonyms for this since its the same login. I think I saw somewhere that = there=20
is a session based 'set' command where you can say use this schema. I = think it=20
was on asktom and in reference to a question about public synonyms. I = cant find=20

it. Anyone know it? </FONT></DIV>

<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Also is it viable to base a context off =
of what is=20
in v$sesion with a logon trigger? How would I 'redirect' all queries to = a=20
specific schema?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>To stress, I cant change the =
Received on Tue Jul 15 2003 - 16:59:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US