| 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_10C8_01C34AFD.DA3E6790 Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
RE: upgrade to AIX 5yeah i think that might be 'viable'. its a big = kludge. but sometimes you have to deal with that.=20
send out page that alters the name of the executable, so each customer's =
executable has a different name
use program in v$session in a logon trigger to get the customer
hit a lookup table to see which schema to use
execute immediate to set that up.=20
thanks.=20
Ryan,
It's alter session set current_schema =3D name;
Tanel.
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
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.
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
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
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
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?
To stress, I cant change the application. Different group with = different skillsets. Any suggestions?=20
------=_NextPart_000_10C8_01C34AFD.DA3E6790 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><TITLE>RE: upgrade to AIX 5</TITLE>
<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>yeah i think that might be 'viable'. =
its a big=20
kludge. but sometimes you have to deal with that. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>send out page that alters the name =
of the=20
executable, so each customer's executable has a different =
name</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>use program in v$session in a logon =
trigger to get=20
the customer</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>hit a lookup table to see which schema =
to=20
use</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>execute immediate to set that up. =
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>thanks. </FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV=20
style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: =
black"><B>From:</B>=20
<A title=3Dtanel.poder.003_at_mail.ee =
href=3D"mailto:tanel.poder.003_at_mail.ee">Tanel=20
Poder</A> </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:09=20
PM</DIV>
<DIV style=3D"FONT: 10pt arial"><B>Subject:</B> Re: security without =
using=20
different usernames</DIV>
<DIV><BR></DIV> <DIV><FONT face=3DArial size=3D2>Ryan,</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>It's alter session set current_schema ==3D=20
name;</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Tanel.</FONT></DIV><BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV=20
<A title=3Drgaffuri_at_cox.net =
href=3D"mailto:rgaffuri_at_cox.net">Ryan</A> </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> Wednesday, July 16, =
2003 1:29=20
AM</DIV>
<DIV style=3D"FONT: 10pt arial"><B>Subject:</B> security without =
using=20
different usernames</DIV>
<DIV><BR></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=20
method. I have no power to change their gui. It rides on the = database. I=20
have to live with it. This is not a high enough transaction database = to=20
warrant seperate instances. </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>We have a variety of customers. = Each of them=20
has their own versions of data. However, the schema is exactly the = same.=20
These 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> </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> </DIV> <DIV><FONT face=3DArial size=3D2>Id like to find a way to get all = the clients in=20
the same instance with just different schemas and tablespaces. One = thing I=20
may have control over would be to slightly rename the executable. If = you=20
check v$session, in a client-server application the name of the = product=20
connecting to the database is recording. I can handle security based = off of=20
that. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>My question is what would be the =
best way? Cant=20
do synonyms for this since its the same login. I think I saw = somewhere that=20
there is a session based 'set' command where you can say use this = schema. I=20
think it was on asktom and in reference to a question about public = synonyms.=20
I cant find it. Anyone know it? </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Also is it viable to base a context = off of what=20
is 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> </DIV>
<DIV><FONT face=3DArial size=3D2>To stress, I cant change the =
Received on Tue Jul 15 2003 - 17:21:04 CDT
 |
 |